二进制日志记录开关,二进制日志用于记录mysql数据更新的日志文件,对于非变更数据的操作不予记录,比如select、show等。
show variables like 'log_bin';
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.01 sec)
说明开关是没有开启的。
show variables like '%log_bin%';
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.00 sec)
开启二进制日志
修改my.cnf
配置文件
vim /etc/my.cnf
这个仅仅是针对linux系统的,my.cnf
文件是mysql的配置文件,不同系统,my.cnf
的位置不尽相同。如果不知道当前系统读取mysql配置的地方,修改一个错误的配置,会使配置不生效。如何查看当前正在使用的mysql配置?可以使用以下命令查找:
[root@cdh1 ~]# which mysqld
/usr/sbin/mysqld
[root@cdh1 ~]# /usr/sbin/mysqld --verbose --help|grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
通过上面两个命令,我们了解到,my.cnf
是按照上面的顺序读取的,如果前一个没有就读下一个配置。
一般Debian下,/etc/mysql/my.cnf
才是MySQL读取的配置文件,而不是/etc/my.cnf
。
my.cnf
即mysql配置文件通常分成多个部分,每个部分的开头是一个用方括号括起来的分段名称。MySQL程序通常读取跟它同名的分段部分,许多客户端程序还会读取client部分,这是一个存放公用设置的地方。服务器通常读取mysqld这一段。一定要确认配置项放在了文件正确的分段中,否则配置是不会生效的。
运行vim /etc/my.cnf
后,可看到my.cnf
的内容:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
skip-name-resolve
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names=1
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 64M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
在[mysqld]后追加以下内容:
server-id=1
log-bin=mysql-bin
binlog-format=ROW
- server-id Mysql的Id属性的唯一值,同步时用于区分主从、主主实例等。
- log-bin 打开二进制日志功能。主从复制里,只有主节点才会打开二进制日志功能。
- binlog-format 二进制日志的模式与配置
binlog-format 一共有三种模式:
- 基于SQL语句的复制(Statement-Based Replication,SBR)。
- 基于行的复制(Row-Based Replication,RBR)。
- 混合模式复制(Mixed-Based Replication,MBR)。
mysql默认使用statement level模式,对于主从复制来说,需要使用行的复制。
添加完后,重启mysql
[root@cdh1 ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
然后登陆数据库查看:
[root@cdh1 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.32-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.02 sec)
binlog是开启了。
查看二进制文件的名称和大小
使用命令show binary logs;
查看
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql>
查看binlog内容,执行命令system mysqlbinlog <log文件路径>
:
mysql> system mysqlbinlog /var/lib/mysql/mysql-bin.000001;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230910 15:31:13 server id 1 end_log_pos 123 CRC32 0x197399f9 Start: binlog v 4, server v 5.7.32-log created 230910 15:31:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
wXD9ZA8BAAAAdwAAAHsAAAABAAQANS43LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADBcP1kEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AfmZcxk=
'/*!*/;
# at 123
#230910 15:31:13 server id 1 end_log_pos 154 CRC32 0xfe36874d Previous-GTIDs
# [empty]
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*/;
mysql>
如果发现上述命令观察不容易理解,可以使用show binlog events in ‘mysql-bin.000001’;
来观察。
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql>
指定查看某行之后的内容:
mysql> show binlog events in 'mysql-bin.000001' from 123;
+------------------+-----+----------------+-----------+-------------+------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+------+
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
+------------------+-----+----------------+-----------+-------------+------+
1 row in set (0.00 sec)
mysql>
指定分页参数:
mysql> show binlog events in 'mysql-bin.000001' from 4 limit 2;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql>
limit表示指定数量。通过from和limit达到的分页的目的。
将二进制文件转换为文本文件:
使用mysqlbinlog命令将binlog二进制文件转换为文本文件:
[root@cdh1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001 > /log.txt
root@cdh1 /]# cat /log.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230910 15:31:13 server id 1 end_log_pos 123 CRC32 0x197399f9 Start: binlog v 4, server v 5.7.32-log created 230910 15:31:13 at startup
ROLLBACK/*!*/;
BINLOG '
wXD9ZA8BAAAAdwAAAHsAAAAAAAQANS43LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADBcP1kEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AfmZcxk=
'/*!*/;
# at 123
#230910 15:31:13 server id 1 end_log_pos 154 CRC32 0xfe36874d Previous-GTIDs
# [empty]
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*/;
[root@cdh1 /]#
忽略注释内容,可以看到一些数据库操作的记录。这便是binlog的查看方式。
通过binlog文件恢复mysql数据
在test库中新增一张student表:
mysql> CREATE TABLE IF NOT EXISTS `student` ( `id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR ( 100 ) NOT NULL, `sex` INT DEFAULT 1, `age` INT, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8;
Query OK, 0 rows affected (0.02 sec)
向表中插入三条测试数据:
mysql> INSERT INTO student (name,sex,age) VALUES ('张三',1,18);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO student (name,sex,age) VALUES ('李四',2,19);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO student (name,sex,age) VALUES ('王五',1,20);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (name,sex,age) values ('葫芦瓢',1,35);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+-----------+------+------+
| id | name | sex | age |
+----+-----------+------+------+
| 1 | 张三 | 1 | 18 |
| 2 | 李四 | 2 | 19 |
| 3 | 王五 | 1 | 20 |
| 4 | 葫芦瓢 | 1 | 35 |
+----+-----------+------+------+
4 rows in set (0.00 sec)
现在要对王五进行删除:
mysql> delete from student where name = '王五';
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-----------+------+------+
| id | name | sex | age |
+----+-----------+------+------+
| 1 | 张三 | 1 | 18 |
| 2 | 李四 | 2 | 19 |
| 4 | 葫芦瓢 | 1 | 35 |
+----+-----------+------+------+
3 rows in set (0.00 sec)
查看下binlog日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000004 | 1974 |
| mysql-bin.000005 | 154 |
| mysql-bin.000006 | 154 |
| mysql-bin.000007 | 177 |
| mysql-bin.000008 | 413 |
| mysql-bin.000009 | 177 |
| mysql-bin.000010 | 1913 |
+------------------+-----------+
7 rows in set (0.00 sec)
通过show binary logs;
命令查看当前最新的二进制文件,发现最新的二进制文件是mysql-bin.000010
查看binlog文件内容
这里有两种方式:
- mysql命令
- mysql工具
对于mysql命令如下:
mysql> system mysqlbinlog /var/lib/mysql/mysql-bin.000010;
使用mysql工具,将binlog转换为普通的文本文件,然后阅读文件里的内容
[root@cdh1 /]# mysqlbinlog /var/lib/mysql/mysql-bin.000010 > /log1.txt
[root@cdh1 /]# cat /log1.txt
通过阅读文件内容如下:
mysql> system mysqlbinlog /var/lib/mysql/mysql-bin.000010;
use `mytest`/*!*/;
SET TIMESTAMP=1699672231/*!*/;
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/*!*/;
create table if not exists `student` (`id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR ( 100 ) NOT NULL, `sex` INT DEFAULT 1, `age` INT, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8
/*!*/;
# at 495
#231111 11:11:24 server id 1 end_log_pos 560 CRC32 0x388f7839 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 560
#231111 11:11:24 server id 1 end_log_pos 634 CRC32 0xd78113a4 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1699672284/*!*/;
BEGIN
/*!*/;
# at 634
#231111 11:11:24 server id 1 end_log_pos 691 CRC32 0x65990a83 Table_map: `mytest`.`student` mapped to number 109
# at 691
#231111 11:11:24 server id 1 end_log_pos 747 CRC32 0xd3b5afcd Write_rows: table id 109 flags: STMT_END_F
BINLOG '
3PBOZRMBAAAAOQAAALMCAAAAAG0AAAAAAAEABm15dGVzdAAHc3R1ZGVudAAEAw8DAwIsAQyDCpll
3PBOZR4BAAAAOAAAAOsCAAAAAG0AAAAAAAEAAgAE//ABAAAABgDlvKDkuIkBAAAAEgAAAM2vtdM=
'/*!*/;
# at 747
#231111 11:11:24 server id 1 end_log_pos 778 CRC32 0x3bf1fa1a Xid = 18
COMMIT/*!*/;
# at 778
#231111 15:11:45 server id 1 end_log_pos 843 CRC32 0x626bcf13 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 843
#231111 15:11:45 server id 1 end_log_pos 917 CRC32 0xbac0b136 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1699686705/*!*/;
BEGIN
/*!*/;
# at 917
#231111 15:11:45 server id 1 end_log_pos 974 CRC32 0xad33ae7a Table_map: `mytest`.`student` mapped to number 109
# at 974
#231111 15:11:45 server id 1 end_log_pos 1030 CRC32 0x50ab65d1 Write_rows: table id 109 flags: STMT_END_F
BINLOG '
MSlPZRMBAAAAOQAAAM4DAAAAAG0AAAAAAAEABm15dGVzdAAHc3R1ZGVudAAEAw8DAwIsAQx6rjOt
MSlPZR4BAAAAOAAAAAYEAAAAAG0AAAAAAAEAAgAE//ACAAAABgDmnY7lm5sCAAAAEwAAANFlq1A=
'/*!*/;
# at 1030
#231111 15:11:45 server id 1 end_log_pos 1061 CRC32 0xd305aad1 Xid = 56
COMMIT/*!*/;
# at 1061
#231111 16:46:37 server id 1 end_log_pos 1126 CRC32 0x81fc9d92 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1126
#231111 16:46:37 server id 1 end_log_pos 1200 CRC32 0x261d0f7a Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1699692397/*!*/;
BEGIN
/*!*/;
# at 1200
#231111 16:46:37 server id 1 end_log_pos 1257 CRC32 0x5e0a933c Table_map: `mytest`.`student` mapped to number 109
# at 1257
#231111 16:46:37 server id 1 end_log_pos 1313 CRC32 0x8fc14b9c Write_rows: table id 109 flags: STMT_END_F
BINLOG '
bT9PZRMBAAAAOQAAAOkEAAAAAG0AAAAAAAEABm15dGVzdAAHc3R1ZGVudAAEAw8DAwIsAQw8kwpe
bT9PZR4BAAAAOAAAACEFAAAAAG0AAAAAAAEAAgAE//ADAAAABgDnjovkupQBAAAAFAAAAJxLwY8=
'/*!*/;
# at 1313
#231111 16:46:37 server id 1 end_log_pos 1344 CRC32 0x00dfa9c8 Xid = 57
COMMIT/*!*/;
# at 1344
#231111 16:49:03 server id 1 end_log_pos 1409 CRC32 0xc5e77bba Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1409
#231111 16:49:03 server id 1 end_log_pos 1483 CRC32 0xf73e44f8 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1699692543/*!*/;
BEGIN
/*!*/;
# at 1483
#231111 16:49:03 server id 1 end_log_pos 1540 CRC32 0xff33508a Table_map: `mytest`.`student` mapped to number 109
# at 1540
#231111 16:49:03 server id 1 end_log_pos 1599 CRC32 0xf0d2e310 Write_rows: table id 109 flags: STMT_END_F
BINLOG '
/z9PZRMBAAAAOQAAAAQGAAAAAG0AAAAAAAEABm15dGVzdAAHc3R1ZGVudAAEAw8DAwIsAQyKUDP/
/z9PZR4BAAAAOwAAAD8GAAAAAG0AAAAAAAEAAgAE//AEAAAACQDokavoiqbnk6IBAAAAIwAAABDj
0vA=
'/*!*/;
# at 1599
#231111 16:49:03 server id 1 end_log_pos 1630 CRC32 0x4f43662b Xid = 59
COMMIT/*!*/;
# at 1630
#231111 16:50:06 server id 1 end_log_pos 1695 CRC32 0x6c8aebf5 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1695
#231111 16:50:06 server id 1 end_log_pos 1769 CRC32 0xae6f20e5 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1699692606/*!*/;
BEGIN
/*!*/;
# at 1769
#231111 16:50:06 server id 1 end_log_pos 1826 CRC32 0xf4a30819 Table_map: `mytest`.`student` mapped to number 109
# at 1826
#231111 16:50:06 server id 1 end_log_pos 1882 CRC32 0xbede0a74 Delete_rows: table id 109 flags: STMT_END_F
BINLOG '
PkBPZRMBAAAAOQAAACIHAAAAAG0AAAAAAAEABm15dGVzdAAHc3R1ZGVudAAEAw8DAwIsAQwZCKP0
PkBPZSABAAAAOAAAAFoHAAAAAG0AAAAAAAEAAgAE//ADAAAABgDnjovkupQBAAAAFAAAAHQK3r4=
'/*!*/;
# at 1882
#231111 16:50:06 server id 1 end_log_pos 1913 CRC32 0xc2bd5e7e Xid = 61
COMMIT/*!*/;
Write_rows代表写入的操作,Delete_rows代表删除操作,日志里还有其他操作,分别是:
- Start
- Previous-GTIDs
- Anonymous_GTID
- Query
- Table_map
- Xid
这些都是binlog里的事件类型,也就是event_type,具体的解释可见官方文档。
从日志内容中我们能看到创建表的记录:
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/*!*/;
create table if not exists `student` (`id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR ( 100 ) NOT NULL, `sex` INT DEFAULT 1, `age` INT, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8
/*!*/;
# at 495
#231111 11:11:24 server id 1 end_log_pos 560 CRC32 0x388f7839 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
注释中的end_log_pos代表每次操作的所在行,如果想恢复到该操作之前的状态,需要找到指定操作的end_log_pos行就要按照指定pos行数来恢复。
上述的操作中,我们插入了4条数据,可以看到有4条Write_rows
操作
#231111 11:11:24 server id 1 end_log_pos 747 CRC32 0xd3b5afcd Write_rows: table id 109 flags: STMT_END_F
#231111 15:11:45 server id 1 end_log_pos 1030 CRC32 0x50ab65d1 Write_rows: table id 109 flags: STMT_END_F
#231111 16:46:37 server id 1 end_log_pos 1313 CRC32 0x8fc14b9c Write_rows: table id 109 flags: STMT_END_F
#231111 16:49:03 server id 1 end_log_pos 1599 CRC32 0xf0d2e310 Write_rows: table id 109 flags: STMT_END_F
对于需要提交事务的操作都有会有BEGIN和COMMIT的操作记录,所以我们在指定binlog命令运行范围时对于事务类的操作需要把BEGIN和COMMIT包含在范围内。
然后下一个操作开始前都是以at开头的,可以看下binlog里面的内容,看看at是不是对应上一次操作的end_log_pos对应的值。所以以at确定范围也具有参考意义。
我们一共插入了四条数据,其中第三条是插入王五数据的操作,由于删除的是王五的数据,如果想恢复王五的数据,就需要把插入王五的那条数据操作命令再运行一遍即可,但要注意不能丢了事务的操作:
BEGIN
/*!*/;
# at 1200
#231111 16:46:37 server id 1 end_log_pos 1257 CRC32 0x5e0a933c Table_map: `mytest`.`student` mapped to number 109
# at 1257
#231111 16:46:37 server id 1 end_log_pos 1313 CRC32 0x8fc14b9c Write_rows: table id 109 flags: STMT_END_F
BINLOG '
bT9PZRMBAAAAOQAAAOkEAAAAAG0AAAAAAAEABm15dGVzdAAHc3R1ZGVudAAEAw8DAwIsAQw8kwpe
bT9PZR4BAAAAOAAAACEFAAAAAG0AAAAAAAEAAgAE//ADAAAABgDnjovkupQBAAAAFAAAAJxLwY8=
'/*!*/;
# at 1313
#231111 16:46:37 server id 1 end_log_pos 1344 CRC32 0x00dfa9c8 Xid = 57
COMMIT/*!*/;
# at 1344
我们看到BEGIN和COMMIT限制的范围是1200和1344,所以只需要指定这块命令重新运行下即可:
mysql> system mysqlbinlog /var/lib/mysql/mysql-bin.000010 --start-position=1200 --stop-position=1344 |mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
--start-position
--stop-position
用于限制binlog的范围
我们可以看到王五的这条数据已经恢复了,
mysql> select * from student;
+----+-----------+------+------+
| id | name | sex | age |
+----+-----------+------+------+
| 1 | 张三 | 1 | 18 |
| 2 | 李四 | 2 | 19 |
| 3 | 王五 | 1 | 20 |
| 4 | 葫芦瓢 | 1 | 35 |
+----+-----------+------+------+
4 rows in set (0.00 sec)
这种操作很鸡肋,如果数据多,你都不知道该恢复哪条数据,所以要对binlog数据进行转义操作,便于查看。
mysql> system mysqlbinlog --no-defaults --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000010;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#231111 10:32:06 server id 1 end_log_pos 123 CRC32 0xcda570d3 Start: binlog v 4, server v 5.7.32-log created 231111 10:32:06 at startup
ROLLBACK/*!*/;
# at 123
#231111 10:32:06 server id 1 end_log_pos 154 CRC32 0xe3265f58 Previous-GTIDs
# [empty]
# at 154
#231111 11:10:31 server id 1 end_log_pos 219 CRC32 0x0abf6480 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#231111 11:10:31 server id 1 end_log_pos 495 CRC32 0xc8e5a2a3 Query thread_id=2 exec_time=0 error_code=0
use `mytest`/*!*/;
SET TIMESTAMP=1699672231/*!*/;
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/*!*/;
create table if not exists `student` (`id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR ( 100 ) NOT NULL, `sex` INT DEFAULT 1, `age` INT, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8
/*!*/;
# at 495
#231111 11:11:24 server id 1 end_log_pos 560 CRC32 0x388f7839 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 560
#231111 11:11:24 server id 1 end_log_pos 634 CRC32 0xd78113a4 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1699672284/*!*/;
BEGIN
/*!*/;
# at 634
#231111 11:11:24 server id 1 end_log_pos 691 CRC32 0x65990a83 Table_map: `mytest`.`student` mapped to number 109
# at 691
#231111 11:11:24 server id 1 end_log_pos 747 CRC32 0xd3b5afcd Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `mytest`.`student`
### SET
### @1=1
### @2='张三'
### @3=1
### @4=18
# at 747
#231111 11:11:24 server id 1 end_log_pos 778 CRC32 0x3bf1fa1a Xid = 18
COMMIT/*!*/;
# at 778
#231111 15:11:45 server id 1 end_log_pos 843 CRC32 0x626bcf13 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 843
#231111 15:11:45 server id 1 end_log_pos 917 CRC32 0xbac0b136 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1699686705/*!*/;
BEGIN
/*!*/;
# at 917
#231111 15:11:45 server id 1 end_log_pos 974 CRC32 0xad33ae7a Table_map: `mytest`.`student` mapped to number 109
# at 974
#231111 15:11:45 server id 1 end_log_pos 1030 CRC32 0x50ab65d1 Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `mytest`.`student`
### SET
### @1=2
### @2='李四'
### @3=2
### @4=19
# at 1030
#231111 15:11:45 server id 1 end_log_pos 1061 CRC32 0xd305aad1 Xid = 56
COMMIT/*!*/;
# at 1061
#231111 16:46:37 server id 1 end_log_pos 1126 CRC32 0x81fc9d92 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1126
#231111 16:46:37 server id 1 end_log_pos 1200 CRC32 0x261d0f7a Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1699692397/*!*/;
BEGIN
/*!*/;
# at 1200
#231111 16:46:37 server id 1 end_log_pos 1257 CRC32 0x5e0a933c Table_map: `mytest`.`student` mapped to number 109
# at 1257
#231111 16:46:37 server id 1 end_log_pos 1313 CRC32 0x8fc14b9c Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `mytest`.`student`
### SET
### @1=3
### @2='王五'
### @3=1
### @4=20
# at 1313
#231111 16:46:37 server id 1 end_log_pos 1344 CRC32 0x00dfa9c8 Xid = 57
COMMIT/*!*/;
# at 1344
#231111 16:49:03 server id 1 end_log_pos 1409 CRC32 0xc5e77bba Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1409
#231111 16:49:03 server id 1 end_log_pos 1483 CRC32 0xf73e44f8 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1699692543/*!*/;
BEGIN
/*!*/;
# at 1483
#231111 16:49:03 server id 1 end_log_pos 1540 CRC32 0xff33508a Table_map: `mytest`.`student` mapped to number 109
# at 1540
#231111 16:49:03 server id 1 end_log_pos 1599 CRC32 0xf0d2e310 Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `mytest`.`student`
### SET
### @1=4
### @2='葫芦瓢'
### @3=1
### @4=35
# at 1599
#231111 16:49:03 server id 1 end_log_pos 1630 CRC32 0x4f43662b Xid = 59
COMMIT/*!*/;
# at 1630
#231111 16:50:06 server id 1 end_log_pos 1695 CRC32 0x6c8aebf5 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1695
#231111 16:50:06 server id 1 end_log_pos 1769 CRC32 0xae6f20e5 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1699692606/*!*/;
BEGIN
/*!*/;
# at 1769
#231111 16:50:06 server id 1 end_log_pos 1826 CRC32 0xf4a30819 Table_map: `mytest`.`student` mapped to number 109
# at 1826
#231111 16:50:06 server id 1 end_log_pos 1882 CRC32 0xbede0a74 Delete_rows: table id 109 flags: STMT_END_F
### DELETE FROM `mytest`.`student`
### WHERE
### @1=3
### @2='王五'
### @3=1
### @4=20
# at 1882
#231111 16:50:06 server id 1 end_log_pos 1913 CRC32 0xc2bd5e7e Xid = 61
COMMIT/*!*/;
# at 1913
#231111 11:10:31 server id 1 end_log_pos 1978 CRC32 0x1ea427d5 Anonymous_GTID last_committed=6 sequence_number=7 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
从上可以看出,解码后可以看到每条执行语句的明文记录。
#231111 16:46:37 server id 1 end_log_pos 1313 CRC32 0x8fc14b9c Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `mytest`.`student`
### SET
### @1=3
### @2='王五'
### @3=1
### @4=20
这样就可以知道根据具体业务参数搜索对指定数据进行恢复了,其中命令参数:
- 日志是以二进制方式存储的,不能直接读取,需要通过二进制日志查询工具mysqlbinlog查看
- mysqlbinlog -d logfilename 指定数据库名称,只列出指定的数据库相关操作
- mysqlbinlog -o 忽略掉日志中的前n行命令
- mysqlbinlog -v 将行事件(数据变更)重构为sql语句
- mysqlbinlog -vv 将行事件(数据变更)重构为sql语句,并输出注释信息
数据恢复的核心是找到关键操作行然后根据业务是选择指定行恢复,或者关键操作时间点之前的所有操作进行恢复。
删除二进制文件
删除某个日志文件之前的所有的二进制日志文件
首先先查看当前所有的二进制文件:
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
| mysql-bin.000002 | 1329 |
| mysql-bin.000003 | 154 |
| mysql-bin.000004 | 1974 |
| mysql-bin.000005 | 154 |
| mysql-bin.000006 | 154 |
| mysql-bin.000007 | 154 |
+------------------+-----------+
7 rows in set (0.00 sec)
比如,我想删除mysql-bin.000004
之前的二进制日志文件,那么就是要删除mysql-bin.000001
、mysql-bin.000002
、mysql-bin.000003
三个日志文件。
mysql> purge binary logs to 'mysql-bin.000004';
Query OK, 0 rows affected (0.02 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000004 | 1974 |
| mysql-bin.000005 | 154 |
| mysql-bin.000006 | 154 |
| mysql-bin.000007 | 154 |
+------------------+-----------+
4 rows in set (0.00 sec)
删除某个时间点之前的所有二进制日志文件
这个时候就要查看二进制日志文件是什么时候创建的,以及其他时间。
进入到二进制文件所在路径下,然后使用stat命令进行查看:
[root@cdh1 ~]# cd /var/lib/mysql
[root@cdh1 mysql]# ls
auto.cnf dolphinscheduler ibtmp1 mysql-bin.000007 private_key.pem sys
ca-key.pem ib_buffer_pool mysql mysql-bin.index public_key.pem test
ca.pem ibdata1 mysql-bin.000004 mysql.sock server-cert.pem
client-cert.pem ib_logfile0 mysql-bin.000005 mysql.sock.lock server-key.pem
client-key.pem ib_logfile1 mysql-bin.000006 performance_schema store
[root@cdh1 mysql]# stat mysql-bin.000005
File: ‘mysql-bin.000005’
Size: 154 Blocks: 8 IO Block: 4096 regular file
Device: fd00h/64768d Inode: 2188570 Links: 1
Access: (0640/-rw-r-----) Uid: ( 27/ mysql) Gid: ( 27/ mysql)
Access: 2023-11-01 20:03:56.870012935 +0800
Modify: 2023-11-01 20:03:56.858012935 +0800
Change: 2023-11-01 20:03:56.858012935 +0800
Birth: -
[root@cdh1 mysql]#
stat命令的Birth字段表示文件的创建时间。
但是从输出可知,Birth为空。如果stat命令查看xfs文件系统的文件时,如果要Birth字段不显示空置,必须满足几个条件,一个是xfs的版本为v5,另外,对操作系统内核版本也有要求(如下所示)
stat “now prints file creation time when supported by the file system, on GNU Linux systems with glibc >= 2.28 and kernel >= 4.11.”
查看当前系统的内核版本:
[root@cdh1 mysql]# uname -r
3.10.0-327.4.5.el7.x86_64
[root@cdh1 mysql]#
3.10是小于4.11所以stat命令Birth字段显示为空值。