第48章 MySQL主流备份工具之mysqldump详解 - xiaoboluo768/qianjinliangfang GitHub Wiki
48.3.8 输出格式化选项
# 以备份world库下的City表为例
[root@localhost ~]# mysqldump --xml -u root world City
......
48.4.1 完全备份与恢复
mysql> select * from test;
......
6 rows in set (0.00 sec)
mysql> insert into test(name) values('test11'),('test12'),('test13'),('test14');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test;
......
10 rows in set (0.00 sec)
[root@localhost ~]# mkdir /data/backup/mysqldump -p
[root@localhost ~]# cd /data/backup/mysqldump/
[root@localhost mysqldump]# mysqldump -h 10.10.30.241 -uadmin -ppassword --single-transaction\ --master-data=2 --triggers --routines --events --all-databases > backup_`date\ +%F_%H_%M_%S`.sql
[root@localhost mysqldump]# ls -lh
......
# binlog pos位置用于搭建基于binlog pos的复制（不限定是否启用了GTID）
[root@localhost mysqldump]# head -100 backup_2017-05-26_17_43_32.sql |grep -i change
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=57572231;
# GTID位置用于搭建基于GTID的复制
[root@localhost mysqldump]# head -100 backup_2017-05-26_17_43_32.sql | grep –i\ 'GLOBAL.GTID_PURGED'
SET @@GLOBAL.GTID_PURGED='06188301-b333-11e8-bdfe-0025905b06da:1-28';
[root@localhost mysqldump]# mysql --defaults-file=/home/mysql/conf/my1.cnf -uadmin\ -ppassword -e "reset master;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysqldump]# mysql --defaults-file=/home/mysql/conf/my1.cnf -uadmin\ -ppassword < backup_2017-05-26_17_43_32.sql
mysql> select * from test;
......
10 rows in set (0.00 sec)
48.4.3 搭建主从复制架构
[root@localhost ~]# mkdir /data/backup/mysqldump -p
[root@localhost ~]# cd /data/backup/mysqldump/
[root@localhost mysqldump]# mysqldump -h 10.10.30.241 -uadmin -ppassword –single\ -transaction --master-data=2 --triggers --routines --events --all-databases > backup_`date\ +%F_%H_%M_%S`.sql
[root@localhost mysqldump]# ls -lh
......
[root@localhost mysqldump]# head -100 backup_2017-05-27_00_18_25.sql |grep -i change
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=13725925;
[root@localhost mysqldump]# mysql --defaults-file=/home/mysql/conf/my1.cnf –uadmin\ -ppassword -e "reset master;"
[root@localhost mysqldump]# mysql --defaults-file=/home/mysql/conf/my1.cnf –uadmin\ -ppassword < backup_2017-05-27_00_18_25.sql
mysql> select * from test order by id desc limit 10;
......
10 rows in set (0.00 sec)
mysql> show slave status\G
Empty set (0.00 sec)
mysql> change master to master_host='10.10.30.241',master_user='qfsys',master_ password='password',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G
......
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='10.10.30.250', master_user='qfsys',master_ password='password',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G
......
48.4.4 克隆从库
[root@localhost ~]# mkdir /data/backup/mysqldump/ -p
[root@localhost ~]# cd /data/backup/mysqldump/
[[root@localhost mysqldump]# rm -rf *
[root@localhost mysqldump]# mysqldump -h 10.10.30.241 -uadmin –ppassword\ --single-transaction --master-data=2 --triggers --routines --events --all-databases\ --dump-slave > backup_`date+%F_%H_%M_%S`.sql
[root@localhost mysqldump]# ls -lh
......
[root@localhost mysqldump]# mysql --defaults-file=/home/mysql/conf/my1.cnf -uadmin\ -ppassword -e "reset master;"
[root@localhost mysqldump]# mysql --defaults-file=/home/mysql/conf/my1.cnf -uadmin\ -ppassword -e "stop slave;reset slave all;"
Note (Code 3084): Replication thread(s) for channel '' are already stopped.
[root@localhost mysqldump]# mysql --defaults-file=/home/mysql/conf/my1.cnf -uadmin\ -ppassword < backup_2017-05-27_09_57_44.sql
[root@localhost mysqldump]# head -100 backup_2017-05-27_09_57_44.sql |grep -i change
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 24731476;
mysql> change master to master_host='10.10.30.241',master_user='qfsys',master_ password='password',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
......
48.4.5 指定库表备份与恢复
[root@localhost mysqldump]# mysqldump -h 10.10.30.241 -uadmin –ppassword\ --single-transaction --master-data=2 --triggers --routines --events --databases xiaoboluo\ test --dump-slave=2 --set-gtid-purged=OFF > backup_`date +%F_%H_%M_%S`.sql
[root@localhost mysqldump]# ls -lh
......
mysql> show databases;
......
10 rows in set (0.00 sec)
mysql> drop database test;drop database xiaoboluo;
......
mysql> show databases;
......
8 rows in set (0.00 sec)
[root@localhost mysqldump]# mysql -uadmin -ppassword -h 10.10.30.241 <\ backup_2017-05-27_11_50_34.sql
mysql> show databases;
......
10 rows in set (0.00 sec)
mysql> show tables from test;
......
2 rows in set (0.00 sec)
mysql> show tables from xiaoboluo;
......
7 rows in set (0.00 sec)
48.4.6 纯文本备份与恢复
mysql> insert into test(name,test) values('test22',now()),('test23',now()),('test24', now()),('test25',now());
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test;
......
18 rows in set (0.00 sec)
[root@localhost ~]# mkdir /data/backup/mysqldump/ -p
[root@localhost ~]# cd /data/backup/mysqldump/
[root@localhost mysqldump]# rm -f *
[root@localhost mysqldump]# chown mysql.mysql /data/backup /mysqldump/
[root@localhost mysqldump]# mysqldump -h 10.10.30.241 -uadmin –ppassword\ --single-transaction --triggers --routines --events --tables test -T /data/backup/\ mysqldump/
......
[root@localhost mysqldump]# ll
......
mysql> use test;
Database changed
mysql> show tables;
......
2 rows in set (0.00 sec)
mysql> drop table checksums;drop table test;
......
mysql> show tables;
Empty set (0.00 sec)
# 先恢复表结构
## 命令行生成source建表语句
[root@localhost mysqldump]# ls -lh /data/backup/mysqldump/*.sql |awk '{print "source\ "$9";"}' > /tmp/tables.sql
[root@localhost mysqldump]# cat /tmp/tables.sql
source /data/backup/mysqldump/checksums.sql;
source /data/backup/mysqldump/test.sql;
## 将/tmp/tables.sql导入数据库实例中
[root@localhost mysqldump]# mysql -uadmin -ppassword -h 10.10.30.241 test <\ /tmp/tables.sql
# 再将文本数据加载到数据库中
[root@localhost mysqldump]# mysqlimport -uadmin -ppassword -h10.10.30.241\ --use-threads=8 test /data/backup/mysqldump/*.txt
test.checksums: Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
test.test: Records: 18 Deleted: 0 Skipped: 0 Warnings: 0
mysql> use test
Database changed
mysql> show tables;
......
2 rows in set (0.00 sec)
mysql> select * from test;
......
18 rows in set (0.00 sec)
上一篇：第47章 Percona Toolkit常用工具详解 | 下一篇：第49章 MySQL主流备份工具之XtraBackup详解