Shulou(Shulou.com)06/01 Report--

mysql5.7.21 Open Gtid configuration master-slave copy

I. Environment:

mysql master library: 10.0.0.101 172.168.1.101

mysql slave from library: 10.0.0.103 172.168.1.103

mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz binary installation complete

II. Introduction to the parameters of the master-slave library/etc/my.cf file

mysql main library complete my.cnf configuration file

[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 from library complete my.cnf profile description:

mysql slave The parameter in the configuration file/etc/my.cnf of the slave library is only sever_id different from that of the master library, and other parameters remain the same.

III. Configuration GTID parameter introduction:

For the configuration of GTID, several important parameters related to GTID characteristics in the configuration file are mainly modified (it is recommended to use mysql-5.6.5 or above), as follows:

1. Main library: [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. From the library: [mysqld]#GTID:gtid_mode=onenforce_gtid_consistency=onserver_id=197#binlog-bin =slave-binloglog-slave-updates=1binlog_format=row#relay logskip_slave_start=1

mysql master on the operation:

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 on the operation:

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

The dump data on the master database reported an 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"

After performing the above operations on slave, show slave status\G, check master-slave replication, sql thread on slave reports error, master-slave replication configuration fails

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 dumped from the master library contains the SET @@GLOBAL.GTID_PURGED operation.

Solution:

Method 1: reset mater This operation can set the GTID_EXECUTED value of the current slave library to null Method 2: --set-gtid-purged=off When dump exports master data, add--set-gtid-purged=off parameter to avoid exporting gtid information on master and then importing it into slave library

This document uses both of the above steps:

Master Library Operations:

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 on the operation:

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

The dump data on the master library is normal during import to 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 slave, show slave status\G, check master-slave replication, IO thread and sql thread on slave are both yes, master-slave configuration of GTID mode of mysql is completed

References:

https://www.2cto.com/database/201801/710551.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.