Setting up Replication in MySQL on Ubuntu

MySQL replication is an interesting feature that allows users to manage multiple copies of one or more MySQL databases. The data is copied automatically from source to replica databases. It can be useful in numerous situations, such as working with the data without compromising the main database, data backup, or scaling the database access, etc.

In this guide, we will go over the steps of setting up a MySQL instance on one server as a source database, then configuring a MySQL instance on another server to function as its replica.

Prerequisites

This guide will demonstrate a very simple example of MySQL replication. It involves one source and one replica database. The source database is the primary copy of the database whereas the replica database will be the replica of the source database. For our demonstration, two servers are configured with the following IP addresses:

• Source server: 31.171.240.179
• Replica server: 31.171.250.139

Each server is configured with the latest Ubuntu 20.04 server configuration. First, follow the steps of the tutorial that show how to set up your Ubuntu server. Note that the number of replica databases can be more. This guide assumes that you already have MySQL installed and configured. Need help with the MySQL installation? This guide demonstrates in-depth the steps of MySQL installation and basic usage.

In short, here are the packages that you need:

Firewalls on both systems should be configured to allow traffic from both systems on port 3306. It’s the default port for MySQL. You can learn more about UFW basics with a demonstration from our blog post.

Source Database Configuration

• Tweaking MySQL configuration

MySQL uses my.cnf as the primary configuration file. We will update my.cnf to designate the server as the source. First, open up the configuration file with a text editor:

Then, add the following lines under the mysqld section:

What do these lines mean?

• bind-address: This is the entry that defines the association between a service and an IP address. By default, the value may be 127.0.0.1 (localhost). The new value will be the IP address of the server.
• server-id: In MySQL replication, each server must have a unique server ID. It can be any number. For the sake of simplicity, it’s set to 1.
• log_bin: It stores the real details of the replication. The replica database is going to copy everything registered in the log.
• binlog_do_db: This entry designates the database that will be the subject of replication on the replica server. There can be more than one database. Here, the sample database is newdatabase.

After making the changes, save the configuration file. MySQL requires to restart to load changes to the my.cnf:

• Granting permission to the replica user

The next step is to create a replica user and grant appropriate privileges. It needs to be done from the MySQL shell. First, launch the MySQL shell:

Next, create a dedicated user for the replica database. Change the username and password appropriately:

Now, grant the appropriate privileges to the user:

You can learn more about the MySQL user and permissions from our blog post. Next, reload the grant table to take the changes into effect:

• Tweaking the database

We need a copy of the source database on the replica. It’s possible to manually build the structure. However, in most cases, that’s quite inconvenient. That’s why exporting the database directly is the most optimal solution. In this example, the source database is newdatabase. Change the current database:

The following command will lock the database, preventing any new changes:

Then, check the database status:

From this position, the replica database will start replicating from the source. These numbers will be useful later, so keep a record of them. If any changes are made from the same window, MySQL will automatically unlock the database. So, it is recommended to perform the following steps on a different terminal tab or window. The database is still locked. Export it to a portable SQL file:

The task is now done. Next, unlock the database:

Finally, exit the shell:

Replica Configuration

It’s now time to configure the replica database.

• Importing the source database

We need a copy of the source database on the replica server. We will be using the SQL file we exported earlier to do so. Launch the MySQL shell:

After that, create a blank database using the same database name:

Next, exit the shell:

Now, import the SQL file to the database:

• Tweaking the MySQL configuration

There are a few things required to declare in the MySQL configuration file. Open the configuration file in a text editor:

The following entries will go under the mysqld section. Otherwise, it won’t work. The first is the server ID. As mentioned earlier, it has to be unique for all the servers in the source-replica replication configuration. For demonstration, it’s set to 2:

Next, add the following lines:

Here, only relay-log is a new entry. It’s the log that the replica server creates during replication. The log format is the same as the binary log. Save the configuration file, and restart MySQL:

• Enabling the replication

Finally, we are ready to enable the replication from within MySQL. Launch the MySQL shell:

Run the following command. First, change the IP address, username, and passwords accordingly:

The command accomplishes the following:

• The current server is marked as the replica of the source server.
• The replica server has proper login credentials.
• The replica server knows from where to start replicating from. Remember the database status we checked on the source server? The source log file and log position come from there.

Finally, activate the replica server:

• Miscellaneous

Need to check the details of the replica’s current state? Run the following command in MySQL shell. The \G at the end is to rearrange the texts to make them more readable:

If there’s a connection issue, try starting the replica server to skip over it:

Conclusion

MySQL replication has lots of implications. It’s only a brief demonstration of its basic form. However, it can easily extend to multiple source-replica configurations. The same steps will also apply to any higher-level complex configurations. It’s always a good idea to test any configuration afterward. Try performing some insert,  delete or update commands on the source database. If the setup is working, then the replica database should pick up everything properly.

Furthermore, you can take a look at more resource from our blog covering what you can do with MySQL:

• SQLite vs MySQL vs. PostgreSQL: Relational Database Management Systems Compared
• How to Reset MariaDB or MySQL Root Password
• How to Install the LEMP stack (Linux, Nginx, MySQL PHP) on Ubuntu 20.04

Happy Computing!