Basic replication 8-step

from the Artful MySQL Tips List


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 [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 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 log_bin enabled, 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 [mysqld] set relay-log=machinemame-relay-bin.

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

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 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 andRELOAD 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='toshnb-bin.000033', MASTER_LOG_POS=666 ;



The output of SHOW MASTER STATUS looks like this:


+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| toshnb-bin.000033 |      582 |              |                  |

+-------------------+----------+--------------+------------------+



6. Load the data snapshot on the slave: Execute the script from step 5 with …


mysql -uroot –pPWD <dbsnapshot.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 slave.

7. Execute 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

  master_host='localhost',

  master_port=3306,

  master_user='repl',

  master_password='repl',

  master_log_file= 'toshnb-binlog.000033',

  master_log_pos=582;



MySQL writes 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_USER='rpl',

  MASTER_PORT=3451, 

  MASTER_PASSWORD='', \

  MASTER_AUTO_POSITION = 1 

  FOR CHANNEL 'master-N';



8. Execute 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 'toshnb-bin.000033' at position 582, relay log

'.\toshnb-relay-bin.000001' position:4

040320  6:39:58  Slave I/O thread: connected to master

'repl@localhost:3306',  replication started in log

'toshnb-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 |
+-------------------+----------+--------------+------------------+
| toshnb-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

                Master_Host: localhost

                Master_User: repl

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: toshnb-bin.000046

        Read_Master_Log_Pos: 3133

             Relay_Log_File: toshnb-relay-bin.000015

              Relay_Log_Pos: 3268

      Relay_Master_Log_File: toshnb-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




Return to the Artful MySQL Tips page