Basic replication 8-step

from the Artful MySQL Tips List


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 REPLICA as a synonym for SLAVE.

This 8-step sets up traditional MySQL one-way asynchronous replication.

1. Configure the master: Master needs these my.cnf/ini settings under [mysqld]:

server-id=1
log-bin=yourlogname
innodb_flush_log_at_trx_commit=1
# For safety, sync every transaction:
sync_binlog=1
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 log_bin enabled, 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 [mysqld], set relay-log=machinemame-relay-bin.

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 replicate-do-db=dbname.

3. Set replication privileges on the replica: Ensure that the user who will execute replication commands on the replica 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:

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 PASSWORD() function.

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 SUPER and 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;
... where 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 or --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.

7. Execute 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
  master_host='localhost',
  master_port=3306,
  master_user='repl',
  master_password='repl',
  master_log_file= 'xxxxxx-binlog.000033',
  master_log_pos=582;
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 ....
192.168.1.42    mylanserver
MySQL writes 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_USER='rpl',
  MASTER_PORT=3451, 
  MASTER_PASSWORD='', \
  MASTER_AUTO_POSITION = 1 
  FOR CHANNEL 'master-N';
8. Execute 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
'.\xxxxxx-relay-bin.000001' position:4
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
                Master_Host: localhost
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: xxxxxx-bin.000046
        Read_Master_Log_Pos: 3133
             Relay_Log_File: xxxxxx-relay-bin.000015
              Relay_Log_Pos: 3268
      Relay_Master_Log_File: xxxxxx-bin.000046
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB: mysql
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 3133
            Relay_Log_Space: 3173
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:Some
  Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 164
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 29 Dec 2020


Return to the Artful MySQL Tips page