Replication is the process of replicating data from one server to another simultaneously. Generally, it is used to boost data availability and recover data in the event of a failure. After deploying MySQL replication, you no longer need to perform regular database backups. All databases on the Master node will be automatically replicated to the Slave node. Replication can help minimise the strain on the master server by having the slave node handle application requests. In the event of a failure, you can activate the slave node.

Prerequisites

  • An EC2 instance or any running server

    • Adjust the Firewall Rules

    Before starting, you need to adjust the firewall rules on the Master server. You can do this by running the following commands:

    1
    sudo ufw allow from `replica_server_ip` to any port 3306

    Install MySQL on Master and Slave

    First, you need to install MySQL on both the Master and Slave servers. Before it you need to update the package list and install the MySQL.

    1
    sudo apt update

    Now we can install MySQL on both servers.

    1
    sudo apt-get install mysql-server -y

    Once the installation has been completed, you will need to set a MySQL root password on both nodes. You can set it with the following command:

    1
    sudo mysql_secure_installation

    You will be asked to set a root password as shown below:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    Press y|Y for Yes, any other key for No: 
    Press-Enter 
    Please set the password for root here. 
    New password: 
    Re-enter new password: 

    Remove anonymous users? (Press y|Y for Yes, any other key for No): Y 

    Disallow root login remotely? (Press y|Y for Yes, any other key for No): Y 

    Remove test database and access to it? (Press y|Y for Yes, any other key for No): Y 

    Reload privilege tables now? (Press y|Y for Yes, any other key for No): Y

    Configure MySQL Master Server

    After installing and setup password for MySQL on both the Master and Slave servers, you need to configure the Master server. By default, MySQL listens on the localhost and doesn’t allow connection from the remote system. So you will need to configure MySQL to listen on the public IP. You can do this by editing the MySQL configuration file:

    1
    sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

    Then you need the find the following line:

    1
    bind-address =  127.0.0.1

    And replace it with the following line:

    1
    bind-address = YOUR_MASTER_IP

    Next we need to uncomment the following line:

    1
    server-id = 1

    The server-id setting allows MySQL to distinguish between multiple servers in a replication setup, as each server has its own server-id value. Uncomment the line (remove the #) and, since this is the master server, keep the value 1.

    Now we need to uncomment the following line:

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

    The log_bin setting enables binary logging, which is required for replication. Uncomment the line (remove the #) and set the value to the path where the binary log file will be stored. The default path is /var/log/mysql/mysql-bin.log.
    Or you can set it to any other path you want to store the binary log file like below:

    1
    2
    3
    4
    log_bin = /var/log/mysql/mysql-bin.log
    log_bin_index = /var/log/mysql/mysql-bin.log.index
    relay_log = /var/log/mysql/mysql-relay-bin
    relay_log_index = /var/log/mysql/mysql-relay-bin.index

    Now we need to save and close the file. Then restart the MySQL service to apply the changes:

    1
    sudo systemctl restart mysql

    Create a Replication User on Master Server

    In this step, create a MySQL user, which the slave server will use to connect to the source database. This dedicated user will perform any actions related to the replication process. If you want to use an existing user with appropriate privileges to connect to the MySQL server, skip the creation and only grant the privileges. First, connect to the MySQL shell with the following command:

    1
    sudo mysql -u root -p

    Then create a new user with the following command:

    1
    mysql> CREATE USER 'slave'@'YOUR_SLAVE_IP' IDENTIFIED WITH mysql_native_password BY 'password';

    Next, grant the replication user the necessary privileges with the following command:

    1
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'YOUR_SLAVE_IP';

    Next, flush the privileges to apply the changes:

    1
    mysql> FLUSH PRIVILEGES;

    Lock the Master’s Database and Export Data

    Before replicating data, you need to create a snapshot of the current state of the master. Lock the database to prevent further writes:

    1
    mysql> FLUSH TABLES WITH READ LOCK;

    Next, verify the Master status with the following command:

    1
    mysql> SHOW MASTER STATUS;

    You should get the output like below:

    1
    2
    3
    4
    5
    6
    7
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000001 | 154      |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

    From the above output, note down the mysql-bin.000001 value and the Position 154. You will need both to set up a slave server.

    Next, export the database with the following command:

    1
    mysqldump -u root -p --all-databases --master-data > masterdata.sql

    Next, unlock the database with the following command:

    1
    mysql> UNLOCK TABLES;

    Now you have successfully exported the database from the Master server.

    Prepare the Slave Node

    As we have configured the Master server, now we need to configure the Slave server. First, you need to edit the MySQL configuration file on the Slave server:

    1
    sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

    Then you need to find the following line:

    1
    bind-address = 127.0.0.1

    And replace it with the following line:

    1
    bind-address = YOUR_SLAVE_IP

    Next, you need to uncomment the following line:

    1
    server-id = 2

    Change the value to a number different from the one you have set on the master server (for example hereI have used 2):

    Next you need to uncomment the following line:

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

    The log_bin setting enables binary logging, which is required for replication. Uncomment the line (remove the #) and set the value to the path where the binary log file will be stored. The default path is /var/log/mysql/mysql-bin.log. Or you can set it to any other path you want to store the binary log file like below:

    1
    2
    3
    4
    log_bin = /var/log/mysql/mysql-bin.log
    log_bin_index = /var/log/mysql/mysql-bin.log.index
    relay_log = /var/log/mysql/mysql-relay-bin
    relay_log_index = /var/log/mysql/mysql-relay-bin.index

    Now you need to save and close the file. Then restart the MySQL service to apply the changes:

    1
    sudo systemctl restart mysql

    Import the Database on the Slave Node

    Copy the dump file (masterdata.sql) to the slave server and import it into MySQL:

    1
    mysql -u root -p < masterdata.sql

    Configure MySQL Slave Server

    After importing the database, you need to configure the Slave server. First, log in to the MySQL shell with the following command:

    1
    sudo mysql -u root -p

    Then stop the slave with the following command:

    1
    mysql> STOP SLAVE;

    Run the following command to allow the slave server to replicate the Master server:

    1
    2
    3
    4
    5
    6
    CHANGE MASTER TO
        MASTER_HOST='YOUR_MASTER_IP',
        MASTER_USER='slave',
        MASTER_PASSWORD='password',
        MASTER_LOG_FILE='mysql-bin.000001',  <-- Use the file from SHOW MASTER STATUS
        MASTER_LOG_POS=154;  <-- Use the position from SHOW MASTER STATUS

    Next, start the SLAVE with the following command:

    1
    mysql> START SLAVE;

    Next, verify the slave status with the following command:

    1
    mysql> SHOW SLAVE STATUS\G;

    Test the Replication

    At this point, Master-Slave replication is configured. Now it’s time to test whether the replication works or not. To do so, we will create a database on the Master Node and verify whether it will be replicated on the Slave Node. First, log in to the MySQL on the Master Node:

    1
    sudo mysql -u root -p

    Then create a new database with the following command:

    1
    mysql> CREATE DATABASE testdb;

    Next, create a new table in the testdb database with the following command:

    1
    2
    3
    mysql> USE testdb; 

    mysql> CREATE TABLE testtable (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));

    Next, insert some data into the testtable with the following command:

    1
    mysql> INSERT INTO testtable (name) VALUES ('Dinush');

    Then exit from the MySQL shell with the following command:

    1
    mysql> exit;

    Next, log in to the MySQL on the Slave Node:

    1
    sudo mysql -u root -p

    Then list the databases with the following command:

    1
    mysql> SHOW DATABASES;

    You should see the testdb database on the Slave Node. Next, list the tables in the testdb database with the following command:

    1
    2
    3
    mysql> USE testdb; 

    mysql> SHOW TABLES;

    You should see the testtable table in the testdb database. Next, select the data from the testtable table with the following command:

    1
    mysql> SELECT * FROM testtable;

    You should see the data you inserted on the Master Node. This means that the replication is working correctly.

    Conclusion

    In this tutorial, you have learned how to set up MySQL Master-Slave replication. This will help you to replicate data from one server to another simultaneously. This will help you to boost data availability and recover data in the event of a failure. After deploying MySQL replication, you no longer need to perform regular database backups. All databases on the Master node will be automatically replicated to the Slave node. Replication can help minimise the strain on the master server by having the slave node handle application requests. In the event of a failure, you can activate the slave node. In my next article I will show you how to automate the process of promoting a MySQL slave to master when the master fails.

    Happy Coding