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            = 3306socket          = /tmp/mysql.sock[mysql]no-auto-rehash[mysqld]user    = mysqlport    = 3306socket  = /tmp/mysql.sockbasedir = /usr/local/mysqldatadir = /data/mysql/databack_log = 2000open_files_limit = 1024max_connections = 800max_connect_errors = 3000max_allowed_packet = 33554432external-locking = FALSEcharacter_set_server = utf8#binloglog-slave-updates = 1binlog_format = rowlog-bin = /data/mysql/logs/bin-log/mysql-binexpire_logs_days = 5sync_binlog = 1binlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2M#replicate-ignore-db=mysqlskip-name-resolveslave-skip-errors = 1032,1062skip_slave_start=1###relay logrelay-log = /data/mysql/logs/relay-log/relay-binrelay-log-info-file = /data/mysql/relay-log.info###slow_logslow_query_log = 1slow-query-log-file = /data/mysql/logs/mysql-slow.loglog-error = /data/mysql/logs/error.log##GTIDserver_id = 1103gtid_mode=onenforce_gtid_consistency=onevent_scheduler = ONinnodb_autoinc_lock_mode = 1innodb_buffer_pool_size = 10737418innodb_data_file_path = ibdata1:10M:autoextendinnodb_data_home_dir = /data/mysql/datainnodb_log_group_home_dir = /data/mysql/datainnodb_file_per_table = 1innodb_flush_log_at_trx_commit = 2innodb_flush_method = O_DIRECTinnodb_io_capacity = 2000innodb_log_buffer_size = 8388608innodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 50innodb_open_files = 512innodb_read_io_threads = 8innodb_thread_concurrency = 20innodb_write_io_threads = 8innodb_lock_wait_timeout = 10innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1key_buffer_size = 3221225472innodb_log_file_size = 1Glocal_infile = 1log_bin_trust_function_creators = 1log_output = FILElong_query_time = 1myisam_sort_buffer_size = 33554432join_buffer_size = 8388608tmp_table_size = 33554432net_buffer_length = 8192performance_schema = 1performance_schema_max_table_instances = 200query_cache_size = 0query_cache_type = 0read_buffer_size = 20971520read_rnd_buffer_size = 16Mmax_heap_table_size = 33554432bulk_insert_buffer_size = 134217728secure-file-priv = /data/mysql/tmpsort_buffer_size = 2097152table_open_cache = 128thread_cache_size = 50tmpdir = /data/mysql/tmpslave-load-tmpdir = /data/mysql/tmpwait_timeout = 120transaction_isolation=read-committedinnodb_flush_log_at_trx_commit=0lower_case_table_names=1[mysqldump]quickmax_allowed_packet = 64M[mysqld_safe]log-error = /data/mysql/logs/error.logpid-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=54gtid_mode=onenforce_gtid_consistency=on#binloglog_bin=master-binloglog-slave-updates=1    binlog_format=row#relay logskip_slave_start=1            2、从库：[mysqld]#GTID:gtid_mode=onenforce_gtid_consistency=onserver_id=197#binloglog-bin=slave-binloglog-slave-updates=1binlog_format=row#relay logskip_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.gzmysqldump: [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库的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模式的主从配置完成

参考文档：
https://www.2cto.com/database/201801/710551.html