MySQL replication setup — step-by-step guide

MySQL replication is the process of copying data from one server to another server.
In this post, we shall discuss steps to do replication from one server to another server only.

Let’s see how to setup replication from db-01 to db-02

Prerequisites:

1. MySQL Installed: Ensure that MySQL is installed and running on both servers (db-01 and db-02).
2. Network Connectivity: The two servers must have network connectivity to each other. Verify that the necessary firewall rules are in place to allow MySQL traffic (port 3306 by default) between them.
3. Server Versions: The MySQL versions on both servers should be compatible for replication. It’s recommended to use the same or compatible versions. ( it is not necessary to be exact same version but need to make sure they are compatible)

Step-by-Step Setup:

On db-01 (Master Server):

1. Edit MySQL Configuration: Open the MySQL configuration file (usually my.cnf or my.ini) and add the following lines under the [mysqld] section:server-id is a unique identifier for the master server, and log_bin enables binary logging, which is necessary for replication.

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

2. Restart MySQL: Restart the MySQL service to apply the configuration changes.

3. Create Replication User: Log in to the MySQL shell and create a replication user with appropriate privileges. Replace replication_user, password, and db-02-ip with your desired values:

CREATE USER 'replication_user'@'db-02-ip' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'db-02-ip';
FLUSH PRIVILEGES;

4. Show Master Status: Run the following command in the MySQL shell to obtain the values of File and Position for the master server:

SHOW MASTER STATUS;

Sample output is below:

+------------------+----------+------------------+------------------+
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000001 | 123456   |                  |                  |
+------------------+----------+------------------+------------------+

On db-02 (Slave Server):

1. Edit MySQL Configuration: Open the MySQL configuration file and add the following lines under the [mysqld] section:

server-id = 2

In our example, we shall server-id = 2 on db-02 (the slave server) to ensure that it has a distinct identifier from db-01 (the master server), which has server-id = 1. This unique identification helps maintain the integrity of the replication process and allows each server to play its designated role effectively in the replication setup.

2. Restart MySQL: Restart the MySQL service to apply the configuration changes.

3. Replication Configuration: Log in to the MySQL shell and configure replication using the values from the master server:

CHANGE MASTER TO
MASTER_HOST = 'db-01-ip',
MASTER_USER = 'replication_user',
MASTER_PASSWORD = 'password',
MASTER_LOG_FILE = 'mysql-bin.000001', -- Use the value from the master's output
MASTER_LOG_POS = 123456; -- Use the value from the master's output

4. Start Replication:

Start the replication process

START SLAVE;

5. Check Replication Status: Check the replication status to ensure it’s running without errors:

SHOW SLAVE STATUS\G

Look for the Slave_IO_Running and Slave_SQL_Running fields. They should both be "Yes."

MySQL replication is now set up from db-01 to db-02. Ensure that the servers continue to have network connectivity and regularly monitor the replication status for any issues.