数据库备份实战二

实战案例2
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
备份实战
请准备两套root密码
密码1
QianFeng@123
配置到当前数据库中。
密码2
QianFeng@1234
备用
准备库1
注意
请清理掉之前的test库。实验后半部分，需要继续创建test库，避免混淆。
testdb1.t1 创建库和表，插入数据
create database testdb1;
use testdb1 进入库里
create table t1(id int); 创建表
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);

				mysql> select * from testdb1.t1;   查看库中表的全部内容

±-----+
| id |
±-----+
| 1 |
| 2 |
| 3 |
±-----+
执行备份，先创建备份的目标文件夹
[root@localhost ~]# mysqldump -p’QianFeng@123’ --all-databases --single-transaction --master-data=2 --flush-logs > /backup/date +%F-%H-mysql-all.sql
这是一条命令，注意空格。master-data=2 注释掉日志记录
观察备份细节

			LOCK TABLES `user` WRITE;
				观察各种锁机制，用来保证数据一致性
			22 CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000004', MASTER_LOG_POS=154;
				二进制日志截断位置。第22行
			业务正常推进... 相当于完整备份

备份后的数据变更行为。修改数据，用二进日志文件备份恢复。注意变更后的文件没有备份，需要用二进制日志重新操作。
在testdb2.t2 中插入数据1，2,3；
创testdb2库，t2表，插入数据。
切断二进制日志（重启数据库）
create database testdb2；
use testdb2
create table t2；
insert into t2 values(1);
insert into t2 values(2);
insert into t2 values(3);

				mysql> select * from testdb2.t2;

±-----+
| id |
±-----+
| 1 |
| 2 |
| 3 |
±-----+
在testdb2.t2 中插入数据4；在testdb2.t2 中插入数据5；
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| testdb1 |
| testdb2 |
±-------------------+
12 rows in set (0.00 sec)

mysql>

	恢复实战
		1 备份二进制日志文件
			cp /var/lib/mysql/*bin* ~
		2. 停止数据库
			systemctl stop mysqld
		3. 清理环境
			rm -rf /var/lib/mysql/*
		4.启动数据库
			systemctl start mysqld
			grep 'password' /var/log/mysqld.log
				找密码，再改一下密码。请使用密码2
				Kyzw;u2dq<mh
				mysqladmin -uroot -p'Kyzw;u2dq<mh' password 'QianFeng@1234'
				注意
					语法要求password后面有空格。
		5.mysql恢复数据
			 mysql -p'QianFeng@1234' < /backup/2016-12-08-04-mysql-all.sql
			mysql -p'QianFeng@1234' -e 'flush privileges'
				请使用备份时密码 
			登陆并验证数据恢复结果。请思考恢复的数据库有几个？
				1个
		6.二进制日志恢复
			观察二进制截取记录
				vim /backup/2016-11-25-14-mysql-all.sql
				CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.0000010', MASTER_LOG_POS=154;
		这是一条命令	mysqlbinlog localhost-bin.000002 localhost-bin.000003 --start-position=154  | mysql -p'QianFeng@123'
			注意后续有多少日志，要跟多少日志名字。
		7.观察数据
			请思考数据是否恢复完整。
				是完整的。
		8.请思考误删除的问题
			如果误删除testdb2库，怎么恢复testdb2库

在恢复最后log数据进行操作mysqlbinlog localhost-bin.000005 >1.txt 把做删除操作的那一段日志重定向到1.txt文件中。 vi 1.txt 打开找到删除的操作，删除at 478,保存 退出。然后让数据库在读取一遍。
读取命令
cat 1.txt | mysql -p’QianFeng@123’

		9.关于数据恢复时的多余日志。
			方法一
				原因
					每次还原数据库都会增加日志的体积。但这些都是还原操作。恢复数据库的日志占用了存储空间。
				mysql>  set sql_log_bin =0  
				 再mysql>  source   /backup/*.sql
				观察
					二进制日志并没有发生变化。
			方法2
				在备份文件中，加入关闭二进制日志。