配置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; |