MySQL 主从搭建与管理

博主： admin
发布时间：2025 年 07 月 20 日
20次浏览
2367字数
分类：数据库已索引

主从搭建

1.创建主库，my.cnf 模版已经打开了相关关键参数

准备主库

# rm /opt/database/3306/auto.cnf
mysql> create user 'repl'@'主库IP' identified by 'xxx';
mysql> create user 'repl'@'从库IP' identified by 'xxx';
mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'主库IP';
mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'从库IP';
alter user 'root'@'localhost' identified by 'xxx';

3.备份主库

如果存储引擎都支持事务：

mysqldump -u root -p --single-transaction -A -B --master-data=2 > /opt/database/alldb.sql

-- master-data=2 是之前没有使用 GTID 时的方法，可以保留，但实际上不会用到。

如果有不支持事务的存储引擎：

mysqldump -u root -p -A -B -x --events --master-data=2 > /xxx

-x 会在备份过程中锁表

如果使用 mydumper 进行备份恢复，其产生的 metadata 文件示例如下：

Started dump at: 2019-12-25 09:24:41
SHOW MASTER STATUS:
    Log: bin.000009
    Pos: 453
    GTID:0e7c2f36-ca06-11e9-bdf8-000c291ca865:1

Finished dump at: 2019-12-25 09:24:41

4.准备从库

# rm -f /opt/database/3306/auto.cnf
# vim /etc/my.cnf
server-id = 22
read_only = 1
# /etc/init.d/mysqld start
mysql> alter user 'root'@'localhost' identified by 'xxx'; < --- 将备库 root 密码修改为主库一致

5.恢复数据库并配置主从关系

如果是 mydumper 备份，需要配置跳过 GTID，执行这一步的作用是表明这儿的GTID已经在备份中包含了，不需要在 slave 上重做了。如果是通过 mysqldump 进行的备份，跳过该步骤，在备份文件中会自动包含 SET 命令。

mysql> SET @@GLOBAL.GTID_PURGED='0e7c2f36-ca06-11e9-bdf8-000c291ca865:1';
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

mysql> reset master;
mysql> SET @@GLOBAL.GTID_PURGED='0e7c2f36-ca06-11e9-bdf8-000c291ca865:1';

# mysql -uroot -p < alldb.sql
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

mysql> reset master;
mysql> reset slave;

# mysql -uroot -p < alldb.sql

mysql> change master to master_host="主库IP",master_port=3306,master_user='repl',master_password='xxx',master_auto_position=1;
以前都是通过 master_log_file 和 master_log_pos 来定位，引入 GTID 后，使用 master_auto_position 自动定位

mysql> start slave;
mysql> show slave status\G

查看从库状态：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.111.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000001    <-- change master中的filename
          Read_Master_Log_Pos: 585            <-- change master中的pos
               Relay_Log_File: object-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000001
             Slave_IO_Running: No            <-- 目前slave还未运行，所以是No
            Slave_SQL_Running: No            <-- 目前slave还未运行，所以是No

开启同步

mysql> start slave;

查看从库状态：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event        <-- IO 线程的状态
                  Master_Host: 192.168.111.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000001                            <-- IO线程读取到的文件 [The name of the source binary log file from which the I/O thread is currently reading.]
          Read_Master_Log_Pos: 585                                    <-- IO线程读取文件中的位置
               Relay_Log_File: object-relay-bin.000003
                Relay_Log_Pos: 314
        Relay_Master_Log_File: bin.000001                            <-- SQL线程执行到的文件 [The name of the source binary log file containing the most recent event executed by the SQL thread.]
             Slave_IO_Running: Yes                                    <-- IO 线程正在运行
            Slave_SQL_Running: Yes                                    <-- SQL 线程正在运行
              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: 585                                    <-- SQL线程执行到文件的位置 [The position in the current source binary log file to which the SQL thread has read and executed]
              Relay_Log_Space: 522
              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                                    <-- Slave 落后 Master 的秒数
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:                                         <-- IO 线程相关的错误
               Last_SQL_Errno: 0
               Last_SQL_Error:                                         <-- SQL 线程相关的错误 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 128
                  Master_UUID: 0e7c2f36-ca06-11e9-bdf8-000c291ca865
             Master_Info_File: /usr/local/mysql-5.7.23-linux-glibc2.12-x86_64/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

通过 show processlist 在主库可以看到 "Binlog Dump" 进程，在从库可以看到 IO 进程和 SQL 进程。如果在备库上开启了并行复制，还可以看到 Coordinator 线程。

关于 Seconds_Behind_Master

This field is an indication of how “late” the replica is:
• When the replica is actively processing updates, this field shows the difference between the current timestamp on the replica and the original timestamp logged on the source for the event currently being processed on the replica.
• When no event is currently being processed on the replica, this value is 0.
In essence, this field measures the time difference in seconds between the replica SQL thread and the replica I/O thread. If the network connection between source and replica is fast, the replica I/O thread is very close to the source, so this field is a good approximation of how late the replica SQL thread is compared to the source.

Seconds_Behind_Master 字面意思为 Slave 落后 Master 的秒数，但是实际上并不是十分准确。其计算的方式为：Slave 的 SQL 线程执行时刻的时间减去 event 产生时刻的时间（event_header 中的前4个字节就是 timestamp）。Seconds_Behind_Master 测算主从延时的方式并不准确，因为回放的SQL线程可能落后很多。我们可以使用 Percona ToolKit 中的 pt-heardbeat 工具来计算主从之间的延时时间，其原理是：在 Master 节点上创建一个带有时间戳字段的表，pt-heartbeat --update 每隔一段时间写入时间戳，通过主从复制，将表中的内容（带有时间戳的记录）复制到 Slave 节点，在某台主机上运行 pt-heartbeat --monitor 连接到从服务器，使用本地时间戳减去复制到 Slave 节点的记录的时间戳，就可知道Slave落后Master的时间。

主从复制的启停

mysql> start slave;
mysql> stop slave;
mysql> stop slave io_thread;
mysql> stop slave sql_thread;

查看复制关系

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|       130 |      | 3306 |       128 | b6c03287-15af-11ea-a20c-000c29e56203 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

看到 HOST 字段为空，如果想能显示是哪一台主机，需要在 slave 的 my.cnf 中增加 report-host 配置，然后重启MySQL实例。

[mysqld]
report-host=192.168.111.130    <-- 指明 slave 的 IP 地址，这儿写什么，上面的 show 命令就显示什么

如果想看到主从关系的拓扑图，可以使用第三方的 mysqlrplshow 工具。

查看半同步状态（是否启用半同步复制）

mysql> show global status like "%rpl%";
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |    <-- 连接的半同步复制的client数量
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |    <-- master平均等待slave的时间，mysql-5.7.8 后被废弃
| Rpl_semi_sync_master_net_wait_time         | 0     |    <-- master总的等待slave的时间，mysql-5.7.8 后被废弃
| Rpl_semi_sync_master_net_waits             | 1     |    <-- master总的等待slave的次数
| Rpl_semi_sync_master_no_times              | 1     |    <-- 切成异步的次数（no = number of）
| Rpl_semi_sync_master_no_tx                 | 1     |    <-- 切成异步后提交的事务数
| Rpl_semi_sync_master_status                | ON    |    <-- 当前半同步复制的状态（是否启用了半同步，如果超时切成了异步，这儿会显示 OFF）
| Rpl_semi_sync_master_timefunc_failures     | 0     |    <-- master调用gettimeofday()函数失败的次数
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |    <-- master等待事务的平均时间
| Rpl_semi_sync_master_tx_wait_time          | 0     |    <-- master等待事务的总的时间
| Rpl_semi_sync_master_tx_waits              | 0     |    <-- master等待事务的次数
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_status                 | OFF   |
+--------------------------------------------+-------+
15 rows in set (0.02 sec)

引入 GTID 后的冲突处理

场景：先在 slave 上插入一台记录，然后在主库插入一台相同主键的记录，此时，slave 的 SQL 线程会停止，状态信息中会报 1062 错误。

开始 GTID 后，不能使用 sql_slave_skip_counter 参数来处理复制错误。

下面是接收到的 GTID 和执行了的 GTID 信息：

           Retrieved_Gtid_Set: 0e7c2f36-ca06-11e9-bdf8-000c291ca865:2-3
            Executed_Gtid_Set: 0e7c2f36-ca06-11e9-bdf8-000c291ca865:1-2,
b6c03287-15af-11ea-a20c-000c29e56203:1

可见，接收到了3，只执行到了2，如果主继续生产一个新的事务，仍会被接收，3会变成4，但2还是保持2。

解决办法：将下一下要执行但是不能执行的事务设置为一个空事务，以此跳过该有问题的事务：

set gtid_next='0e7c2f36-ca06-11e9-bdf8-000c291ca865:3';
begin;
commit;
set gtid_next='automatic';
start slave;

多源复制

要求：将 192.168.111.128 上的 demodb 和 192.168.111.130 上的 cydb 数据库都复制到 192.168.111.131 上面。

场景：这种操作可以起到数据聚合的效果。将分库分表后的数据聚合在一起，以供其他应用进行分析（前提是数据不冲突）。

思想：还是以前的方法，只是在做最后一步 change master 时要加上 for channel 的语法。

1.将两个源库备份并在 slave 上还原。

2.设定要复制的数据库名

在 slave 上面设置需要复制的库，如果不设置的话，默认会同步系统的库（mysql），这样可能会复制出错（因为两个主库的mysql库中可能有相同的记录）。
replicate_do_db=dbn1
replicate_do_db=dbn2

3.配置主库信息

mysql> change master to master_host="192.168.111.128", master_port=3306, master_user='rpl', master_password='rpl', master_auto_position=1 for channel 'demodb';
mysql> change master to master_host="192.168.111.130", master_port=3306, master_user='rpl', master_password='rpl', master_auto_position=1 for channel 'cydb';

4.启动同步

mysql> start slave [for channel 'demodb'];

思考：如果192.168.111.130上面也有一个demodb库，会有什么问题？

如果从库设置了 slave_skip_errors = ddl_exist_errors，且两个 demodb 没有重复数据，是没有问题的
如果从没有设置 slave_skip_errors = ddl_exist_errors，或两个 demodb 有重复数据，就会报错

删除复制关系（配置）

stop slave;
reset slave all;

读写分离

应用实现读写分离至少有两种方法：

应用本身通过代码实现，例如基于动态数据源、AOP的原理来实现写操作时用主数据库，读操作时用从数据库。
通过中间件的方式实现，例如通过Mycat，Mycat会“拦截”SQL进行分析，然后选择操作的数据库。

SpringBoot整合读写分离（动态切换数据源）

1.在项目中创建读数据源和写数据源
2.将读数据源和写数据源注册到RoutingDataSource(会给各数据源绑定一个key)
3.配置使用AOP技术拦截业务逻辑层方法，判断方法的前缀是否需要做读或者写。如果方法名称中是 get, select, count, list 等，使用读数据源，否则使用写数据源

Mycat 实现读写分离

官方网址：www.mycat.io
Mycat——活跃的、性能好的分库分表开源数据库中间件！
Mycat支持MySQL、SQL Server、Oracle、DB2、PostgreSQL 等主流数据库，也支持 MongoDB 这种新型 NoSQL 方式的存储，未来还会支持更多类型的存储。

mycat类似于nginx，客户端访问mycat的IP和端口（8066），mycat将其转发给后端的DB服务器。

Mycat是阿里的开源框架。其竞争对手是 sharding-jdbc。

安装

下载软件包，解压即可。

export JAVA_HOME=/usr/local/jdk1.8.0_172
export JRE_HOME=$JAVA_HOME/jre
export CLASSPATH=$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/bin:$PATH
export MYCAT_HOME=/usr/local/mycat # 非必须

配置文件

wrapper.conf：Mycat本身的配置文件
server.xml：Mycat定义用户以及系统相关变量，如端口等
schema.xml：Mycat虚拟数据库和物理数据库表的关系
rule.xnl：Mycat分片(分库分表）规则

读写分离配置示例

数据库主从复制已经搭建完成，有一个业务用户 app，对数据库 demodb 有完全权限。
主：192.168.111.128:3306
从：192.168.111.130:3306

1.配置server.xml

    <user name="appcat">
        <property name="password">appcat</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">false</property>
    </user>

其它保持默认，按如上所示修改文件最后的内容。
这儿配置的用户名和密码是提供给客户端连接使用的，与数据库中真实存在的用户没有关系。
这儿配置的数据库名 TESTDB 是虚拟的数据库名，与数据库服务器中的真实存在的数据库没有关系。

2.配置schema.xml文件

    <?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="cynode" />
        <dataNode name="cynode" dataHost="cyhost" database="demodb" />
        <dataHost name="cyhost" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native">
        <!-- balance="3" 表示所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不负担读压力 -->
            <heartbeat>select user()</heartbeat>
            <writeHost host="master" url="192.168.111.128:3306" user="app" password="app">
                <readHost host="slave" url="192.168.111.130:3306" user="app" password="app" />
            </writeHost>
    </dataHost>
</mycat:schema>

3.启动mycat

# ./bin/mycat start
# mysql -h127.0.0.1 -uappcat -pappcat -P9066    <-- 进入Mycat的管理端
mysql> show @@datasource;
+----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST            | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| cynode   | master | mysql | 192.168.111.128 | 3306 | W    |      0 |    1 | 1000 |       5 |         0 |          0 |
| cynode   | slave  | mysql | 192.168.111.130 | 3306 | R    |      0 |    4 | 1000 |      13 |         0 |          0 |
+----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.03 sec)

4.客户端连接

mysql -uappcat -pappcat -h192.168.111.133 -P8066
然后进行读操作和写操作，观察上面的 READ_LOAD 与 WRITE_LOAD 值的变化。

Sharding-JDBC

Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（规划中）这3款相互独立，却又能够混合部署配合使用的产品组成。它们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。

Sharding-JDBC 和 DAO 是在同一个项目，在本地经过分片算法分片后，再发给DB服务器。
Sharding-Jdbc是一个Jar形式，在本地应用层重写Jdbc原生的方法，实现数据库分片形式。

MyCat属于服务器端数据库中间件，而Sharding-Jdbc是一个本地数据库中间件框架。

实现读写分离原理：
①需要在配置文件配置读写分离jdbc连接全部交给Sharding
②Sharding-Jdbc会自动判断SQL语句类型，然后获取主的jdbc连接配置或从的jdbc连接发送请求

另外，一个小知识点了解一下，在Sharding-JDBC中，自增ID通过雪花算法实现。

-- By 许望（RHCA、OCM、VCP）

最后修改：2025 年 07 月 20 日 05 : 49 PM

如果觉得我的文章对你有用，请随意赞赏

Tags: MySQL

MySQL 主从搭建与管理

admin • 2025 年 07 月 20 日

主从搭建

1.创建主库，my.cnf 模版已经打开了相关关键参数

准备主库

# rm /opt/database/3306/auto.cnf
mysql> create user 'repl'@'主库IP' identified by 'xxx';
mysql> create user 'repl'@'从库IP' identified by 'xxx';
mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'主库IP';
mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'从库IP';
alter user 'root'@'localhost' identified by 'xxx';

3.备份主库

如果存储引擎都支持事务：

mysqldump -u root -p --single-transaction -A -B --master-data=2 > /opt/database/alldb.sql

-- master-data=2 是之前没有使用 GTID 时的方法，可以保留，但实际上不会用到。

如果有不支持事务的存储引擎：

mysqldump -u root -p -A -B -x --events --master-data=2 > /xxx

-x 会在备份过程中锁表

如果使用 mydumper 进行备份恢复，其产生的 metadata 文件示例如下：

Started dump at: 2019-12-25 09:24:41
SHOW MASTER STATUS:
    Log: bin.000009
    Pos: 453
    GTID:0e7c2f36-ca06-11e9-bdf8-000c291ca865:1

Finished dump at: 2019-12-25 09:24:41

4.准备从库

# rm -f /opt/database/3306/auto.cnf
# vim /etc/my.cnf
server-id = 22
read_only = 1
# /etc/init.d/mysqld start
mysql> alter user 'root'@'localhost' identified by 'xxx'; < --- 将备库 root 密码修改为主库一致

5.恢复数据库并配置主从关系

mysql> SET @@GLOBAL.GTID_PURGED='0e7c2f36-ca06-11e9-bdf8-000c291ca865:1';
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

mysql> reset master;
mysql> SET @@GLOBAL.GTID_PURGED='0e7c2f36-ca06-11e9-bdf8-000c291ca865:1';

# mysql -uroot -p < alldb.sql
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

mysql> reset master;
mysql> reset slave;

# mysql -uroot -p < alldb.sql

mysql> change master to master_host="主库IP",master_port=3306,master_user='repl',master_password='xxx',master_auto_position=1;
以前都是通过 master_log_file 和 master_log_pos 来定位，引入 GTID 后，使用 master_auto_position 自动定位

mysql> start slave;
mysql> show slave status\G

查看从库状态：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.111.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000001    <-- change master中的filename
          Read_Master_Log_Pos: 585            <-- change master中的pos
               Relay_Log_File: object-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000001
             Slave_IO_Running: No            <-- 目前slave还未运行，所以是No
            Slave_SQL_Running: No            <-- 目前slave还未运行，所以是No

开启同步

mysql> start slave;

查看从库状态：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event        <-- IO 线程的状态
                  Master_Host: 192.168.111.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000001                            <-- IO线程读取到的文件 [The name of the source binary log file from which the I/O thread is currently reading.]
          Read_Master_Log_Pos: 585                                    <-- IO线程读取文件中的位置
               Relay_Log_File: object-relay-bin.000003
                Relay_Log_Pos: 314
        Relay_Master_Log_File: bin.000001                            <-- SQL线程执行到的文件 [The name of the source binary log file containing the most recent event executed by the SQL thread.]
             Slave_IO_Running: Yes                                    <-- IO 线程正在运行
            Slave_SQL_Running: Yes                                    <-- SQL 线程正在运行
              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: 585                                    <-- SQL线程执行到文件的位置 [The position in the current source binary log file to which the SQL thread has read and executed]
              Relay_Log_Space: 522
              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                                    <-- Slave 落后 Master 的秒数
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:                                         <-- IO 线程相关的错误
               Last_SQL_Errno: 0
               Last_SQL_Error:                                         <-- SQL 线程相关的错误 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 128
                  Master_UUID: 0e7c2f36-ca06-11e9-bdf8-000c291ca865
             Master_Info_File: /usr/local/mysql-5.7.23-linux-glibc2.12-x86_64/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

通过 show processlist 在主库可以看到 "Binlog Dump" 进程，在从库可以看到 IO 进程和 SQL 进程。如果在备库上开启了并行复制，还可以看到 Coordinator 线程。

关于 Seconds_Behind_Master

主从复制的启停

mysql> start slave;
mysql> stop slave;
mysql> stop slave io_thread;
mysql> stop slave sql_thread;

查看复制关系

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|       130 |      | 3306 |       128 | b6c03287-15af-11ea-a20c-000c29e56203 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

看到 HOST 字段为空，如果想能显示是哪一台主机，需要在 slave 的 my.cnf 中增加 report-host 配置，然后重启MySQL实例。

[mysqld]
report-host=192.168.111.130    <-- 指明 slave 的 IP 地址，这儿写什么，上面的 show 命令就显示什么

如果想看到主从关系的拓扑图，可以使用第三方的 mysqlrplshow 工具。

查看半同步状态（是否启用半同步复制）

mysql> show global status like "%rpl%";
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |    <-- 连接的半同步复制的client数量
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |    <-- master平均等待slave的时间，mysql-5.7.8 后被废弃
| Rpl_semi_sync_master_net_wait_time         | 0     |    <-- master总的等待slave的时间，mysql-5.7.8 后被废弃
| Rpl_semi_sync_master_net_waits             | 1     |    <-- master总的等待slave的次数
| Rpl_semi_sync_master_no_times              | 1     |    <-- 切成异步的次数（no = number of）
| Rpl_semi_sync_master_no_tx                 | 1     |    <-- 切成异步后提交的事务数
| Rpl_semi_sync_master_status                | ON    |    <-- 当前半同步复制的状态（是否启用了半同步，如果超时切成了异步，这儿会显示 OFF）
| Rpl_semi_sync_master_timefunc_failures     | 0     |    <-- master调用gettimeofday()函数失败的次数
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |    <-- master等待事务的平均时间
| Rpl_semi_sync_master_tx_wait_time          | 0     |    <-- master等待事务的总的时间
| Rpl_semi_sync_master_tx_waits              | 0     |    <-- master等待事务的次数
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_status                 | OFF   |
+--------------------------------------------+-------+
15 rows in set (0.02 sec)

引入 GTID 后的冲突处理

场景：先在 slave 上插入一台记录，然后在主库插入一台相同主键的记录，此时，slave 的 SQL 线程会停止，状态信息中会报 1062 错误。

开始 GTID 后，不能使用 sql_slave_skip_counter 参数来处理复制错误。

下面是接收到的 GTID 和执行了的 GTID 信息：

           Retrieved_Gtid_Set: 0e7c2f36-ca06-11e9-bdf8-000c291ca865:2-3
            Executed_Gtid_Set: 0e7c2f36-ca06-11e9-bdf8-000c291ca865:1-2,
b6c03287-15af-11ea-a20c-000c29e56203:1

可见，接收到了3，只执行到了2，如果主继续生产一个新的事务，仍会被接收，3会变成4，但2还是保持2。

解决办法：将下一下要执行但是不能执行的事务设置为一个空事务，以此跳过该有问题的事务：

set gtid_next='0e7c2f36-ca06-11e9-bdf8-000c291ca865:3';
begin;
commit;
set gtid_next='automatic';
start slave;

多源复制

要求：将 192.168.111.128 上的 demodb 和 192.168.111.130 上的 cydb 数据库都复制到 192.168.111.131 上面。

场景：这种操作可以起到数据聚合的效果。将分库分表后的数据聚合在一起，以供其他应用进行分析（前提是数据不冲突）。

思想：还是以前的方法，只是在做最后一步 change master 时要加上 for channel 的语法。

1.将两个源库备份并在 slave 上还原。

2.设定要复制的数据库名

3.配置主库信息

mysql> change master to master_host="192.168.111.128", master_port=3306, master_user='rpl', master_password='rpl', master_auto_position=1 for channel 'demodb';
mysql> change master to master_host="192.168.111.130", master_port=3306, master_user='rpl', master_password='rpl', master_auto_position=1 for channel 'cydb';

4.启动同步

mysql> start slave [for channel 'demodb'];

思考：如果192.168.111.130上面也有一个demodb库，会有什么问题？

如果从库设置了 slave_skip_errors = ddl_exist_errors，且两个 demodb 没有重复数据，是没有问题的
如果从没有设置 slave_skip_errors = ddl_exist_errors，或两个 demodb 有重复数据，就会报错

删除复制关系（配置）

stop slave;
reset slave all;

读写分离

应用实现读写分离至少有两种方法：

SpringBoot整合读写分离（动态切换数据源）

Mycat 实现读写分离

mycat类似于nginx，客户端访问mycat的IP和端口（8066），mycat将其转发给后端的DB服务器。

Mycat是阿里的开源框架。其竞争对手是 sharding-jdbc。

安装

下载软件包，解压即可。

配置文件

读写分离配置示例

数据库主从复制已经搭建完成，有一个业务用户 app，对数据库 demodb 有完全权限。
主：192.168.111.128:3306
从：192.168.111.130:3306

1.配置server.xml

    <user name="appcat">
        <property name="password">appcat</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">false</property>
    </user>

2.配置schema.xml文件

    <?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="cynode" />
        <dataNode name="cynode" dataHost="cyhost" database="demodb" />
        <dataHost name="cyhost" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native">
        <!-- balance="3" 表示所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不负担读压力 -->
            <heartbeat>select user()</heartbeat>
            <writeHost host="master" url="192.168.111.128:3306" user="app" password="app">
                <readHost host="slave" url="192.168.111.130:3306" user="app" password="app" />
            </writeHost>
    </dataHost>
</mycat:schema>

3.启动mycat

# ./bin/mycat start
# mysql -h127.0.0.1 -uappcat -pappcat -P9066    <-- 进入Mycat的管理端
mysql> show @@datasource;
+----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST            | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| cynode   | master | mysql | 192.168.111.128 | 3306 | W    |      0 |    1 | 1000 |       5 |         0 |          0 |
| cynode   | slave  | mysql | 192.168.111.130 | 3306 | R    |      0 |    4 | 1000 |      13 |         0 |          0 |
+----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.03 sec)

4.客户端连接

mysql -uappcat -pappcat -h192.168.111.133 -P8066
然后进行读操作和写操作，观察上面的 READ_LOAD 与 WRITE_LOAD 值的变化。

Sharding-JDBC

MyCat属于服务器端数据库中间件，而Sharding-Jdbc是一个本地数据库中间件框架。

另外，一个小知识点了解一下，在Sharding-JDBC中，自增ID通过雪花算法实现。