本文由云+社区发表
binlog是mysql sever层维护的一种二进制日志,与innodb引擎中的redo/undo log是完全不同的日志;其主要是用来记录对mysql数据更新或潜在发生更新的sql语句,并以”事务”的形式保存在磁盘中;
作用主要有:
默认情况下binlog日志是二进制格式,无法直接查看。可使用两种方式进行查看:
a. mysqlbinlog: /usr/bin/mysqlbinlog mysql-bin.000007 - mysqlbinlog是mysql官方提供的一个binlog查看工具, - 也可使用–read-from-remote-server从远程服务器读取二进制日志, - 还可使用--start-position --stop-position、--start-time= --stop-time精确解析binlog日志 截取位置1190-1352 binlog如下: *************************************************************************************** # at 1190 //事件的起点 #171223 21:56:26 server id 123 end_log_pos 1190 crc32 0xf75c94a7 intvar set insert_id=2/*!*/; #171223 21:56:26 server id 123 end_log_pos 1352 crc32 0xefa42fea query thread_id=4 exec_time=0 error_code=0 set timestamp=1514123786/*!*/; //开始事务的时间起点 (每个at即为一个event) insert into tb_person set name="name__2", address="beijing", sex="man", other="nothing" //sql语句 /*!*/; # at 1352 #171223 21:56:26 server id 123 end_log_pos 1383 crc32 0x72c565d3 xid = 5 //执行时间,及位置戳,xid:事件指示提交的xa事务 *************************************************************************************** b.直命令行解析 show binlog events [in 'log_name'] //要查询的binlog文件名 [from pos] [limit [offset,] row_count] 1190-135如下:mysql> show binlog events in 'mysql-bin.000007' from 1190 limit 2\g *************************** 13. row *************************** log_name: mysql-bin.000007 pos: 1190 event_type: query //事件类型 server_id: 123 end_log_pos: 1352 //结束pose点,下个事件的起点 info: use `test`; insert into tb_person set name="name__2", address="beijing", sex="man", other="nothing" *************************** 14. row *************************** log_name: mysql-bin.000007 pos: 1352 event_type: xid server_id: 123 end_log_pos: 1383 info: commit /* xid=51 */
mysql binlog日志有row,statement,mixed三种格式;可通过my.cnf配置文件及 ==++set global binlog_format=’row/statement/mixed’++== 进行修改,命令行 ==++show variables like ‘binlog_format’++== 命令查看binglog格式;。
复制是mysql最重要的功能之一,mysql集群的高可用、负载均衡和读写分离都是基于复制来实现的;从5.6开始复制有两种实现方式,基于binlog和基于gtid(全局事务标示符);本文接下来将介绍基于binlog的一主一从复制;其复制的基本过程如下:
a.master将数据改变记录到二进制日志(binary log)中 b.slave上面的io进程连接上master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容 c.master接收到来自slave的io进程的请求后,负责复制的io进程会根据请求信息读取日志指定位置之后的日志信息,返回给slave的io进程。 返回信息中除了日志所包含的信息之外,还包括本次返回的信息已经到master端的bin-log文件的名称以及bin-log的位置 d.slave的io进程接收到信息后,将接收到的日志内容依次添加到slave端的relay-log文件的最末端,并将读取到的master端的 bin-log的 文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉master从某个bin-log的哪个位置开始往后的日志内容 e.slave的sql进程检测到relay-log中新增加了内容后,会马上解析relay-log的内容成为在master端真实执行时候的那些可执行的内容,并在自身执行
接下来使用实例演示基于binlog的主从复制:
a.配置master 主要包括设置复制账号,并授予replication slave权限,具体信息会存储在于master.info文件中,及开启binlog; mysql> create user 'test'@'%' identified by '123456'; mysql> grant replication slave on *.* to 'test'@'%'; mysql> show variables like "log_bin"; +---------------+-------+ | variable_name | value | +---------------+-------+ | log_bin | on | +---------------+-------+ 查看master当前binlogmysql状态:mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 建表插入数据: create table `tb_person` ( `id` int(11) not null auto_increment, `name` varchar(36) not null, `address` varchar(36) not null default '', `sex` varchar(12) not null default 'man' , `other` varchar(256) not null , primary key (`id`) ) engine=innodb auto_increment=0 default charset=utf8; insert into tb_person set name="name1", address="beijing", sex="man", other="nothing"; insert into tb_person set name="name2", address="beijing", sex="man", other="nothing"; insert into tb_person set name="name3", address="beijing", sex="man", other="nothing"; insert into tb_person set name="name4", address="beijing", sex="man", other="nothing"; b.配置slave slave的配置类似master,需额外设置relay_log参数,slave没有必要开启二进制日志,如果slave为其它slave的master,须设置bin_log c.连接master mysql> change master to master_host='10.108.111.14', master_user='test', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=120; d.show slave status; mysql> show slave status\g *************************** 1. row *************************** slave_io_state: ---------------------------- slave io状态,表示还未启动 master_host: 10.108.111.14 master_user: test master_port: 20126 connect_retry: 60 ------------------------- master宕机或连接丢失从服务器线程重新尝试连接主服务器之前睡眠时间 master_log_file: mysql-bin.000003 ------------ 当前读取master binlog文件 read_master_log_pos: 120 ------------------------- slave读取master binlog文件位置 relay_log_file: relay-bin.000001 ------------ 回放binlog relay_log_pos: 4 -------------------------- 回放relay log位置 relay_master_log_file: mysql-bin.000003 ------------ 回放log对应maser binlog文件 slave_io_running: no slave_sql_running: no exec_master_log_pos: 0 --------------------------- 相对于master从库的sql线程执行到的位置 seconds_behind_master: null slave_io_state, slave_io_running, 和slave_sql_running为no说明slave还没有开始复制过程。 e.启动复制 start slave f.再次观察slave状态 mysql> show slave status\g *************************** 1. row *************************** slave_io_state: waiting for master to send event -- 等待master新的event master_host: 10.108.111.14 master_user: test master_port: 20126 connect_retry: 60 master_log_file: mysql-bin.000003 read_master_log_pos: 3469 ---------------------------- 3469 等于exec_master_log_pos,已完成回放 relay_log_file: relay-bin.000002 || relay_log_pos: 1423 || relay_master_log_file: mysql-bin.000003 || slave_io_running: yes || slave_sql_running: yes || exec_master_log_pos: 3469 -----------------------------3469 等于slave读取master binlog位置,已完成回放 seconds_behind_master: 0 可看到slave的i/o和sql线程都已经开始运行,而且seconds_behind_master=0。relay_log_pos增加,意味着一些事件被获取并执行了。 最后看下如何正确判断slave的延迟情况,判定slave是否追上master的binlog: 1、首先看 relay_master_log_file 和 maser_log_file 是否有差异; 2、如果relay_master_log_file 和 master_log_file 是一样的话,再来看exec_master_log_pos 和 read_master_log_pos 的差异,对比sql线程比io线程慢了多少个binlog事件; 3、如果relay_master_log_file 和 master_log_file 不一样,那说明延迟可能较大,需要从master上取得binlog status,判断当前的binlog和master上的差距; 4、如果以上都不能发现问题,可使用pt_heartbeat工具来监控主备复制的延迟。 g.查询slave数据,主从一致 mysql> select * from tb_person; +----+-------+---------+-----+---------+ | id | name | address | sex | other | +----+-------+---------+-----+---------+ | 5 | name4 | beijing | man | nothing | | 6 | name2 | beijing | man | nothing | | 7 | name1 | beijing | man | nothing | | 8 | name3 | beijing | man | nothing | +----+-------+---------+-----+---------+ 关于mysql复制的内容还有很多,比如不同的同步方式、复制格式情况下有什么区别,有什么特点,应该在什么情况下使用....这里不再一一介绍。
恢复是binlog的两大主要作用之一,接下来通过实例演示如何利用binlog恢复数据: a.首先,看下当前binlog位置 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000008 | 1847 | | | | +------------------+----------+--------------+------------------+-------------------+ b.向表tb_person中插入两条记录: insert into tb_person set name="person_1", address="beijing", sex="man", other="test-1"; insert into tb_person set name="person_2", address="beijing", sex="man", other="test-2"; c.记录当前binlog位置: mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000008 | 2585 | | | | +------------------+----------+--------------+------------------+-------------------+ d.查询数据 mysql> select * from tb_person where name ="person_2" or name="person_1"; +----+----------+---------+-----+--------+ | id | name | address | sex | other | +----+----------+---------+-----+--------+ | 6 | person_1 | beijing | man | test-1 | | 7 | person_2 | beijing | man | test-2 | +----+----------+---------+-----+--------+ e.删除一条: delete from tb_person where name ="person_2"; mysql> select * from tb_person where name ="person_2" or name="person_1"; +----+----------+---------+-----+--------+ | id | name | address | sex | other | +----+----------+---------+-----+--------+ | 6 | person_1 | beijing | man | test-1 | +----+----------+---------+-----+--------+ f. binlog恢复(指定pos点恢复/部分恢复) mysqlbinlog --start-position=1847 --stop-position=2585 mysql-bin.000008 > test.sql mysql> source /var/lib/mysql/3306/test.sql d.数据恢复完成 mysql> select * from tb_person where name ="person_2" or name="person_1"; +----+----------+---------+-----+--------+ | id | name | address | sex | other | +----+----------+---------+-----+--------+ | 6 | person_1 | beijing | man | test-1 | | 7 | person_2 | beijing | man | test-2 | +----+----------+---------+-----+--------+ e.总结 恢复,就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已
本文简要介绍binlog原理及其在恢复、复制中的使用方法;更多深入分析可参考mysql官方文档及相关博客。
此文已由作者授权腾讯云+社区在各渠道发布
获取更多新鲜技术干货,可以关注我们
如对本文有疑问, 点击进行留言回复!!
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火,普罗米修斯的崛起
网友评论