mysql 使用binlog恢复表数据到某个时间点前 mysql binlog 按时间恢复恢复

mysql binlog备份与恢复

文章目录

• mysql binlog备份与恢复

• 1. binlog的使用场景
• 2. 启用binlog

• 2.1. 开启binlog
• 2.2. binlog_format详解
• 2.3. 常用binlog操作

• 3. binlog日志查看

• 3.1. 分析工具
• 3.2. 测试

• 3.2.1. 准备测试数据
• 3.2.2. 分析binlog输出

• 4. 参考

1. binlog的使用场景

数据库备份恢复

• mysqldump 每日0点备份
• binlog 每日0点之后，数据库中执行的sql日志

mysqldump+binlog，则可以恢复到当前数据库。

2. 启用binlog

2.1. 开启binlog

• mysql5.7 默认不开启
• mysql8.0 默认开启

在/etc/my.cnf中mysqld节添加开启binlog的配置。（直接在数据库中用set语句会在重启时失效）

第一种方式:

#开启binlog日志
log_bin=ON
#binlog日志的基本文件名
log_bin_basename=/var/lib/mysql/mysql-bin
#binlog文件的索引文件，管理所有binlog文件
log_bin_index=/var/lib/mysql/mysql-bin.index

第二种方式:

#此一行等同于上面log_bin三行
log-bin=/var/lib/mysql/mysql-bin

binlog配置（注意路经需要mysql用户有权限写）

[mysqld]
#设置日志三种格式：STATEMENT（基于语句）、ROW（基于行）、MIXED 。
binlog_format = STATEMENT

#设置日志路径，注意路经需要mysql用户有权限写
log-bin = /data/mysql/logs/mysql-bin.log
#设置binlog清理时间。
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 1000m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m

文件夹授权mysql

chown -R mysql:mysql /data/mysql/logs/
chown -R 755 /data/mysql/logs/

修改之后，重启MySQL生效。

service mysqld restart
/etc/init.d/mysql restart

查看是否修改成功：

show variables like 'log_bin%';

2.2. binlog_format详解

查看binlog_format

show variables like 'binlog_format';

binlog_format三种模式

• ROW 记录行信息
• STATEMENT 记录sql语句
• MIXED 默认使用statement，无法使用statement时，切换成row

事务隔离级别问题

• 若事务隔离级别为READ COMMITTED或READ UNCOMMITTED，则binlog_format不能选择STATEMENT。否则会报如下错误

Error updating database.  Cause: java.sql.SQLException: Cannot execute statement: 
impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. 
InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

文档解释：

If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is possible to change the logging format to STATEMENT, but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts.

• https://dev.mysql.com/doc/refman/8.0/en/binary-log-setting.html

原理分析：todo

2.3. 常用binlog操作

#查看日志开启状态 
show variables like 'log_%';
show variables like 'binlog_format%';

#查看最新一个binlog日志的编号名称，及其最后一个操作事件结束点 
show master status;

#查看所有binlog日志列表
show master logs;

#刷新log日志，立刻产生一个新编号的binlog日志文件，跟重启一个效果。`mysqldump -F` 也会刷新日志
flush logs;

#清空所有binlog日志 
reset master;

3. binlog日志查看

3.1. 分析工具

1、MySQL命令查看binlog信息

# 查询 BINLOG 格式
show VARIABLES like 'binlog_format';

# 查询 BINLOG 位置
show VARIABLES like 'datadir';

# 查询当前数据库中 BINLOG 名称及大小
show binary logs;

# 查看 master 正在写入的 BINLOG 信息
show master status;

# 通过 offset 查看 BINLOG 信息
show BINLOG events in 'mysql-bin.000034' limit 9000,  10;

# 通过 position 查看 binlog 信息
show BINLOG events in 'mysql-bin.000034' from 1742635 limit 10;

2、mysqlbinlog：mysql提供的一个binlog查看工具

mysqlbinlog命令说明

mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-position "4" --stop-position "3442" --database test_binlog  SHIZY-PC-S5-bin.000009

选项：
  --start-position=953                   起始pos点
  --stop-position=1437                   结束pos点
  --start-datetime="2013-11-29 13:18:54" 起始时间点
  --stop-datetime="2013-11-29 13:21:53"  结束时间点
  -u --user=name              Connect to the remote server as username.连接到远程主机的用户名
  -p --password[=name]        Password to connect to remote server.连接到远程主机的密码
  -h --host=name              Get the binlog from server.从远程主机上获取binlog日志
  --read-from-remote-server   Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志
    --database 仅仅列出配置的数据库信息
    --no-defaults 不读取配置文件, 指定的原因是由于 mysqlbinlog 无法识别 my.cnf 中的 default-character-set=utf8 指令
    --offset 跳过 log 中 N 个条目
    --verbose 将日志信息重建为原始的 SQL 陈述。
        -v 仅仅解释行信息
        -vv 不但解释行信息，还将 SQL 列类型的注释信息也解析出来
    --start-datetime 显示从指定的时间或之后的时间的事件。
        接收 DATETIME 或者 TIMESTRAMP 格式。
    --base64-output=decode-rows row模式生效。是否屏蔽BINLOG二进制数据
        AUTO 默认，自动显示 BINLOG 语句
        NEVER 不会显示任何的 BINLOG 语句，如果遇到必须显示的 BINLOG 语言，则会报错退出。
        DECODE-ROWS 过滤到BINLOG二进制数据。导出为sql时不能屏蔽（被屏蔽的BINLOG是行数据，若被屏蔽，则数据不会被恢复）

3.2. 测试

3.2.1. 准备测试数据

刷新日志文件

flush logs;

创建数据库

# 创建临时数据库
CREATE DATABASE IF NOT EXISTS test_binlog 
default charset utf8 COLLATE utf8_general_ci; 

# 创建临时表
CREATE TABLE `sync_test` (`id` int(11) 
NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL,  
PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建测试表，运行insert、delete语句，删除测试表

# 添加数据
insert into sync_test (id, name) values (null, 'aaa');
insert into sync_test (id, name) values (null, '啊啊啊');

# 查看添加的数据
select * from sync_test;

查看当前binlog

show master status;

导出binlog为sql

# statement模式
mysqlbinlog SHIZY19888-bin.000028 > binlogtest.sql
# row模式 -vv 在二进制文件上加上sql语句注释
mysqlbinlog -vv SHIZY19888-bin.000028 > binlogtest.sql

3.2.2. 分析binlog输出

1、row模式

BEGIN
/*!*/;
# at 316
#210603 22:13:23 server id 1  end_log_pos 433 CRC32 0x0fcb1b86 	Rows_query
# /* ApplicationName=DataGrip 2018.3.1 */ insert into sync_test (id, name) values (null, 'aaa')
# at 433
#210603 22:13:23 server id 1  end_log_pos 501 CRC32 0x93c48c0b 	Table_map: `test_binlog`.`sync_test` mapped to number 79
# at 501
#210603 22:13:23 server id 1  end_log_pos 546 CRC32 0xafa6c0cb 	Write_rows: table id 79 flags: STMT_END_F

BINLOG '
g+O4YB0BAAAAdQAAALEBAACAAF0vKiBBcHBsaWNhdGlvbk5hbWU9RGF0YUdyaXAgMjAxOC4zLjEg
Ki8gaW5zZXJ0IGludG8gc3luY190ZXN0IChpZCwgbmFtZSkgdmFsdWVzIChudWxsLCAnYWFhJymG
G8sP
g+O4YBMBAAAARAAAAPUBAAAAAE8AAAAAAAEAC3Rlc3RfYmlubG9nAAlzeW5jX3Rlc3QAAgMPAv0C
AAEBAAIBIQuMxJM=
g+O4YB4BAAAALQAAACICAAAAAE8AAAAAAAEAAgAC/wAVAAAAAwBhYWHLwKav
'/*!*/;
### INSERT INTO `test_binlog`.`sync_test`
### SET
###   @1=21 /* INT meta=0 nullable=0 is_null=0 */
###   @2='aaa' /* VARSTRING(765) meta=765 nullable=0 is_null=0 */
# at 546
#210603 22:13:23 server id 1  end_log_pos 577 CRC32 0x9af6f842 	Xid = 48
COMMIT/*!*/;
# at 577
#210603 22:13:23 server id 1  end_log_pos 656 CRC32 0x2c5c4bf5 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes	original_committed_timestamp=1622729603266412	immediate_commit_timestamp=1622729603266412	transaction_length=434
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1622729603266412 (2021-06-03 22:13:23.266412 ?D1迆㊣那℅?那㊣??)
# immediate_commit_timestamp=1622729603266412 (2021-06-03 22:13:23.266412 ?D1迆㊣那℅?那㊣??)
/*!80001 SET @@session.original_commit_timestamp=1622729603266412*//*!*/;
/*!80014 SET @@session.original_server_version=80017*//*!*/;
/*!80014 SET @@session.immediate_server_version=80017*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 656
#210603 22:13:23 server id 1  end_log_pos 738 CRC32 0xd8854af2 	Query	thread_id=8	exec_time=0	error_code=0
SET TIMESTAMP=1622729603/*!*/;
BEGIN
/*!*/;
# at 738
#210603 22:13:23 server id 1  end_log_pos 861 CRC32 0x809972e6 	Rows_query
# /* ApplicationName=DataGrip 2018.3.1 */ insert into sync_test (id, name) values (null, '????)
# at 861
#210603 22:13:23 server id 1  end_log_pos 929 CRC32 0x9cea7d71 	Table_map: `test_binlog`.`sync_test` mapped to number 79
# at 929
#210603 22:13:23 server id 1  end_log_pos 980 CRC32 0x22f7fa0e 	Write_rows: table id 79 flags: STMT_END_F

BINLOG '
g+O4YB0BAAAAewAAAF0DAACAAGMvKiBBcHBsaWNhdGlvbk5hbWU9RGF0YUdyaXAgMjAxOC4zLjEg
Ki8gaW5zZXJ0IGludG8gc3luY190ZXN0IChpZCwgbmFtZSkgdmFsdWVzIChudWxsLCAn5ZWK5ZWK
5ZWKJynmcpmA
g+O4YBMBAAAARAAAAKEDAAAAAE8AAAAAAAEAC3Rlc3RfYmlubG9nAAlzeW5jX3Rlc3QAAgMPAv0C
AAEBAAIBIXF96pw=
g+O4YB4BAAAAMwAAANQDAAAAAE8AAAAAAAEAAgAC/wAWAAAACQDllYrllYrllYoO+vci
'/*!*/;
### INSERT INTO `test_binlog`.`sync_test`
### SET
###   @1=22 /* INT meta=0 nullable=0 is_null=0 */
###   @2='???? /* VARSTRING(765) meta=765 nullable=0 is_null=0 */
# at 980
#210603 22:13:23 server id 1  end_log_pos 1011 CRC32 0xd9045b73 	Xid = 60
COMMIT/*!*/;

这里得到两条binlog，每条对应的insert语句有注释。

# INSERT INTO `test_binlog`.`sync_test` SET @1=21 @2='aaa'
BINLOG '
g+O4YB0BAAAAdQAAALEBAACAAF0vKiBBcHBsaWNhdGlvbk5hbWU9RGF0YUdyaXAgMjAxOC4zLjEg
Ki8gaW5zZXJ0IGludG8gc3luY190ZXN0IChpZCwgbmFtZSkgdmFsdWVzIChudWxsLCAnYWFhJymG
G8sP
g+O4YBMBAAAARAAAAPUBAAAAAE8AAAAAAAEAC3Rlc3RfYmlubG9nAAlzeW5jX3Rlc3QAAgMPAv0C
AAEBAAIBIQuMxJM=
g+O4YB4BAAAALQAAACICAAAAAE8AAAAAAAEAAgAC/wAVAAAAAwBhYWHLwKav
'

# INSERT INTO `test_binlog`.`sync_test` SET @1=22 @2='????'
BINLOG '
g+O4YB0BAAAAewAAAF0DAACAAGMvKiBBcHBsaWNhdGlvbk5hbWU9RGF0YUdyaXAgMjAxOC4zLjEg
Ki8gaW5zZXJ0IGludG8gc3luY190ZXN0IChpZCwgbmFtZSkgdmFsdWVzIChudWxsLCAn5ZWK5ZWK
5ZWKJynmcpmA
g+O4YBMBAAAARAAAAKEDAAAAAE8AAAAAAAEAC3Rlc3RfYmlubG9nAAlzeW5jX3Rlc3QAAgMPAv0C
AAEBAAIBIXF96pw=
g+O4YB4BAAAAMwAAANQDAAAAAE8AAAAAAAEAAgAC/wAWAAAACQDllYrllYrllYoO+vci
'

• 一条binlog，就是一条row数据。这里的binlog是可以直接作为sql语句执行的，执行后，会插入注释中的数据。
• 插入时，id是自增的，并没有在sql语句中指定，这里row模式把id直接写到binlog中了。可见row模式是直接输出sql执行结果中的行数据的。

中文乱码问题

• 这里注释中的中文乱码了，但是binlog执行到数据库中的数据，是没有乱码的。
• 乱码环境: windows + mysql 8.0 + utf8mb4
• 不乱码环境: linux + mysql 5.7 + utf8

2、statement模式

BEGIN
/*!*/;
# at 330
# at 362
#210603 22:10:22 server id 1  end_log_pos 362 CRC32 0x7ac6b8fb 	Intvar
SET INSERT_ID=19/*!*/;
#210603 22:10:22 server id 1  end_log_pos 546 CRC32 0x00b1f4a9 	Query	thread_id=8	exec_time=0	error_code=0
use `test_binlog`/*!*/;
SET TIMESTAMP=1622729422/*!*/;
/* ApplicationName=DataGrip 2018.3.1 */ insert into sync_test (id, name) values (null, 'aaa')
/*!*/;
# at 546
#210603 22:10:22 server id 1  end_log_pos 577 CRC32 0x60edfbe9 	Xid = 171
COMMIT/*!*/;
# at 577
#210603 22:10:22 server id 1  end_log_pos 656 CRC32 0x39130863 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1622729422570888	immediate_commit_timestamp=1622729422570888	transaction_length=428
# original_commit_timestamp=1622729422570888 (2021-06-03 22:10:22.570888 ?D1迆㊣那℅?那㊣??)
# immediate_commit_timestamp=1622729422570888 (2021-06-03 22:10:22.570888 ?D1迆㊣那℅?那㊣??)
/*!80001 SET @@session.original_commit_timestamp=1622729422570888*//*!*/;
/*!80014 SET @@session.original_server_version=80017*//*!*/;
/*!80014 SET @@session.immediate_server_version=80017*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 656
#210603 22:10:22 server id 1  end_log_pos 752 CRC32 0x3b8bbbd1 	Query	thread_id=8	exec_time=0	error_code=0
SET TIMESTAMP=1622729422/*!*/;
BEGIN
/*!*/;
# at 752
# at 784
#210603 22:10:22 server id 1  end_log_pos 784 CRC32 0xf03ed2f5 	Intvar
SET INSERT_ID=20/*!*/;
#210603 22:10:22 server id 1  end_log_pos 974 CRC32 0x379a0938 	Query	thread_id=8	exec_time=0	error_code=0
SET TIMESTAMP=1622729422/*!*/;
/* ApplicationName=DataGrip 2018.3.1 */ insert into sync_test (id, name) values (null, '????)

输出了两条sql语句：

insert into sync_test (id, name) values (null, 'aaa')
insert into sync_test (id, name) values (null, '????)

• STATEMENT模式原样记录了运行的两条sql语句

中文乱码问题

• 这里sql的中文乱码了，sql无法运行。
• 乱码环境: windows + mysql 8.0 + utf8mb4
• 不乱码环境: linux + mysql 5.7 + utf8

4. 参考

• https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html
• 
  
• https://www.jianshu.com/p/8e7e288c41b1
•