MySQL GTID多源复制

àì夳堔傛蜴生んèń 2022-09-02 05:17 162阅读 0赞

> MySQL 5.7之前只能实现一主一从、一主多从或者多主多从的复制，在MySQL 5.7版本已经可以实现多主一从的复制了。本文是直接讲怎么配置多源复制，需要对GTID复制配置有一定的了解。

### 一、环境说明 ###

<table> 
 <thead> 
  <tr> 
   <th>角色</th> 
   <th>IP</th> 
   <th>MySQL版本</th> 
   <th>端口</th> 
   <th>复制帐号</th> 
   <th>复制密码</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Master1</td> 
   <td>10.10.1.50</td> 
   <td>MySQL 5.7.35</td> 
   <td>3306</td> 
   <td>repl</td> 
   <td>repl</td> 
  </tr> 
  <tr> 
   <td>Master2</td> 
   <td>10.10.1.51</td> 
   <td>MySQL 5.7.35</td> 
   <td>3306</td> 
   <td>repl</td> 
   <td>repl</td> 
  </tr> 
  <tr> 
   <td>Slave</td> 
   <td>10.10.1.52</td> 
   <td>MySQL 5.7.35</td> 
   <td>3306</td> 
   <td>repl</td> 
   <td>repl</td> 
  </tr> 
 </tbody> 
</table>

### 二、配置说明 ###

*  Master数据库配置

# Master1 和 Master2 配置文件一样，但是server-id 参数需要配置成不一样
    # cat /etc/my.cnf
    user                      = mysql
    socket                    = /tmp/mysql3306.sock
    datadir                   = /data/mysql/
    server-id                 = 20210203
    log_bin                   = mysqlbin-log
    log_slave_updates         = 1
    binlog_format             = ROW
    
    gtid_mode                 = on
    enforce_gtid_consistency  = on
    ......

*  Slave 数据库配置

# cat /etc/my.cnf
    user                      = mysql
    socket                    = /tmp/mysql3306.sock
    datadir                   = /data/mysql/
    server-id                 = 20210730
    log_bin                   = mysqlbin-log
    log_slave_updates         = 1
    binlog_format             = ROW
    
    gtid_mode                 = on
    enforce_gtid_consistency  = on
    master_info_repository    = TABLE     # 多源复制只能配置为table
    relay_log_info_repository = TABLE     # 多源复制只能配置为table
    ......

*  在Master1,Master2上创建复制帐号：

mysql> grant replication slave on *.* to 'repl'@'10.10.%' identified by 'repl';
    mysql> flush privileges;

### 三、数据处理 ###

*  MySQL 数据导出

# 把两台Master数据导出
    # mysqldump -h 10.10.1.50 -u root -p -R -E --single-transaction --master-data --databases test1 > /tmp/50_test1.sql
    # mysqldump -h 10.10.1.51 -u root -p -R -E --single-transaction --master-data --databases test2 > /tmp/51_test2.sql

*  MySQL 数据导入

# 在Slave上把数据依次导入
    # mysql -u root -p  test1 < 50_test1.sql
    ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
    mysql>reset master;
    
    # 解决办法：在Slave上先执行reset master操作,执行这个命令相当于之前的二进制日志全部清除，重新生成的意思，GTID位点又从1开始计算
    # mysql -u root -p  test1 < 50_test1.sql
    ---
    # 导入test2的数据
    mysql> reset master;  # 导入test1数据又生成了GTID_EXECUTED
    # mysql -u root -p  test1 < 50_test2.sql

*  Slave上重新设置GTID值

# 获取导出SQL文件中的GTID_PURGED的值
    # grep -w  "GTID_PURGED" /tmp/50_test1.sql 
    SET @@GLOBAL.GTID_PURGED='a4cd0613-b57f-11ea-b9ee-14dda952d846:1-1542816';
    # grep -w  "GTID_PURGED" /tmp/51.sql 
    SET @@GLOBAL.GTID_PURGED='c55ff5f6-def6-11eb-bb70-40167ea6ca4f:1-199';
    
    # 然后把上面获取的GTID_PURGED值在Slave上配置
    mysql> reset master;        # 需要先清除GTID信息，否则无法重新手动指定
    mysql> set @@global.GTID_PURGED='a4cd0613-b57f-11ea-b9ee-14dda952d846:1-1542816,c55ff5f6-def6-11eb-bb70-40167ea6ca4f:1-199'    # 注意两个库的GTID值都要设置，以逗号分隔

### 四、配置主从 ###

*  配置主从同步

# 在slave上执行
    mysql>change master to master_host='10.10.1.50',master_port=3306,master_user='repl',master_password='repl',master_auto_position=1 for channel 'master1';
    
    mysql>change master to master_host='10.10.1.51',master_port=3306,master_user='repl',master_password='repl',master_auto_position=1 for channel 'master2';

*  启动Slave

mysql> start slave for channel 'master1';    # 对应master1的同步
    mysql> start slave for channel 'master2';    # 对应master2的同步

**Referencen**  
[https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html][https_dev.mysql.com_doc_refman_5.7_en_change-master-to.html]  
[https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source.html][https_dev.mysql.com_doc_refman_5.7_en_replication-multi-source.html]  
[https://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html][https_dev.mysql.com_doc_refman_5.7_en_change-replication-filter.html]

[https_dev.mysql.com_doc_refman_5.7_en_change-master-to.html]: https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
[https_dev.mysql.com_doc_refman_5.7_en_replication-multi-source.html]: https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source.html
[https_dev.mysql.com_doc_refman_5.7_en_change-replication-filter.html]: https://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html