binlog文件分为三种模式&binlog文件恢复数据

最新推荐文章于 2024-04-21 09:59:51 发布

Stefanboy

最新推荐文章于 2024-04-21 09:59:51 发布

阅读量1.1k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/csdn_mycsdn/article/details/103589615

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

binlog文件分为三种模式，分别区别是什么

binlog对于mysql是至关重要的，binlog与undo redo一起保证了数据的完整性，用于数据恢复，崩溃恢复、任一时间点恢复、甚至是任意一条数据的恢复。所有的高可用模式也都是基于binlog进行处理的。

本文主要对binlog的三种存储格式statement、row、mixed进行分析对比其优缺点。

statement格式：

在statement格式下，binlog忠实的记录的执行过的语句，你执行过什么语句它就照搬复制到binlog中。由于其可能造成主从不一致的情况，所以生产环境基本都不会设置为statement。

优势：

1、节省空间。由于只是对执行的语句进行记录，所以相比row模式binlog所占的空间很小

2、提高数据库性能。由于binlog是在事务提交时才进行fsync刷盘操作，而刷盘的操作是最耗费IO的，statement只需要记录一条语句而不是记录所有操作过的数据行。

劣势：

可能造成主从不一致

在测试环境执行delete from test limit 10;删除表中的10条数据，观察binlog的内容变化，binlog部分内容如下：

在这里插入图片描述

可以看到执行过的语句被原原本本的记录到binlog中，被同步到从库重做。

执行delete from test limit 10;时还会产生一个警告，大意就是使用statement格式时执行limit语句可能造成主从同步不一致。因为limit语句只是指定了删除10条记录，但没有指定具体是哪10条，当mysql在两次执行时选择了不同的索引进行操作时，删除的记录就是不同的。当然还有其他函数也可能会造成主从不一致。

row格式（mysql默认的格式，也只有这种格式才能够通过binlog文件恢复数据）：

在row格式下，binlog对于DDL操作记录执行的SQL语句，对于DML语句则记录具体操作的数据行。一般生产环境采用该格式。

优势：

对于DML操作记录了具体的行数据，保证重放的一致性，同时也可以对一些误操作的数据进行单独恢复提供了可能性

劣势：

由于记录了每条数据的内容变更，导致了binlog日志占用了很大的空间，由于fsync时一次写入数据过多，在一定程度上影响了性能。

调整binlog格式为row，执行delete from testxxxx limit 10;观察binlog的内容变化，binlog部分内容如下：
在这里插入图片描述

可见binlog修改的每一行数据的具体值都被记录了下来。如果我需要恢复其中的某一条记录只要把delete转换成insert就可以了，这是其他格式做不到的。

mixed格式：

集前两种格式的优点，对于DDL只对SQL语句进行记录。对DML操作则会进行判断，如果mysql判断会造成主从不一致，就会采用row格式记录，反之则用statement格式记录。

优点：

节省空间，提高数据库性能，通过判断保证数据重放时的一致性。

缺点：

无法对误操作数据进行单独恢复
调整binlog格式为mixed，执行delete from test limit 1;和delete from test where a =1;观察binlog的内容变化，binlog部分内容如下：
在这里插入图片描述

binlog文件查看

查看是否开启binlog

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

只查看第一个binlog文件的内容

show binlog events;

查看指定binlog文件的内容

show binlog events in 'mysql-bin.000002';

查看当前正在写入的binlog文件

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 28599
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

获取binlog文件列表

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     67251 |
| mysql-bin.000002 |   1265917 |
| mysql-bin.000003 |     14647 |
| mysql-bin.000004 |     28599 |
+------------------+-----------+
4 rows in set (0.00 sec)

使用binlog文件恢复数据

方法一和方法二的场景：数据库每天中午备份，突然间今天中午之前挂掉了，也就是说从昨天中午到今天中无之前的数据都没有了，根据binlog中的时间去进行备份；或者是数据都丢了，需要每个binlog文件都要读取，然后一个个binlog的手动恢复；前提是生产库和测试库的数据库名跟表名和表字段名是一样的，在测试库恢复完成后，在生成sql脚本，导入到生产库

方法三：适用于表中误操作语句恢复
mysqlbinlog命令常用参数参数说明

-d ,--database=name	根据指定库拆分binlog（拆分单表binlog可通过SQL关键字过滤）
-r ,--result-file=name	指定解析binlog输出SQL语句的文件
-R，--read-from-remote-server	从mysql服务器读取binlog日志，是下面参数的别名
-j,--start-position=#	读取binlog的起始位置点，#号是具体的位置点
--stop-position=#	读取binlog的停止位置点，#号是具体的位置点
--start-datetime=name	读取binlog的起始位置点，name是具体的时间，格式为：2004-12-25 11:25:26
--stop-datetime=name	读取binlog的停止位置点，name是具体的时间，格式为：2004-12-25 11:25:26
--base64-output=decode-rows	解析row级别binlog日志的方法，例如：mysqlbinlog  --base64-output=decode-rows -v  mysqlbin.000016
常用参数选项解释：
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)

不常用选项：

-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
--read-from-remote-server 从某个MySQL服务器上读取binlog日志

方法一：

在bin目录下找到mysqlbinlog，使用该脚本可以查到该binlog文件position和datetime的起始位置

JD:mysqladmin:/usr/local/mysql/bin:>ls
innochecksum       mysqlbug                    mysqldumpslow              mysqltest_embedded
msql2mysql         mysqlcheck                  mysql_embedded             mysql_tzinfo_to_sql
myisamchk          mysql_client_test           mysql_find_rows            mysql_upgrade
myisam_ftdump      mysql_client_test_embedded  mysql_fix_extensions       mysql_waitpid
myisamlog          mysql_config                mysqlhotcopy               mysql_zap
myisampack         mysql_config_editor         mysqlimport                perror
my_print_defaults  mysql_convert_table_format  mysql_plugin               replace
mysql              mysqld                      mysql_secure_installation  resolveip
mysqlaccess        mysqld-debug                mysql_setpermission        resolve_stack_dump
mysqlaccess.conf   mysqld_multi                mysqlshow                  z4a1s12.so
mysqladmin         mysqld_safe                 mysqlslap                  z4a1s1.so
mysqlbinlog        mysqldump                   mysqltest

可以查看pos点，以及所做的事物，便于观看，和mysqlbinlog结合着看。
mysql> show binlog events in ‘mysql-bin.000001’;

找到对应的事物*（该binlog文件开始位置为4 开始时间为191123 18:11:41）
JD:mysqladmin:/usr/local/mysql:> /usr/local/mysql/bin/mysqlbinlog -vv /usr/local/mysql/arch/mysql-bin.000001 |less

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;

# at 4
#191123 18:11:41 server id 1  end_log_pos 120 CRC32 0xba0c5d82  Start: binlog v 4, server v 5.6.23-log created 191123 18:11:41 at startup


ROLLBACK/*!*/;
BINLOG '
3QXZXQ8BAAAAdAAAAHgAAAAAAAQANS42LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADdBdldEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYJd
DLo=
'/*!*/;

查看该binlog文件最后10行（结束位置为67228 结束时间为191123 18:11:41）

JD:mysqladmin:/usr/local/mysql:>/usr/local/mysql/bin/mysqlbinlog -vv /usr/local/mysql/arch/mysql-bin.000001 |tail -10

SET TIMESTAMP=1574503901/*!*/;
DROP TEMPORARY TABLE IF EXISTS `mysql`.`tmp_proxies_priv` /* generated by server */
/*!*/;


# at 67228
#191123 18:11:41 server id 1  end_log_pos 67251 CRC32 0x7f1eecc4        Stop
DELIMITER ;


# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

恢复：
JD:mysqladmin:/usr/local/mysql/bin:>./mysqlbinlog --start-position=‘4’ --stop-position=‘67228’ --database=d1 /usr/local/mysql/arch/mysql-bin.000001 |mysql -uroot -p1234 -v d1

方法二

[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position=“794” --stop-position=“1055” | /usr/bin/mysql -uroot -p123456 test

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.000012 --database=money_a --start-datetime=‘2016-07-20 00:00:01’ --stop-datetime=‘2016-07-24 23:59:59’ > 26-day.sql

database 为要恢复的数据库名称
start-datetime 恢复的起始时间
-stop-datetime 恢复的结束时间

读取所有：

mysqlbinlog --base64-output=decode-rows -v -v /data/mysql/binlog.000001 -r 1.sql

读取指定的库

mysqlbinlog -d d1 --base64-output=decode-rows -v -v /data/mysql/binlog.000002

读指定的表的数据：

mysqlbinlog -d d1 --base64-output=decode-rows -v -v /data/mysql/binlog.000001 |grep t1 >1.sql

恢复：

/usr/bin/mysqlbinlog --no-defaults /data/mysql/binlog.000001 --start-position="388" --stop-position="891" | /usr/bin/mysql -uroot -p1234 d1

开始从BEGIN，结束为end_log_pos

BEGIN
/*!*/;
# at 771
#180601 19:36:34 server id 1  end_log_pos 817 CRC32 0xf248d290  Table_map: `d1`.`t1` mapped to number 70
# at 817
#180601 19:36:34 server id 1  end_log_pos 860 CRC32 0x52e97a2f  Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `d1`.`t1`
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='bb' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 860
#180601 19:36:34 server id 1  end_log_pos 891 CRC32 0xd8cb322c  Xid = 15

方法三

1.安装依赖
tar -xvf Python-2.7.8.tar
cd Python-2.7.8
./configure && make && make install

tar -xzvf pip-1.5.4.tar.gz
cd pip-1.5.4
python setup.py install

unzip setuptools-26.1.1.zip
cd setuptools-26.1.1
python setup.py install

unzip binlog2sql-master.zip
cd binlog2sql-master
pip install -r requirements.txt

执行bin-log日志
[root@zk2 binlog2sql]# python binlog2sql.py -uroot -p1234 -P3306 -dd1 -tt1 --start-file=‘mysql-bin.000001’

报错：
Traceback (most recent call last):
File “binlog2sql.py”, line 150, in
binlog2sql.process_binlog()
File “binlog2sql.py”, line 74, in process_binlog
for binlog_event in stream:
File “/usr/local/lib/python2.7/site-packages/pymysqlreplication/binlogstream.py”, line 408, in fetchone
pkt = self._stream_connection._read_packet()
File “/usr/local/lib/python2.7/site-packages/pymysql/connections.py”, line 1014, in _read_packet
packet.check_error()
File “/usr/local/lib/python2.7/site-packages/pymysql/connections.py”, line 393, in check_error
err.raise_mysql_exception(self._data)
File “/usr/local/lib/python2.7/site-packages/pymysql/err.py”, line 107, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.InternalError: (1236, u’Misconfigured master - server_id was not set’)

解决方法：
set global server_id=1;
再次执行：（其中4为start-position的值 1611为stop-position的值）
[root@zk2 binlog2sql]# python binlog2sql.py -uroot -p1234 -P3306 -dd1 -tt1 --start-file='mysql-bin.000001’

INSERT INTO d1.t1(id, name) VALUES (1, ‘w’); #start 4 end 278 time 2018-07-09 15:36:21
INSERT INTO d1.t1(id, name) VALUES (2, ‘e’); #start 309 end 467 time 2018-07-09 15:36:28
INSERT INTO d1.t1(id, name) VALUES (3, ‘r’); #start 498 end 656 time 2018-07-09 15:36:35
INSERT INTO d1.t1(id, name) VALUES (4, ‘t’); #start 687 end 845 time 2018-07-09 15:36:40
INSERT INTO d1.t1(id, name) VALUES (5, ‘u’); #start 876 end 1034 time 2018-07-09 15:36:47
INSERT INTO d1.t1(id, name) VALUES (6, ‘qw’); #start 1065 end 1224 time 2018-07-09 15:36:55
UPDATE d1.t1 SET id=6, name=‘p’ WHERE id=6 AND name=‘qw’ LIMIT 1; #start 1255 end 1422 time 2018-07-09 15:37:27
DELETE FROM d1.t1 WHERE id=4 AND name=‘t’ LIMIT 1; #start 1453 end 1611 time 2018-07-09 15:37:56

生成回滚语句（stop-position）：
[root@zk2 binlog2sql]# python binlog2sql.py --flashback -uroot -p1234 -P3306 -dd1 -tt1 --start-file=‘mysql-bin.000001’ --start-position=“4” --stop-position="1611"

INSERT INTO d1.t1(id, name) VALUES (4, ‘t’); #start 1453 end 1611 time 2018-07-09 15:37:56
UPDATE d1.t1 SET id=6, name=‘qw’ WHERE id=6 AND name=‘p’ LIMIT 1; #start 1255 end 1422 time 2018-07-09 15:37:27
DELETE FROM d1.t1 WHERE id=6 AND name=‘qw’ LIMIT 1; #start 1065 end 1224 time 2018-07-09 15:36:55
DELETE FROM d1.t1 WHERE id=5 AND name=‘u’ LIMIT 1; #start 876 end 1034 time 2018-07-09 15:36:47
DELETE FROM d1.t1 WHERE id=4 AND name=‘t’ LIMIT 1; #start 687 end 845 time 2018-07-09 15:36:40
DELETE FROM d1.t1 WHERE id=3 AND name=‘r’ LIMIT 1; #start 498 end 656 time 2018-07-09 15:36:35
DELETE FROM d1.t1 WHERE id=2 AND name=‘e’ LIMIT 1; #start 309 end 467 time 2018-07-09 15:36:28
DELETE FROM d1.t1 WHERE id=1 AND name=‘w’ LIMIT 1; #start 4 end 278 time 2018-07-09 15:36:21

生成回滚语句（stop-datetime）：结束时间要大于生成语句的时间

[root@zk2 binlog2sql]# python binlog2sql.py --flashback -uroot -p1234 -P3306 -dd1 -tt1 --start-file=‘mysql-bin.000001’ --start-datetime=“2018-07-09 15:36:21” --stop-datetime=“2018-07-09 15:37:59”

总结
利用 binlog2sql 来做闪回的前提如下：
1、在配置文件中设置了以下参数：
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full # 默认
2、在闪回的时候必须启动 MySQL 服务，因为它是通过 BINLOG_DUMP 协议来获取 binlog 内容，需要读取server端 information_schema.COLUMNS 表，来获取表结构的元信息，才能拼接成 SQL 语句。因此需要给用户提供的最小权限如下：
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘user’@’%’;

Stefanboy

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
binlog文件分为三种模式&binlog文件恢复数据

binlog文件分为三种模式，分别区别是什么binlog对于mysql是至关重要的，binlog与undo redo一起保证了数据的完整性，用于数据恢复，崩溃恢复、任一时间点恢复、甚至是任意一条数据的恢复。所有的高可用模式也都是基于binlog进行处理的。本文主要对binlog的三种存储格式statement、row、mixed进行分析对比其优缺点。statement格式：在stateme...
复制链接

扫一扫