mysql利用binlog恢复数据详细例子

时间:2023-03-08 16:58:54
mysql利用binlog恢复数据详细例子

模拟数据恢复的案例

有些时候脑瓜就会短路,难免会出错

场景:在生产环境中,我们搭建了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=;

mysql -uroot -p123456 -e "show binlog events in 'mysql-bin.000001'"|tail -n1000

找到了对应pos点:

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利用binlog恢复数据详细例子

好了,数据恢复

二、删库

其实删除数据库都是一样的道理

比如我们的备份是还是昨天晚上的,但是今天下午清理数据,删除无效的数据库,误删了

恢复步骤和原理都是差不多的,我就不演示了,简单讲解下

比如下午有新加了一些数据:

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利用binlog恢复数据详细例子

此时普通用户不能进行写操作啦,比如下面
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的原理及其使用