پشتیبان گرفتن از دیتابیس ها
|
1 2 3 4 |
$ mysqldump -uroot -proot --all-databases > backup.sql $ mysqldump -uroot -proot my_database > backup.sql # compress backup $ mysqldump -uroot -proot my_database | gzip > backup.gz |
چک کردن سلامت دیتابیس ها
|
1 |
$ mysqlcheck --verbose -h host -u root -p --auto-repair --optimize --all-databases |
دستورات مهم
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
# Load a csv file to table LOAD DATA INFILE '/home/input.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; # Rename table RENAME TABLE mytable TO _mytable # Set auto increment number to 1 ALTER TABLE mytable AUTO_INCREMENT=1 # Add index to column ALTER TABLE mytable ADD INDEX inedex_name(field); # Add unique index to column ALTER TABLE mytable ADD UNIQUE INDEX index_name(field); # Remove index ALTER TABLE mytable DROP INDEX index_name; # Insert data from another table INSERT INTO mytable(id, name) SELECT id,name FROM y; # Create database with character set CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # Change database character set ALTER DATABASE mydatabase CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; # See what's going on with table SHOW TABLE STATUS WHERE name LIKE 'x'; |