How to configure MySQL replication

Introduction

When working with databases, it can be useful to have backups of your data. This provides redundancy if one of the database servers fails and improves availability. The practice of synchronizing data across multiple separate databases is called replication.

MySQL is a relational database management system and is currently the world's most popular open-source relational database. It comes with a number of built-in replication features, allowing you to maintain multiple copies of the information.

This article will explain how to configure a MySQL instance on one server as the source database and then configure an instance on another server to act as its replica. 

Requirements

This article will use two Ubuntu 22.04 LTS servers for the explanation, but any version later than Ubuntu 16.04 LTS or any Debian distribution with the systemd daemon manager can be used. 

It can also be used for RHEL-based distributions that include systemd, adapting the MySQL installation to use the corresponding package manager. The following article lists all Linux distributions available on Clouding: Linux images.

Creating a source server scenario

First, we will explain how to create a source server scenario (Master) for this article. If you already have MySQL installed with the database to replicate skip to the section Source server configuration.

First, run the following on the source server to install MySQL:

# apt install mysql-server -y

Once installed, you will need to create the databases. Running the following will create two databases; two are created to explain how to ignore a database during replication:

# mysql -u root -e "CREATE DATABASE base_de_datos_replicacion; CREATE DATABASE base_de_datos_no_replica"

Next, create a table and add sample data: 

# mysql -u root -e "USE base_de_datos_replicacion; CREATE TABLE tabla (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, usuario VARCHAR(20) UNIQUE); INSERT INTO tabla (id, usuario) VALUES ('01', 'clouding')"

To display the contents of the table you created, you can run a SELECT:

# mysql -u root -e "USE base_de_datos_replicacion; SELECT * FROM tabla"

You should get the following query result:

  +----+----------+
  | id | usuario  |
  +----+----------+
  | 1 | clouding  |
  +----+----------+
  1 row in set (0.00 sec)

Source server configuration

First, create a user on the source server for replication. You can replace % with the IP address of the replica server or range of replica servers (remember to change the example password):

# mysql -u root -e "CREATE USER 'usuario_replicacion'@'%' IDENTIFIED WITH mysql_native_password BY 'contraseña'"

Next, grant replication privileges to the user created on the source server:

# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO 'usuario_replicacion'@'%'"

You can find more information about creating users in the following article How to manage users and permissions in MySQL.

Then edit the MySQL configuration file on the source server:

# nano /etc/mysql/mysql.conf.d/mysqld.cnf

You will need to edit and uncomment the following lines in the configuration:

  bind-address = 10.20.10.0
  mysqlx-bind-address = 10.20.10.0
  server-id = 1
  log_bin = /var/log/mysql/mysql-bin.log
  binlog_do_db = base_de_datos_replicacion # database for replication
  binlog_ignore_db = base_de_datos_no_replica # database to ignore

The default DHCP internal network range has been specified, but you will need to change it if you use a different network for replication and also enter the names of the databases to replicate. Also specify the databases you want to ignore during replication and not provide to the replica server (Caution: add one database per option).

Once you have modified the file, restart MySQL to apply the changes:

# systemctl restart mysql

Now you will need to lock the tables to export the databases:

# mysql -u root -e "FLUSH TABLES WITH READ LOCK"

Also check the position by checking the source status (remember to note the position):

# mysql -u root -e "SHOW MASTER STATUS"

You should get a result similar to the following:

  +------------------+----------+----------------------------+---------------------------+-------------------+
  | File             | Position | Binlog_Do_DB               | Binlog_Ignore_DB          | Executed_Gtid_Set |
  +------------------+----------+----------------------------+---------------------------+-------------------+
  | mysql-bin.000001 | 157      | base_de_datos_replicacion  | base_de_datos_no_replica  |                   |
  +------------------+----------+----------------------------+---------------------------+-------------------+
  1 row in set (0.00 sec)

Once the tables are locked and you have noted the position, export the databases on the source server:

# mysqldump -u root base_de_datos_replicacion > dump.sql

And transfer the dump to the replica server. By running the following, you can copy it using the SSH protocol from the source server:

# scp dump.sql root@IP_servidor_replica:/tmp/

Once the dump has been created, you can unlock the tables on the source server:

# mysql -u root -e "UNLOCK TABLES"

Replica server configuration

Now you will need to access the replica server; all commands in this section must be run on it. First, you will need to install MySQL on the replica server so you can configure it afterward:

# apt install mysql-server -y

Also create the database for replication; in this case, the one created in the section Creating a source server scenario:

# mysql -u root -e "CREATE DATABASE base_de_datos_replicacion"

Once the database has been created, you will need to import the copies from the source server. Running the following on the replica server lets you import the copy into the database:

# mysql base_de_datos_replicacion < /tmp/dump.sql

Next, edit the MySQL configuration on the replica server:

# nano /etc/mysql/mysql.conf.d/mysqld.cnf

You will need to uncomment, modify, or add the following lines (Caution: add one database per option):

  server-id = 2
  log_bin = /var/log/mysql/mysql-bin.log
  binlog_do_db = base_de_datos_replicacion
  relay-log = /var/log/mysql/mysql-relay-bin.log

Remember to change the server ID number if this is for a third replica or higher.

Once you have saved the changes to the configuration file, restart MySQL on the replica to apply the changes:

# systemctl restart mysql

Now you will need to configure replication. Run the following command on the replica server and modify it to specify the source server's IP address and the user previously created for replication, check the source file, and change the position noted during Replica server configuration:

# mysql -u root -e "CHANGE REPLICATION SOURCE TO SOURCE_HOST='master',SOURCE_USER='usuario_replicacion',SOURCE_PASSWORD='contraseña',SOURCE_LOG_FILE='mysql-bin.000001',SOURCE_LOG_POS=157"

In MySQL versions earlier than 8.0, replace "CHANGE REPLICATION SOURCE TO" with the command "CHANGE MASTER TO".

Finally, start replication by running the following on the replica server again ("SLAVE" in earlier versions):

# mysql -u root -e "START REPLICA"

You can view or display the replication status by running the following:

# mysql -u root -e "SHOW REPLICA STATUS\G" | grep State

You should get the following result if it has all the information from the source server:

   Replica_IO_State: Waiting for source to send event
 Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates

Checking replication

To check that replication is working correctly, you can simply try to insert data on the source server. Running the following would add data to the sample table created in this article:

# mysql -u root -e "USE base_de_datos_replicacion; INSERT INTO tabla (id, usuario) VALUES ('02', 'usuario')"

With the following command, run on the replica server, you can query the replicated table:

# mysql -u root -e "USE base_de_datos_replicacion; SELECT * FROM tabla"

With the example scenario, you should get the following result on the replica server when running the SELECT:

  +----+----------+
  | id | usuario  |
  +----+----------+
  | 1 | clouding  |
  | 2 | usuario   |
  +----+----------+
  1 row in set (0.00 sec)

We hope this tutorial has been helpful. If you have questions about this or any other issue related to your servers on Clouding, please do not hesitate to contact soporte@clouding.io We're here to help!