MySQL使用binlog(二进制日志)主从复制教程

SQL herman 1134浏览 0评论
公告:“业余草”微信公众号提供免费CSDN下载服务(只下Java资源),关注业余草微信公众号,添加作者微信:xttblog,发送下载链接帮助你免费下载!
本博客日IP超过1800,PV 2600 左右,急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包,请加博主新的微信号:xttblog,之前的微信号好友位已满,备注:返现
所有面试题(java、前端、数据库、springboot等)一网打尽,请关注文末小程序
视频教程免费领

复制是mysql最重要的功能之一,mysql集群的高可用、负载均衡和读写分离都是基于复制来实现的;从5.6开始复制有两种实现方式,基于binlog和基于GTID(全局事务标示符);本文接下来将介绍基于binlog的一主一从复制。

基于binlog的主从复制过程如下:

  • Master将数据改变记录到二进制日志(binary log)中
  • Slave上面的IO进程连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容
  • Master接收到来自Slave的IO进程的请求后,负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息,返回给Slave的IO进程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
  • Slave的IO进程接收到信息后,将接收到的日志内容依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉Master从某个bin-log的哪个位置开始往后的日志内容
  • Slave的Sql进程检测到relay-log中新增加了内容后,会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容,并在自身执行

下面我们通过一个案例来学习它。

根据上面的主从复制过程,我们先要对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 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

配置完master后,我们就可以建表插入数据,测试binlog是否产生了日志信息(可以通过上一篇文章,查看binlog日志。MySQL binlog(二进制日志)解析)。

接下来的第二步,我们需要对slave进行配置。Slave的配置类似master,需额外设置relay_log参数,slave没有必要开启二进制日志,如果slave为其它slave的master,须设置bin_log。

再接下来,我们需要连接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;

使用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还没有开始复制过程。

再接下来,我们使用start slave启动复制。

start slave

再次观察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工具来监控主备复制的延迟。

查询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复制的内容还有很多,比如不同的同步方式、复制格式情况下有什么区别,有什么特点,应该在什么情况下使用等等。有兴趣的同学,可以自行的去研究一下!

业余草公众号

最后,欢迎关注我的个人微信公众号:业余草(yyucao)!可加QQ1群:135430763(2000人群已满),QQ2群:454796847(已满),QQ3群:187424846(已满)。QQ群进群密码:xttblog,想加微信群的朋友,之前的微信号好友已满,请加博主新的微信号:xttblog,备注:“xttblog”,添加博主微信拉你进群。备注错误不会同意好友申请。再次感谢您的关注!后续有精彩内容会第一时间发给您!原创文章投稿请发送至532009913@qq.com邮箱。商务合作可添加助理微信进行沟通!

本文原文出处:业余草: » MySQL使用binlog(二进制日志)主从复制教程