Reviewing Current Replica Configuration

To begin the process of updating replication credentials, you should first inspect the current configuration stored on the replica server. This can be done by querying the mysql.slave_master_info table to identify the host, user, and existing password metadata.

SELECT 
    Host, 
    User_name, 
    User_password, 
    Master_log_name, 
    Master_log_pos 
FROM mysql.slave_master_info\G

The output will provide details similar to the following:

*************************** 1. row ***************************
          Host: 10.0.5.20
     User_name: replica_user
 User_password: old_password_hash
Master_log_name: binlog.000124
 Master_log_pos: 107654321

Updating the Credential on the Primary Instance

On the primary (source) database server, execute an ALTER USER statement to define the new password for the replication account. Ansure the hostname matches your network configuration.

-- Update password for a specific network range
ALTER USER 'replica_user'@'10.0.5.%' IDENTIFIED BY 'new_secure_password_2024';

-- Or update for a specific replica IP
ALTER USER 'replica_user'@'10.0.5.21' IDENTIFIED BY 'new_secure_password_2024';

Reconfiguring the Replica Server

Once the primary has been updated, the replica will lose its connection until its internal configuration is synchronized. You must stop the replication threads before applying the changes.

STOP SLAVE; -- Or STOP REPLICA;

Scenario A: Traditional Position-Based Replication

If you are using binary log file names and positions, use the CHANGE MASTER TO command. If the replica failed and you need to resume from a specific point found in the error logs, include the log file and position parameters.

CHANGE MASTER TO
  MASTER_HOST='10.0.5.20',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='new_secure_password_2024',
  MASTER_LOG_FILE='binlog.000124',
  MASTER_LOG_POS=107654321;

Scenario B: GTID-Based Replication

For environments utilizing Global Transaction Identifiers (GTIDs), the process is more streamlined. You can update the credentials while maintaining the MASTER_AUTO_POSITION setting.

-- Update credentials with GTID auto-positioning
CHANGE MASTER TO
  MASTER_HOST='10.0.5.20',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='new_secure_password_2024',
  MASTER_AUTO_POSITION=1;

If you need to reset the GTID execution history (for example, during a fresh sync or recovery), use the GTID_PURGED global variable:

SET @ORIG_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN = 0;
SET @@GLOBAL.GTID_PURGED = '4db2210a-b123-11ed-8f01-005056b12345:1-500';
SET @@SESSION.SQL_LOG_BIN = @ORIG_LOG_BIN;

Verification and Restart

After applying the configuration changes, verify that the mysql.slave_master_info table reflects the new details, then resume replication.

-- Verify changes
SELECT Host, User_name, User_password FROM mysql.slave_master_info;

-- Restart replication
START SLAVE; -- Or START REPLICA;

-- Confirm status
SHOW SLAVE STATUS\G

Considerations for Dual-Primary (Master-Master) Setups

In a bidirectional replication setup, the password change must be applied to both nodes. After updating the user on both servers, execute the CHANGE MASTER TO command on each node to ensure the cross-connections use the new credentials.

-- Execute on Node A to connect to Node B
CHANGE MASTER TO 
  MASTER_HOST='10.0.5.21', 
  MASTER_USER='replica_user', 
  MASTER_PASSWORD='new_secure_password_2024';

-- Execute on Node B to connect to Node A
CHANGE MASTER TO 
  MASTER_HOST='10.0.5.20', 
  MASTER_USER='replica_user', 
  MASTER_PASSWORD='new_secure_password_2024';