【转载请注明出处】:https://cloud.tencent.com/developer/article/1632663
binlog是Mysql sever层维护的一种二进制日志,与innodb引擎中的redo/undo log是完全不同的日志;其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句,并以"事务"的形式保存在磁盘中。mysql的binlog是多文件存储,定位一个LogEvent需要通过binlog filename + binlog position,进行定位。
作用主要有:
MySQL Replication 复制可以是基于一条语句 (Statement Level) ,也可以是基于一条记录 (Row Level),可以在 MySQL 的配置参数中设定这个复制级别,不同复制级别的设置会影响到 Master 端的 bin-log 日志格式。
在基于行的日志中,master会将事件写入二进制日志文件以表明单个表的行如何受到影响。日志中会记录成每一行数据被修改的形式,然后在 slave 端再对相同的数据进行修改。
优点: 在 row 模式下,bin-log 中可以不记录执行的 SQL 语句的上下文相关的信息,仅仅只需要记录那一条记录被修改了,修改成什么样了。所以 row 的日志内容会非常清楚的记录下每一行数据修改的细节,非常容易理解。而且不会出现某些特定情况下的存储过程或 function ,以及 trigger 的调用和触发无法被正确复制的问题。
缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比如一条update语句,修改多条记录,则binlog中每一条修改都会有记录,这样造成binlog日志量会很大,特别是当执行alter table之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。
每一条会修改数据的 SQL 都会记录到 master 的 bin-log 中。slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行。
优点: 不记录每一行数据的变化,减少了 bin-log 日志量,节省 I/O 以及存储资源,提高性能。因为只记录在 master 上所执行的语句的细节,以及执行语句时候的上下文的信息。
缺点: 由于记录的执行语句,所以,为了让这些语句在 slave 端也能正确执行,那么他还必须记录每条语句在执行的时候的一些相关信息,也就是上下文信息,以保证所有语句在 slave 端杯执行的时候能够得到和在 master 端执行时候相同的结果。在 statement 中,目前已经发现的就有不少情况会造成 MySQL 的复制出现问题,主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现,比如:sleep() 函数在有些版本中就不能被正确复制,在存储过程中使用了 last_insert_id() 函数,可能会使 slave 和 master 上得到不一致的 id 等等。由于 row 是基于每一行来记录的变化,所以不会出现类似的问题。
从 5.1.8 版本开始,MySQL 提供了除 statement 和 row 之外的第三种复制模式:mixed,实际上就是前两种模式的结合。在 mixed 模式下,MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式,也就是在 statement 和 row 之间选择一种。一般的语句修改使用statment格式保存binlog,如表结构变更,但对于statement无法完成主从复制的操作,如一些函数,则采用row格式保存binlog。
如果 binlog 采用了 Mixed 模式,那么在以下几种情况下会自动将 binlog 的模式由 statement 模式变为 row 模式:
statement 优点:
statement 缺点:
row 优点:
row 缺点:
另外,针对系统库 MySQL 里面的表发生变化时的处理准则如下:
1、如果生产中使用MySQL的特殊功能相对少(存储过程、触发器、函数)。选择默认的语句模式,Statement Level。
2、如果生产中使用MySQL的特殊功能较多的,可以选择Mixed模式。
3、如果生产中使用MySQL的特殊功能较多,又希望数据最大化一致,此时最好Row level模式;但是要注意,该模式的binlog非常“沉重”。
两者是完全不同的日志,主要有以下几个区别:
sync_binlog
相关。显然,我们执行SELECT等不涉及数据更新的语句是不会记binlog的,而涉及到数据更新则会记录。要注意的是,对支持事务的引擎如innodb而言,必须要提交了事务才会记录binlog。
binlog刷新到磁盘的时机跟sync_binlog
参数相关,如果设置为0,则表示MySQL不控制binlog的刷新,由文件系统去控制它缓存的刷新,而如果设置成不为0的值则表示每sync_binlog次事务,MySQL调用文件系统的刷新操作刷新binlog到磁盘中。设为1是最安全的,在系统故障时最多丢失一个事务的更新,但是会对性能有所影响,一般情况下会设置为100或者0,牺牲一定的一致性来获取更好的性能。
my.cnf配置中设置:
[mysqld]
log-bin=mysql-bin #binlog文件名前缀
binlog-format=ROW #ROW,Statement,MiXED三种格式
expire_logs_days=7 #binlog过期清理时间
sync_binlog=1 #刷新到磁盘的时机
重启mysql
service mysqld start
不重启mysql重新加载my.cnf文件的情况下,修改binlog类型的方法:
#只对当前会话有效,mysql重启无效
mysql> SET SESSION binlog_format = 'ROW';
#新会话有效,mysql重启无效
mysql> SET GLOBAL binlog_format = 'ROW';
binlog文件的目录在my.cnf
配置文件中datadir
指定的位置,也可以通过sql语句查看所在位置及相关信息
#查看所在位置
mysql> show variables like '%datadir%';
#查看binlog的开启状态及文件名
mysql> show variables like '%log_bin%';
#查看binlog当前的格式
mysql> show variables like '%format%';
#查看binlog文件列表
mysql> show binary logs;
#查看binlog的状态
mysql> show master status;
默认情况下binlog日志是二进制格式,无法直接查看。可使用两种方式进行查看:
datadir
查看binlog文件 $ mysqlbinlog mysql-bin.000001或者,远程读取 binlog 文件 $ mysqlbinlog -R -hIP -uNAME -pPASSWORD mysql-bin.000001 # at 524
#190406 23:36:43 server id 1 end_log_pos 589 CRC32 0x790d8d0f Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 589
#190406 23:36:43 server id 1 end_log_pos 676 CRC32 0xc0498a17 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1554565003/*!*/;
BEGIN
/*!*/;
# at 676
# at 708
#190406 23:36:43 server id 1 end_log_pos 708 CRC32 0xe7b12002 Intvar
SET INSERT_ID=75/*!*/;
#190406 23:36:43 server id 1 end_log_pos 863 CRC32 0x902ebba7 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1554565003/*!*/;
insert into `test`.`t` ( `d`, `s`) values ( NOW(), '2019-03-15 09:53:47')
/*!*/;
# at 863
#190406 23:36:43 server id 1 end_log_pos 894 CRC32 0x30487fcc Xid = 7
COMMIT/*!*/;
# at 428
#190404 17:07:00 server id 1 end_log_pos 493 CRC32 0xcb947c46 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 493
#190404 17:07:00 server id 1 end_log_pos 573 CRC32 0x5050376a Query thread_id=33 exec_time=0 error_code=0
SET TIMESTAMP=1554368820/*!*/;
BEGIN
/*!*/;
# at 573
#190404 17:07:00 server id 1 end_log_pos 621 CRC32 0xe48f4fe2 Table_map: `test`.`t` mapped to number 108
# at 621
#190404 17:07:00 server id 1 end_log_pos 671 CRC32 0x8af05d9b Write_rows: table id 108 flags: STMT_END_F
BINLOG '
NMmlXBMBAAAAMAAAAG0CAAAAAGwAAAAAAAEABHRlc3QAAXQAAwMSEgIAAATiT4/k
NMmlXB4BAAAAMgAAAJ8CAAAAAGwAAAAAAAEAAgAD//hJAAAAmaLJEcCZop6db5td8Io=
'/*!*/;
# at 671
#190404 17:07:00 server id 1 end_log_pos 702 CRC32 0x82022821 Xid = 8
COMMIT/*!*/;
两种格式的日志都包括如下信息:
# at 573
),说明该事件记录从文件哪个字节开始#190404 17:07:00
)Query
)SET TIMESTAMP=1554368820/*!*/;
: 开始事物的时间end_log_pos 671
)#查看所在位置
mysql> show variables like '%datadir%';
#查看binlog的开启状态及文件名
mysql> show variables like '%log_bin%';
#查看binlog当前的格式
mysql> show variables like '%format%';
#查看binlog文件列表
mysql> show binary logs;
#查看binlog的状态,即最后一个binlog日志的编号名称,及其最后一个操作事件pos结束点
mysql> show master status;
使用mysqlbinlog工具查看binlog文件
$ mysqlbinlog mysql-bin.000001
## 或者,远程读取 binlog 文件
$ mysqlbinlog -R -hIP -uNAME -pPASSWORD mysql-bin.000001
#基于时间点恢复:
$ mysqlbinlog --start-datetime="2019-04-04 23:20:35" --stop-datetime="2019-04-04 23:50:18" mysql-bin.000001 | mysql -uroot -p123456
# 基于时间点恢复:
$ mysqlbinlog --start-position= 428 --stop-position=671 mysql-bin.000001 | mysql -uroot -p123456
也可以使用工具binlog2sql(https://github.com/danfengcao/binlog2sql
),从MySQL binlog解析出你要的SQL。根据不同选项,你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。
#刷新log日志,自此刻开始产生一个新编号的binlog日志文件
#每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;
mysql> flush logs;
#重置(清空)所有binlog日志
mysql> reset master;
#删除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs before '2019-03-15 09:35:00';
#删除指定日志文件的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000001';
复制是mysql最重要的功能之一,mysql集群的高可用、负载均衡和读写分离都是基于复制来实现的;从5.6开始复制有两种实现方式,基于binlog和基于GTID(全局事务标示符),基于binlog的一主一从复制的基本过程如下:
【转载请注明出处】:https://cloud.tencent.com/developer/article/1632663
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。