پشتیبان گرفتن از دیتابیس ها
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# to backup schema only use -d or --no-data # backup all databases $ mysqldump -uroot -proot --all-databases > backup.sql # schema only $ mysqldump -uroot -proot -d --all-databases > backup.sql # backup specific database $ mysqldump -uroot -proot my_database > backup.sql # backup specific tables of database $ mysqldump -uroot -proot my_database my_table1 my_table2 > backup.sql # better backup $ mysqldump -uroot --proot --single-transaction --routines --triggers --all-databases > backup.sql # compress backup $ mysqldump -uroot -proot --all-databases | gzip > backup.gz |
چک کردن سلامت دیتابیس ها
|
1 2 |
$ mysqlcheck --verbose -h HOST -u USERNAME -p --auto-repair --optimize --all-databases # you will be prompted to enter password |
دستورات مهم
|
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 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
# 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 # add auto increment to a column ALTER TABLE mytable CHANGE id itemid INT(10) AUTO_INCREMENT PRIMARY KEY; # 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'; # set sql_mode in current mysql session SET sql_mode = ''; # or set it to all mysql session SET GLOBAL sql_mode = ''; # show mysql configurations SHOW VARIABLES: SHOW VARIABLES LIKE '%timeout%'; # show current activities SHOW PROCESSLIST; # or more verbose SHOW FULL PROCESSLIST; |