mysqldump备份脚本
2017-02-27 本文已影响0人
jsdjl
- 备份全库
mysqldump -uroot -p --single-transaction --flush-logs -R -E --all-databases --set-gtid-purged=off --master-data=2 >/root/backup-`date +%F-%H-%M-%S`.sql
- 备份单库
mysqldump -uroot -p --single-transaction --flush-logs -R -E --databases DB_NAME --set-gtid-purged=off --master-data=2 >/root/backup-`date +%F-%H-%M-%S`.sql
- 备份多库
mysqldump -uroot -p --single-transaction --flush-logs -R -E -B DB1 DB2 --set-gtid-purged=off --master-data=2 >/root/backup-`date +%F-%H-%M-%S`.sql
- 只备份单库下的全部表结构：
mysqldump --databases DB_NAME --no-data --single-transaction --flush-logs --set-gtid-purged=off > /root/backup-`date +%F-%H-%M-%S`.sql
- 单库下只备份某些表结构：
mysqldump DB_NAME --no-data table1 table2 --single-transaction --flush-logs --skip-dump-date --set-gtid-purged=off --master-data=2 > /root/backup-`date +%F-%H-%M-%S`.sql
- 单库下的只备份数据(不带表结构)：
mysqldump DB_NAME -t --single-transaction --flush-logs --set-gtid-purged=off --master-data=2 > /root/backup-`date +%F-%H-%M-%S`.sql
- 单库下的只备份某些表的数据(不带表结构)：
mysqldump DB_NAME -t table1 table2 --single-transaction --flush-logs --set-gtid-purged=off --master-data=2 > /root/backup-`date +%F-%H-%M-%S`.sql
- 备份单库下的某些表(带表结构):
mysqldump DB_NAME table1 table2 --single-transaction --flush-logs --set-gtid-purged=off --master-data=2 > /root/backup-`date +%F-%H-%M-%S`.sql
- 抽出为csv格式：
select * from t_user into outfile '/tmp/t_user.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';
- 导入：
load data infile '/tmp/t_user.csv' into table table_name fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';
1.select version();--版本需求
2.show variables like 'gtid%'; --set-gtid-purge=off
3.新库set global log_bin_trust_function_creators=1;
4.倒入单库 mysql -uroot -p DB_NAME < /root/BACKUP.sql
