MySQL5.7.21 Enable Gtid configuration master-slave replication

mysql5.7.21 Enable Gtid configuration master-slave replication

First, the environment:

MySQL Main library master:10.0.0.101 172.168.1.101
MySQL from library slave:10.0.0.103 172.168.1.103
mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz Binary Installation Complete

Second, the master-slave library/etc/my.cf file parameter introduction

MySQL Master library full my.cnf configuration file

[[email protected] ~]# cat/etc/my.cnf[client]port = 3306socket =/tmp/mysql.sock[mysql]no-auto-r Ehash[mysqld]user = Mysqlport = 3306socket =/tmp/mysql.sockbasedir =/usr/local/mysqldatadir =/data/mysql/databac K_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 full my.cnf configuration file from library description:

MySQL slave from the library configuration file/etc/my.cnf The parameters are just sever_id and the main library is not the same, the other parameters are consistent

Three, configuration Gtid parameter introduction:

For Gtid configuration, it mainly modifies several important parameters related to the Gtid feature in the configuration file (recommended use of mysql-5.6.5 or above), as follows:

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

Iv. configuration of master-slave Library

Operation on MySQL master:

mysql -uroot -p‘123456‘ -e "grant replication slave on *.* to [email protected]‘172.168.1.103‘ identified by ‘JuwoSdk21TbUser‘; flush privileges;" [[email protected] ~]# mysqldump -uroot -p‘123456‘ -B -A -F  --master-data=2 --single-transaction  

Operation on MySQL slave:

mysql -uroot -p‘123456‘ -e "source /root/juwo_$(date +%F).sql"
Dump out data on the main library error during import to 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 after performing the above operation, show slave status\g, see the Master-slave replication, slave SQL thread error, master-slave replication configuration failed

V. RECONFIGURE master-slave replication:

Refer to the Documentation:
Https://www.cnblogs.com/tonnytangy/p/7779164.html
The current gtid_executed parameter already has a value, and the dump file that is poured out from the Master Master library contains the SET @ @GLOBAL. Operation of the Gtid_purged

Workaround:

方法一：reset mater这个操作可以将当前库的GTID_EXECUTED值置空方法二：--set-gtid-purged=off在dump导出时，添加--set-gtid-purged=off参数，避免将gtid信息导出

This document incorporates the 2 steps described above:

Master Library Operations:

mysql -uroot -p‘123456‘ -e "reset mater;"mysql -uroot -p‘123456‘ -e "grant replication slave on *.* to [email protected]‘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

Operation on MySQL slave:

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

Dump out data on the main library is normal during import into 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"

After performing the above operations on the slave, show slave status\g, see the Master-slave replication, the IO thread on slave and the SQL thread are yes, to this MySQL gtid mode master-slave configuration is complete

Reference Documentation:
Https://www.2cto.com/database/201801/710551.html

MySQL5.7.21 Enable Gtid configuration master-slave replication