Error
Error Code:
1776
MySQL Error 1776: Conflicting Replication Position Settings
Description
This error occurs when you attempt to configure MySQL replication using both GTID-based auto-positioning and manual log file/position parameters simultaneously. MySQL's `MASTER_AUTO_POSITION` feature, which relies on Global Transaction Identifiers (GTIDs), is incompatible with specifying `MASTER_LOG_FILE`, `MASTER_LOG_POS`, `RELAY_LOG_FILE`, or `RELAY_LOG_POS` in the `CHANGE MASTER TO` statement.
Error Message
Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
Known Causes
3 known causesMixing Replication Methods
You are trying to set up replication using `MASTER_AUTO_POSITION` (GTID-based) while also providing manual log file and position parameters. These two methods of tracking replication progress are mutually exclusive.
Incorrect `CHANGE MASTER TO` Syntax
The `CHANGE MASTER TO` statement includes parameters for both GTID-based auto-positioning and traditional file/position-based replication, which is an invalid combination. This often happens when migrating or adapting existing scripts.
Misunderstanding GTID Replication
A lack of clear understanding about how GTID-based replication (`MASTER_AUTO_POSITION`) works, particularly that it automates position tracking and renders manual parameters unnecessary and conflicting.
Solutions
3 solutions available1. Disable Auto-Positioning and Manually Set Replication Coordinates medium
Turn off MASTER_AUTO_POSITION and explicitly define the replication log file and position.
1
Connect to your MySQL replication slave server using a MySQL client.
2
Stop the replication threads if they are running.
STOP SLAVE;3
Reset the replication slave configuration to remove the conflicting auto-positioning setting.
RESET SLAVE ALL;4
Configure the replication slave with the desired master log file and position. You will need to obtain these from your master server.
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxxxxx', MASTER_LOG_POS=yyyyyy, MASTER_AUTO_POSITION=0;5
Start the replication threads.
START SLAVE;2. Enable Auto-Positioning and Let MySQL Handle Coordinates easy
Remove manual log file and position settings to allow MASTER_AUTO_POSITION to manage replication.
1
Connect to your MySQL replication slave server using a MySQL client.
2
Stop the replication threads if they are running.
STOP SLAVE;3
Reset the replication slave configuration to clear any existing manual settings.
RESET SLAVE ALL;4
Configure the replication slave to use auto-positioning. If you are setting up replication for the first time, you might not have explicitly set MASTER_LOG_FILE or MASTER_LOG_POS, but this step ensures they are not present and MASTER_AUTO_POSITION is enabled.
CHANGE MASTER TO MASTER_AUTO_POSITION=1;5
Start the replication threads.
START SLAVE;3. Reconfigure Replication from Scratch advanced
Completely remove and re-establish replication to resolve persistent configuration issues.
1
On the replication slave, stop the replication threads.
STOP SLAVE;2
On the replication slave, reset the slave configuration entirely.
RESET SLAVE ALL;3
On the replication master, ensure binary logging is enabled and set a unique server ID.
log_bin = /var/log/mysql/mysql-bin.log
server_id = 14
On the replication master, create a replication user.
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;5
On the replication master, obtain the current binary log file and position. This is crucial for initial setup.
SHOW MASTER STATUS;6
On the replication slave, configure the master connection details, including the user, password, host, and the log file/position obtained from the master. Choose either auto-positioning or manual positioning.
CHANGE MASTER TO MASTER_HOST='master_host_ip', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.xxxxxx', MASTER_LOG_POS=yyyyyy, MASTER_AUTO_POSITION=0; -- For manual positioning
-- OR
CHANGE MASTER TO MASTER_HOST='master_host_ip', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password', MASTER_AUTO_POSITION=1; -- For auto-positioning7
On the replication slave, start the replication threads.
START SLAVE;