MySQL全备与binlog恢复

最新推荐文章于 2025-03-25 21:23:29 发布

蜜丝卡木丝卡

最新推荐文章于 2025-03-25 21:23:29 发布

阅读量244

点赞数

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/weixin_49042937/article/details/117225701

版权

本文详细阐述了如何在MySQL数据库周一晚上的自动备份丢失后，通过分析binlog进行数据恢复，包括创建新数据库、导入全备文件和binlog片段，确保t1数据库数据的找回。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL每天晚上10点进行自动备份，假设周一晚上10点到周二的早上10点文件丢失，如何进行恢复

进入MySQL数据库

[root@centos ~]# mysql -uroot -p
Enter password:

mysql> create database cool; 创建名为cool的数据库
Query OK, 1 row affected (0.05 sec)

mysql> use cool; 进入cool数据库
Database changed
mysql> create table cool (id int,name varchar(10)); 创建表
Query OK, 0 rows affected (0.34 sec)

mysql> insert into cool values(1,'a'),(2,'b'),(3,'c'); 插入数据
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> commit; 上传保存数据
Query OK, 0 rows affected (0.00 sec)

mysql> quit 退出
Bye

对周一晚上10点进行一个全备

[root@centos ~]# mysqldump -uroot -p --set-gtid-purged=OFF --master-data=2 --single-transaction -A > /opt/full_$(date +%F).sql
Enter password:

[root@centos opt]# ls
full_2021-05-24.sql mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz remi-release-7.rpm

新建一个数据库

[root@centos opt]# mysql -uroot -p
Enter password:

mysql> create database t1; 创建名为t1的数据库
Query OK, 1 row affected (0.03 sec)

mysql> use t1; 进入t1数据库
Database changed
mysql> create table t1(id int,name varchar(20)); 创建表
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1(id,name) values(1,'php'),(2,'java'),(3,'python'); 插入数据
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t1; 查询表 t1下的所有字段的所有记录
+------+--------+
| id | name |
+------+--------+
| 1 | php |
| 2 | java |
| 3 | python |
+------+--------+
3 rows in set (0.00 sec)

模拟故障删除库

mysql> drop database t1; 删除数据库 t1
Query OK, 1 row affected (0.02 sec)

mysql> commit; 上传保持数据
Query OK, 0 rows affected (0.00 sec)

mysql> quit 退出
Bye

查看binlog的起点

[root@centos opt]# vi /opt/full_2021-05-24.sql 在故障的MySQL服务器上查看周一晚上10点备份的binlog起点

查看binlog的终点

mysql> show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| master-binlog.000001 | 911 | | | 8ee842a8-b1fd-11eb-8db4-000c290328a7:1-4 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'master-binlog.000001';
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| master-binlog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| master-binlog.000001 | 123 | Previous_gtids | 1 | 154 | |
| master-binlog.000001 | 154 | Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= '8ee842a8-b1fd-11eb-8db4-000c290328a7:1' |
| master-binlog.000001 | 219 | Query | 1 | 307 | create database t1 |
| master-binlog.000001 | 307 | Gtid | 1 | 372 | SET @@SESSION.GTID_NEXT= '8ee842a8-b1fd-11eb-8db4-000c290328a7:2' |
| master-binlog.000001 | 372 | Query | 1 | 482 | use `t1`; create table t1(id int,name varchar(20)) |
| master-binlog.000001 | 482 | Gtid | 1 | 547 | SET @@SESSION.GTID_NEXT= '8ee842a8-b1fd-11eb-8db4-000c290328a7:3' |
| master-binlog.000001 | 547 | Query | 1 | 617 | BEGIN |
| master-binlog.000001 | 617 | Table_map | 1 | 663 | table_id: 153 (t1.t1) |
| master-binlog.000001 | 663 | Write_rows | 1 | 729 | table_id: 153 flags: STMT_END_F |
| master-binlog.000001 | 729 | Xid | 1 | 760 | COMMIT /* xid=634 */ |
| master-binlog.000001 | 760 | Gtid | 1 | 825 | SET @@SESSION.GTID_NEXT= '8ee842a8-b1fd-11eb-8db4-000c290328a7:4' |
| master-binlog.000001 | 825 | Query | 1 | 911 | drop database t1 |
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
13 rows in set (0.00 sec)

mysql> quit
Bye

根据以上信息得知，binlog的起点为154，终点为825

截取binlog起点及终点的日志

[root@centos opt]# mysqlbinlog --skip-gtids --start-position=154 --stop-position=825 /data/mysql/data/master-binlog.000001 >/opt/binlog.sql
[root@centos opt]# ls
binlog.sql full_2021-05-24.sql mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz remi-release-7.rpm

临时库上恢复全备和binlog，将全备文件和截取到的binlog文件恢复到临时库上（另一个临时虚拟机）

[root@centos opt]# scp /opt/full_2021-05-24.sql root@192.168.60.11:/opt/ 将全备传到另一台虚拟机
The authenticity of host '192.168.60.11 (192.168.60.11)' can't be established.
ECDSA key fingerprint is SHA256:FQYKNNo8vJk2wWeCQhOXxtyNXBwSk4miwXfB+al6i3w.
ECDSA key fingerprint is MD5:33:8c:32:51:9a:8f:d1:54:34:75:3b:00:37:47:e0:9b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.60.11' (ECDSA) to the list of known hosts.
root@192.168.60.11's password:
full_2021-05-24.sql 100% 1503KB 6.9MB/s 00:00

[root@centos opt]# scp /opt/binlog.sql root@192.168.60.11:/opt/ 将截取到的binlog传到另一台虚拟机
root@192.168.60.11's password:
binlog.sql 100% 2290 903.1KB/s 00:00

另一台临时虚拟机操作

[root@centos11 ~]# cd /opt/
[root@centos11 opt]# ls
binlog.sql full_2021-05-24.sql mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

[root@centos11 opt]# mysql -uroot -p 进入数据库
Enter password:

mysql> source /opt/full_2021-05-17.sql; 将全备导入

mysql> source /opt/binlog.sql; 将binlog导入

mysql> show databases; 查看信息
+--------------------+
| Database |
+--------------------+
| information_schema |
| cool |
| mysql |
| performance_schema |
| sys |
| t1 |
| wordpress |
+--------------------+
7 rows in set (0.00 sec)
mysql> use t1; 进入t1数据库
Database changed
mysql> select * from t1; 查询表 t1下的所有字段的所有记录
+------+--------+
| id | name |
+------+--------+
| 1 | php |
| 2 | java |
| 3 | python |
+------+--------+
3 rows in set (0.00 sec)