Primary/Secondary Switchover

After the data consistency verification is complete and all data in the secondary database is correct, perform a primary/secondary switchover to adjust the existing primary/secondary replication structure. After the switchover is complete, check the consistency between the new primary database and the new secondary database.

• Before the primary/secondary switchover, there is one primary database and two secondary databases. The primary database and secondary database 1 are on x86 servers, and the secondary database 2 is on a Kunpeng server.
• Switch the Kunpeng secondary database to the new primary database. The original x86 primary database turns into a new secondary database

The primary/secondary switchover is shown in Figure 1.

Figure 1 Switchover of primary/secondary databases

The following describes how to perform a primary/secondary switchover. After the switchover, the new secondary database can successfully connect to and keep data consistent with the new Kunpeng primary database.

1. Upgrade secondary database 2 (Kunpeng) to the new primary database.
   1. Log in to the Kunpeng server (running the original secondary database 2).
   2. Stop the secondary database service and delete all secondary database configurations to upgrade it to the primary database.

      stop slave;
      reset slave all;

   3. Ensure that the value of log_bin of the new primary database is ON.

      show variables like 'log_bin';

      

      If the value of log_bin is OFF, perform the following operations to enable log_bin.

      1. Open the /etc/my.cnf file.

         vim /etc/my.cnf

      2. Press i to enter the insert mode and find the [mysqld] section in the file. If this section does not exist, add it. Modify the [mysqld] section as follows:

         log-error=/data/mysql/log/mysql.log
         pid-file=/data/mysql/run/mysqld.pid
         [mysqldump]
         quick
         [mysql]
         no-auto-rehash
         [client]
         default-character-set=utf8
         [mysqld]
         basedir=/usr/local/mysql
         socket=/data/mysql/run/mysql.sock
         tmpdir=/data/mysql/tmp
         datadir=/data/mysql/data
         default_authentication_plugin=caching_sha2_password
         port=3306
         user=mysql
         server-id=3 # The server-id of the new primary database server is 3.
         log-bin=/data/mysql/data/mysql-bin
         relay_log=/data/mysql/relaylog/relay-bin
         report_host=192.168.0.3 # 192.168.0.3 is the IP address of the new primary database server. Change it based on the actual IP address.

      3. Press Esc, type :wq!, and press Enter to save the file and exit.
      4. Restart MySQL to make the configurations take effect. See 3 for the restart commands.
2. Connect secondary database 1 (x86) to the new primary database.
   1. Log in to secondary database 1 on the x86 server.
   2. Stop the secondary database service and delete all secondary database configurations.

      stop slave;
      reset slave all;

   3. Connect secondary database 1 (x86) to the new primary database.

      change master to master_host='192.168.0.3',master_port=3306,master_user='replicate',master_password='123456',master_auto_position=1, get_master_public_key=1;

      

      192.168.0.3 is the IP address of the new primary database server. Change it based on the actual IP address.

   4. Start and verify the status of the secondary database.

      start slave;
      show slave status \G;

      If the values of Slave_IO_Running and Slave_SQL_Running are both Yes, secondary database 1 has been successfully connected to the new primary database.

      

3. Connect the original primary database to the new primary database.
   1. Log in to the new secondary database (which is the original primary database).
   2. Stop the secondary database service and delete all secondary database configurations.

      stop slave;
      reset slave all;

   3. Connect the new secondary database to the new primary database.

      change master to master_host='192.168.0.3',master_port=3306,master_user='replicate',master_password='123456',master_auto_position=1, get_master_public_key=1;

      

      192.168.0.3 is the IP address of the new primary database server. Change it based on the actual IP address.

   4. Start and verify the status of the secondary database.

      start slave;
      show slave status \G;

      If the values of Slave_IO_Running and Slave_SQL_Running are both Yes, the new secondary database has been successfully connected to the new primary database.

      

4. Verify the primary/secondary consistency again.

   pt-table-checksum h=192.168.0.3,u=checksums,p=123456,P=3306 --databases=tpcc --no-check-binlog-format --replicate=pt.checksums --max-lag=3 --recursion-method=hosts

   

   192.168.0.3 is the IP address of the new primary database server. Change it based on the actual IP address.