mysql主从复制原理以及搭建

吴

吴老二2019-12-16数据库2710 次浏览0 条评论

首先，我们要知道如果使用了主从复制能否解决那些问题？主从复制的原理是什么？依靠什么实现的主从复制，如果主mysql出现问题，从mysql是否能够担起主mysql的能力完成数据的处理。以下内容我们围绕上面的这些疑问进行一一解答。了解了mysql的主从复制，以及原理等，在去实现主从实验那就方便多了。

主从复制原理

主从复制能够解决那些问题？对于这个问题我们需要先了解一下mysql的主从复制原理。下面是一张mysql的主从原理图

在实现主从复制之前我们首先要做的就是对于整个环境有一个了解，主从复制过程中需要有一个服务器充当主服务器(也可以有多个主，不过阿里云上不支持多主多从)。mysql主服务器我们习惯叫它master，从服务器习惯叫slave，在主从的过程中主服务器master需要开启二进制日志，二进制日志记录了mysql的操作日志，从服务器slave会通过一个叫I/O的线程读取到mysql主的binlog，然后传输到slave中，因为读取的日志不属于从服务器的日志，传输过来的日志在slave中就叫中继日志。然后slave会使用SQL的线程读取中继日志，并应用到自身的数据库中，从而实现mysql的主从复制，这就是主从复制的原理。了解了主从复制的原理，我们看看在主从复制中有几种mysql的主从复制方式。

主从复制方法

基于SQL语句的复制(statement-based replication, SBR)--针对在主服务器上执行的SQL语句，在从服务器上执行同样的语句，也是MySql默认采用的一种复制方式，效率比较高。
基于行的复制(row-based replication, RBR)--针对把改变内容的复制到slave上，而不是把命令复制到slave上执行一遍。
混合模式复制(mixed-based replication, MBR)--默认采用的是基于语句的复制，一旦发现基于语句无法精确复制时，会采用基于行的复制。

基于SQL语句的复制方式是最古老的，也是目前默认的方式，后面两种的复制方式也是在MySQL 5以后才有的复制方式。下面针对上面的两种模式总结一下优缺点：基于SQL的复制优点：

历史悠久，技术比较成熟，binlog文件较小，log可读
binlog中包含了所有数据库更改信息，可以以此来审核数据的安全等情况
binlog 可以用于实时的还原，而不仅仅用于复制
主从版本可以不一样，从服务器版本可以比主服务器版本高

缺点：

不是所有的 UPDATE 语句都能被复制，尤其是包含不确定操作的时候。
复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁
对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
执行复杂语句如果出错的话，会消耗更多资源

基于行复制的优点：

任何情况下都可以被复制，这对复制来说比较安全可靠
和大多数数据库的复制技术一样，比较成熟
多数情况下，从服务器上的表如果有主键的话，复制就会快很多。
从服务器上采用多线程来执行复制成为可能
不会出现某些特定情况下的存储过程，或function，以及trigger 的调用和触发无法被正确复制的问题。

缺点：

相比SBR的binlog 大了很多，还原的时候可能比较慢
复杂的回滚时binlog中会包含大量的数据
主服务器上执行UPDATE语句是，所有发生变化的记录都会在binlog中。而基于行的复制只会写一次，这会导致频繁发生binlog的并发写问题。
无法从bilog中看到复制了写什么语句
采取RBR模式记录的话，log是不可读的。（经过加密）注：采用 RBR 模式后，能解决很多原先出现的主键重复问题。
当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生

主从复制方式

全同步复制：在主节点上写入的数据，在从服务器上都同步完了以后才会给客户端返回成功消息，相对来说比较安全，比较靠谱。但是返回信息的时间比较慢异步复制：在主节点接收到客户端发送的数据就给客户端返回执行成功的消息。然后再开始再从上面同步，不太靠谱，因为如果我给你返回消息以后，但是我的主节点坏了，并没有在从节点上同步完成，数据就会丢失，就算给客户端返回成功消息，但是我执行是不成功的(mysql默认使用异步复制)

半同步复制：在接收到客户端发送的数据，主节点会将数据同步到至少一台从节点以后再给客户端发送执行成功的消息，这个以前是没有的，是谷歌贡献的一个插件才可以做，它相当于是同步和异步的一个中和的复制方式。

前两种方式都是最少两台mysql就可以完成的实验，半同步复制最少三台mysql才可以，如果主节点的mysql出现问题，最少有一个从节点的数据是完整的。后面实验的时候可以验证一下这个问题。

主从读写分离

读写分离是在主从复制的基础上实现的，先有主从复制才会有读写分离。读写分离是为了分担主的压力，把不同的请求发送到相应的服务器上。那么如果判断请求的是读操作还是写操作呢？这就需要用到mysql的中间件，最早期的时候是依据程序中的代码根据读写请求发送到不同的机器上，后来有了中间件mysql_proxy，mysql_proxy就会根据用户发送的请求判断是读还是写，然后在发送到相应的服务器上。360在mysql_proxy 0.8.2的基础上进行了优化，增加朗一些新的特性，中间件的名字就叫Altas，优化后的Altas运行mysql能够每天承载的读写请求达十几亿条，并且支持事务以及存储过程。阿里云也开发了支持读写分离的中间件，叫Amoeba，不过不支持事物以及存储过程。

实验环境

主机名	主机IP	角色
wulaoer_mysql01	10.211.55.130	主服务
wulaoer_mysql02	10.211.55.131	从服务01
wulaoer_mysql03	10.211.55.132	从服务02

我已经在三个节点上都安装了相同版本mysql 5.6。如需要安装方式请参考：https://www.wulaoer.org/?p=220 。

开启二进制

先查看一下mysql的二进制日志是否开启，因为复制需要读取二进制日志，我的已经开启，我做了一个关闭的，演示一下开启二进制。

[root@wulaoer_mysql01 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.44 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |  #OFF是关闭状态，NO是开启
+---------------+-------+
1 row in set (0.10 sec)

mysql> show master status;  #查看一下master状态
Empty set (0.00 sec)

这是关闭的状态，下面我开启一下mysql的二进制日志，需要修改一下/etc/my.cnf文件，根据自己的mysql安装路径找到自己的my.cnf文件，在[mysqld]下。

[root@wulaoer_mysql ~]# vi /etc/my.cnf
...............................
[mysqld]
....................................

log-bin=mysql-bin      #如果有注释去掉，不存在添加--开启二进制日志
binlog_format=mixed   
server-id   = 1        #当前节点唯一全局ID号
expire_logs_days = 10

重启一下mysql，重新登录一下查看是否开启。

[root@wulaoer_mysql01 ~]# /etc/init.d/mysql  restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@wulaoer_mysql01 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.44-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

开启了二进制日志，下面设置主从复制，

主从复制配置

先给mysql master节点配置授权，授权客户端网段，并赋予读权限，并设置密码，方便mysql slave节点能够读取到mysql master节点的信息，并传输。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      502 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> grant replication slave on *.* to 'wulaoer'@'10.211.55.%' identified by 'wulaoer';
Query OK, 0 rows affected (0.00 sec)

在从节点上添加远程连接，并设置同步日志以及日志的Position(就是日志的进度)，然后在开启中继，进行数据复制。先设置一下从服务的ID，下面我针对两个从的id依次为2，3

[root@wulaoer_mysql01 ~]# vi /etc/my.cnf 
..........
log-bin=mysql-bin
binlog_format=mixed
server-id   = 2   #原1改成2
expire_logs_days = 10
[root@wulaoer_mysql02 ~]# vi /etc/my.cnf 
..........
log-bin=mysql-bin
binlog_format=mixed
server-id   = 2   #原1改成3
expire_logs_days = 10

设置从库连接主库并开启中继

[root@wulaoer_mysql01 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.44-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Database changed
mysql> CHANGE MASTER TO MASTER_HOST='10.211.55.130', MASTER_USER='wulaoer', MASTER_PASSWORD='wulaoer', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=502;
Query OK, 0 rows affected, 2 warnings (0.13 sec)

mysql> start slave; 
Query OK, 0 rows affected (0.10 sec)

[root@wulaoer_mysql02 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.44-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Database changed
mysql> CHANGE MASTER TO MASTER_HOST='10.211.55.130', MASTER_USER='wulaoer', MASTER_PASSWORD='wulaoer', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=502;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave; 
Query OK, 0 rows affected (0.00 sec)

这里要注意一下，如果第一次添加的配置连接主库的信息错误，或者配置错了，可以先关闭中继，从新配置即可，关闭中继

mysql> CHANGE MASTER TO MASTER_HOST='10.211.55.130', MASTER_USER='wulaoer', MASTER_PASSWORD='wulaoer', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=502;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql>  CHANGE MASTER TO MASTER_HOST='10.211.55.130', MASTER_USER='wulaoer', MASTER_PASSWORD='wulaoer', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=502;
Query OK, 0 rows affected, 2 warnings (0.31 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看主从复制状态：

#从库wulaoer_mysql01
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.211.55.130 #主库master的ip地址
                  Master_User: wulaoer        #连接主库的用户
                  Master_Port: 3306           #连接主库的端口
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004 #I/O线程当前正在读取的主服务器二进制日志文件的名称。
          Read_Master_Log_Pos: 1020             #在当前的主服务器二进制日志中，I/O线程已经读取的位置。
               Relay_Log_File: wulaoer_mysql01-relay-bin.000002 #SQL线程当前正在读取和执行的中继日志文件的名称。
                Relay_Log_Pos: 801  #在当前的中继日志中，SQL线程已读取和执行的位置。
        Relay_Master_Log_File: mysql-bin.000004  #由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称。
             Slave_IO_Running: Yes     #Yes表示io_thread的和主库连接正常并能实施复制工作，No则说明与主库通讯异常，多数情况是由主从间网络引起的问题；
            Slave_SQL_Running: Yes     #具体就是语句是否执行通过，常会遇到主键重复或是某个表不存在。 
              Replicate_Do_DB: 
          Replicate_Ignore_DB:           #使用–replicate-do-db和–replicate-ignore-db选项指定的数据库清单。
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table:     #使用–replicate-do-table,–replicate-ignore-table,–replicate-wild-do-table和–replicate-wild-ignore_table选项指定的表清单。
                   Last_Errno: 0   #Last_Errno: 1051 Last_Error: error ‘Unknown table ‘z” on query ‘drop table z’该消息指示，表z曾经存在于在主服务器中并已被取消了，但是它没有在从属服务器中存在过，因此对于从属服务器，DROP TABLE失败。（举例说明，在设置复制时，如果您忘记了把此表拷贝到从属服务器中，则这有可能发生。）
                   Last_Error:  
                 Skip_Counter: 0    #最近被使用的用于SQL_SLAVE_SKIP_COUNTER的值。
          Exec_Master_Log_Pos: 1020  #来自主服务器的二进制日志的由SQL线程执行的上一个时间的位置（Relay_Master_Log_File）。在主服务器的二进制日志中的(Relay_Master_Log_File, Exec_Master_Log_Pos)对应于在中继日志中的(Relay_Log_File,Relay_Log_Pos)。
              Relay_Log_Space: 984   #所有原有的中继日志结合起来的总大小。
              Until_Condition: None   
               Until_Log_File: 
                Until_Log_Pos: 0   #Until_Condition,Until_Log_File,Until_Log_Pos在START SLAVE语句的UNTIL子句中指定的值。
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
#Master_SSL_Allowed,Master_SSL_CA_File,Master_SSL_CA_Path,Master_SSL_Cert,Master_SSL_Cipher,Master_SSL_Key这些字段显示了被从属服务器使用的参数。这些参数用于连接主服务器。
#Master_SSL_Allowed具有以下值：
# 如果允许对主服务器进行SSL连接，则值为Yes
# 如果不允许对主服务器进行SSL连接，则值为No
# 如果允许SSL连接，但是从属服务器没有让SSL支持被启用，则值为Ignored。与SSL有关的字段的值对应于–master-ca,–master-capath,–master-cert,–master-cipher和–master-key选项的值
        Seconds_Behind_Master: 0 #如果主服务器和从属服务器之间的网络连接较快，则从属服务器I/O线程会非常接近主服务器，所以本字段能够十分近似地指示，从属服务器SQL线程比主服务器落后多少。如果网络较慢，则这种指示不准确；从属SQL线程经常会赶上读取速度较慢地从属服务器I/O线程，因此，Seconds_Behind_Master经常显示值为0。即使I/O线程落后于主服务器时，也是如此。换句话说，本列只对速度快的网络有用。即使主服务器和从属服务器不具有相同的时钟，时间差计算也会起作用（当从属服务器I/O线程启动时，计算时间差。并假定从此时以后，时间差保持不变）。如果从属SQL线程不运行，或者如果从属服务器I/O线程不运行或未与主服务器连接，则 Seconds_Behind_Master为NULL（意义为“未知”）。举例说明，如果在重新连接之前，从属服务器I/O线程休眠了master- connect-retry秒，则显示NULL，因为从属服务器不知道主服务器正在做什么，也不能有把握地说落后多少
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 13fe5e61-1d96-11ea-a91d-000c299d5dbc
             Master_Info_File: /usr/local/mysql/var/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

#从库wulaoer_mysql02
mysql>  show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.211.55.136
                  Master_User: wulaoer
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1020
               Relay_Log_File: wulaoer_mysql02-relay-bin.000002
                Relay_Log_Pos: 801
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1020
              Relay_Log_Space: 985
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 13fe5e61-1d96-11ea-a91d-000c299d5dbc
             Master_Info_File: /usr/local/mysql/var/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

也可以使用"show full processlist\G;",到现在mysql的主从复制已经搭建完成，这里要注意，如果防火墙没有关闭那就需要你先关闭或者设置防火墙的端口，把主从之间的服务能够通过3306端口进行通信才可以实现主从复制。下面验证一下主从复制的功能，我们在主服务器上创建一个库，并在库中写一些数据，查看一下从库中是否有数据生成。

#主库wulaoer_mysql01
mysql> create database wulaoer;
Query OK, 1 row affected (0.15 sec)
mysql> CREATE TABLE object(
    -> sno VARCHAR(20) NOT NULL PRIMARY KEY COMMENT"编号",
    -> sname VARCHAR(20) NOT NULL COMMENT"语言",
    -> ssex VARCHAR(20) NOT NULL COMMENT"语言类型",
    -> sbirthday datetime COMMENT"发行时间"
    -> );
Query OK, 0 rows affected (0.12 sec)
mysql> use wulaoer;
Database changed
mysql> INSERT INTO object values(100,"python","面向对象语言",19910000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO object values(101,"java","面向对象语言",19950000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO object values(102,"golong","不是面向对象语言",20090000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO object values(103,"ruby","面向对象语言",19950000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO object values(104,"C","面向对象语言",19720000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO object values(105,"C++","面向对象语言",19540000);
Query OK, 1 row affected (0.00 sec)
#从库wulaoer_mysql02
mysql> use wulaoer
Database changed
mysql> show tables;
+-------------------+
| Tables_in_wulaoer |
+-------------------+
| object            |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from object;
+-----+--------+--------------------------+---------------------+
| sno | sname  | ssex                     | sbirthday           |
+-----+--------+--------------------------+---------------------+
| 100 | python | 面向对象语言             | 1991-00-00 00:00:00 |
| 101 | java   | 面向对象语言             | 1995-00-00 00:00:00 |
| 102 | golong | 不是面向对象语言         | 2009-00-00 00:00:00 |
| 103 | ruby   | 面向对象语言             | 1995-00-00 00:00:00 |
| 104 | C      | 面向对象语言             | 1972-00-00 00:00:00 |
| 105 | C++    | 面向对象语言             | 1954-00-00 00:00:00 |
+-----+--------+--------------------------+---------------------+
6 rows in set (0.00 sec)
#从库wulaoer_mysql03
mysql> use wulaoer;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_wulaoer |
+-------------------+
| object            |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from object;
+-----+--------+--------------------------+---------------------+
| sno | sname  | ssex                     | sbirthday           |
+-----+--------+--------------------------+---------------------+
| 100 | python | 面向对象语言             | 1991-00-00 00:00:00 |
| 101 | java   | 面向对象语言             | 1995-00-00 00:00:00 |
| 102 | golong | 不是面向对象语言         | 2009-00-00 00:00:00 |
| 103 | ruby   | 面向对象语言             | 1995-00-00 00:00:00 |
| 104 | C      | 面向对象语言             | 1972-00-00 00:00:00 |
| 105 | C++    | 面向对象语言             | 1954-00-00 00:00:00 |
+-----+--------+--------------------------+---------------------+
6 rows in set (0.10 sec)

验证数据同步正常，整个mysql的主从复制实验测试完成，下面增加个知识点就是mysql的半同步实现。

半同步复制部署

这里的实验是在上面的基础上实现的，必须满足一下条件： 1、mysql版本 5.5以上 2、变量have_dynamic_loading为YES(查看命令：show variables like "have_dynamic_loading";主库从库都一样) 3、mysql的主从复制环境下完成，同步的不是某个库而是整个数据库首先要加载一下半同步需要的插件，权限最后是root用户下执行，我们可以看看mysql安装环境下某人有没有安装，看一下mysql的插件路径：

mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.00 sec)

我们要安装相关的插件，那要保证相关目录下有插件才可以，上面找的插件路径我们看一下路径下是否有"semisync_master.so"

[root@wulaoer_mysql01 ~]# cd /usr/local/mysql/lib/plugin/
[root@wulaoer_mysql01 plugin]# ll semisync_*
-rwxr-xr-x. 1 mysql mysql 515688 Dec  7 17:38 semisync_master.so
-rwxr-xr-x. 1 mysql mysql 276424 Dec  7 17:38 semisync_slave.so

从服务也要查看一下，这两个文件一个是针对主库的一个是针对从库的，一般编译安装之后就会存在。下面在主库上安装一下，

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  #主库安装插件
 Query OK, 0 rows affected (0.17 sec)
mysql>  INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';   #从库安装插件
Query OK, 0 rows affected (0.00 sec)
mysql>  show plugins;         #验证插件安装是否成功
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL               | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |  #主库安装成功
+----------------------------+----------+--------------------+--------------------+---------+
43 rows in set (0.00 sec)
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%'; #也可以这样验证
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

如果按照错误可以卸载，从库安装卸载方法一样。

mysql> uninstall plugin  rpl_semi_sync_master;  
Query OK, 0 rows affected (0.00 sec)

安装完插件那就需要启动它，默认插件是关闭状态，主库从库启动方法如下：

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;  #主库开启
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;   #从库开启
Query OK, 0 rows affected (0.01 sec)

这是在终端开启方法，也可以在配置文件(my.cnf)中修改相应的参数，推荐修改配置文件参数开启,在mysqld下面增加

[root@wulaoer_mysql01 ~]# vi /etc/my.cnf
.......................................
[mysqld]
..................................................
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
................................................
[root@wulaoer_server02 ~]# vi /etc/my.cnf 
.................................................
[mysqld]
..................................................
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
[root@wulaoer_server03 ~]# vi /etc/my.cnf 
.................................................
[mysqld]
..................................................
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

添加之后重启一下数据库，配置才能生效。如果是在高可用环境下，master和slave需要同时启动，以便在切换后能继续使用半同步复制！即主从数据库的my.cnf配置文件中都要增加：

plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

添加完之后需要重启一下从库上的IO线程，

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

下面验证一下半同步是否在运行

#mysql_wulaoer01
mysql> show status like 'Rpl_semi_sync_master_status';  
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)
#mysql_wulaoer02
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)
#mysql_wulaoer03
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

半同步并不是完全的半同步，每次半同步都有时间限制，超过时间就会自动暂时关闭半同步转成异步复制(有一次超时就自动转为异步)。当master dump线程发完一个事务的所有事件之后，如果rpl_semi_sync_master_timeout内，收到了从库的响应，则主从就会重新恢复为半同步复制。在主库中查看一下超时时间，默认时间可以修改。

mysql> show variables like "rpl_semi_sync_master_timeout";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 10000 |
+------------------------------+-------+
1 row in set (0.00 sec)

半同步默认超时时间是10s，这个可以修改，在my.cnf文件中mysqld下增加一个"rpl_semi_sync_master_timeout=60000",然后重启一下mysql，超时时间就改成了一分钟。

[root@wulaoer_mysql01 ~]# vi /etc/my.cnf
.......................................
[mysqld]
..................................................
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=60000   #增加了这个，然后重启一下mysql
................................................
mysql> show variables like "rpl_semi_sync_master_timeout";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 60000 |
+------------------------------+-------+
1 row in set (0.00 sec)

下面验证一下，我们先在主库上创建一个库并创建表，表中插入一条数据。也可以使用原来的库，这里我就使用原来的库了，先创建一个表在插入一条数据。

mysql>  create table wulaoer.ceshi(id int);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into wulaoer.ceshi values(1);
Query OK, 1 row affected (0.00 sec)

插入好数据之后，把从库中的中继关闭，执行"stop slave;"

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

关闭了中继，并查看异步复制也关闭了，这个时候在主库中插入一条数据。异步复制就会出现超时的现象。

mysql> insert into bobo.ceshi values(2);          
Query OK, 1 row affected (1 min 0.00 sec)          #这个时间就是定义的超时时间1分钟
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

然后我们在再从库上开启中继，执行"start slave;"两个从库都要开启，并查看一下状态。

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

然后在主库上重新插入一条数据，在看一下异步复制

mysql>  insert into bobo.ceshi values(3);
Query OK, 1 row affected (0.00 sec)

mysql>  show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

瞬间就执行了，整个异步复制环境测试完成。

评论需登录后发表（注册并验证邮箱）。

还没有评论，来抢沙发～

mysql主从复制原理以及搭建

主从复制原理

主从复制方法

主从复制方式

主从读写分离

实验环境

开启二进制

主从复制配置

半同步复制部署

相关文章

PostgreSQL 18.6 安装记录

ProxySQL 基本排查方法指南

ProxySQL 安装与基本配置指南 —— 实现 MySQL 读写分离

评论 0