MySQL 主从复制的原理、如何实现、常见问题及解决方案

一、环境介绍

LNMP(centos7,mysql5.6)

vmware workstation pro配置了3个虚拟机,均安装了LNMP环境:

master:  192.168.0.105

slave:   192.168.0.106 、192.168.0.107

主从复制架构部署的原则:

1、不要有太多的备库(同步binlog也是需要消耗性能的)

2、一个备库实例只能有一个主库

3、每一个集群库,都必须有一个唯一的服务ID(server-id)

  

二、原理

(1)主数据库进行增删改操作后,相应操作记录的语句(比如 create database test)会记录到binlog日志文件中(binlog日志文件一般和数据库data文件夹在一起)。

(2)从数据库会请求主数据库的binlog日志文件,获取到新的操作语句,然后在自己的从数据库上自动执行相同的操作语句,进而实现主从的同步。

注意:这里,我们所需要配置的只是主从环境以及开启binlog日志,其他的mysql会自动完成。

注意:mysql 会在每次重启后,都重新生成一份binlog文件,比如mysql-bin.000023,所有binlog文件的索引记录在mysql-bin.index。每个binlog日志文件只是记录那一次mysql服务运行时产生的sql语句。

可以使用reset master 命令清空binlog日志,但该命令请谨慎使用。

  

三、详细主从复制过程

(1)Master开启bin-log功能,binlog日志文件用于记录数据库的增删改操作。

(2)需要开启三个线程,Master:I/O线程;Slave:I/O线程,SQL线程。

(3)Slave start;通过I/O线程连接Master,并且请求某个bin-log,position之后的内容。

(4)Master服务器收到Slave I/O线程发过来的日志请求信息,然后Master I/O线程将bin-log内容、position返回给Slave IO线程。

(5)Slave服务器收到bin-log日志内容,将bin-log日志内容写入到relay-log中继日志,创建一个master.info文件,该文件记录master IP、用户名、密码、master bin-log名称、bin-log position。

(6)Slave已经开启了sql线程,由sql线程实时监测relay-log日志内容是否有更新,如果有更新,则解析文件中的sql语句,并在Slave数据库中执行相同的操作语句。

注:可以通过show slave status \G  来查看具体的中继日志路径以及连接的master的其他信息。

  

四、主从复制的实现

  
1、master机器上的配置

(1)vim /etc/my.cnf   #修改mysql的配置文件

(2)配置bind-address (配置成master-ip)和 server-id (改成ip后三位即可,也可以改为其他,比如5)以及开启bin-log(默认是已经开启),如下:

bind-address = 0.0.0.0
server-id = 105

注:此处允许所有ip地址可以远程登录此mysql,出于安全考虑,可以配置防火墙规则进行mysql远程登录的ip的限制。

配置防火墙规则链接:https://blog.csdn.net/m_nanle_xiaobudiu/article/details/81070480

  
(3)创建用于slave机器获取master机器上binlog文件的账号(从机器复制用户)

grant replication slave on . to 'xiaobudiu'@'%' identified by 'xiaobudiu123';
  
(4) flush privileges;  #刷新数据库

markdown
  
(5)show master status; #查看master状态,并记录下binlog日志文件名以及position

markdown
  
  
2、slave机器上的配置

(1)在两台从机器上分别配置mysql配置文件中的bind-address 和 server-id

192.168.0.106机器上:

bind-address = 0.0.0.0
server-id = 106

192.168.0.107机器上:

bind-address = 0.0.0.0
server-id = 107

  
(2)分别在两台从库上操作

change master to master_host='192.168.0.105', master_port=3306, master_user='xiaobudiu', master_password='xiaobudiu123', master_log_file='mysql-bin.000045', master_log_pos=402;

  
(3)分别开启两台从库

markdown

  
(4)验证从库状态

markdown

当从库的 IO线程和SQL线程的状态都是yes时,说明主从模式配置成功。

 

注:配置主从时,其他的一些常用配置

(1)log-slave-updates

主要用来配置从服务器的更新是否写入二进制日志,该选项默认是不打开的,如果这个从服务器同时也作为其他服务器的主服务器,搭建一个链式的复制,那么就需要开启这个选项,这样他的从服务器才能获取它的二进制日志进行同步操作。

(2)master-connect-retry

用来设置在和主服务器连接丢失的时候,重试的时间间隔,默认是60秒。

(3)read-only

用来限制普通用户对从数据库的更新操作,以确保从数据库的安全性,不过如果是超级用户依然可以对从数据库进行更新操作。如果主数据库创建了一个普通用户,在默认情况下,该用户是可以更新从数据库中的数据的,如果使用read-only选项启动从数据库以后,该用户对从数据库的更新会提示错误。

(4)slave-skip-errors

在复制的过程中,从服务器可能会执行BINLOG中的错误的SQL语句,此时如果不忽略错误,从服务器将会停止复制进程,等待用户处理错误。

这种错误如果不能及时发现,将会对应用或者备份产生影响。slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号,设置该参数后,MySQL会自动跳过所配置的一系列错误,直接执行后面的SQL语句,该参数可以定义多个错误号,如果设置成all,则表示跳过所有的错误,具体语法如下。

vi /etc/my. cnf
slave-skip-errors=1007,1051,1062

 

五、实际开发中常见问题及解决方案

实际开发中,我们配置主从往往出于两个目的,要不就是做主库的备份,要不就是减轻主库的查询压力。并且,我们做主从,往往不是开发项目时就直接做主从了,而是规模发展到一定程度,才开始扩大规模做主从,随之而来的就带来了一些问题。下面介绍几个常见问题及解决方案。

1、从库只复制主库的一个库或一个表
# 通过配置从库的 my.cnf 的参数实现

指定需要复制的表    replicate-do-table=test.product

指定不复制的表 replicate-ignore-table=test.product

指定复制的数据库    replicate-do-db=test

指定不复制的数据库   replicate-ignore-db=test

 

2、主库已经有数据,才开始做主从复制

这种情况大概才是实际生产中的场景,这个场景的问题就在于主从复制过程中,我们往往是不希望主库停机去同步的,因此这里就涉及备份。

注:MySQL 如何进行数据备份

 

3、主从一致性问题

注:使用percona-toolkit工具做校验