MySQL备份二-mysqldump

mysqldump+binlog

优势：1、自动记录日志position位置。show master status\G;? ?2、可用性一致性-锁表机制

语法：mysqldump -h 服务器? -u用户名 -p密码 数据库 > 备份文件.sql

参数说明：库的范围，-A, --all-databases?? ?所有库。school?? ?数据库名，school stu_info t1?? ?是指school数据库的表stu_info、t1，-B, --databases bbs test mysql?? ?多个数据库，?--single-transaction? #InnoDB 一致性 服务可用性，--master-data=1|2???#该选项将会记录binlog的日志位置与文件名并追加到文件中，或添加注释。

高级选项：?--opt? #同时启动各种高级选项，-R, --routines? #备份存储过程和存储函数，?-F, --flush-logs? #备份之前刷新日志，截断日志。备份之后新binlog。--triggers? #备份触发器

帮助：mysqldump --help

?1、备份实战

1.1 准备两套root密码

Aa.123456,配置到当前数据库中

Aa.1234567,备用

1.2 准备数据库testdb1并插入数据

1.3 执行备份

mysqldump -p'Aa.123456' \
--all-databases --single-transaction \
 --master-data=2 \
--flush-logs \
>  /backup/backup-mysql-all.sql

1.4 观察备份细节

vim /backup/backup-mysql-all.sql

?LOCK TABLES `user` WRITE;??观察各种锁机制，用来保证数据一致性

?22 CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000004', MASTER_LOG_POS=154;二进制日志截断位置。第22行

1.5 备份后的，数据变更行为

在testdb2.t2 中插入数据1，2,3；
创testdb3库
切断二进制日志（重启数据库）
在testdb2.t2 中插入数据4；
删testdb3库
在testdb2.t2 中插入数据5；

2、恢复实战

2.11 备份二进制日志文件

cp /var/lib/mysql/*bin* ~

2.22. 停止数据库/清理环境

systemctl stop mysqld
rm -rf /var/lib/mysql/*

2.3?启动数据库

systemctl start mysqld
grep 'password' /var/log/mysqld.log     #找密码，再改一下密码。请使用密码2
mysqladmin -uroot -p'Kyzw;u2dq<mh' password 'Aa.1234567' 

2.4.mysql恢复数据

mysql -p'Aa.1234567 < /backup/backup-mysql-all.sql
mysql -p'Aa.1234567' -e 'flush privileges'

?登陆并验证数据恢复结果。请思考恢复的数据库有几个？? ?1个

2.5 二进制日志恢复

观察二进制截取记录

vim /backup/backup-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.0000010', ASTER_LOG_POS=154;

mysqlbinlog localhost-bin.000002 localhost-bin.000003 --start-osition=154  | mysql -p'Aa.123456

?注意后续有多少日志，要跟多少日志名字。

2.6 观察数据

请思考数据是否恢复完整。

2.7?请思考误删除的问题

如何保留testdb3库

mysqlbinlog  localhost-bin.00004      ......005.....006...... > 1.txt

?删除1.txt中不需要的at（比如test3库是误操作）

cat ?1.txt | mysql -p'Aa.123456'

2.8?关于数据恢复时的多余日志

原因:每次还原数据库都会增加日志的体积。但这些都是还原操作。恢复数据库的日志占用了存储空间。

set sql_log_bin =0;
source   /backup/*.sql;