配置MySQL5.7基于GTID的复制

配置MySQL5.7基于GTID的复制

一、       安装前规划

1.1   大体架构规划如下

master

192.168.9.71:3306

Slave

192.168.9.72:3306

 数据库搭建过程请参考《mysql5.7部署文档》

二、       主从复制搭建

 

2.1 创建同步用户repl(master上)

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

flush privileges;

注：其中'repl'@'%'部分可以修改为'repl'@'xxx.xxx.xxx.*'或'repl'@'xxx.xxx.xxx.xxx'的形式，出于安全目的用以限制网段或IP访问（其中xxx为具体IP）

2.2 主库my.cnf添加同步参数

server_id=1

log-bin = /mysql/3306/binlog/bin.log #binlog存放路径,需要根据实际情况修改

log_bin_index = /mysql/3306/binlog/bin.index

max-binlog-size=209715200

expire-logs-days = 7

sync-binlog = 1

binlog_format="ROW"

log_bin_trust_function_creators = 1

binlog_cache_size = 2097152

 

上诉参数数据库搭建过程已经配置。

# replication settings #

gtid_mode = on

enforce_gtid_consistency = 1

log_slave_updates

2.3 从库my.cnf添加同步参数

server_id=2

log-bin = /mysql/3306/binlog/bin.log #binlog存放路径,需要根据实际情况修改

log_bin_index = /mysql/3306/binlog/bin.index

max-binlog-size=209715200

expire-logs-days = 7

sync-binlog = 1

binlog_format="ROW"

log_bin_trust_function_creators = 1

binlog_cache_size = 2097152

上诉参数数据库搭建过程已经配置。

# replication settings #

master_info_repository = TABLE

relay_log_info_repository = TABLE

gtid_mode = on

enforce_gtid_consistency = 1

log_slave_updates

relay_log = /mysql/3306/binlog/relay.log

relay_log_index = /mysql/3306/binlog/relay_log.index

relay_log_purge = 1

relay_log_recovery = 1

report-port = 3306

report-host = 192.168.9.72

slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'

# new replication settings #

slave-parallel-type = LOGICAL_CLOCK

slave-parallel-workers = 8

slave_preserve_commit_order=1

slave_transaction_retries=128

注：

1）   以上添加参数如已在my.cnf文件里则无需添加，其中主库server_id的数字不能和备库server_id相同，

一般一主一从架构中，备库server_id默认设为2即可

2）log-slave-updates参数用来控制slave库是否把replication的event也写进binlog，如果是M->S1->S2的级联同步架构，则S1(第一个slave库)需要打开binlog和把log-slave-updates参数设为1

2.4  主库备份与从库导入

2.4.1         mysqldump备份

主库备份:

mysqldump -uroot -p --single-transaction --default-character-set=utf8 --master-data=2 --flush-logs --triggers --routines --events  --all-databases > allDB.`hostname`_`date +"%Y%m%d%H%M"`.sql

备库导入：

mysql> reset master;

Query OK, 0 rows affected (0.01 sec)

mysql -uroot -p < allDB.`hostname`_`date +"%Y%m%d%H%M"`.sql

2.4.2         配置主从同步

配置同步参数

CHANGE MASTER TO

   MASTER_HOST = '192.168.9.71',

   MASTER_PORT = 3306,

   MASTER_USER = 'repl',

   MASTER_PASSWORD = '123456',

   MASTER_AUTO_POSITION = 1;

注:

master_host参数配置主库IP地址

master_port参数配置主库端口号

master_user参数配置主库的同步用户名

master_password参数配置主库同步用户的密码

2.4.3         开起主从同步并查看状态

mysql> start slave ;

Query OK, 0 rows affected (0.04 sec)

mysql>

mysql> show slave statusG;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.9.71

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: bin.000004

          Read_Master_Log_Pos: 194

               Relay_Log_File: relay.000002

                Relay_Log_Pos: 355

        Relay_Master_Log_File: 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: 194

              Relay_Log_Space: 552

              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: 15c4b75c-cd03-11e9-be6b-080027364db6

             Master_Info_File: mysql.slave_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: 15c4b75c-cd03-11e9-be6b-080027364db6:1-231665

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

三、       主从复制基本维护

3.1 监控主从同步延迟

1、安装pt工具

rpm -ivh perl-DBD-MySQL-4.022-1.el6.rfx.x86_64.rpm

rpm -ivh perl-TermReadKey-2.30-3.el6.rfx.x86_64.rpm

rpm -ivh percona-toolkit-3.0.13-1.el6.x86_64.rpm

2、创建监控用户

mysql> grant select,process,super,replication slave on *.* to 'pt_checksum'@'%' identified by 'pt_checksum';

mysql> grant all privileges on testdb.* to 'pt_checksum'@'%';

mysql> flush privileges;

3、主库启动heartbeat进程

首次启动需要创建心跳监控的表：

pt-heartbeat h='192.168.9.71',u='pt_checksum',p='pt_checksum',P=3306 -D testdb --create-table --interval=1 --update --replace –daemonize

以后则不需要：

pt-heartbeat h='192.168.9.71',u='pt_checksum',p='pt_checksum',P=3306 -D testdb  --interval=1 --update --replace --daemonize

4、检测复制延时

pt-heartbeat h='192.168.9.72',u='pt_checksum',p='pt_checksum',P=3306 -D testdb --table=heartbeat --monitor --master-server-id=1

pt-heartbeat h='192.168.9.72',u='pt_checksum',p='pt_checksum',P=3306 -D testdb --table=heartbeat --check --master-server-id=1

5、停止守护进程

pt-heartbeat h='192.168.9.71',u='pt_checksum',p='pt_checksum',P=3306 -D testdb --stop

Successfully created file /tmp/pt-heartbeat-sentinel

Remove this file to permit pt-heartbeat to run

3.2 主从不一致数据修复

1、Checksum

create database percona;

grant all on percona.* to 'pt_checksum'@'%';

pt-table-checksum h='192.168.9.71',u='pt_checksum',p='pt_checksum',P=3306 -d testdb --nocheck-replication-filters --replicate=percona.checksums --no-check-binlog-format

返回结果如下：

 

 

2、借助pt-table-sync修复

输出SQL不执行修复

pt-table-sync --print --replicate=percona.checksums h='192.168.9.71',u='pt_checksum',p='pt_checksum',P=3306 h='192.168.9.72',u='pt_checksum',p='pt_checksum',P=3306

执行修复（正确输入主库从库信息）

pt-table-sync --execute --replicate=percona.checksums h='192.168.9.71',u='pt_checksum',p='pt_checksum',P=3306 h='192.168.9.72',u='pt_checksum',p='pt_checksum',P=3306

3.3             主从同步异常处理

由于某些原因，有可能主库上已经purge掉了一些binlog，但从库都还没有接收到。会遭遇如下异常情况：

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

处理思路1：把slave上的gtid_purged设置为master还没有被purge掉的值，然后借助第三方一致性同步工具来做数据的一致性同步。

mysql>reset master;

mysql>set global GTID_PURGED="326fe663-cdab-11e9-8ef6-080027364db6:1-616463";

mysql>start slave;

mysql>show slave status G

当然执行完这个之后数据是不一致的，那么此时就需要通过pt-table-checksum和pt-table-sync来做数据的一致性恢复了。

处理思路2：重建slave。

mysql>reset master;

mysql>source allDB.`hostname`_`date +"%Y%m%d%H%M"`.sql;

mysql>CHANGE MASTER TO MASTER_HOST ='192.168.9.71',MASTER_PORT = 3306,MASTER_USER = 'repl',MASTER_PASSWORD = '123456',MASTER_AUTO_POSITION = 1;

mysql>start slave;

mysql>show slave statusG;