逻辑复制与物理复制

为了解决逻辑复制慢的问题，MySQL 5.7 以后推出了并行复制 ，速度提升了很多。

binlog_format

线上必须设置为 binlog_format = ROW。

主从复制架构

1. set sync_binlog=1 to assure crash safe
2. InnoDB group commit is fairly random
3. set innodb_flush_log_at_trx_commit=1 to garantee crash safe
4. set relay_log recovery=1 to enable IO thread crash safe
5. semi-sync replication need send ACK to master
6. set relay_log_info_repository=TABLE to make SQL threads crash safe

• 在 MySQL 5.7 版本中， Prepare log 部分的日志也是组提交的
• MySQL Dump Thread 把 binlog 推送到远程的 Slave 服务器（每一个Slave，就会对应有一个 dump 线程）
• Master Thread 每隔1秒从 redo log buffer 中写入 redo file
• 在 MySQL 5.6 中的多线程回放是基于库的， 单个库还是单线程，在 MySQL 5.7 中的多线程是在主上如何并行执行的，从机上也是如何并行回放的
• master-info.log 存放了接受到的 binlog 的位置（event的位置）
• relay-info.log 存放了回放到的 relay log 的位置（event的位置）

主流的主从复制搭建方法

当前环境

在 192.168.111.128 上面跑着 3306 实例，有一个数据库 demodb
在 192.168.111.130 上面跑着 3306 实例，没有 demodb 数据库

设置 binlog 和 server-id

打开 binlog（主必开），格式设置为 ROW，配置 server-id（主从唯一），确保 MySQL Server UUID 不一样（auto.conf）。

log_bin=bin.log
server_id=128（主从唯一）

创建复制用户

在主库上创建用于复制的用户：

create user 'repl'@'%' identified by 'repl';
grant replication slave on *.* to 'repl'@'%'; 

在备库上测试是否可以连接：

mysql -u repl -prepl -h 192.168.111.128 -P3306

做初始同步

主库上产生一致性备份数据：

# mysqldump --single-transaction --databases demodb --master-data=2 > demodb.sql

备库上恢复数据：

# mysql < demodb.sql

建立到主库的同步关系

mysql> change master to
master_host='192.168.111.128',
master_user='rpl',
master_password='rpl',
master_port=3306, 
master_log_file='bin.000001',
master_log_pos=585;

查看从库状态：

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线程读取到的文件
          Read_Master_Log_Pos: 585                                    <-- IO线程读取文件中的位置
               Relay_Log_File: object-relay-bin.000003
                Relay_Log_Pos: 314
        Relay_Master_Log_File: bin.000001                            <-- SQL线程执行到的文件
             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线程执行到文件的位置
              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

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的时间。

搭建真正的主从复制

上面的做法，后期经常会遭遇 1062 错误。为了避免此类问题，主从数据库的参数文件请参考如下配置：

• Master

  • binlog-do-db = # 哪此库记录二进制日志
  • binlog-ignore-db = # 哪此库不记录二进制日志
  • max_binlog_size = 2048M # 默认为1024M
  • binlog_format = ROW # 必须为ROW
  • transaction-isolation = READ-COMMITTED
  • expire_logs_days = 7 # binlog保留多少天，看公司计划安排
  • server-id = 128 # 必须在一套主从复制环境中全局唯一
  • binlog_cache_size = # binlog 缓存的大小，设置时要当心
  • sync_binlog = 1 # 必须设置为1，默认为0
  • innodb_flush_log_at_trx_commit = 1 # 提交事物的时候刷新日志
  • innodb_support_xa = 1

• Slave

  • log_slave_updates # 将SQL线程回放的数据写入到从机的binlog中去（用于级联复制）
  • replicate-do-db = # 需要复制的库
  • replicate-ignore-db = # 需要忽略的库
  • replicate-do-table = # 需要复制的表
  • replicate-ignore-table = 需要忽略的表
  • server-id = 130 # 必须在一套主从复制环境中全局唯一
  • relay-log-recover = 1 # I/O thread crash safe（IO线程安全）
  • relay_log_info_repository = TABLE # SQL thread crash safe（SQL线程安全）
  • master_info_repository = TABLE
  • read_only = 1

SQL线程高可靠问题

如果将 relay_log_info_repository 设置为 FILE ，MySQL 会把回放情况信息记录在参数 relay_log_info_file 指定的 relay-info.log 的文件中，其中包含SQL线程回放到的 Relay_log_name 和 Relay_log_pos，以及对应的 Master 的 Master_log_name 和 Master_log_pos。SQL 线程回放的基本单位是 event，参数 sync_relay_log_info = 10000 代表每回放 10000 个event， 写一次 relay-info.log [the slave synchronizes its relay-log.info file to disk(using fdatasync()) after every N transactions]，这样就可能发生如下情况：event2 和 event3 回放写入成功(且已经落盘)，但是在 relay-info.log 中的记录还是 event1 的位置。此时Slave宕机，然后重启，便会产生如下的状况：

1. Slave 的库中存在 event2 和 event3
2. Slave 读取 relay-info.log 中的 Relay_log_name 和 Relay_log_pos，此时记录的是回放到 event1 的位置
3. Slave 从 event1 开始回放 ，继续回放 event2 和 event3
4. 但是，此时的数据库中存在 event2 和 event3，于是发生了 1062 的错误（重复记录）

如果该参数设置为 1 ，则表示每回放一个 event ， 就写一次 relay-info.log，那写入代价很大，性能很差，即使性能上可以接受，还是可能会丢最后一次的操作，恢复起来后还是有 1062 的错误（重复执行event）。

SQL 线程的数据回放是写数据库操作，relay-info.log 是写文件操作，这两个操作很难保证一致性。

解决办法：在 MySQL5.6+ 以后，将 relay_log_info_repository 设置为 TABLE ，relay-info 将写入到 mysql.slave_relay_log_info 这张表中。如此，可将 event 的回放和 relay-info 的更新放在同一个事务里面，变成原子操作，从而保证一致性（要么都写入，要么都不写）。

假设 sync_relay_log_info = N (N>0)，当设置为 TABLE 时：

• 如果存储引擎支持事务，则 the table is updated after each transaction，参数 sync_relay_log_info 被忽略。
• 如果存储引擎不支持事务，the table is updated after every N events.

I/O线程高可用

IO 线程也是接收一个个的 event ，更新 master-info 信息到参数 master_info_repository 指定的位置（文件 FILE 或者数据库 TABLE: mysql.slave_master_info），然后将接收到的 event 写入 relay log file。参数 sync_master_info=10000 表示每接收 10000 个 event，写一次 master-info。这里同样存在前面的问题：master-info.log 和 relay-log 无法保证一致性。例如，event2 和 event3 已经写入到了 relay-log，但是 master-info 只记录了 event1，此时如果服务宕机后，MySQL重启，I/O 线程会读取 master-info.log 的内容，读取到的位置为 event1 的位置，然后 I/O 线程会继续将 event2 和 event3 拉取过来，然后继续写入到 relay-log 中，当SQL线程回放时，就会产生 1062 的错误（重复记录），看到的现象还是 IO 线程正常，SQL 线程报错。

master_info_repository 设置为 TABLE 或者 FILE 对复制的可靠性是没有帮助的，因为 event 到 relay-log 中去还是文件操作，但是 master_info_repository 也一定要设置为 TABLE ，性能上比设置为 FILE 有很高的提升（官方BUG）。解决该问题的方法是设置参数 relay-log-recover = 1 ，该参数表示当发生 crash 时，将当前接收到的 relay-log 全部删除，然后从 SQL 线程回放到的位置重新拉取（SQL线程通过配置后是可靠的）。但是注意：这种删除 RELAY-LOG 的操作也有风险，因为主库可能因为 binlog 超过了保存时长而被删除了。

所以说，真正的MySQL复制的高可靠是从 5.6 版本开始的，通过设置如下三个参数来确保整体复制的高可靠：
• relay-log-recover = 1
• relay_log_info_repository = TABLE
• master_info_repository = TABLE
换言之，之前的版本复制不可靠是正常的。

read_only与super_read_only

如果在Slave机器上对数据库进行修改或者删除，会导致主从的不一致，需要对Slave机器设置为 read_only = 1 ，让Slave提供 只读 操作。但 read_only 仅仅对没有SUPER权限的用户有效（即mysql.user表的Super_priv字段为Y）（好在一般给 App 用户的权限是不会是 SUPER权限）。参数 super_read_only 可以将有 SUPER权限 的用户也设置为只读，且该参数设置为 ON 后， read_only 也跟着自动设置为 ON。

注意：MySQL5.7.11 中，在 /etc/my.cnf 中将 super_read_only=1 配置好后重启，还是可以插入或修改数据。需要在命令行中执行 set global super_read_only=1; 才能真正修改为只读。

主从复制的启停

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 工具。

并行复制（Multi-Threaded Slave，MTS）

MySQL的并行复制基于组提交：一个组提交中的事务都是可以并行执行的，因为既然处于组提交中，这意味着事务之间没有冲突（不会去更新同一行数据），否则不可能在同一个组里面。

开启并行复制，只需要在 Slave 上配置如下参数：

slave-parallel-type=LOGICAL_CLOCK    <-- 意思是主上怎么并行执行，从上也怎么并行执行。如果设置为 DATABASE，表示基于库级别的并行复制，如果只有一个库，就还是串行，DATABASE 只是为了兼容 5.6 而存在，否则使用 LOBICAL_CLOCK
slave-parallel-workers=4    <-- 并行复制的线程数（SQL Thread），线上物理机设置为 16 或者 32 足够了，在线修改后，需要 stop slave; start slave 做一个启停以生效
slave_preserve_commit_order=1 <-- Slave 上 commit 的顺序保持一致，必须为1，否则可能会有GAP锁产生

如果是多线程复制，无论是5.6库级别的假多线程还是MariaDB或者5.7的真正的多线程复制，SQL线程只做coordinator，只负责把relay log中的binlog读出来然后交给worker线程，woker线程负责具体binlog event的执行。

半同步复制

之前的复制，都是异步复制，Master 并不关心数据是否被 Slave 节点所获得 ，所以复制效率很高，但是主从切换后，数据有可能会丢失。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

半同步架构介绍

从 MySQL5.5 开始，MySQL推出了 semi-sync replication（半同步复制）。
从 MySQL5.7 开始，MySQL推出了 lossless semi-sync replication（无损复制）。

这两种都是半同步复制。主在提交一个事务时，会有写 prepare log，写 binlog，写 redolog 三个步骤。启用半同步复制后，主都会等待至少一个 Slave 节点的 IO 线程回复 ACK后（表示从收到了 binlog ），才能继续下一个事物。如果在一定时间内（Timeout）内没有收到 ACK，则切换为异步模式，当 Slave 又追上 Master 了（IO线程），则会自动切换回半同步复制。半同步复制与无损复制的区别在于，semi-sync 是在三个步骤完成后等 ACK，lossless semi-sync 是在前两个步骤完成后等 ACK。

当主机失败，且主从未切换，恢复后，两种复制模式下的主从数据都是最终一致的（配置是 crash_safe 的）。
若主机失败，做了主从切换，则两种方式的表现不一样：

• semi-sync 可以减少数据丢失风险但不能完全避免数据丢失
• lossless semi-sync 可保证数据完全不丢失

假设主从复制时产生异常（比如 Master 宕机了），Master的binlog还没有传递到Slave上，此时半同步复制与无损复制，在主从数据一致性上的表现是不一样的。

• semi-sync replication 在commit完成后，才传输binlog，意味着在Master节点上，这个刚刚提交的事务对数据库的修改， 对其他事务是可见的，假如此时Master宕机了，且发生主从切换，此时的 Slave 提升为 New Master，但是此时的 New Master 上是没有之前提交的事务的内容的，这样就产生了主从数据的不一致。对App而言，之前读取到的内容，现在读取不到了。
• loss less semi-sync replication 在write binlog完成后，就传输binlog，但还没有去写commit log，意味着当前这个事务对数据库的修改，其他事务也是不可见的，假如此时Master宕机了，且发生了主从切换，此时的 Slave 提升为 New Master，由于Master上对该事务还没有提交，且此时的 New Master 上同样也没有该事务的内容，此时主从的数据是一致的，对于 APP 来说，发生主从切换后，APP 读取到的内容前后是一致的。（如果原来的Master又恢复了，需要让原来的Master不要提交宕机前的那个事务）

安装半同步插件

两种同步方式使用相同的插件。注意：这儿仅仅是安装了插件而已，还未启用。主从上都要安装。

安装方式一：手工安装

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so’;

安装方式二：写入配置文件

[mysqld]
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

启用半同步

[mysqld]
loose_rpl_semi_sync_master_enabled=1 <-- 开启主的半同步复制
loose_rpl_semi_sync_slave_enabled=1  <-- 开启从的半同步复制
loose_rpl_semi_sync_master_timeout=1000  <-- 超时1秒，如果可靠性要求高，不想它切加异步，就把这个值调高

[mysqld57]
loose_rpl_semi_sync_master_wait_point=AFTER_SYNC <-- 5.7 可以设置为 AFTER_SYNC 来使用无损复制，AFTER_COMMIT 使用半同步
loose_rpl_semi_sync_master_wait_for_slave_count=1 <-- 5.7 可以配置至少收到多少个slave发回的 ACK

使用 loose_ 前缀表示如果没有加载 semi_sync 的插件，则忽略该参数。

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

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    |    <-- 半同步复制的状态（是否启用了半同步）
| 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)

两种复制方式的性能

在 IO Bound 场景下：

• 异步复制性能很好，但是随着并发数的增长，性能有所下降
• 无损复制随着并发数的增长，性能几乎是线性增长的，在高并发下，性能会优于异步复制
• 半同步复制性能较低

无损复制性能优于半同步复制的原因：

1. 就等待ACK回包问题上，其实两种复制的开销是一样的，没有区别，都是网络的等待开销。
2. 无损复制由于在 write binlog 后，需要等待ACK，后续的事物无法提交，这样就堆积了很多需要落盘的事物（半同步复制由于已经提交了事物，没有堆积事物的效果），通过组提交机制，一次 fsync 的事物变多了（半同步复制也有组提交，只是一次fsync 的事物数没那么多），相当于提高了 I/O 性能 。
   所以线程（事物）越多，效果越明显，以至于有超过异步复制的效果。（无损复制的组提交比原版组提交的高3~4倍）

不要设置的两个参数

下面这两个参数不要去设置，设置了反而性能差

mysql> show variables like "%binlog_group%";
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay          | 0     |
| binlog_group_commit_sync_no_delay_count | 0     |  <-- 等待一组里面有多少事务我才提交
+-----------------------------------------+-------+
2 rows in set (0.03 sec)

mysql> show variables like "%binlog_max%";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| binlog_max_flush_queue_time | 0     |  <-- 等待多少时间后才进行组提交
+-----------------------------+-------+
1 row in set (0.03 sec)

slave_rows_search_algorithms

参数 slave_rows_search_algorithms 的默认值为 "TABLE_SCAN,INDEX_SCAN"，不要去更改，这个参数表明了怎样来提高复制的性能。

之前强调过的每张表一定要有主键的要求，除了是符合范式的要求，也可以提高主从复制的性能。因为Slave进行回放的时候，是根据索引进行回放的，过程为：先找主键 --> 没有主键则找唯一索引 --> 没有唯一索引则找普通索引 --> 没有普通索引则全表扫描。

例如：delete from cyt where id in (1, 3);
针对上述语句，假设 cyt 不存在任何主键或索引，则在 Master 上操作的时候只要扫描一遍该表即可，但是复制是基于行的（ROW格式），在 Slave 上就要扫描两次，一次扫描是为了 id=1 的行，一次扫描 是为了 id=3 的行。即全表扫描时：Master 扫描一次，复制时，Slave上扫 N 次。

新增的 Hash Scan 方式，会先增加一个哈希表，这样就只扫描一次了，但是创建哈希表的代价很大。所以默认没有启用的。

所以强烈建议：每张表上都要有一个主键。

通过GTID复制

GTID（Global Transaction Identifier，全局事物ID）是MySQL 5.6的新特性，GTID 由 Server_UUID + Transaction_ID 组成，作用是替代 Filename + Position。

在没有 GTID 时，一主多从的架构中，当 Master 宕机后，某个 Slave 被提升为 New Master，如果需要继续维持复制关系，就需要把另外的 Slave 的 CHANGE MASTER 指向 New Master，新 Master 的 Filename + Position 是比较难以确定的。如果使用了 GTID，就可以通过 GTID 来确定位置（全局唯一）。

那 GTID 是如何快速定位的呢？

因为 binlog 在 rotate（rotate events）的时候，是知道当前最大的 GTID 的，可以将该值写入到下一个新的 binlog 的开头，即 binlog 中记录的 Previous_gtids：

mysql> show binlog events in 'bin.000021'\G

当从需要跟主同步时，就可以使用从当前的 GTID 值，跟 binlog 头部的 Previous_gtids 作比较：如果我要的下一个 GTID 比 Previous_gtids 值大，就扫描当前文件，反之则扫描之前的文件，依次类推。

启用 GTID

GTID 的配置很简单，只需要写上如下的4条配置即可：

[mysqld]
log_bin = bin.log
gtid_mode = ON
log_slave_updates = 1
enforce_gtid_consistency = 1

1.MySQL 5.6 必须开启参数 log_slave_updates （5.6版本的限制），5.7 版本开始可以不开启
2.MySQL 5.6 升级到gtid模式需要停机重启，5.7.6 版本开始可以在线升级成gtid模式

开启 GTID 后的注意事项

1.启用 GTID 后，数据库之前的备份就不能用了，需要重新对数据库做备份。
2.使用 mysqldump 备份单个数据库时会有 Warning，大致意思为你只备份了部分数据库，但是启用GTID后包含了所有的事物。（可以忽略该警告）
3.启用 GTID 后，某些不安全的语句会被禁用，比如 CTAS，因为这其实是多个事务了，GTID没法对应。
4.开启 GTID 后，不能在一个事物中使用创建临时表的语句，需要参数 autocommit=1 才可以。

通过 GTID 复制的演示

1.对 master 上的库进行备份，并且在 slave 上恢复：

这儿我们使用 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

2.跳过GTID：

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

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';

3.设置主库信息：

mysql> change master to master_host="192.168.111.128", master_port=3306, master_user='rpl', master_password='rpl', master_auto_position=1;

4.启用同步：

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

冲突处理

场景：先在 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通过雪花算法实现。