mysql全备+binlog恢复
我们模拟一个需要用到MySQL全备和binlog恢复的场景
假设周一到周日,每天晚上22:00都备份了一个全备,某一天(周四)早上10:00数据库down了,怎么恢复到最新的时间点。
思路:周三晚上22:00通过全备恢复,周三晚上22:00-周四早上10:00通过binlog恢复
准备两台mysql服务器,不要是主从服务器。
准备数据
- 创建数据库
mysql> create database cool;
Query OK, 1 row affected (0.02 sec)
- 使用数据库
mysql> use cool;
Database changed
- 创建表
mysql> create table cool (id int,name varchar(10));
Query OK, 0 rows affected (0.04 sec)
- 将数据写入表中
mysql> insert into cool values(1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
- 上传数据
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
备份数据库
假设周三晚上10:00进行了一个全备,如下:
[root@centos opt]# mysqldump -uroot -p --set-gtid-purged=OFF --master-data=2 --single-transaction -A > /opt/full_$(date +%F).sql
新建一个数据库及表
模拟周三晚上十点到周四早上十点新增数据。
- 再创建一个数据库
mysql> create database t1;
Query OK, 1 row affected (0.00 sec)
- 使用数据库
mysql> use t1;
Database changed
- 创建表
mysql> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.48 sec)
- 将数据写入表中
mysql> insert into t1(id,name) values(1,'php'),(2,'java'),(3,'python');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
- 查看表中的数据
mysql> select * from t1;
+------+--------+
| id | name |
+------+--------+
| 1 | php |
| 2 | java |
| 3 | python |
+------+--------+
3 rows in set (0.00 sec)
- 上传数据
mysql> commit;
Query OK, 0 rows affected (0.00 sec
模拟故障删库
周四早上十点模拟故障删库t1
- 删除数据库
mysql> drop database t1;
Query OK, 1 row affected (0.01 sec)
- 上传数据
mysql> commit;
查找binlog的起点及终点
在故障的mysql服务器上查看周三晚上十点备份的binlog起点。
vi /opt/full_2021-05-17.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='slave-binlog.000002', MASTER_LOG_POS=234;
- 查看binlog终点
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| slave-binlog.000002 | 840 | | | 18d11475-b5fd-11eb-9b94-000c29c40ee0:1-14,
79c2fe65-b621-11eb-a65a-000c29d77752:1-7 |
+---------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'slave-binlog.000002';
+---------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| slave-binlog.000002 | 4 | Format_desc | 2 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| slave-binlog.000002 | 123 | Previous_gtids | 2 | 234 | 18d11475-b5fd-11eb-9b94-000c29c40ee0:1-14,
79c2fe65-b621-11eb-a65a-000c29d77752:1-4 |
| slave-binlog.000002 | 234 | Gtid | 2 | 299 | SET @@SESSION.GTID_NEXT= '79c2fe65-b621-11eb-a65a-000c29d77752:5' |
| slave-binlog.000002 | 299 | Query | 2 | 387 | create database t1 |
| slave-binlog.000002 | 387 | Gtid | 2 | 452 | SET @@SESSION.GTID_NEXT= '79c2fe65-b621-11eb-a65a-000c29d77752:6' |
| slave-binlog.000002 | 452 | Query | 2 | 562 | use `t1`; create table t1(id int,name varchar(20)) |
| slave-binlog.000002 | 562 | Gtid | 2 | 627 | SET @@SESSION.GTID_NEXT= '79c2fe65-b621-11eb-a65a-000c29d77752:7' |
| slave-binlog.000002 | 627 | Query | 2 | 697 | BEGIN |
| slave-binlog.000002 | 697 | Table_map | 2 | 743 | table_id: 154 (t1.t1) |
| slave-binlog.000002 | 743 | Write_rows | 2 | 809 | table_id: 154 flags: STMT_END_F |
| slave-binlog.000002 | 809 | Xid | 2 | 840 | COMMIT /* xid=656 */ |
| slave-binlog.000002 | 840 | Gtid | 2 | 905 | SET @@SESSION.GTID_NEXT= '79c2fe65-b621-11eb-a65a-000c29d77752:8' |
| slave-binlog.000002 | 905 | Query | 2 | 991 | drop database t1 |
+---------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)
所以可以知道,binlog的起点是234,终点为905
截取binlog起点及终点的日志
[root@centos opt]# mysqlbinlog --skip-gtids --start-position=234 --stop-position=905 /data/mysql/data/slave-binlog.000002 > /opt/binlog.sql
#按时间截取是如下命令:
# mysqlbinlog slave-binlog.000002 --start-datetime='2021-05-19 22:00:00' --stop-datetime='2021-05-19 10:00:00' -r > /opt/binlog.sql
临时库上恢复数据库到最新时间点
临时库上恢复全备+binlog,将全备文件及截取的binlog文件恢复到临时库上(另外一台临时服务器上)
[root@centos opt]# scp /opt/full_2021-05-17.sql root@192.168.137.18:/opt
root@192.168.137.18's password:
full_2021-05-17.sql 100% 913KB 10.5MB/s 00:00
[root@test data]# scp /opt/binlog.sql root@192.168.137.18:/opt
root@192.168.137.18's password:
binlog.sql 100% 1621 1.6MB/s 00:00
- 恢复全备的数据库
mysql> source /opt/full_2021-05-17.sql;
现在只是恢复到周三晚上十点。所以并没有周三晚上十点到周四早上十点的数据。即t1数据库。
- 查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cool |
| mysql |
| performance_schema |
| sys |
| test |
| wordpress |
+--------------------+
7 rows in set (0.00 sec)
- 恢复周三晚上十点到周四早上十点新增的数据。
mysql> source /opt/binlog.sql;
- 查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cool |
| mysql |
| performance_schema |
| sys |
| t1 |
| test |
| wordpress |
+--------------------+
8 rows in set (0.00 sec)
- 使用数据库
mysql> use t1;
Database changed
- 查看表中数据
mysql> select * from t1;
+------+--------+
| id | name |
+------+--------+
| 1 | php |
| 2 | java |
| 3 | python |
+------+--------+
3 rows in set (0.00 sec)
可以发现已经恢复完所有数据了