Performing a Switchover Upon an Exception

• The primary database breaks down.

  Shut down the OS.

  1	shutdown -h now

  Or forcibly stop the database process.

  1 2	kill -p 'pidof mysqld' ps -ef | grep mysql

• The primary database is disconnected from the network.

  Run the following command on the OS to shut down the NIC:

  1	ifdown enp5s0

• The drive (that is, data drive) of the primary database is faulty.

  Delete all data files from the primary database.

  1 2 3

  ls /data/mysql/data/ibdata* rm –rf /data/mysql/data/ibdata* ls /data/mysql/data/ibdata*

show slave status\G;

After confirming that the secondary database will no longer receive binlog information from the primary database, stop the client application. (To ensure the integrity of service data, stop the client's operations on the primary database.)

If any of the following situations occurs, the secondary database will no longer receive binlog information from the primary database:

• The value of Slave_IO_Running is No.
• Last_IO_Error contains error information.
• The value of Retrieved_Gtid_Set or Read_Master_Log_Pos keeps unchanged.

1. Log in to a secondary database.

   /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock

2. Query the status of all secondary databases.

   show slave status\G;

   Compare the values of Exec_Master_Log_Pos (traditional mode) or Executed_Gtid_Set (GTID mode). Select the secondary database with the largest value as the new primary database. If the values are the same, select any of the secondary databases as the new primary database.

3. Manually switch the selected secondary database as the new primary database. (The MySQL database does not support automatic switchover. You can use third-party software such as MHA to implement automatic switchover.)

   Stop the secondary database, delete all information about the secondary database, and query the status of the secondary database.

   stop slave;
   reset slave all;
   show slave status \G;

   Query the status of the primary database and record the values of File (binlog file name) and Position (latest position in the binlog). These two parameters will be used in 7.e.

   show master status;

   Query the configuration, including the deployment mode, replication mode, and whether parallel replication is enabled, of the secondary database (see Querying Primary-Secondary Replication). Then, configure the new primary database (see "Configuring the Primary Database" in Deploying MySQL Primary-Secondary Replication).

   

   To reset File (binlog file name) and Position (latest position in the binlog) for the primary database, run the following commands:

   reset master;
   show master status;

4. 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:

   1. Open the configuration file.

      1	vim /etc/my.cnf

   2. Press i to enter the insert mode and modify the file as follows:

      [mysqld]
      log-bin=mysql-bin

   3. Press Esc, type :wq!, and press Enter to save the file and exit.
   4. Restart the MySQL service.

      1	service mysql restart

      Or

      1 2	/usr/local/mysql/bin/mysqladmin -uroot -p123456 shutdown -S /data/mysql/run/mysql.sock /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf &

1. Log in to a secondary database.

   1	/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock

2. Query the replay status of the relay log on the secondary database.

   show slave status \G;

   If the value of Slave_SQL_Running_State is Slave has read all relay log;waiting for more updates, the playback is successful.

   

3. Stop the secondary database.

   stop slave;

   Delete all information about the secondary database, and query the status of the secondary database.

   reset slave all;
   show slave status \G;

4. Configure the replication mode. For details, see 4.
5. Configure the secondary database to point to the new primary database based on the replication mode configured in 4.
   • Traditional mode (The values of master_log_file and master_log_pos are obtained in 6.c. Replace them based on your requirements):

     change master to master_host='192.168.220.72',master_port=3306,master_user='replicate',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=155;

   • GTID mode:

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

6. Start the secondary database.

   start slave;

7. Query the status of the secondary database.

   show slave status \G;

   If the values of the following two parameters are Yes, the secondary database is configured successfully.

   Slave_IO_Running: Yes

   Slave_SQL_Running: Yes

1. Restore the original primary database after it breaks down.

   Start the OS and start the database process.

   1 2	ps -ef | grep mysql /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

2. Restore the original primary database after it is disconnected from network.

   1 2 3

   ip a ifup enp5s0 ip a

3. Restore the original primary database after its drive is faulty.

   If the primary database is unavailable because the drive is damaged, you are advised to use the physical backup (for example, XtraBackup) or logical backup (mysqldump) to restore the data.

   Query the configurations (including the deployment mode, replication mode, and whether parallel replication is enabled) of the secondary database by referring to Querying Primary-Secondary Replication. Then, configure the original primary database by referring to the sections "Configuring Secondary Databases" in Deploying MySQL Primary-Secondary Replication.