MySQL通过Binlog恢复数据
一、结合备份+binlog恢复数据
场景：用户误删表；
解决方案；另外一台设备恢复全库和binlog日志，然后导出表，导入生产环境；
实验环境：一台生产环境数据库，一台测试环境数据库
1.1 备份数据库
[root@localhost ~]# mysqldump -uroot -p --databases jl>jl.bak2
1.2 查看日志记录
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| BIN_LOG.000001 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1.3 对数据做变更
mysql> select * from yddl2;
+------+---------------------+
| id | timepoint |
+------+---------------------+
| 6 | 2022-10-13 15:01:36 |
| 7 | 2022-10-18 21:45:37 |
| 8 | 2022-10-18 21:45:40 |
| 9 | 2022-10-18 21:45:44 |
+------+---------------------+
4 rows in set (0.00 sec)
1.4 模拟修改数据
mysql> update yddl2 set id=10 where id=6;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from yddl2 where id=9;
Query OK, 1 row affected (0.01 sec)
修改后的数据：
mysql> select * from yddl2; +------+---------------------+ | id | timepoint | +------+---------------------+ | 10 | 2022-10-13 15:01:36 | | 7 | 2022-10-18 21:45:37 | | 8 | 2022-10-18 21:45:40 | +------+---------------------+ 3 rows in set (0.00 sec)
1.5 删除表
mysql> drop tables yddl2; Query OK, 0 rows affected (0.04 sec)
通过查找日志确定删除表的日志记录
mysql> show binlog events in 'BIN_LOG.000001';
+----------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| BIN_LOG.000001 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| BIN_LOG.000001 | 123 | Previous_gtids | 6 | 154 | |
| BIN_LOG.000001 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000001 | 219 | Query | 6 | 289 | BEGIN |
| BIN_LOG.000001 | 289 | Table_map | 6 | 337 | table_id: 110 (jl.yddl2) |
| BIN_LOG.000001 | 337 | Update_rows | 6 | 393 | table_id: 110 flags: STMT_END_F |
| BIN_LOG.000001 | 393 | Xid | 6 | 424 | COMMIT /* xid=1320 */ |
| BIN_LOG.000001 | 424 | Anonymous_Gtid | 6 | 489 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000001 | 489 | Query | 6 | 559 | BEGIN |
| BIN_LOG.000001 | 559 | Table_map | 6 | 607 | table_id: 110 (jl.yddl2) |
| BIN_LOG.000001 | 607 | Delete_rows | 6 | 652 | table_id: 110 flags: STMT_END_F |
| BIN_LOG.000001 | 652 | Xid | 6 | 683 | COMMIT /* xid=1321 */ |
| BIN_LOG.000001 | 683 | Anonymous_Gtid | 6 | 748 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000001 | 748 | Query | 6 | 862 | use `jl`; DROP TABLE `yddl2` /* generated by server */ |
+----------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
14 rows in set (0.00 sec)
1.6 把备份数据拷贝到测试主机
[root@localhost ~]# scp jl.bak2 root@192.168.43.9:/root
root@192.168.43.9's password:
jl.bak2 100% 4730 426.9KB/s 00:00
1.7 在原来生产主机上查看库的创建语句
mysql> show create database jl; +----------+-------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------+ | jl | CREATE DATABASE `jl` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-------------------------------------------------------------+ 1 row in set (0.00 sec)
在测试库创建要恢复的库
mysql> CREATE DATABASE `jl` /*!40100 DEFAULT CHARACTER SET utf8 */; Query OK, 1 row affected (0.00 sec)
1.8 在测试库恢复数据库
[root@localhost ~]# mysql -uroot -p -o jl</root/jl.bak2
Enter password:
导入后进入数据库报错：
mysql> use jl; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
加参数-A解决问题
[root@localhost ~]# mysql -uroot -p -A
Enter password:
查看数据是备份之前的数据；
mysql> select * from yddl2;
+------+---------------------+
| id | timepoint |
+------+---------------------+
| 6 | 2022-10-13 15:01:36 |
| 7 | 2022-10-18 21:45:37 |
| 8 | 2022-10-18 21:45:40 |
| 9 | 2022-10-18 21:45:44 |
+------+---------------------+
4 rows in set (0.00 sec)
1.9将二进制日志拷贝到测试主机
[root@localhost binlog]# scp BIN_LOG.000001 root@192.168.43.9:/root
root@192.168.43.9's password:
BIN_LOG.000001 100% 862 11.1KB/s 00:00
[root@localhost binlog]#
在主机查看恢复位置，恢复到删除之前位置 754之前
mysql> show binlog events in 'BIN_LOG.000001';
+----------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| BIN_LOG.000001 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| BIN_LOG.000001 | 123 | Previous_gtids | 6 | 154 | |
| BIN_LOG.000001 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000001 | 219 | Query | 6 | 289 | BEGIN |
| BIN_LOG.000001 | 289 | Table_map | 6 | 337 | table_id: 110 (jl.yddl2) |
| BIN_LOG.000001 | 337 | Update_rows | 6 | 393 | table_id: 110 flags: STMT_END_F |
| BIN_LOG.000001 | 393 | Xid | 6 | 424 | COMMIT /* xid=1320 */ |
| BIN_LOG.000001 | 424 | Anonymous_Gtid | 6 | 489 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000001 | 489 | Query | 6 | 559 | BEGIN |
| BIN_LOG.000001 | 559 | Table_map | 6 | 607 | table_id: 110 (jl.yddl2) |
| BIN_LOG.000001 | 607 | Delete_rows | 6 | 652 | table_id: 110 flags: STMT_END_F |
| BIN_LOG.000001 | 652 | Xid | 6 | 683 | COMMIT /* xid=1321 */ |
| BIN_LOG.000001 | 683 | Anonymous_Gtid | 6 | 748 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| BIN_LOG.000001 | 748 | Query | 6 | 862 | use `jl`; DROP TABLE `yddl2` /* generated by server */ |
+----------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
14 rows in set (0.00 sec)
1.10 用二进制日志恢复，上一步查看删除前的位置为683
[root@localhost ~]# mysqlbinlog --stop-position=683 --database=jl BIN_LOG.000001 |mysql -uroot -p
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case.
If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
Enter password:
查看日志数据恢复完成后的表：
mysql> select * from yddl2; +------+---------------------+ | id | timepoint | +------+---------------------+ | 10 | 2022-10-13 15:01:36 | | 7 | 2022-10-18 21:45:37 | | 8 | 2022-10-18 21:45:40 | +------+---------------------+ 3 rows in set (0.00 sec)
1.11 从测试库备份这张表：
[root@localhost ~]# mysqldump -uroot -p jl yddl2>yddl2.sql
Enter password:
1.12 将导出的表拷贝到生产库
拷贝数据：
[root@localhost ~]# scp yddl2.sql root@192.168.43.10:/root The authenticity of host '192.168.43.10 (192.168.43.10)' can't be established. ECDSA key fingerprint is SHA256:YLyHddZaxFIT0VawsRWWUTz7WBtDKWzAuGdNyw9zLwU. ECDSA key fingerprint is MD5:39:0b:eb:4d:fa:95:1b:61:1e:b0:5f:af:2c:51:05:9b. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.43.10' (ECDSA) to the list of known hosts. root@192.168.43.10's password: yddl2.sql 100% 1923 502.4KB/s 00:00 [root@localhost ~]#
导入数据：
[root@localhost bak]# mysql -uroot -p jl <yddl2.sql
Enter password:
数据找回：
mysql> select * from yddl2; +------+---------------------+ | id | timepoint | +------+---------------------+ | 10 | 2022-10-13 15:01:36 | | 7 | 2022-10-18 21:45:37 | | 8 | 2022-10-18 21:45:40 | +------+---------------------+ 3 rows in set (0.00 sec)
