七、备份实战2

1.备份实战2

　　mysqldump+binlog

　　优势

　　　　自动记录日志position位置

　　　　可用性，一致性

　　

　　语法

　　　　mysqldump -h 服务器地址 -u用户名 -p密码 数据库名 > 备份文件.sql

　　　　mysqldump --help

　　

　　备份实战

　　　　准备两套root密码

　　　　密码1　　Qianfeng@123　　配置当前数据库中

　　　　密码2　　Qianfeng@1234　　备用

　　　　准备库1

　　　　mysql -uroot -p'Qianfeng@123'

　　　　drop database testdb;　　清除之前的库

　　　　create database testdb1;

　　　　use testdb1;

　　　　create table t1 (id int);

　　　　instert into testdb1.t1 values (1), (2), (3);

　　　　select * from t1;

　　　　\q

　　　　执行备份

　　　　mkdir /backup

　　　　mysqldump -uroot -p'Qianfeng@123' -all-databases --single-transaction --master-data=2 --flush-logs > /backup/`data +%F-%H`-mysql-all.sql　　账号  密码  所有库  保持数据的可用性  注释掉日志记录，写2可以多一个#  刷新切断日志 > 放到这个目录/  时间 -mysql-all.sql

　　　　观察备份细节

　　　　vim /backup/2022-01-18-12mysql-all.sql

　　　　LOCK TABLES `user` WRITE;　　观察各种锁机制，用来保证数据的一致性，当在备份一个表时，该表会被锁住，不允许写，但可以读，等备份完该表后就会解锁，因为这一个过程很快，所以不影响使用

　　　　22 -- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000003', MASTER_LOG_POS=154;　　可以看到这行，是flush-logs截断的，--就是master-data=2的效果，如果是1就没有-- ，但是内容依旧在

　　　　备份后再次创库和变更数据

　　　　mysql -uroot -p'Qianfeng@123'

　　　　create database testdb2;

　　　　create table testdb2.t2 (id, int);

　　　　insert into testdb2.t2(1),(2),(3);

　　　　\q

　　　　mysql -uroot -p'Qianfeng@123'

　　　　insert into testdb.t2(4),(5);

　　　　create database testdb3;

　　　　\q

　　　　观察备份二进制日志文件

　　　　ls /var/lib/mysql/　　可以看到bin文件已经到00003了

　　　　停止数据库

　　　　systemctl stop mysqld

　　　　清理环境

　　　　rm -rf /var/lib/mysql/*

　　　　启动数据库

　　　　systemctl start mysqld

　　　　grep password /var/log/mysqld.log

　　　　mysqladmin -uroot -p'RxN2AdV!u(Ah' password 'Qianfeng@1234'　　这里用的是1234的备用密码

　　　　mysql -uroot -p'Qianfeng@1234'

　　　　show databases;　　是新的mysql

　　　　\q

　　　　mysql -uroot -p'Qianfeng@1234' < /backup/2022-01-18-12mysql-all.sql　　用备份恢复数据

　　　　mysql -uroot -p'Qianfeng@1234'

　　　　show databases;　　可以发现是有testdb1的

　　　　且只有testdb1的库，并没有testdb2和testdb3，这时候就需要使用二进制日志恢复了

　　　　

　　　　二进制日志恢复

　　　　观察二进制截取记录

　　　　vim /backup/2022-01-18-12mysql-all.sql

　　　　22 -- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000003', MASTER_LOG_POS=154;　　改变主服务器到主服务器文件=localhost-bin.000003，从154往后都是新的

 　　　　mysqlbinlog localhost-bin.000003 localhost-blin.000004 --start-position=154 | mysql -uroot -p'Qianfeng@123'　　通过mysqlbinlog重写数据 用导管给到该用户

　　　　mysql -uroot -p'Qianfeng@123' -e 'show databases'　　可以发现2和3都回来

 

　　　　如果我需要保留testdb3库，而testdb3库因为误操删了，不是正常的行为，不是我们需要做的范畴

　　　　mysqlbinlog localhost-bin.000003 > 1.txt

　　　　vim 1.txt

　　　　删除1.txt中不需要的at（比如删除drop database testdb3的那行at）

　　　　cat 1.txt | mysql -p'Qianfeng@123'　　很可能失败

　　　　这样会有许多和二进制日志行为所冲突

　　　　停止二进制日志

　　　　mysql -uroot -p'Qianfeng@123'

　　　　set sql_log_bin = 0;

　　　　读取资源

　　　　source 1.txt;

　　　　开启二级制日志

　　　　set sql_log_bin = 1;

　　　　这样就不会有二进制日志行为冲突