MySQL5.7.21开启Gtid配置主从复制

mysql5.7.21开启Gtid配置主从复制

一、环境：

mysql 主库master：10.0.0.101 172.168.1.101
mysql 从库slave：10.0.0.103 172.168.1.103
mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz 二进制安装完成

二、主从库/etc/my.cf文件参数介绍

mysql主库完整my.cnf配置文件

[root@slave01 ~]# cat /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /tmp/mysql.sock basedir = /usr/local/mysql datadir = /data/mysql/data back_log = 2000 open_files_limit = 1024 max_connections = 800 max_connect_errors = 3000 max_allowed_packet = 33554432 external-locking = FALSE character_set_server = utf8 #binlog log-slave-updates = 1 binlog_format = row log-bin = /data/mysql/logs/bin-log/mysql-bin expire_logs_days = 5 sync_binlog = 1 binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M #replicate-ignore-db=mysql skip-name-resolve slave-skip-errors = 1032,1062 skip_slave_start=1 ###relay log relay-log = /data/mysql/logs/relay-log/relay-bin relay-log-info-file = /data/mysql/relay-log.info ###slow_log slow_query_log = 1 slow-query-log-file = /data/mysql/logs/mysql-slow.log log-error = /data/mysql/logs/error.log ##GTID server_id = 1103 gtid_mode=on enforce_gtid_consistency=on event_scheduler = ON innodb_autoinc_lock_mode = 1 innodb_buffer_pool_size = 10737418 innodb_data_file_path = ibdata1:10M:autoextend innodb_data_home_dir = /data/mysql/data innodb_log_group_home_dir = /data/mysql/data innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_io_capacity = 2000 innodb_log_buffer_size = 8388608 innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 50 innodb_open_files = 512 innodb_read_io_threads = 8 innodb_thread_concurrency = 20 innodb_write_io_threads = 8 innodb_lock_wait_timeout = 10 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 key_buffer_size = 3221225472 innodb_log_file_size = 1G local_infile = 1 log_bin_trust_function_creators = 1 log_output = FILE long_query_time = 1 myisam_sort_buffer_size = 33554432 join_buffer_size = 8388608 tmp_table_size = 33554432 net_buffer_length = 8192 performance_schema = 1 performance_schema_max_table_instances = 200 query_cache_size = 0 query_cache_type = 0 read_buffer_size = 20971520 read_rnd_buffer_size = 16M max_heap_table_size = 33554432 bulk_insert_buffer_size = 134217728 secure-file-priv = /data/mysql/tmp sort_buffer_size = 2097152 table_open_cache = 128 thread_cache_size = 50 tmpdir = /data/mysql/tmp slave-load-tmpdir = /data/mysql/tmp wait_timeout = 120 transaction_isolation=read-committed innodb_flush_log_at_trx_commit=0 lower_case_table_names=1 [mysqldump] quick max_allowed_packet = 64M [mysqld_safe] log-error = /data/mysql/logs/error.log pid-file = /data/mysql/mysqld.pid

mysql从库完整my.cnf配置文件介绍：

 

mysql的slave从库的配置文件/etc/my.cnf中参数只是sever_id和主库的不一样，其他的参数都保持一致

三、配置GTID参数介绍：

对于GTID的配置，主要修改配置文件中与GTID特性相关的几个重要参数(建议使用mysql-5.6.5以上版本)，如下：

1、主库： [mysqld] #GTID: server_id=54 gtid_mode=on enforce_gtid_consistency=on #binlog log_bin=master-binlog log-slave-updates=1 binlog_format=row #relay log skip_slave_start=1 2、从库： [mysqld] #GTID: gtid_mode=on enforce_gtid_consistency=on server_id=197 #binlog log-bin=slave-binlog log-slave-updates=1 binlog_format=row #relay log skip_slave_start=1

四、配置主从库

mysql master上操作:

mysql -uroot -p'123456' -e "grant replication slave on *.* to novelrep@'172.168.1.103' identified by 'JuwoSdk21TbUser'; flush privileges;" [root@master01 ~]# mysqldump -uroot -p'123456' -B -A -F --master-data=2 --single-transaction --events|gzip >/opt/juwo_$(date +%F).sql.gz mysqldump: [Warning] Using a password on the command line interface can be insecure. Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions,

even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

mysql slave上操作:

mysql -uroot -p'123456' -e "source /root/juwo_$(date +%F).sql"
主库上的dump出数据在导入到slave过程中报错：

 

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty

mysql -uroot -p'123456' -e "CHANGE MASTER TO MASTER_HOST='172.168.1.101',MASTER_PORT=3306,MASTER_USER='novelrep',MASTER_PASSWORD='JuwoSdk21TbUser',MASTER_AUTO_POSITION = 1;start slave;show slave status\G" |grep -i "yes"

slave上执行完以上操作后，show slave status\G,查看到主从复制，slave上的sql线程报错，主从复制配置失败

五、重新配置主从复制：

参考一下文档：
https://www.cnblogs.com/tonnytangy/p/7779164.html

当前GTID_EXECUTED参数已经有值，而从master主库倒出来的dump文件中包含了SET @@GLOBAL.GTID_PURGED的操作

解决方法：

方法一：reset mater 这个操作要在slave机器上操作，可以将当前slave库的GTID_EXECUTED值置空 方法二：--set-gtid-purged=off 在dump导出master数据时，添加--set-gtid-purged=off参数，避免将master上的gtid信息导出，然后再导入到slave库

本文档同时采用了上述2个步骤：

 

master 库操作：

mysql -uroot -p'123456' -e "reset mater;" mysql -uroot -p'123456' -e "grant replication slave on *.* to novelrep@'172.168.1.103' identified by 'JuwoSdk21TbUser';

flush privileges;" mysqldump -uroot -p'123456' -B -A -F --set-gtid-purged=OFF --master-data=2 --single-transaction

--events|gzip >/opt/juwo_$(date +%F).sql.gz

mysql slave上操作:

mysql -uroot -p'123456' -e "source /root/juwo_$(date +%F).sql"

 

主库上的dump出数据在导入到slave过程中一切正常

mysql -uroot -p'123456' -e "CHANGE MASTER TO MASTER_HOST='172.168.1.101',MASTER_PORT=3306,MASTER_USER='novelrep',MASTER_PASSWORD='JuwoSdk21TbUser',MASTER_AUTO_POSITION = 1;start slave;show slave status\G" |grep -i "yes"

slave上执行完以上操作后，show slave status\G,查看到主从复制，slave上的IO线程和sql线程都是yes,到此mysql的GTID模式的主从配置完成

mysql5.7.21开启Gtid配置主从复制

一、环境：

mysql 主库master：10.0.0.101 172.168.1.101
mysql 从库slave：10.0.0.103 172.168.1.103
mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz 二进制安装完成

二、主从库/etc/my.cf文件参数介绍

mysql主库完整my.cnf配置文件

[root@slave01 ~]# cat /etc/my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user    = mysql
port    = 3306
socket  = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql/data

back_log = 2000
open_files_limit = 1024
max_connections = 800
max_connect_errors = 3000
max_allowed_packet = 33554432
external-locking = FALSE
character_set_server = utf8

#binlog
log-slave-updates = 1
binlog_format = row
log-bin = /data/mysql/logs/bin-log/mysql-bin
expire_logs_days = 5
sync_binlog = 1
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M

#replicate-ignore-db=mysql
skip-name-resolve
slave-skip-errors = 1032,1062
skip_slave_start=1

###relay log
relay-log = /data/mysql/logs/relay-log/relay-bin
relay-log-info-file = /data/mysql/relay-log.info

###slow_log
slow_query_log = 1
slow-query-log-file = /data/mysql/logs/mysql-slow.log

log-error = /data/mysql/logs/error.log

##GTID
server_id = 1103
gtid_mode=on
enforce_gtid_consistency=on

event_scheduler = ON
innodb_autoinc_lock_mode = 1
innodb_buffer_pool_size = 10737418
innodb_data_file_path = ibdata1:10M:autoextend
innodb_data_home_dir = /data/mysql/data
innodb_log_group_home_dir = /data/mysql/data

innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_log_buffer_size = 8388608
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_open_files = 512
innodb_read_io_threads = 8
innodb_thread_concurrency = 20
innodb_write_io_threads = 8
innodb_lock_wait_timeout = 10
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
key_buffer_size = 3221225472
innodb_log_file_size = 1G
local_infile = 1
log_bin_trust_function_creators = 1
log_output = FILE
long_query_time = 1

myisam_sort_buffer_size = 33554432
join_buffer_size = 8388608
tmp_table_size = 33554432
net_buffer_length = 8192
performance_schema = 1
performance_schema_max_table_instances = 200

query_cache_size = 0
query_cache_type = 0
read_buffer_size = 20971520
read_rnd_buffer_size = 16M
max_heap_table_size = 33554432
bulk_insert_buffer_size = 134217728
secure-file-priv = /data/mysql/tmp

sort_buffer_size = 2097152
table_open_cache = 128
thread_cache_size = 50
tmpdir = /data/mysql/tmp
slave-load-tmpdir = /data/mysql/tmp
wait_timeout = 120
transaction_isolation=read-committed
innodb_flush_log_at_trx_commit=0
lower_case_table_names=1
[mysqldump]
quick
max_allowed_packet = 64M
[mysqld_safe]
log-error = /data/mysql/logs/error.log
pid-file = /data/mysql/mysqld.pid

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.
• 39.
• 40.
• 41.
• 42.
• 43.
• 44.
• 45.
• 46.
• 47.
• 48.
• 49.
• 50.
• 51.
• 52.
• 53.
• 54.
• 55.
• 56.
• 57.
• 58.
• 59.
• 60.
• 61.
• 62.
• 63.
• 64.
• 65.
• 66.
• 67.
• 68.
• 69.
• 70.
• 71.
• 72.
• 73.
• 74.
• 75.
• 76.
• 77.
• 78.
• 79.
• 80.
• 81.
• 82.
• 83.
• 84.
• 85.
• 86.
• 87.
• 88.
• 89.
• 90.
• 91.
• 92.
• 93.
• 94.
• 95.
• 96.
• 97.
• 98.
• 99.
• 100.
• 101.
• 102.
• 103.
• 104.
• 105.
• 106.
• 107.
• 108.
• 109.
• 110.
• 111.

 

 

mysql从库完整my.cnf配置文件介绍：

mysql的slave从库的配置文件/etc/my.cnf中参数只是sever_id和主库的不一样，其他的参数都保持一致

三、配置GTID参数介绍：

对于GTID的配置，主要修改配置文件中与GTID特性相关的几个重要参数(建议使用mysql-5.6.5以上版本)，如下：

1、主库：
[mysqld]
#GTID:
server_id=54
gtid_mode=on
enforce_gtid_consistency=on
 
#binlog
log_bin=master-binlog
log-slave-updates=1    
binlog_format=row
 
#relay log
skip_slave_start=1            
 
2、从库：
[mysqld]
#GTID:
gtid_mode=on
enforce_gtid_consistency=on
server_id=197
 
#binlog
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row
 
#relay log
skip_slave_start=1

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.

 

 

四、配置主从库

mysql master上操作:

mysql -uroot -p'123456' -e "grant replication slave on *.* to novelrep@'172.168.1.103' identified by 'JuwoSdk21TbUser'; flush privileges;"
[root@master01 ~]# mysqldump -uroot -p'123456' -B -A -F  --master-data=2 --single-transaction  --events|gzip >/opt/juwo_$(date +%F).sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore 

GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

 

 

mysql slave上操作:

mysql -uroot -p'123456' -e "source /root/juwo_$(date +%F).sql"
主库上的dump出数据在导入到slave过程中报错：

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty

mysql -uroot -p'123456' -e "CHANGE MASTER TO MASTER_HOST='172.168.1.101',MASTER_PORT=3306,MASTER_USER='novelrep',MASTER_PASSWORD='JuwoSdk21TbUser',MASTER_AUTO_POSITION = 1;start slave;show slave 

status\G" |grep -i "yes"

• 1.
• 2.
• 3.

 

 

slave上执行完以上操作后，show slave status\G,查看到主从复制，slave上的sql线程报错，主从复制配置失败

五、重新配置主从复制：

参考一下文档：
https://www.cnblogs.com/tonnytangy/p/7779164.html
当前GTID_EXECUTED参数已经有值，而从master主库倒出来的dump文件中包含了SET @@GLOBAL.GTID_PURGED的操作

解决方法：

方法一：reset mater

这个操作要在slave机器上操作，可以将当前slave库的GTID_EXECUTED值置空

方法二：--set-gtid-purged=off

在dump导出master数据时，添加--set-gtid-purged=off参数，避免将master上的gtid信息导出，然后再导入到slave库

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

 

 

本文档同时采用了上述2个步骤：

master 库操作：

mysql -uroot -p'123456' -e "reset mater;"
mysql -uroot -p'123456' -e "grant replication slave on *.* to novelrep@'172.168.1.103' identified by 'JuwoSdk21TbUser'; flush privileges;"
mysqldump -uroot -p'123456' -B -A -F --set-gtid-purged=OFF  --master-data=2 --single-transaction  --events|gzip >/opt/juwo_$(date +%F).sql.gz

• 1.
• 2.
• 3.

 

 

mysql slave上操作:

mysql -uroot -p'123456' -e "source /root/juwo_$(date +%F).sql"

主库上的dump出数据在导入到slave过程中一切正常

mysql -uroot -p'123456' -e "CHANGE MASTER TO MASTER_HOST='172.168.1.101',MASTER_PORT=3306,MASTER_USER='novelrep',MASTER_PASSWORD='JuwoSdk21TbUser',MASTER_AUTO_POSITION = 1;start slave;show slave 

status\G" |grep -i "yes"

• 1.
• 2.
• 3.

 

 

slave上执行完以上操作后，show slave status\G,查看到主从复制，slave上的IO线程和sql线程都是yes,到此mysql的GTID模式的主从配置完成