【最新】MySQL主从复制主主复制原理/流程/教程/代码

吾爱分享
建站运维评论12字数 4863阅读16分12秒阅读模式

2024年最新方法：MySQL主从复制、主主复制详细图文教程

本文全篇无废话，一文快速搞懂Mysql 主从复制/主主复制。整理并汇总了：Mysql主从复制和主主复制的图文教程、复制流程和模式的说明、常用代码汇总。最新且稳定的Mysql主从复制方案教程。文章源自 吾爱分享 吾爱分享网-https://www.wuaishare.cn/3499.html

本教程中使用的原理与宝塔面板软件商店中的“MySQL主从复制(重构版)”插件原理相同，如果你在使用该插件时遇到了问题也可以通过学习本文进行错误排查，搞懂原理后排错就会变得很简单。文章源自 吾爱分享 吾爱分享网-https://www.wuaishare.cn/3499.html

MySQL主从复制原理

MySQL主从复制原理图

【最新】MySQL主从复制主主复制原理/流程/教程/代码
  1. 存在几个线程：主库一个线程，从库两个线程。
  2. 主库生成一个 log dump 线程与从库IO线程进行交互。
  3. 从库IO线程请求主库binlog，写入到自己的中继日志relay log。
  4. 从库SQL线程读取中继日志，解析然后写入到从库中。

MySQL主从复制流程

【最新】MySQL主从复制主主复制原理/流程/教程/代码

Master端需要开启binlog日志，Slave端需要开启relaylog。文章源自 吾爱分享 吾爱分享网-https://www.wuaishare.cn/3499.html

  1. Slave端的I/O读取master.info文件，获取binlog文件名和位置点，然后向Master端的I/O线程请求，该binlog文件名和位置点的binlog信息。
    （master.info文件在配置主从复制时使用change master命令来指定生成）
  2. Master端的I/O线程会根据Slave端的I/O线程请求的信息来读取Master的binlog日志信息与及读取到最新的binlog文件名和位置点一同返回给Slave的I/O线程。
  3. Slave端的I/O线程会把获取到的binlog日志写入relaylog（中继日志）文件中，并且更新master.info文件信息。（把读取到Master最新的binlog日志文件名和位置点更新到master.info文件中，下一次当前位置去读取Master的binlog日志）
  4. Slave端的SQL线程会定期读取relaylog，把二进制的日志解析成SQL语句，并执行这些SQL语句，同步数据到从库中。

注意：mysql主从复制要求主从两个数据库版本相同，或者从机比主机版本高；要实现主主复制，则需要两个数据库版本相同。文章源自 吾爱分享 吾爱分享网-https://www.wuaishare.cn/3499.html

MySQL主从复制模式

1.  异步模式（mysql async-mode）文章源自 吾爱分享 吾爱分享网-https://www.wuaishare.cn/3499.html

主节点不会主动push bin文章源自 吾爱分享 吾爱分享网-https://www.wuaishare.cn/3499.html

log到从节点，这样有可能导致failover的情况下，也许从节点没有即时地将最新的bin文章源自 吾爱分享 吾爱分享网-https://www.wuaishare.cn/3499.html

log同步到本地。文章源自 吾爱分享 吾爱分享网-https://www.wuaishare.cn/3499.html

【最新】MySQL主从复制主主复制原理/流程/教程/代码

2.  半同步模式(mysql semi-sync)文章源自 吾爱分享 吾爱分享网-https://www.wuaishare.cn/3499.html

主节点只需要接收到其中一台从节点的返回信息，就会commit；否则需要等待直到超时时间然后切换成异步模式再提交；这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交后，binlog至少传输到了一个从节点上，不能保证从节点将此事务更新到db中。性能上会有一定的降低，响应时间会变长。文章源自 吾爱分享 吾爱分享网-https://www.wuaishare.cn/3499.html

【最新】MySQL主从复制主主复制原理/流程/教程/代码

3.  全同步模式

主节点和从节点全部执行了commit并确认才会向客户端返回成功。

4.  GTID复制模式【推荐】

在传统的复制里面，当发生故障，需要主从切换，需要找到binlog和pos点，然后将主节点指向新的主节点，相对来说比较麻烦，也容易出错。在MySQL 5.6里面，不用再找binlog和pos点，我们只需要知道主节点的ip，端口，以及账号密码就行，因为复制是自动的，MySQL会通过内部机制GTID自动找点同步。

多线程复制（基于库），在MySQL 5.6以前的版本，slave的复制是单线程的。一个事件一个事件的读取应用。而master是并发写入的，所以延时是避免不了的。唯一有效的方法是把多个库放在多台slave，这样又有点浪费服务器。在MySQL 5.6里面，我们可以把多个表放在多个库，这样就可以使用多线程复制。

基于GTID复制实现的工作原理

主节点更新数据时，会在事务前产生GTID，一起记录到binlog日志中。

从节点的I/O线程将变更的bin log，写入到本地的relay log中。

SQL线程从relay log中获取GTID，然后对比本地binlog是否有记录（所以MySQL从节点必须要开启binary log）。

如果有记录，说明该GTID的事务已经执行，从节点会忽略。

如果没有记录，从节点就会从relay log中执行该GTID的事务，并记录到bin log。

在解析过程中会判断是否有主键，如果没有就用二级索引，如果有就用全部扫描。

复制方式

1、基于语句的复制：在Master上执行的SQL语句，在Slave上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制

2、基于行的复制：把改变的内容复制到Slave，而不是把命令在Slave上执行一遍。从MySQL5.0开始支持

3、混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。【推荐】

相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。

MySQL主从复制教程

本教程讲采用基于GTID的复制模式(GTID的优势可以看这里)，配置过程如下：

1. 两台数据库服务器，IP分别为 192.168.216.128（后面成为“A服务器”）和 192.168.216.129（后面成为“B服务器”），在服务器上装MYSQL（我的配置版本为5.5.56）

2. 打开 A 服务器上的MYSQL的配置文件 /etc/my.cnf （路径根据自己服务器的情况来看），将其中的 server-id 设为1（默认为1，总之两台服务器要设置为不同的ID）,然后重启MYSQL服务

3. 打开 B 服务器上的MYSQL的配置文件 /etc/my.cnf （路径根据自己服务器的情况来看），将其中的 server-id 设为2（默认为1），然后重启MYSQL服务

4. 设置 A 为主服务器，那么在主服务器上加一个从服务器可以登录的用户，语句如下： 

CREATE USER 'root'@'%' IDENTIFIED BY 'wuaishare@123';
GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.216.129';
FLUSH PRIVILEGES;

建好后，在 B 服务器上执行以下语句 

mysql -h 192.168.216.128 -u root -p

然后输入密码试一下可不可以连上，如果可以，则正确，如果连不上，看一下什么原因，是否是防火墙的原因，如果是则去配置防火墙的规则。

5. 以上完成后在主服务器A上执行以下语句，查询master的状态 

SHOW MASTER STATUS;
【最新】MySQL主从复制主主复制原理/流程/教程/代码

可以看到以上结果，这儿我们使用master_auto_position配置，因此不需要再关注File 和 Position，其它的两个分别是白名单和黑名单，意思为同步哪几个数据库和不同步哪几个数据库，可自行根据需求进行设置。在从库B上执行以下语句： 

CHANGE MASTER TO
  MASTER_HOST='192.168.216.128',
  MASTER_USER='root',
  MASTER_PASSWORD='wuaishare@123',
  MASTER_AUTO_POSITION=1;

6. 执行完毕后，在从库B上继续执行如下语句： 

START SLAVE;
SHOW SLAVE STATUS\G;

这样，查看从服务器的状态，如果状态中的用红线标出来两个参数的值都为YES，那证明配置已经成功，否则可以检查一下具体问题出现在什么地方。

【最新】MySQL主从复制主主复制原理/流程/教程/代码

这样，就算配置完成了。在主库中新建数据库，新建一张表，插几条数据，到从库上查询一下看是否已经同步过来。

如果失败，可以从以下几个方面去排查问题：　　

  1. 首先试一下主从服务器相互之间是否PING 得通
  2. 试一下远程连接是否正确，如果连不上，则有可能是网卡不一致、防火墙没有放行3306 端口
  3. server-id 是否配成一致，主从不能一致
  4. bin-log 的信息是否正确

MySQL主主复制教程

主主复制的原理实际上是主从复制的原理，让两台服务器互为主从，就实现了主主复制。

1. 在主服务器A上配置 /etc/my.cnf 文件，配置如下： 

binlog_format=mixed
auto_increment_increment=2         #步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_offset=1            #起始值。一般填第n台主MySQL。此时为第一台主MySQL
binlog-ignore=mysql                #忽略mysql库【我一般都不写】
binlog-ignore=information_schema   #忽略information_schema库【我一般都不写】
gtid-mode = on

配置之后重启MYSQL服务

2.在从服务器B上配置 /etc/my.cnf 文件，配置如下 

binlog_format=mixed
auto_increment_increment=2   #步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_offset=2   #起始值。一般填第n台主MySQL。此时为第二台主MySQL
binlog-ignore=mysql   #忽略mysql库【我一般都不写】
binlog-ignore=information_schema   #忽略information_schema库【我一般都不写】
gtid-mode = on

配置之后重启MYSQL服务

3. 在从服务器B上添加一个主服务器A可以访问的用户，命令如下： 

CREATE USER 'root'@'%' IDENTIFIED BY 'wuaishare@123';
GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.216.128';
FLUSH PRIVILEGES

建好后，在 A 服务器上执行以下语句 

mysql -h 192.168.216.129 -u root -p

输入密码，如果可以连上，则进行下一步，连不上的话，参考上面进行问题排查。

4. 因为要互为主从，所以现在从服务器也是master ，所以也要查看一下状态 

SHOW MASTER STATUS;
【最新】MySQL主从复制主主复制原理/流程/教程/代码

查到相应的信息后，在原来的主服务器上执行以下命令（因为现在它现在也是另一台的从服务器） 

CHANGE MASTER TO
  MASTER_HOST='192.168.216.129',
  MASTER_USER='root',
  MASTER_PASSWORD='raisecom@123',
  MASTER_AUTO_POSITION=1;

5. 执行完毕后，在原主库上继续执行如下语句： 

START SLAVE;
SHOW SLAVE STATUS\G;

同上，如果出现如下画面，则证明配置成功。

【最新】MySQL主从复制主主复制原理/流程/教程/代码

6. 在两台服务器的MYSQL中分别进行一些建库、建表、插入、更新等操作，看一下另一台会不会进行同步，如果可以则证明主主配置成功，否则还是上面的排错方法，进行错误排查。

MySQL主从复制常用代码汇总

MySQL连接 

mysql -u root -p

创建用户 

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

授予用户复制权限 

GRANT REPLICATION SLAVE ON *.* TO 'username'@'%';

查询并列出MySQL中所有用户和host权限 

SELECT User, Host FROM mysql.user;

查询某1个用户的host权限 

SELECT User, Host FROM mysql.user WHERE User='root';

更新某1个用户的host权限 

UPDATE mysql.user SET Host = '%' WHERE User = 'root';

刷新权限表 

FLUSH PRIVILEGES

显示主库状态 

SHOW MASTER STATUS;

显示从库状态 

SHOW SLAVE STATUS\G;

启动从库 

START SLAVE;

停止从库 

STOP SLAVE;

退出MySQL 

EXIT;
历史上的今天
1 月
20
吾爱分享

发表评论