|Before setting up your replication master and slave(s), read the MySQL manual's replication section. 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 slave.
2. Get the slave server running: Check master-slave version compatibility. Ensure that the slave server is running correctly. Replication imposes specific configuration requirements: the master needs
server_ids of the master and of all slaves must be unique, and all slaves must be version-compatible with the master. Since 5.5.17, a slave may connect using a plugin. To ease recovery from lost connection to master, my.cnf/ini under
A master with no
server_id setting refuses all slave connections, and a slave 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 slave my.cnf/ini under
[mysqld] the line
3. Set replication privileges on the slave: Ensure that the user who will execute replication commands on the slave has
SUPER privilege there.
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:
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 slave calls with a connection request. Without the slave 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 slave, will correctly create and populate designated master databases and tables there, and will set the slave 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='xxxxxx-bin.000033', MASTER_LOG_POS=666 ;
The output of
SHOW MASTER STATUS looks like this:
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| xxxxxx-bin.000033 | 582 | | |
6. Load the data snapshot on the slave: Execute the script from step 5 with …
mysql -uroot -ppPWD <dbsnapshot.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 slave.
CHANGE MASTER on the slave. 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
CHANGE MASTER settings to a master.info file in the slave’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 slave I/O thread, but beware, if there are errors in configuration variables when setting up replication, when the master or slave version is earlier than 5.7.4 you need to physically delete machineName*.* and *.info in the slave data folder before restarting the slave 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 slave. To start just channel n, add
FOR CHANNEL n to the command. Inspect the slave’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 slave 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 slave can connect to the master 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 slave to a master on a LAN using the master's machine name as host argument, yet trying to connect to that master as a slave fails with connection error 2005 ("unknown host"). Oddly, a solution that worked for us was to add the master's IP address and machine name to the slave machine's hosts file.
Once errors are resolved, the slave 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 slave 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