mysql全备+binlog恢复

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)

可以发现已经恢复完所有数据了

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值