ag@linux:~$

history 9 of 36

How to Set Up MySQL Master-Slave and Master-Master Replication

Step-by-step notes for MySQL replication between two servers: my.cnf settings, a replication user, a locked dump, and CHANGE MASTER TO.

Written

From my Linux study notes, 2013. Some commands and versions here are old, so please reuse them carefully. Anything marked "added later", and the notes at the end, explain what has changed since.

These are my notes for replicating MySQL between two servers, ServerA and ServerB. The first part sets up master to slave replication (ServerA to ServerB). The second part adds the reverse direction to make master to master replication.

(Added later.) These steps use the older MySQL syntax (CHANGE MASTER TO, START SLAVE). They still describe how replication works, but newer MySQL versions renamed several commands. See “Notes for today” at the end.

Before you start

(Added later.)

  • You need root access on both servers (run shell commands as root or with sudo) and the MySQL root password.
  • Both servers should run the same MySQL version.
  • ServerB must be able to reach ServerA on the MySQL port (3306 by default), and for master to master, ServerA must reach ServerB. Open that port in the firewall only for the other server’s IP address.
  • Take a backup before you start. Step 5 locks the database, so do this at a quiet time.

Master to slave replication

1. Edit /etc/my.cnf on both servers

Terminal window
vi /etc/my.cnf

Add the following lines. Added note: they must go in the [mysqld] section of the file, otherwise the server ignores them.

For ServerA:

[mysqld]
server-id = 5
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
relay-log = /var/lib/mysql/serverA-relay-bin
relay-log-index = /var/lib/mysql/serverA-relay-bin.index
log-error = /var/log/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/serverA-relay-log.info
log-bin = /var/lib/mysql/ServerA-bin

For ServerB:

[mysqld]
server-id = 6
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
relay-log = /var/lib/mysql/serverB-relay-bin
relay-log-index = /var/lib/mysql/serverB-relay-bin.index
log-error = /var/log/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/serverB-relay-log.info
log-bin = /var/lib/mysql/ServerB-bin

ServerA will start at 1 and increment by 2, always giving odd numbers.

ServerB will start at 2 and increment by 2, always giving even numbers.

This prevents an AUTO_INCREMENT field from getting duplicate values when the servers replicate to each other.

Added notes on these settings:

  • server-id must be a different number on every server in the replication setup.
  • log-bin turns on the binary log, which records every change. The slave reads changes from the master’s binary log.
  • relay-log is where the slave stores the changes it has copied from the master before applying them.
  • replicate-same-server-id = 0 makes a server skip events that carry its own server ID, so changes do not loop back in master to master setups. 0 is already the default.
  • The directory for log-error (/var/log/mysql here) must exist and be writable by the mysql user, or MySQL will not start.

2. Enable or disable binary logging from the command line

SET sql_log_bin = 0;
SET sql_log_bin = 1;

Added note: this is optional. sql_log_bin only affects your current session: 0 stops the statements you run from being written to the binary log (so they are not replicated), and 1 turns logging back on. The binary log itself is turned on by log-bin in step 1.

3. Restart MySQL on both servers

Terminal window
service mysqld restart

4. Create the MySQL replication user account on both servers

grant replication slave on *.* to 'replication'@'<ip>' identified by 'password';
flush privileges;

Replace <ip> with the IP address of the other server (the one that will connect as a slave), keeping the quotes, and use a strong password. The original did not quote <ip>; an IP address in the host part must be in quotes.

5. Lock the database and note the binary log position (on ServerA)

FLUSH TABLES WITH READ LOCK;
show master status;

Note: record the binary log file name (the File column) and position (the Position column). You need them in step 8.

Keep this MySQL session open. The read lock stays only while this session is connected.

6. Open a new terminal on ServerA and dump the databases

Terminal window
mysqldump --all-databases --lock-all-tables > dbdump.db

Added note: add -u root -p if you are not using a ~/.my.cnf file, so mysqldump asks for the password. Then copy dbdump.db to ServerB, for example with scp.

7. Go back to the first ServerA terminal and unlock the tables

UNLOCK TABLES;

8. On ServerB, import the dump from ServerA and start the slave

Added note: the original said to import the dump but did not show the command. Import it first:

Terminal window
mysql -p < dbdump.db

Then log in to MySQL and point ServerB at ServerA:

Terminal window
mysql -p
stop slave;
CHANGE MASTER TO MASTER_HOST='masterservername or ip',
    MASTER_LOG_FILE='binarylogname',
    MASTER_USER='replication_username',
    MASTER_PASSWORD='replication_user_password',
    MASTER_LOG_POS=binarylogposition;
start slave;

Changes from the original:

  • The original used curly quotes (’), which MySQL does not accept. They are now plain ' quotes.
  • MASTER_LOG_POS is a number and must not be in quotes. Use the position you recorded in step 5.
  • MASTER_HOST is ServerA’s host name or IP address, and MASTER_LOG_FILE is the file name from step 5 (for example ServerA-bin.000001).

From the above steps we have configured master to slave replication.

Check that replication is running

(Added later.) On ServerB:

SHOW SLAVE STATUS\G

Both Slave_IO_Running and Slave_SQL_Running should say Yes. If either says No, the Last_IO_Error or Last_SQL_Error fields explain why. You can also create a test table on ServerA and check that it appears on ServerB.

Master to master replication

To configure master to master replication, do the following additional steps.

9. On ServerB, note its binary log position

show master status;

Note: record the binary log file name and position.

10. On ServerA, point it at ServerB and start the slave

stop slave;
CHANGE MASTER TO MASTER_HOST='serverB name or ip',
    MASTER_LOG_FILE='binarylogname',
    MASTER_USER='replication_username',
    MASTER_PASSWORD='replication_user_password',
    MASTER_LOG_POS=binarylogposition;
start slave;

Added note: here the “master” is ServerB, so use ServerB’s host name or IP address and the file name and position from step 9. The original repeated the step 8 text, which could be read as pointing ServerA at itself.

In this way we have configured master to master replication.

Notes for today

  • New command names. Newer MySQL versions use “source” and “replica” instead of “master” and “slave”. START REPLICA, STOP REPLICA and SHOW REPLICA STATUS arrived in 8.0.22, and CHANGE REPLICATION SOURCE TO in 8.0.23:

    Older syntax MySQL 8.0.22/8.0.23 and later
    CHANGE MASTER TO MASTER_HOST=... CHANGE REPLICATION SOURCE TO SOURCE_HOST=...
    START SLAVE / STOP SLAVE START REPLICA / STOP REPLICA
    SHOW SLAVE STATUS SHOW REPLICA STATUS

    The option names change in the same way (MASTER_LOG_FILE becomes SOURCE_LOG_FILE, MASTER_LOG_POS becomes SOURCE_LOG_POS, and so on). SHOW BINARY LOG STATUS was added in MySQL 8.2 to replace SHOW MASTER STATUS, which was removed in 8.4. Check the documentation for your exact version: https://dev.mysql.com/doc/

  • Creating the replication user in MySQL 8.0. GRANT ... IDENTIFIED BY no longer works. Create the user first, then grant:

    CREATE USER 'replication'@'<ip>' IDENTIFIED BY 'password';
    GRANT REPLICATION SLAVE ON *.* TO 'replication'@'<ip>';

    MySQL 8.0’s default authentication plugin (caching_sha2_password) needs either an encrypted connection for replication or the GET_SOURCE_PUBLIC_KEY=1 option (GET_MASTER_PUBLIC_KEY=1 in the older syntax) in the CHANGE REPLICATION SOURCE TO command.

  • Old my.cnf options. In MySQL 8.0, replication metadata is stored in tables by default rather than in files, so file-related options such as master-info-file and relay-log-info-file may be deprecated or ignored in your version. Check the documentation for your version before copying them.

  • Master to master is risky. If both servers accept writes to the same data, conflicts can break replication. The auto-increment settings only stop duplicate IDs; they do not stop two servers updating the same row. Many setups write to only one server at a time.

  • Newer approaches. MySQL also supports GTID-based replication, which tracks transactions by ID so you do not need to record log file names and positions by hand.

  • Commands on newer systems. service mysqld restart became systemctl restart mysqld (the service is mariadb on systems that ship MariaDB).

  • Security. Replication traffic, including the password, is not encrypted unless you configure TLS. Keep the MySQL port closed to everything except the other server.