MySQL主库故障切换后数据同步及角色恢复问题咨询

Hey there, let's walk through each of your questions clearly—this is a common failover scenario in MySQL, so I'll break it down step by step based on standard replication setups (since your my.cnf mentions large systems, I’m assuming you’re using async or semi-sync replication):

When your master goes down, you need to unlock the slave for writes temporarily:

• First, stop the replication process on db2 to avoid conflicts:

  STOP SLAVE;
  

• Disable read-only mode:
  • Temporary (no restart needed): Use these commands to lift restrictions immediately. super_read_only is recommended because it blocks even superusers from writing:

    SET GLOBAL super_read_only = OFF;
    SET GLOBAL read_only = OFF;
    

  • Permanent (requires restart): Edit db2's my.cnf file. Look for lines like read_only=1 or super_read_only=1 and change them to 0:

    read_only=0
    super_read_only=0
    

    Then restart MySQL (adjust the command based on your OS):

    systemctl restart mysqld
    

• Verify the change with:

  SELECT @@read_only, @@super_read_only;
  

  You should see 0, 0 in the results.

Once db1 is stable again, you need to reconcile the data that was written to db2 during the failover. Here are three reliable methods:

Option 1: Use Percona Toolkit (pt-table-sync) #

This is the go-to tool for incremental syncs and fixing replication discrepancies:

• First, check for data differences between db1 and db2:

  pt-table-checksum h=db1,u=replication_user,p=your_password h=db2,u=replication_user,p=your_password
  

• Sync the mismatched data from db2 to db1 (always run --dry-run first to preview changes):

  pt-table-sync --dry-run h=db1,u=replication_user,p=your_password h=db2,u=replication_user,p=your_password
  # If the preview looks good, run with --execute
  pt-table-sync --execute h=db1,u=replication_user,p=your_password h=db2,u=replication_user,p=your_password
  

Option 2: mysqldump (for Smaller Datasets) #

If your database isn’t massive, a full or filtered dump works:

• On db2, export the data (use --single-transaction to avoid locking):

  mysqldump -u root -p --databases your_target_db --single-transaction --master-data=2 > db2_post_failover_dump.sql
  

• On db1, first set it to read-only to prevent new writes during import:

  SET GLOBAL super_read_only = ON;
  

• Import the dump:

  mysql -u root -p < db2_post_failover_dump.sql
  

Option 3: Reverse Replication Temporarily #

Flip the replication direction to let db1 catch up to db2, then switch back:

1. On db2, get its master status:

   SHOW MASTER STATUS;
   

   Note the File and Position values.
2. On db1, configure it to replicate from db2:

   CHANGE MASTER TO 
     MASTER_HOST='db2', 
     MASTER_USER='replication_user', 
     MASTER_PASSWORD='your_password', 
     MASTER_LOG_FILE='the_file_from_db2', 
     MASTER_LOG_POS=the_position_from_db2;
   

3. Start replication on db1 and wait for it to sync:

   START SLAVE;
   # Check status until Seconds_Behind_Master is 0 and both IO/SQL threads are running
   SHOW SLAVE STATUS\G
   

4. Once synced, stop replication on db1 and reset its slave config:

   STOP SLAVE;
   RESET SLAVE ALL;
   

After syncing data, restore db1 as your primary read-write database:

• Disable read-only mode on db1:

  SET GLOBAL super_read_only = OFF;
  SET GLOBAL read_only = OFF;
  

• Reconfigure db2 to replicate from db1 again:
  1. On db1, get its master status:

     SHOW MASTER STATUS;
     

     Note the File and Position values.
  2. On db2, stop any existing replication and set up the new master:

     STOP SLAVE;
     CHANGE MASTER TO 
       MASTER_HOST='db1', 
       MASTER_USER='replication_user', 
       MASTER_PASSWORD='your_password', 
       MASTER_LOG_FILE='the_file_from_db1', 
       MASTER_LOG_POS=the_position_from_db1;
     

  3. Start replication on db2 and verify:

     START SLAVE;
     SHOW SLAVE STATUS\G
     

     Ensure Slave_IO_Running and Slave_SQL_Running both say Yes.

Once everything is back to normal, lock db2 down to read-only again:

• Temporary (no restart):

  SET GLOBAL read_only = ON;
  SET GLOBAL super_read_only = ON;
  

• Permanent (requires restart): Edit db2's my.cnf to restore the read-only settings:

  read_only=1
  super_read_only=1
  

  Then restart MySQL:

  systemctl restart mysqld
  

• Confirm the settings with:

  SELECT @@read_only, @@super_read_only;
  

  You should see 1, 1 in the results.

Critical Notes #

• Backup first: Always take full backups of both db1 and db2 before making any replication or write-mode changes—data safety comes first!
• GTID considerations: If you’re using GTID replication, replace the CHANGE MASTER TO commands that specify log file/position with MASTER_AUTO_POSITION=1; for simpler setup.
• Replication user permissions: Ensure your replication user has REPLICATION SLAVE, REPLICATION CLIENT, and appropriate database-level permissions.

内容的提问来源于stack exchange，提问作者oxxxo