MySQL Primary/Secondary Switchover

Primary/Secondary switchover means that when the MySQL writer node fails, services are automatically switched to the secondary node. MyCAT uses the show slave status command to check the heartbeat between the primary and secondary databases. The MyCAT heartbeat mechanism identifies the primary-secondary synchronization status and replication latency based on Seconds_Behind_Master, Slave_IO_Running, and Slave_SQL_Running in the command output. If the value of Seconds_Behind_Master is greater than that of slaveThreshold, the read/write splitting filter filters out the secondary node to prevent old data from being read. If the primary node breaks down, the switchover logic checks whether the value of Seconds_Behind_Master on the secondary node is 0. If the value is 0, primary-secondary replication is used, and in this case, a switchover can be performed.

1. Configure the MyCAT schema.xml file.
   1. Open the file.

      1	vim /usr/local/mycat/conf/schema.xml

   2. Add the following content to the file:

      1	<writeHost host="hosts" url="localhost:3313" user="root" password="123456" />

      

   3. Press Esc, type :wq!, and press Enter to save the file and exit.
   4. Start MyCAT and query its status.

      1 2 3

      mycat stop mycat start mycat status

      

      Configure the MyCAT primary/secondary switchover by using switchType. In this document, the default value is used.

      • -1: Automatic switchover is not performed.
      • 1 (default): Automatic switchover is performed.
      • 2: Whether to perform a switchover depends on the status of MySQL primary-secondary synchronization.
2. Perform a connection test.
   1. Open the file.

      1	vim /home/write.sh

   2. Replace the file content with the following:

      1 2 3 4 5

      #!/bin/bash for i in {20001..22000} do /usr/local/mysql/bin/mysql -uroot -p1234567 -P8066 -h127.0.0.1 --default_auth=mysql_native_password -Dsysbench -e "insert into test1(id,report_hostname) values($i,@@report_host)" done

   3. Press Esc, type :wq!, and press Enter to save the file and exit.
   4. Execute the script.

      1	sh /home/write.sh

3. Confirm the test data.

   1	/usr/local/mysql/bin/mysql -uroot -p1234567 -P8066 -h127.0.0.1 --default_auth=mysql_native_password -Dsysbench -e "select * from test1"

   

4. Connect to the MySQL database and shut down the primary database.

   1 2	/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqlm.sock shutdown;

5. Perform a connection test.
   1. Open the file.

      1	vim /home/write.sh

   2. Change the number of inserted records from 22001 to 24000.

      1 2 3 4 5

      #!/bin/bash for i in {22001..24000} do /usr/local/mysql/bin/mysql -uroot -p1234567 -P8066 -h127.0.0.1 --default_auth=mysql_native_password -Dsysbench -e "insert into test1(id,report_hostname) values($i,@@report_host)" done

   3. Press Esc, type :wq!, and press Enter to save the file and exit.
   4. Execute the script.

      1	sh /home/write.sh

6. Confirm the data again.

   1	/usr/local/mysql/bin/mysql -uroot -p1234567 -P8066 -h127.0.0.1 --default_auth=mysql_native_password -Dsysbench -e "select * from test1"

   

   The original secondary database is switched to the primary database, and all write operations are routed to the new primary database.