Let's talk about how to change the replication type online (log copy to Global thing replication), refer to Course: mysql5.7 copy Combat
Prerequisite
(1) All server versions in the cluster are higher than 5.7.6
(2) All server Gtid_mode in the cluster are set to OFF (view with show variables like ' gtid_mode ' command)
1: Change log-based replication to a thing-based copy processing step
(1) Setting parameters
Gtid_mode has 4 values in version 5.7
Off: Off
Off_permissive: Ready to close
On_permissive: Ready to open
On: Open
These 4 values can only be from Off-->off_permissive-->on_permissive-->on or (off<--off_permissive<--on_permissive<-- ON) Order setting
(The primary and slave servers execute the following commands separately, with the first 2 commands pointing in order, instead of all commands on a single server)
SET @ @global. Enforce_gtid_consistency=warn; Force set Gtid Consistency, the value is: Warn, after setting up the recommended tail-f command to see if mysql-error.log have exception information
SET @ @global. Enforce_gtid_consistency=on; Force set Gtid consistency with a value of: on
SET @ @global. gtid_mode=off_permissive; Set Gtid_mode to ready to turn off state
SET @ @global. gtid_mode=on_permissive;Set Gtid_mode to ready to open state
(2) Viewing the status from the server
Show status like ' Ongoing_anonymous_transaction_count '; View the number of things that log-based replication is, 0 or null to indicate normal. This step is to ensure consistency of the data based on the log replication and to prevent things from being
Execution on the server is complete and is not executed from the server, resulting in data loss
SET @ @global. Gtid_mode=on; Set Gtid_mode as Open
On the slave server
Stop slave;
Stop slave;
Change Master to Master_auto_position=1; (Change copy mode)
Start slave;
View
(3) Persistent configuration (important operation)
After confirming the successful switch to the object-based replication, you need to add 2 parameters to the MY.CNF (all MySQL server nodes)
Ensure that the next time you restart the MySQL server will automatically start the global thing replication mode for master-slave synchronization
Enforce_gtid_consistency=on
Gtid_mode=on
This step is very important, don't forget it. Or the next time you start MySQL, you will find the master-slave synchronization kneeling.
2: Change based on thing replication to log-based replication
Scenario (change to log-based replication if the tool or third-party app that is connected does not support the replication of Things)
Prerequisite
(1) All server versions in the cluster are higher than 5.7.6
(2) All server Gtid_mode in the cluster are set to ON (view with show variables like ' gtid_mode ' command)
Steps:
(1)
Stop slave;
Change Master to master_auto_position=0,master_log_file= ' file ', master_log_pos=; Stop things copying, set log files and log file nodes for log replication
Start slave;
(2)
set @ @global. gtid_mode=on_ Permissive //set Gtid_mode to ready to open
set @ @global. gtid_mode=off_ Permissive //set Gtid_mode to prepare to turn off status
gtid_mode=off
More information about MySQL GTID mode mysql5.6 new features GTID fundamentals MySQL 5.6 global Transaction ID (GTID) Redemption principle (i)
Turn from
mysql5.7 Master-slave Replication--Online Change replication type-CSDN blog http://blog.csdn.net/u014739462/article/details/70183458
MySQL 5.7 gtid copy online switch to traditional replication-zengxuewen2045 blog-csdn Blog http://blog.csdn.net/zengxuewen2045/article/details/52464390
mysql5.7 Master-slave Replication--Online change replication type "Go"