mysql5.7 Master-slave Replication--Online change replication type "Go"

Source: Internet
Author: User

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"

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.