模拟数据恢复的案例
有些时候脑瓜就会短路，难免会出错
场景：在生产环境中，我们搭建了mysql主从，备份操作都是在从备份数据库上
前提：有最近一天或者最近的全备
或者最近一天相关数据库的备份
最重要的是，二进制日志必须完整
| 服务器信息 | 角色 | 端口 |
| 192.168.1.21 | mysql主 | 30136 |
| 192.168.1.21 | mysql从 | 30236 |
接下来，我们模拟下
案例一、update未加where条件，误操作修改数据
全备命令： mysqldump -uroot -p123456 --single-transaction --set-gtid-purged=OFF --master-data=2 -A > all_database.sql
mysqldump -uroot -p123456 --single-transaction --set-gtid-purged=OFF --master-data=2 xcrm > xcrm.sql
模拟灾难现场
我这个是在k8s里面搭建了一个主从
kubectl get all -o wide
kubectl exec -it mysql-master-659958ff4f-l4sgt bash #mysql主
mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xcrm |
+--------------------+
5 rows in set (0.00 sec)
mysql> use xcrm
mysql> create table edai_test like ding_cun;
mysql> insert into edai_test select * from ding_cun;
比如凌晨全备是做到这里的
命令：mysqldump -uroot -p123456 --single-transaction --set-gtid-purged=OFF --master-data=2 -A > all_database.sql
早上九点的时候，又有新的数据进来
mysql> insert into edai_test(nper,money) select nper,money from ding_cun;
110 rows in set (0.00 sec)
悲剧来了，上线中的sql，没有检查，where条件没加，直接执行
mysql> update edai_test set money=0;
Query OK, 110 rows affected (0.07 sec)
Rows matched: 110 Changed: 110 Warnings: 0
糟糕，一不小心金额被我修改为0了，偶my嘎，
赶紧恢复，找出binlog点，根据之前的全备+binlog恢复
恢复步骤：
1.找出时间段
root@mysql-master-659958ff4f-l4sgt:~/backu1.p# cat all_database.sql |grep -i 'CHANGE MASTER TO MASTER_LOG_FILE'|head -n1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
mysql -uroot -p123456 -e "show binlog events in 'mysql-bin.000001'"|tail -n1000
找到了对应pos点：4326
2.mysql导入昨天晚上的备份数据，并且查看pos节点
mysql -uroot -p123456 < all_databases.sql
3.拷贝二进制日志文件到其他地方和二进制恢复
cp /var/lib/mysql/mysql-bin.000001 .
root@mysql-master-659958ff4f-l4sgt:~/backup# mysqlbinlog --start-position=154 --stop-position=4326 -d xcrm mysql-bin.000001|mysql -uroot -p123456 xcrm
好了，数据恢复
二、删库
其实删除数据库都是一样的道理
比如我们的备份是还是昨天晚上的，但是今天下午清理数据，删除无效的数据库，误删了
恢复步骤和原理都是差不多的，我就不演示了，简单讲解下
比如下午有新加了一些数据：
mysql> create table customers( id int not null auto_increment, name char(20) not null, age int not null, primary key(id)
)engine=InnoDB; Query OK, 0 rows affected (0.35 sec) mysql> insert into customers values(1,"wangbo","24"); Query OK, 1 row affected (0.07 sec) mysql> insert into customers values(2,"guohui","22"); Query OK, 1 row affected (0.12 sec) mysql> insert into customers values(3,"zhangheng","27"); Query OK, 1 row affected (0.06 sec) mysql> select * from customers; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 1 | wangbo | 24 | | 2 | guohui | 22 | | 3 | zhangheng | 27 | +----+-----------+-----+ 3 rows in set (0.00 sec)
然后我一不小心把数据库删除了
mysql> drop database xcrm; Query OK, 5 rows affected (0.40 sec)
删库恢复步骤
到这里，如果是在生产环境，必须立刻做处理
1.立刻设置全局只读
进入数据库：set global read_only=1; #普通权限的用户只读，不能写数据
mysql> show variables like '%read_only%';
此时普通用户不能进行写操作啦，比如下面
mysql -udemo -pdemo -P30136 -h192.168.1.21 xcrm mysql> select * from customers ; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 1 | wangbo | 24 | | 2 | guohui | 22 | | 3 | zhangheng | 27 | +----+-----------+-----+ 3 rows in set (0.00 sec) mysql> delete from customers where id=3; ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
2.找到最近备份文件和二进制日志点
root@mysql-master-659958ff4f-l4sgt:~/backup# mysql -uroot -p123456 -e "show binlog events in 'mysql-bin.000001'"|grep -i 'DROP DATABASE'
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-bin.000001 15035 Query 22 15127 drop database xcrm
找到最近备份文件的最后pos位置
root@mysql-master-659958ff4f-l4sgt:~/backup# cat all_database.sql |grep -i 'CHANGE MASTER TO MASTER_LOG_FILE'|head -n1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=13871;
3.进行恢复
先将今天凌晨备份的导进去
mysql -uroot -p123456 < all_database.sql
13871 15035
查看数据中间还相差很多数据，没关系，再根据pos期间来恢复
mysqlbinlog --start-position=13871 --stop-position=15035 -d xcrm mysql-bin.000001|mysql -uroot -p123456 xcrm
进入数据库看看，数据是否正常
mysql -uroot -p123456 mysql> use xcrm Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_xcrm | +------------------+ | customers | | ding_cun | | edai_app_version | | edai_test | +------------------+ 4 rows in set (0.00 sec) mysql> select * from customers; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 1 | wangbo | 24 | | 2 | guohui | 22 | | 3 | zhangheng | 27 | +----+-----------+-----+ 3 rows in set (0.00 sec)
好了，没问题，drop数据库也可以恢复啦
现在把全局锁解开：
mysql> set global read_only=0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%read_only%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_read_only | OFF | | read_only | OFF | | super_read_only | OFF | | tx_read_only | OFF | +------------------+-------+ 4 rows in set (0.00 sec) 大家有没有什么疑问？
再三强调：
在生产环境误操作之后，应该立刻判断这个表或者库的重要性
1.如果是不常用的数据库或者表，几乎没有数据更新，可以不用锁库，只读
2.如果是很常用的数据库或者表，必须处理，设置为只读，如果更为严重，需要停止mysql服务器。目的就是防止有新的数据写进来，数据混乱
另外呢，假如在生产环境误操作了，比如，insert，delete，update等表的操作，利用mysql全备+binlog来恢复，时间成本很大，这种方式不是很好。
我们可以用python开发的一个工具：binlog2sql (mysql闪回工具)
Mysql闪回工具之binlog2sql的原理及其使用
