Impact of Different Replication User Creation Methods on MySQL Master‑Slave Replication

Background A customer observed that MySQL master‑slave replication could be set up whether the replication user was created only on the master or on both master and slave. The author reproduced the scenario to examine the impact of different user‑creation methods.

Verification

Scenario 1: Create replication user only on the master

/usr/local/mysql5732/bin/mysqldump --single-transaction --master-data=2 -B zlm -S /tmp/mysql3332.sock -p > zlm.sql
scp zlm.sql [email protected]:~

Import on the slave:

mysql> source zlm.sql

Create the replication user on the master:

mysql> create user repl1 identified by 'repl1';

Configure the slave and start replication:

mysql> change master to master_host='10.186.60.62',master_port=3332,master_user='repl1',master_password='repl1',master_auto_position=1;
mysql> start slave;

Initially the slave reports Error 1045 because the user lacks REPLICATION SLAVE privilege; granting it on the master resolves the error. The replication user is automatically created on the slave during the binlog replay, so a separate user on the slave is unnecessary.

Conclusion 1 Creating the replication user only on the master is sufficient; the user is synchronized to the slave.

Scenario 2: Create replication users separately on master and slave using CREATE statements

After importing the dump, the master creates the user:

mysql> create user repl2 identified by 'repl2';
mysql> grant replication slave on *.* to repl2;

The slave also creates its own user (with sql_log_bin=0 to avoid generating GTID entries). When the slave later replays the master’s CREATE USER transaction, Error 1396 occurs because the user already exists.

Attempting to create a duplicate user confirms the conflict.

Conclusion 2 When both master and slave create the replication user with CREATE, the slave’s replay of the master’s CREATE USER statement aborts replication.

Scenario 3: Create replication users separately using GRANT statements

Master creates the user with a GRANT statement:

mysql> grant replication slave on *.* to repl3 identified by 'repl3';

After configuring and starting replication, no errors appear. GRANT is effectively idempotent; replaying the same GRANT on the slave does not disrupt replication. Note that MySQL 8.0 disallows user creation via GRANT.

Conclusion 3 Using GRANT to create the replication user on both master and slave does not break replication, but this syntax is deprecated in MySQL 8.0.

Summary

All three methods can establish replication, but creating the user only on the master (recommended) avoids unnecessary GTID conflicts.

When using CREATE on both sides, disable binary logging on the slave ( sql_log_bin=0 ) before creating the user, or create the user only on the master.

GRANT works without breaking replication in MySQL 5.7, but should be avoided for future compatibility.