SOURCE as a synonym for MASTER or REPLICA as a synonym for SLAVE.
Also before you start, beware that MySQL doesn't support replication where source and slave versions or character_set and 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 [mysqld]:
server-id=1 log-bin=yourlogname innodb_flush_log_at_trx_commit=1 # For safety, sync every transaction: sync_binlog=1If 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 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 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 There's a gotcha here. As delivered, the Another gotcha to remember: though Provided the master account is for replication only, and if 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 …
7. Execute Multi-source replication will require statements like …
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 Last updated 18 Nov 2021replica_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 replica_preserve_commit_order=1).
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.
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.
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.
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.
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 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.
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.
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.
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
