|Before setting up your replication primary [master] and replica(s) [slave(s)], read the MySQL manual's replication section. Beware that before 8.0, MySQL does not support |
SOURCE as a synonym for
REPLICA as a synonym for
Also before you start, beware that MySQL doesn't support replication where source and slave versions or
collate settings differ. You may want to run the MySQL Tips script "Compare MySQL configurations" script, and make adjustments accordingly.
This 8-step sets up traditional MySQL one-way asynchronous replication.
1. Configure the master: Master needs these my.cnf/ini settings under
# For safety, sync every transaction:
If you are replicating only specific databases, (i) for each such database add to the master my.cnf/ini under
[mysqld] the line
binlog-do-db dbname and remember to restart the server, and (ii) ensure that all master users referenced by stored routine creation commands exist on the replica.
2. Get the replica server running: Check master-replica version compatibility. Ensure that the replica server is running correctly. Replication imposes specific configuration requirements: the master needs
server_ids of the master and of all replicas must be unique, and all replicas must be version-compatible with the master. Since 5.5.17, a repica may connect using a plugin. To ease recovery from lost connection to master, in my.cnf/ini under
A master with no
server_id setting refuses all replica connections, and a replica without a
server_id will not connect to a master. With a new replication setup at least, set
log_error_verbosity=2 (before version 5.7.2, set
log_warnings) in my.cnf/ini). Finally, if you are replicating only specific databases, for each of them add to the replica my.cnf/ini under
[mysqld] the line
3. Set replication privileges and options on the replica: Ensure that the user who will execute replication commands on the replica has
SUPER privilege there.
Since 8.0.27 the system variable
sets the number of parallel applier threads, 0 for no parallelism, 4 for minimal paralellism, up to 1024. The system variable
replica_preserve_commit_order=1 forces transactions to execute on the replica in relay log order;
replica_parallel_type=LOGICAL_CLOCK forces transactions to execute in binary log order (required with
4. Set up replication account on the master: On the master, set up the user account that the replication server is to use; it needs
REPLICATION SLAVE privilege:
CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; -- substitute password value
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
This permits replication to any server anywhere. To restrict replication to one host, replace ‘%’ in this command with a specific host name.
There's a gotcha here. As delivered, the
mysql.user table may have a row with
host=localhost and a blank user.name. If this row remains, and if you run the above command and then run the mysql client as the new user
repl, MySQL will log you in as the user with no name, not as user
repl! So will the master server when your replication replia calls with a connection request. Without the replica I/O thread, nothing replicates. So if you’ve not already deleted the
mysql.user row with the blank user.name, do it before you issue the
GRANT REPLICATION command.
Another gotcha to remember: though
GRANT syntax allows you to create a
mysql.user with a hashed password without explicitly calling
PASSWORD(), changing a password before 5.7 with
SET PASSWORD or
UPDATE, and creating a new mysql.user via a direct
INSERT statement, do require the
Provided the master account is for replication only, and if
LOAD DATA FROM MASTER will not be used, the account needs no other privileges. If you have only
MYISAM tables, and if the replication user will be using
LOAD DATA | TABLE FROM MASTER, this replication account on the master also needs global
RELOAD privileges, and
SELECT privileges for all tables that will be loaded.
5. Take a snapshot of master data: Replication must begin with an exact copy of the master server’s data at a defined moment, and a record of a specific location in a named binary log. The simplest way to acquire a data snapshot is to run mysqldump on the master server. If all databases are to be replicated, issue from the command line …
mysqldump -uUSR -pPWD --master-data –add-drop-database -E -R -A >repl.sql
To replicate specific databases, replace
-A in the above command with
databases db1 db2 … substituting desired database names for
db1 db2 …. This creates an internally consistent SQL script repl.sql which, run on the replica, will correctly create and populate designated master databases and tables there, and will set the replica to start replicating in the correct position in the correct log file with a
CHANGE MASTER TO … script command like this …
CHANGE MASTER TO MASTER_LOG_FILE='yourlogname-bin.xxxxxx', MASTER_LOG_POS=yyyy;
yourlogname is the name you specified in step 1 above for in the master my.cnf/ini file, and
xxxxxx, yyy are the master log name and position respectively obtained by executing
SHOW MASTER STATUS\G on the master; the output of
SHOW MASTER STATUS looks like this ...
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| yourlogname-bin.xxxxxx | yyy | | |
6. Load the data snapshot on the replica: Execute the script from step 5 with …
mysql -uroot -pPWD <repl.sql
If the snapshot was taken with mysqldump with the argument
--master-data=1. the generated script sets the master binary log position where replication is to begin; if the option was set to 2, copy the commented command from the script and issue it on the replica.
CHANGE MASTER on the replica. For example if master_host is
localhost at port 3306, and the replication user, password, log file and position are as in the example we've been annotating, the command will be …
CHANGE MASTER TO
If the MySQL primary is on a another machine on the local area network, it will need to be configured to use a static IP address; substitute that address for
localhost in the above command, eg ...
CHANGE MASTER TO master_host='192.168.1.42', ...
On a correctly configured network, you can use the primary machine's name instead of its IP address. In some cases that also requires an entry in the replica machine's hosts file ....
CHANGE MASTER settings to a master.info file in the replica’s data folder. The file has a line for each setting shown by
SHOW SLAVE STATUS plus one, the master user replication password. It’s updated by subsequent
CHANGE MASTER commands and by the replica I/O thread, but beware, if there are errors in configuration variables when setting up replication, when the master or replica version is earlier than 5.7.4 you need to physically delete machineName*.* and *.info in the replica data folder before restarting the replica server, in order for revised configuration variable settings to take effect.
Multi-source replication will require statements like …
SET GLOBAL master_info_repository = 'TABLE';
SET GLOBAL relay_log_info_repository = 'TABLE';
… then for each channel N, execute, depending on your details, something like…
CHANGE MASTER TO MASTER_HOST='masterN',
MASTER_AUTO_POSITION = 1
FOR CHANNEL 'master-N';
START SLAVE [thread_type] on the replica. To start just channel n, add
FOR CHANNEL n to the command. Inspect the replica’s response, which should look like …
040320 6:39:58 Slave SQL thread initialized, starting replication
in log 'xxxxxx-bin.000033' at position 582, relay log
040320 6:39:58 Slave I/O thread: connected to master
'repl@localhost:3306', replication started in log
'xxxxxx-binlog.000033' at position 582
If an error occurs, it displays in this console. Often it's a connect error, and often the culprit is a firewall or other network issue. If pinging master by IP address from the replica fails, there are network issues. If master can be pinged by IP address but not by name, there are DNS or other naming issues. If the the MySQL client program on the replica can connect to the primary server yet
START SLAVE fails, there are probably replication setup errors listed in the mysql error log.
We've also seen it happen that the mysql client program connects without error from a replica to a master on a LAN using the master's machine name as host argument, yet trying to connect to that master as a replica fails with connection error 2005 ("unknown host"). That may require a hosts file edit as described above.
Once errors are resolved, the replica thread starts replicating SQL commands, logs them to a file named machineName-relay-bin.#, and writes
SHOW SLAVE STATUS variables to a file named relay-log.info in the data directory in that order. After
START SLAVE has executed successfully, the command
SHOW MASTER STATUS; on the master will yield a display like this:
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| xxxxxx-bin.000046 | 3133 | | |
and from a replica client, the command…
SHOW SLAVE STATUS\G
will display something like this ...
Slave_IO_State: Waiting for master to send event
Use these queries to retrieve replication info ...
select * from performance_schema.replication_connection_configuation\G
select * from performance_schema.replication_connection_status\G
select * from performance_schema.replication_applier_configuration\G
select * from performance_schema.replication_applier_status\G
select * from performance_schema.replication_applier_status_by_coordinator\G
select * from performance_schema.replication_applier_status_by_worker\G
select * from performance_schema.replication_group_members\G
select * from performance_schema.replication_group_member_stats\G
Last updated 18 Nov 2021