MySQL主从同步（一）——原理详解

一、MySQL主从同步简介

MySQL主从同步，即MySQL Replication，可以实现将数据从一台数据库服务器同步到多台数据库服务器。MySQL数据库自带主从同步功能，经过配置，可以实现基于库、表结构的多种方案的主从同步。

MySQL主从同步的作用主要有以下几点：

1、故障切换。

2、提供一定程度上的备份服务。

3、实现MySQL数据库的读写分离。

二、MySQL主从同步过程

依据MySQL主从同步的身份，可以将MySQL服务器分为主库和从库两种身份。MySQL从库同步主库的数据，MySQL主库将数据同步给从库。MySQL过程如下所示：

从上图可以看出，MySQL主从同步实现主要有以下三个过程：

1、当有数据更改语句执行时，MySQL主库要在更新数据的同时，写二进制日志，将数据修改的内容记录进入日志中。

2、MySQL从库上运行这一些I/O进程，这个进程会监视MySQL主库上的二进制日志，当发现修改时，会立即同步到自身的中继日志。

3、MySQL从库上还会运行一个SQL进程，该进程用于监视自身的中继日志，当发现自身的中继日志发生改变时，立即将该中继日志改变对应的数据更改操作写入自身的数据库。

三、MySQL主从同步常见方案

（一）M-S

M-S即采用一个MySQL主库，多个MySQL从库的方式，原理如下所示：

M-S的架构，最大的问题在于MySQL主库面临单点瓶颈和单点故障问题，I/O压力过大。

（二）M-S-S

为了环节M-S架构中MySQL主库压力过大的问题，我们还可以采用M-S-S架构，其中一个Slave Relay负责从MySQL主库同步数据，但是其本身并不存在数据，该设备作用仅仅是在日志层面。其他的MySQL从库不是从MySQL主库上同步数据，而是从中继Slave上同步数据，这样一来，就可以缓解Master的I/O压力了，M-S-S架构如下所示：

（三）M-M

M-M即两台数据库互为主备，互相同步，每个数据库的更新都会同步到另一个数据库中，有时，我们还可以采用多台数据库，组成环装更新架构。M-M架构如下所示：

四、MySQL主从同步方式

MySQL主从同步有三种方式，这些方式的差异主要存在于写入日志的内容不同，也会导致MySQL主从同步性能上的区别。

1、基于SQL语句的复制。

基于SQL语句的复制模式的Binlog格式为STATEMENT，在这种模式下，每一条修改数据的SQL语句都会记录到Binlog中，这样做的优点是并不需要记录每一条SQL语句和每一行的变化，减少了二进制日志日志量，节约了I/O。但是会导致某些情况下主、从库数据不一致的场景。

2、基于行的复制。

不记录每条SQL语句，仅记录哪条记录数据被修改了，以及修改后的结果。这样做的优点是不容易出现主、从库数据不一致的场景，但是缺点在于会产生大量日志。

3、混合模式复制。

在混合模式下，边的复制采用基于SQL语句的复制方式，只有当该方式无法复制（比如触发器、存储过程等等）时，才会使用基于行的方式。

MySQL主从同步（二）——M-S架构配置实战

一、实战环境

本次实战，我们采用两台Vmware虚拟机，来配置实现MySQL数据库的主从同步M-S架构。环境如下：

MySQL Master：192.168.136.101

MySQL Slave：192.168.135.201

两个MySQL数据库都是5.5.68

其余都是MySQL数据库的默认配置

二、主库配置

（一）数据库配置文件配置

为了实现MySQL主从同步功能，我们需要在配置文件上进行以下改动，打开/etc/my.cnf，该文件是MySQL数据库的主配置文件，需要在该文件的[mysqld]模块中添加一下内容：

log-bin=master-log

server-id=1

binlog-do-db=rep

binlog-ignore-db=mysql

其中log-bin为MySQL主库的二进制日志，后面为二进制日志的日志名；server-id为MySQL主从同步的一个标识符，该标识符可以任意选择，但是MySQL主从架构中任意一个设备的server-id都必须唯一；binlog-do-db为做主从同步的数据库，binlog-ignore-db为禁止进行主从同步的数据库。

修改后的配置文件如下所示：

（二）数据库授权配置

在完成MySQL数据库配置文件相关配置后，我们需要创建我们想要进行同步的数据库，以及数据表，该过程就不再赘述了。

之后，我们还需要登入数据库中进行授权，授权命令如下所示：

grant replication slave on *.* to slave_rep@192.168.136.201 identified by '123456';

在上述命令中，replication slave表示一个主从同步的权限，on后面表示权限针对的库和表，点前面的表示库，后面的标识表，在上述命令中，*表示所有库和所有表，to后面表示权限的授予对象，@符号前面表示权限被授予的用户，后面表示该用户允许登录的IP地址，identified表示该用户在登录时需要提供的验证的密码。

三、从库配置

相应的，从库上也需要进行响应的配置。对于从库而言，也需要更改数据库配置文件，打开/etc/my.cnf文件，在文件中与主库同样的位置，写入如下内容：

server-id=2

在这里，server-id不一定必须是2，但是必须与主库的server-id不同，上述内容修改完成后，就可以启动数据库了。

登录数据库之后，在数据库中添加如下内容：

change master to master_host='192.168.136.101',master_user='slave_rep',master_password='123456';

start slave;

执行情况如下：

在上述命令中，master_host、master_user和master_password分别表示向主库进行同步时的主库IP、用户名和密码，如果主库的开放端口不是3306，则还需要指定master_port。

注意，如果采用的是MySQL5.5版本，那么禁止在从库上创建同步的数据库和数据表！ 或许其他的教程中，有需要创建数据库和数据表，或者是采用数据库导入导出的方式进行，但是，在MySQL5.5版本中，在同步时会自动创建库和表，反倒是如果从库中有了该数据库，会出现一些问题，导致主从同步不成功的结果。因此，重要的事情说三遍：

禁止在从库上创建同步的数据库和数据表！

禁止在从库上创建同步的数据库和数据表！

禁止在从库上创建同步的数据库和数据表！

四、现象查看

在完成上述配置后，我们就可以查看数据库主从同步的现象了，首先在从库上执行命令：

show slave status\G

结果如下所示：

如果发现在I/O和SQL Runing上都有yes，则说明主从同步已经配置成功，此时，我们可以发现，从库上已经自动创建了要同步的数据库和内部的数据表。这样，我们尝试在主库中插入数据，发现从库中也会出现同样的内容，这就说明我们主从同步配置成功了。

MySQL主从同步（三）——M-S-S架构配置实战

本次实验，我们使用三台Vmware虚拟机，来实现MySQL主从同步的M-S-S架构配置，各机器IP地址如下：

Master主库：192.168.136.101

Slave中继：192.168.136.201

Slave从库：192.168.136.120

三个MySQL数据库版本都是5.5.68，其余均是默认配置。

二、实验原理

在MySQL主从同步的M-S-S架构中，原理与开头文章推荐处的基本原理是相同的，但是其独有的问题在于Slave中继的作用。在M-S-S架构中，Slave中继的作用是缓解Master主库设备的压力，但是Slave中继设备本身并不存储数据，这一点可以人为将Slave中继的数据表配置成blackhole黑洞引擎模式，在该模式下，所有的数据并不会被数据库所存储。同时，还要注意对该数据表引擎的修改不要记录到二进制日志中。Slave中继在I/O进程从Master主库抓取数据后，虽然不会写入自身的数据库中，但是必须要写到自己的二进制日志中，这样，其他的Slave从库设备即可以从该Slave中继设备的二进制日志中得到数据库的信息了。

三、实验配置

下面，我就进行实际配置。

（一）主库配置

对于主库而言，首先需要配置的是/etc/my.cnf配置文件，需要在配置文件中写入内容如下：

log-bin=master-log

server-id=1

binlog-do-db= rep

binlog-ignore-db=mysql

sync-binlog=1

binlog-format=row

写入后，文件如下所示：

接下来，我们需要启动（重启）Mariadb数据库，重启并登录后，需要对数据库进行授权操作，相关SQL命令如下所示：

grant replication slave on *.* to 'rep_slave'@192.168.136.201 identified by '123456';

这样，主库就已经配置完成了。

（二）Slave中继配置

对于从库而言，需要配置/etc/my.cnf主配置文件如下：

server-id=2

log-bin=slave-relay-log

log-slave-updates=1

binlog-format=row

配置完成后，主配置文件如下所示：

对于Slave中继而言，在完成配置文件配置、启动（重启）并登录数据库后，需要做三件事：配置对Master主库的主从同步、授权Slave从库对自身的主从同步和自身的blackhole引擎设置。

配置对master主库的主从同步SQL语句如下：

change master to

master_host='192.168.136.101',

master_user='rep_slave',

master_password='123456';

授权Slave从库对自身的主从同步SQL语句如下：

grant replication slave on *.* to 'rep_slave'@'192.168.136.%' identified by '123456';

配置自身的blackhole引擎SQL语句如下：

set sql_log_bin=off;

alter table exp ENGINE=blackhole;

set sql_log_bin=on;

由于该过程不能被记录进入二进制日志，因此在进行这一步操作时，必须合理的设置sql_log_bin参数。

相关配置过程如下所示：

（三）Slave从库配置

对于Slave从库，在/etc/my.cnf主配置文件中，只需要写入如下内容：

server-id=3

log-bin=slave-binlog

binlog-format=row

完成后的主配置文件如下所示：

在完成配置后，需要配置Slave从库设备对Slave中继的主从同步，相关SQL命令如下：

change master to

master_host='192.168.136.201',

master_user='rep_slave',

master_password='123456';

这样，当上述所有配置都完成后，我们的MySQL主从同步M-S-S架构就已经配置完成了。

四、效果验证

接下来，我们对我们配置的架构效果进行验证。

（一）slave中继上没有数据

（二）Slave从库上有数据

（三）在slave从库上查看slave状态

（四）在slave中继上查看slave状态

（五）在master主库设备上查看master状态

（六）在slave中继设备上查看master状态

MySQL主从同步（四）——M-M架构配置实战

一、实战环境

本次实验，我们使用两台Vmware虚拟机，来实现MySQL主从同步的M-M架构配置，各机器IP地址如下：

Master1：192.168.136.101

Master2：192.168.136.201

两个MySQL数据库版本都是5.5.68，其余均是默认配置。

二、实验原理

在MySQL主从同步的M-M架构中，两个设备互为主库和从库，当写入数据时，互相进行备份，因此，这两台设备上，要互相首选，互相指定主库。这样一来，对任何一台数据的更改，都会同步到另一台设备上。

三、实验配置

本次实验，两台设备的配置基本相同，因此，在这里就一起介绍了。

（一）配置文件配置

两台设备都需要更改数据库的主配置文件/etc/my.cnf，其中一台设别上写入内容如下：

log-bin=master-log1

server-id=1

binlog-do-db=rep

binlog-ignore-db=mysql

另一台设备的server-id与本设备必须不同，log-bin参数存放的是日志，这里可以相同也可以不相同。修改完成后，两台设备主配置文件如下所示（其中红色部分是新增加内容）：

（二）授权和指定主库配置

在完成配置文件后，就需要分别对对方进行授权，并且指定对方为主库，以其中Master1设备为例，配置的SQL命令如下：

grant replication slave on *.* to 'master1'@'192.168.136.201' identified by 'master1';

change master to

master_host='192.168.136.201',

master_user='master2',

master_password='master2';

另一台设备的配置区别仅在于IP地址以及授权和指定主库的用户名密码上的区别。

另外，与其他模式不同的是，在该模式下，需要先配置好双方的要备份的数据库和表，才能开启slave进程。

四、效果验证

在完成上述配置后，我们来查看一下我们的架构配置结果。

（一）两个设备进程查看

首先，在两台设备上，分别查看slave状态，发现I/O进程和SQL进程全部开启，如下所示：

（二）数据插入测试

其次，我们在两台设备上分别插入数据，发现对数据的插入操作，都可以同步到另一台设备上去，如下所示：

显然，我们的配置达到了我们预期的效果，最终实验成功！

MySQL主从同步（五）——排错思路

一、排错方法技巧

在配置MySQL主从同步过程中，因为各种原因，我们可能会出现一些错误，这导致我们的架构没能够达到理想效果。在这种情况下，学会排错就是从初级运维工程师到高级运维工程师一个必经之路。

在MySQL主从同步的排错中，必须要看懂的是MySQL主从同步中slave的状态，一个典型的查看如下所示：

在MySQL从库设备上，执行show slave status\G命令，可以查看当前从库是否能够与主库建立链接，在建立链接情况下的基本信息以及在没有建立链接下的错误提示。在上图中，各个参数从上到下分别表示主从同步I/O进程的状态，Master主库的IP、用户名、端口号，失败重传时间，Master主库上的日志名称、偏移量，本地中继日志名称、偏移量，I/O进程和SQL进程状态等信息。

如果Slave-IO-Runing以及Slave-SQL-Runing都显示Yes，则表明MySQL主从同步运行正常，之后就可以进行数据测试，但是如果有其他情况，就代表MySQL主从同步状态没有成功建立，存在一些问题。这时，就应该查看SQL的报错信息了。

一个典型的报错信息如下所示：

根据报错信息，我们就可以得到错误原因，就可以以此来深入查找问题、解决问题，最终实现我们想要的结果。因此，学会查看MySQL主从同步中Slave的状态，尤其是报错信息，是运维一个非常重要的环节。

二、防火墙带来的问题

有时，MySQL主从同步过程会由于防火墙的存在导致问题，如下图所示：

在上图中，I/O进程一直处于connecting的状态，这表示该从库在尝试和主库进行链接，但是暂时还没有链接成功，有时还会报错，错误信息与之类似。如果遇上这种情况，除了防火墙的原因外，还有可能是在MySQL数据库配置change master to命令时，MySQL主库IP地址错误、

三、授权错误问题

有时，我们的MySQL主从同步过程中，主库的授权和从库链接主库的用户名、密码不对时，也会报错，报错信息如下：

如果遇上这种情况，我们就要仔细检查，确保MySQL主库的授权和从库的用户名密码配置正确。

四、日志偏移量未对应的问题

有时，我们在主从同步过程中，由于一些其他类型的错误，导致MySQL主库写到二进制日志中的内容与MySQL读MySQL主库的二进制日志中的内容不一致，这也会导致MySQL主从同步过程失败，典型的报错如下所示：

如果遇上这种情况，我们就需要先stop slave，然后重新执行change master to命令，使用master_log_file和master_log_pos参数人为手工指定MySQL的日志名称和偏移量，这样一般就可以恢复正常乐。

五、同步过程出错问题

有时，我们在MySQL主从同步正确配置后，如果从库中进行了一些数据更改操作，那么就会造成MySQL主库和从库的数据不一致，如果在主库中进行的操作与之冲突，就会导致同步过程出错。这时我们就需要仔细查找MySQL主库和从库之间的差异，消除数据库数据差异带来的影响。必要时，可以先人停止主从同步进程，利用mysqldump工具将主库的数据重新导入从库，然后重新进行配置。

有时，如果我们想要在主库上进行一些操作，但是又不想该操作影响到从库，可以使用sql_log_bin参数来手动控制一些操作在主库上执行但是不写入二进制日志中，这样从库就不会进行该操作了。

六、不同版本错误问题

回到我们之前的那张图片：

该错误就是由于在MySQL从库上已经存在了与要同步的数据库同名数据库而导致主从同步配置失败的。

注意，该图是在配置MySQL5.5版本的主从同步时发生的错误，但是在MySQL5.7及以上的版本中，可能就不会出现。因此，在配置不同版本的MySQL主从同步时，也会遇上不同的错误，这时就需要我们仔细分析，或者查看官方文档，找到不同版本之间的差异，进而找到问题、解决问题。

注意，MySQL主从同步尽管在不同版本的数据库中都可以配置，但是同一个架构中的主库和从库必须处于一个大版本之内，比如都属于5.5.X或者5.7.X，如果版本差异过大，则有可能会配置出错。另外，即使是在一个版本内，主库和从库的版本也最好完全一致，或者主库的版本略高于从库版本。

————————————————

版权声明：本文为CSDN博主「永远是少年啊」的原创文章，遵循CC 4.0 BY-SA版权协议

原文链接：https://blog.csdn.net/weixin_40228200/article/details/122154130