MySQL中binlog+dump备份还原详细教程

binlog日志恢复

MySQL备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在MySQL故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。

binlog介绍

mysql的二进制日志记录着该数据库的所有增删改的操作日志(前提是要在自己的服务器上开启binlog)，还包括了这些操作的执行时间。为了显示这些二进制内容，我们可以使用mysqlbinlog命令来查看。

Binlog的用途

主从同步

恢复数据库

开启binary log功能

通过编辑my.cnf中的log-bin选项可以开启二进制日志；形式如下： log-bin[=DIR/[filename]]（配置文件中只写log_bin不写后面的文件名和路径时，默认存放在/usr/local/mysql/data目录下，文件名为主机名-bin.000001…命名）其中，DIR参数指定二进制文件的存储路径；filename参数指定二级制文件的文件名，其形式为filename.number，number的形式为000001、000002等。

每次重启mysql服务或运行mysql> flush logs;都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增。除了生成上述的文件外还会生成一个名为filename.index的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引

配置保存以后重启mysql的服务器，用mysql> show variables like 'log_bin';查看bin-log是否开启如下所示。

[root@mysql ~]# vim /etc/my.cnf 
log_bin=mysql-bin
server_id=1
[root@mysql ~]# systemctl restart mysqld
[root@mysql ~]# mysql -uroot -p123 -e "show variables like 'log_bin'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

配置binlog

1. 查看产生的binary log

注：查看binlog内容是为了恢复数据 bin-log因为是二进制文件，不能通过文件内容查看命令直接打开查看，mysql提供两种方式查看方式，在介绍之前，我们先对数据库进行一下增删改的操作，否则log里边数据有点空。

[root@mysql ~]# mysql -uroot -p123
#省略部分内容
mysql> reset master;                    #清空所有二进制文件，从000001开始
Query OK, 0 rows affected (0.00 sec)

mysql> create database bbs character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.01 sec)

mysql> use bbs;
Database changed
mysql> create table tb1(
    -> id int primary key auto_increment,
    -> name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb1(name) values('z3'),('l4');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> flush logs;          #刷新日志，下面操作将在000002
Query OK, 0 rows affected (0.01 sec)

mysql> delete from tb1 where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb1(name) values('w5');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | z3   |
|  3 | w5   |
+----+------+
2 rows in set (0.00 sec)

2. 查看MySQL Server上的二进制日志

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       866 |
| mysql-bin.000002 |       670 |
+------------------+-----------+
2 rows in set (0.00 sec)

3. 查看二进制日志信息的命令：

语法格式：SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

查看二进制日志中的事件，默认显示可找到的第一个二进制日志文件中的事件，包含了日志文件名、事件的开始位置、事件类型、结束位置、信息等内容

mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                              |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.40-log, Binlog ver: 4    #此事件为格式描述事件                                          |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                                                                   |
| mysql-bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                              |
| mysql-bin.000001 | 219 | Query          |         1 |         346 | create database bbs character set utf8 collate utf8_bin   //为查询事件                         |
| mysql-bin.000001 | 346 | Anonymous_Gtid |         1 |         411 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                              |
| mysql-bin.000001 | 411 | Query          |         1 |         553 | use `bbs`; create table tb1(
id int primary key auto_increment,
name varchar(20)) |
| mysql-bin.000001 | 553 | Anonymous_Gtid |         1 |         618 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                              |
| mysql-bin.000001 | 618 | Query          |         1 |         689 | BEGIN                             #为查询事件，事务开始                                                 |
| mysql-bin.000001 | 689 | Table_map      |         1 |         737 | table_id: 109 (bbs.tb1)           #为表映射事件                                                       |
| mysql-bin.000001 | 737 | Write_rows     |         1 |         788 | table_id: 109 flags: STMT_END_F   #为我们执行的insert事件                                                |
| mysql-bin.000001 | 788 | Xid            |         1 |         819 | COMMIT /* xid=13 */               #Xid时间是自动提交事务的动作                                                |
| mysql-bin.000001 | 819 | Rotate         |         1 |         866 | mysql-bin.000002;pos=4            #为日志轮换事件，是我们执行flush logs开启新日志文件引起的                                                |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
12 rows in set (0.01 sec)

4. 查看指定的二进制日志中的事件

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.40-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 | 219 | Query          |         1 |         290 | BEGIN                                 |
| mysql-bin.000002 | 290 | Table_map      |         1 |         338 | table_id: 109 (bbs.tb1)               |
| mysql-bin.000002 | 338 | Delete_rows    |         1 |         381 | table_id: 109 flags: STMT_END_F       |
| mysql-bin.000002 | 381 | Xid            |         1 |         412 | COMMIT /* xid=15 */                   |
| mysql-bin.000002 | 412 | Anonymous_Gtid |         1 |         477 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 | 477 | Query          |         1 |         548 | BEGIN                                 |
| mysql-bin.000002 | 548 | Table_map      |         1 |         596 | table_id: 109 (bbs.tb1)               |
| mysql-bin.000002 | 596 | Write_rows     |         1 |         639 | table_id: 109 flags: STMT_END_F       |
| mysql-bin.000002 | 639 | Xid            |         1 |         670 | COMMIT /* xid=16 */                   |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
12 rows in set (0.01 sec)

该命令还包含其他选项以便灵活查看，以pos219下面起始到第三个结束。

mysql> show binlog events in 'mysql-bin.000002' from 219 limit 1,3;
+------------------+-----+-------------+-----------+-------------+---------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                            |
+------------------+-----+-------------+-----------+-------------+---------------------------------+
| mysql-bin.000002 | 290 | Table_map   |         1 |         338 | table_id: 109 (bbs.tb1)         |
| mysql-bin.000002 | 338 | Delete_rows |         1 |         381 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000002 | 381 | Xid         |         1 |         412 | COMMIT /* xid=15 */             |
+------------------+-----+-------------+-----------+-------------+---------------------------------+
3 rows in set (0.00 sec)

SHOW BINARY LOGS 等价于 SHOW MASTER LOGS PURGE BINARY LOGS用于删除二进制日志。

如： PURGEBINARY LOGS TO 'mysql-bin.00010'; #把这个文件之前的其他文件都删除掉

PURGE BINARY LOGS BEFORE '2016-08-28 22:46:26'; #把指定时间之前的二进制文件删除了

RESET MASTER 与 RESET SLAVE 前者清空index文件中列出的所有二进制日志，重置index文件为空，并创建一个新的二进制日志文件，一般用于MASTER首次启动时。后者使SLAVE忘记其在MASTER二进制日志文件中的复制位置，它会删除master.info、relay-log.info 和所有中继日志文件并开始一个新的中继日志文件，以便于开始一个干净的复制。在使用RESET SLAVE前需先关闭SLAVE复制线程。上述方式可以查看到服务器上存在的二进制日志文件及文件中的事件，但是想查看到文件中具体的内容并应于恢复场景还得借助mysqlbinlog这个工具。

语法格式： mysqlbinlog [options] log_file ... 输出内容会因日志文件的格式以及mysqlbinlog工具使用的选项不同而略不同。 mysqlbinlog的可用选项可参考man手册。二进制日志文件的格式包含行模式、语句模式和混合模式（也即有服务器决定在什么情况下记录什么类型的日志），基于语句的日志中事件信息包含执行的语句等，基于行的日志中事件信息包含的是行的变化信息等。混合模式的日志中两种类型的事件信息都会记录。为了便于查看记录了行变化信息的事件在当时具体执行了什么样的SQL语句可以使用mysqlbinlog工具的-v（--verbose）选项，该选项会将行事件重构成被注释掉的伪SQL语句，如果想看到更详细的信息可以将该选项给两次如-vv，这样可以包含一些数据类型和元信息的注释内容，如先切换到binlog所在的目录下

[root@mysql ~]# cd /usr/local/mysql/data
[root@mysql data]# mysqlbinlog mysql-bin.000001             #查看二进制文件

[root@mysql data]# mysqlbinlog -v mysql-bin.000001          #查看详细内容

[root@mysql data]# mysqlbinlog -vv mysql-bin.000001         #查看更详细内容

另外mysqlbinlog和可以通过--read-from-remote-server选项从远程服务器读取二进制日志文件，这时需要一些而外的连接参数，如-h，-P，-p，-u等，这些参数仅在指定了--read-from-remote-server后有效。无论是本地二进制日志文件还是远程服务器上的二进制日志文件，无论是行模式、语句模式还是混合模式的二进制日志文件，被mysqlbinlog工具解析后都可直接应用与MySQL Server进行基于时间点、位置或数据库的恢复。

下面我们就来演示如何使用binlog恢复之前删除数据（id=2那条记录）注意：在实际生产环境中，如果遇到需要恢复数据库的情况，不要让用户能访问到数据库，以避免新的数据插入进来，以及在主从的环境下，关闭主从。查看binlog文件，从中找出delete from test.tb1 where id=2

[root@mysql ~]# cd /usr/local/mysql/data
[root@mysql data]# mysqlbinlog -v mysql-bin.000002 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230324  8:44:33 server id 1  end_log_pos 123 CRC32 0xcbae27e2  Start: binlog v 4, server v 5.7.40-log created 230324  8:44:33
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
cfIcZA8BAAAAdwAAAHsAAAABAAQANS43LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AeInrss=
'/*!*/;
# at 123
#230324  8:44:33 server id 1  end_log_pos 154 CRC32 0xc6b0dd29  Previous-GTIDs
# [empty]
# at 154
#230324  8:45:29 server id 1  end_log_pos 219 CRC32 0x59f973f8  Anonymous_GTID  last_committed=0    sequence_number=1   rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#230324  8:45:29 server id 1  end_log_pos 290 CRC32 0xe9a3eaa9  Query   thread_id=3   exec_time=0   error_code=0
SET TIMESTAMP=1679618729/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 290
#230324  8:45:29 server id 1  end_log_pos 338 CRC32 0xe66de950  Table_map: `bbs`.`tb1` mapped to number 109
# at 338
#230324  8:45:29 server id 1  end_log_pos 381 CRC32 0x6c2d4b4b  Delete_rows: table id 109 flags: STMT_END_F

BINLOG '
qfIcZBMBAAAAMAAAAFIBAAAAAG0AAAAAAAEAA2JicwADdGIxAAIDDwI8AAJQ6W3m
qfIcZCABAAAAKwAAAH0BAAAAAG0AAAAAAAEAAgAC//wCAAAAAmw0S0stbA==
'/*!*/;
### DELETE FROM `bbs`.`tb1`
### WHERE
###   @1=2
###   @2='l4'
# at 381
#230324  8:45:29 server id 1  end_log_pos 412 CRC32 0x09d061ff  Xid = 15
COMMIT/*!*/;
# at 412
#230324  8:45:49 server id 1  end_log_pos 477 CRC32 0x00977c6e  Anonymous_GTID  last_committed=1    sequence_number=2   rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 477
#230324  8:45:49 server id 1  end_log_pos 548 CRC32 0x8ea03cb0  Query   thread_id=3   exec_time=0   error_code=0
SET TIMESTAMP=1679618749/*!*/;
BEGIN
/*!*/;
# at 548
#230324  8:45:49 server id 1  end_log_pos 596 CRC32 0xe32cd3c5  Table_map: `bbs`.`tb1` mapped to number 109
# at 596
#230324  8:45:49 server id 1  end_log_pos 639 CRC32 0x30b3d697  Write_rows: table id 109 flags: STMT_END_F

BINLOG '
vfIcZBMBAAAAMAAAAFQCAAAAAG0AAAAAAAEAA2JicwADdGIxAAIDDwI8AALF0yzj
vfIcZB4BAAAAKwAAAH8CAAAAAG0AAAAAAAEAAgAC//wDAAAAAnc1l9azMA==
'/*!*/;
### INSERT INTO `bbs`.`tb1`
### SET
###   @1=3
###   @2='w5'
# at 639
#230324  8:45:49 server id 1  end_log_pos 670 CRC32 0xcfda2a0b  Xid = 16
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

从中可以看出delete事件发生position是290，事件结束position是412 恢复流程：直接用bin-log日志将数据库恢复到删除位置290前,然后跳过故障点,再进行恢复下面所有的操作,命令如下由于之前没有做过全库备份，所以要使用所有binlog日志恢复，所以生产环境中需要很长时间恢复，导出相关binlog文件。

[root@mysql ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001> /opt/mybin.000001.sql
[root@mysql ~]# mysqlbinlog --stop-position=290 /usr/local/mysql/data/mysql-bin.000002> /opt/mybin.290.sql
[root@mysql ~]# mysqlbinlog --start-position=412 /usr/local/mysql/data/mysql-bin.000002> /opt/mybin.412.sql

删除bbs数据库

mysql> drop database bbs;
Query OK, 1 row affected (0.09 sec)

利用binlog恢复数据

逐步恢复，查看是否恢复全表。

[root@mysql ~]# mysql -uroot -p123 < /opt/mybin.000001.sql 
#验证
mysql> select * from bbs.tb1;
+----+------+
| id | name |
+----+------+
|  1 | z3   |
|  2 | l4   |
+----+------+
2 rows in set (0.00 sec)
[root@mysql ~]# mysql -uroot -p123 < /opt/mybin.290.sql 
#验证
mysql> select * from bbs.tb1;
+----+------+
| id | name |
+----+------+
|  1 | z3   |
|  2 | l4   |
+----+------+
2 rows in set (0.00 sec)
[root@mysql ~]# mysql -uroot -p123 < /opt/mybin.412.sql
#验证
mysql> select * from bbs.tb1;
+----+------+
| id | name |
+----+------+
|  1 | z3   |
|  2 | l4   |
|  3 | w5   |
+----+------+
3 rows in set (0.00 sec)

可以看到完整的都恢复过来了 mysqlbinlog 可以使用多个选项，常见的选项有以下几个：

--start-datetime 从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件。

--stop-datetime 从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件。

--start-position从二进制日志中读取指定position 事件位置作为开始。

--stop-position 从二进制日志中读取指定position 事件位置作为事件截至。

mysqldump

mysqldump是mysql用于备份和数据转移的一个工具。它主要产生一系列的SQL语句，可以封装到文件，该文件包含有所有重建你的数据库所需要的 SQL命令如CREATE DATABASE，CREATE TABLE，INSERT等等。可以用来实现轻量级的快速迁移或恢复数据库。 mysqldump 是将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。 mysqldump一般在数据量很小的时候（几个G）可以用于备份。当数据量比较大的情况下，就不建议用mysqldump工具进行备份了。

数据库的导出

导出对象说明：mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作

    #导出单表
[root@mysql ~]# mysqldump -uroot -p123 库名  表名 > 备份路径
    #导出多表
[root@mysql ~]# mysqldump -uroot -p123 库名 表名1 表名2 ...> 备份路径
    #导出所有表
[root@mysql ~]# mysqldump -uroot -p123 库名 > 备份路径
    #导出单库
[root@mysql ~]# mysqldump -uroot -p123 --databases[-B] 库名  > 备份路径
    #导出多库
[root@mysql ~]# mysqldump -uroot -p123 --databases[-B] 库名1 库名2 ... > 备份路径
    #导出所有库
[root@mysql ~]# mysqldump -uroot -p123 --all-databases[-A] > 备份路径
    #--flush-logs这个选项就会完整备份的时候重新开启一个新binlog
[root@mysql ~]# mysqldump -uroot -p --flush-logs 库名 > 备份路径

数据库的导入

[root@mysql ~]# mysql -uroot -p123 库名 < 备份路径

mysql安装自带的一些库丢失，靠备份导入却不能实现恢复，需要初始化库后在导入才能恢复。那核心库丢失如何恢复？下面跟着步骤备份库，删除库，并且恢复回来。

mysqldump+binlog

在前面我们介绍了mysql的binlog和mysqldump工具，下面我们来学习如何实现mysqldump全库备份+binlog的数据恢复。

先开启二进制日志

[root@mysql ~]# vim /etc/my.cnf
log_bin=mysql-bin
server_id=1
[root@mysql ~]# systemctl restart mysqld

检查开启binlog 先创建一些原始数据

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

mysql> use test_db;
Database changed
mysql> create table tb1(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into tb1(name) values('tom1'),('tom2');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
+----+------+
2 rows in set (0.00 sec)

方案：mysqldump全库备份+binlog还原

1、mysqldump备份方案：每周一凌晨1点全库备份

2、备份步骤

（1）创建备份目录

[root@mysql ~]# mkdir -p /opt/mysqlbackup/daily

（2）全库备份这里我们模拟周一的完整备份数据库任务

[root@mysql ~]# mysqldump -uroot -p123 --flush-logs test_db > /opt/mysqlbackup/test_db_`date +%Y%m%d_%H%M%S`.sql            #备份库 时间戳命名
[root@mysql ~]# ll /opt/mysqlbackup/
总用量 4
drwxr-xr-x. 2 root root    6 3月  29 13:45 daily
-rw-r--r--. 1 root root 1871 3月  29 13:46 test_db_20230329_134659.sql

备份mysqldump全库备份之前的binlog日志文（注：生产环境中可能不只一个binlog文件）

[root@mysql ~]# cp /usr/local/mysql/data/mysql-bin.000001 /opt/mysqlbackup/daily/
[root@mysql ~]# mysql -uroot -p123 -e "purge binary logs to 'mysql-bin.000002'"

登录mysql模拟下操作失误,将数据修改错误了。

mysql> use test_db;
Database changed
mysql> delete from tb1 where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb1(name) values('tom3');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

备份自mysqldump之后的binlog日志文件

[root@mysql ~]# cp /usr/local/mysql/data/mysql-bin.000002 /opt/mysqlbackup/daily/

上面的模拟的误操作是删除了id=1的记录

（3）现在我们使用mysqldump的全库备份和binlog来恢复数据。使用mysqldump的备份进行全库恢复

[root@mysql ~]# mysql -uroot -p123 test_db < /opt/mysqlbackup/test_db_20230329_135149.sql

查询数据

[root@mysql ~]# mysql -uroot -p123 -e "select * from test_db.tb1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
+----+------+

从显示结果可以看到使用mysqldump备份将数据还原到了备份时的状态，刚才删除的数据（id=2）恢复回来了，但备份后产生的数据却丢失了所以还得利用binlog进一步还原因为删除是在全库备份后发生的，而mysqldump全库备份时使用--flush-logs选项，所以只需要分析全库备份后的binlog即mysql-bin.000002。

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |      1853 |
+------------------+-----------+
1 row in set (0.01 sec)

查看mysql-bin.000002中的事件，可以看到有删除事件

mysql> show binlog events in 'mysql-bin.000002';
#省略部分内容
| mysql-bin.000002 |  219 | Query          |         1 |         294 | BEGIN                                                                                                                                                                                     |
| mysql-bin.000002 |  294 | Table_map      |         1 |         346 | table_id: 109 (test_db.tb1)                                                                                                                                                               |
| mysql-bin.000002 |  346 | Delete_rows    |         1 |         391 | table_id: 109 flags: STMT_END_F                                                                                                                                                           |
| mysql-bin.000002 |  391 | Xid            |         1 |         422 | COMMIT /* xid=43 */

使用mysqlbinlog 命令可以查看备份的binlog文件的详细事件。恢复流程：我们直接用bin-log日志将数据库恢复到删除位置前,然后跳过故障点,再进行恢复删除后的所有操作。

[root@mysql ~]# mysqlbinlog -v /opt/mysqlbackup/daily/mysql-bin.000002 
#省略查看内容

我们先用mysqlbinlog命令找到delete那条语句的位置

# at 219
#230329 13:53:58 server id 1  end_log_pos 294 CRC32 0x557ff3dc  Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1680069238/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 294
#230329 13:53:58 server id 1  end_log_pos 346 CRC32 0xa80266ea  Table_map: `test_db`.`tb1` mapped to number 109
# at 346
#230329 13:53:58 server id 1  end_log_pos 391 CRC32 0x69164e4d  Delete_rows: table id 109 flags: STMT_END_F

BINLOG '
dtIjZBMBAAAANAAAAFoBAAAAAG0AAAAAAAEAB3Rlc3RfZGIAA3RiMQACAw8CPAAC6mYCqA==
dtIjZCABAAAALQAAAIcBAAAAAG0AAAAAAAEAAgAC//wBAAAABHRvbTFNThZp
'/*!*/;
### DELETE FROM `test_db`.`tb1`
### WHERE
###   @1=1
###   @2='tom1'
# at 391
#230329 13:53:58 server id 1  end_log_pos 422 CRC32 0xfa0ce547  Xid = 43
COMMIT/*!*/;

通过mysqlbinlog命令所显示的结果可以看到误操作delete的开始postion为219，结束position是422。从二进制日志中读取指定position=219事件位置作为截至，即把数据恢复到delete删除前

[root@mysql ~]# mysqlbinlog --stop-position=219 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -uroot -p123

从二进制日志中读取指定position=422事件位置作为开始，即跳过删除事件，恢复删除事件之后对数据的正常操作

[root@mysql ~]# mysqlbinlog --start-position=422 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -uroot -p123

查看恢复结果：

[root@mysql ~]# mysql -uroot -p123 -e "select * from test_db.tb1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
|  3 | tom3 |
+----+------+

从上面显示可以看出数据恢复到正常状态生产环境中Mysql数据库的备份是周期性重复的操作，所以通常是要编写脚本实现，通过crond计划任务周期性执行备份脚本 mysqldump

备份方案：周日凌晨1点全库备份周一到周六凌晨每隔4个小时增量备份一次设置crontab任务，每天执行备份脚本

[root@mysql ~]# crontab -e
#每个星期日凌晨1:00执行完全备份脚本
0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1
#周一到周六每隔4个小时增量备份一次
0 */4 * * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1

mysqlfullbackup.sh脚本内容：

[root@mysql ~]# vim mysqlfullbackup.sh
#!/bin/sh
# Name:mysqlFullBackup.sh
# 定义数据库目录
mysqlDir=/usr/local/mysql
# 定义用于备份数据库的用户名和密码
user=root
userpwd=123
dbname=test_db
# 定义备份目录
databackupdir=/opt/mysqlbackup
[ ! -d $databackupdir ] && mkdir $databackupdir
# 定义邮件正文文件
emailfile=$databackupdir/email.txt
# 定义邮件地址
email=root@localhost.localdomain
# 定义备份日志文件
logfile=$databackupdir/mysqlbackup.log
DATE=`date -I`
echo "" > $emailfile
echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
cd $databackupdir
# 定义备份文件名
dumpfile=mysql_$DATE.sql
gzdumpfile=mysql_$DATE.sql.tar.gz
# 使用mysqldump备份数据库，请根据具体情况设置参数
$mysqlDir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile 
# 压缩备份文件
if [ $? -eq 0 ]; then
 tar czf $gzdumpfile $dumpfile >> $emailfile 2>&1
 echo "BackupFileName:$gzdumpfile" >> $emailfile
 echo "DataBase Backup Success!" >> $emailfile
 rm -f $dumpfile
else
 echo "DataBase Backup Fail!" >> $emailfile
fi
# 写日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile
# 发送邮件通知
cat $emailfile | mail -s "MySQL Backup" $email

mysqldailybackup.sh脚本内容：

[root@mysql ~]# vim mysqldailbackup.sh
#!/bin/sh
# Name:mysqlDailyBackup.sh
# 定义数据库目录和数据目录
mysqldir=/usr/local/mysql
datadir=$mysqldir/data
# 定义用于备份数据库的用户名和密码
user=root
userpwd=123456
# 定义备份目录，每日备份文件备份到$dataBackupDir/daily
databackupdir=/opt/mysqlbackup
dailybackupdir=$databackupdir/daily
[ ! -d $dailybackupdir ] && mkdir -p $databackupdir/daily
# 定义邮件正文文件
emailfile=$databackupdir/email.txt
# 定义邮件地址
email=root@localhost.localdomain
# 定义日志文件
logfile=$databackupdir/mysqlbackup.log
echo "" > $emailfile
echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
#
# 刷新日志，使数据库使用新的二进制日志文件
$mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs
cd $datadir
# 得到二进制日志列表
filelist=`cat mysql-bin.index`
icounter=0
for file in $filelist
do
 icounter=`expr $icounter + 1` 
done
nextnum=0
ifile=0
for file in $filelist
do
 binlogname=`basename $file`
 nextnum=`expr $nextnum + 1`
# 跳过最后一个二进制日志（数据库当前使用的二进制日志文件）
 if [ $nextnum -eq $icounter ]; then
 echo "Skip lastest!" > /dev/null
 else
 dest=$dailybackupdir/$binlogname
# 跳过已经备份的二进制日志文件
 if [ -e $dest ]; then
 echo "Skip exist $binlogname!" > /dev/null
 else
# 备份日志文件到备份目录
 cp $binlogname $dailybackupdir
 if [ $? -eq 0 ]; then
 ifile=`expr $ifile + 1`
 echo "$binlogname backup success!" >> $emailfile
        fi
    fi
 fi
done
if [ $ifile -eq 0 ];then
 echo "No Binlog Backup!" >> $emailfile
else
 echo "Backup $ifile File(s)." >> $emailfile
 echo "Backup MySQL Binlog OK!" >> $emailfile
fi
# 发送邮件通知
cat $emailfile | mail -s "MySQL Backup" $email
# 写日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile

MySQL中binlog+dump备份还原详细教程_Mysql

目录