Need assistance with digitalocean mysql replication? We can help you.

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Service.

Let’s take a look at how our Support Team help a customer  deal with this digitalocean query.

DigitalOcean MySQL replication

Today, let us see the steps followed by our Support Techs in order to perform the task.

Step1: Adjusting Your Source Server’s Firewall

The source’s firewall will block any connection attempts from your replica MySQL instance.

To change this, you’ll need to include a UFW rule that allows connections from your replica through the source’s firewall.

You can do this by running a command like the following on your source server.

This particular command allows any connections that originate from the replica server’s IP address

• sudo ufw allow from replica_server_ip to any port 3306

Be sure to replace replica_server_ip with your replica server’s actual IP address.

If the rule was added successfully you’ll see the following output:

OutputRule 
added

Following that, you won’t need to make any changes to the replica’s firewall rules.

Then, you can move on to updating the source MySQL instance’s configuration to enable replication.

Step2: Configuring the Source Database

On Ubuntu 20.04, the default MySQL server configuration file is named mysqld.cnf and can found in the /etc/mysql/mysql.conf.d/ directory.

Open this file on the source server with your preferred text editor.

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

Within the file, find the bind-address directive. It will look like this by default:

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

. . .
bind-address = 127.0.0.1 . . .

To allow this, you must configure your source MySQL instance to listen for connections on an IP address which the replica will be able to reach, such as the source server’s public IP address.

Replace 127.0.0.1 with the source server’s IP address.

After doing so, the bind-address directive will look like this, with your own server’s IP address in place of source_server_ip:

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

. . .
bind-address            = source_server_ip
. . .

Next, find the server-id directive, which defines an identifier that MySQL uses internally to distinguish servers in a replication setup.

This directive will be commented out by default and will look like this:

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

. . .
# server-id             = 1
. . .

Uncomment this line by removing the pound sign (#).

To keep things simple the following example leaves this value as the default, 1:

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

. . .
server-id               = 1
. . .

Below the server-id line, find the log_bin directive. This defines the base name and location of MySQL’s binary log file.

When commented out, as this directive is by default, binary logging is disabled.

After doing so, it will look like this: /etc/mysql/mysql.conf.d/mysqld.cnf

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

Lastly, scroll down to the bottom of the file to find the commented-out binlog_do_db directive: /etc/mysql/mysql.conf.d/mysqld.cnf

. . .
# binlog_do_db          = include_database_name

Remove the pound sign to uncomment this line and replace include_database_name with the name of the database you want to replicate.

This example shows the binlog_do_db directive pointing to a database named db.

. . .
binlog_do_db          = db

. . .
binlog_do_db          = db
binlog_do_db          = db_1
binlog_do_db          = db_2

Alternatively, you can specify which databases MySQL should not replicate by adding a binlog_ignore_db directive for each one:

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

. . .
binlog_ignore_db          = db_to_ignore

After making these changes, save and close the file.

Then restart the MySQL service by running the following command:

sudo systemctl restart mysql

With that, this MySQL instance is ready to function as the source database which your other MySQL server will replicate.

Before you can configure your replica, though, there are still a few more steps you need to perform on the source to ensure that your replication topology will function correctly.

Finally, the first of these is to create a dedicated MySQL user which will perform any actions related to the replication process.

Step3: Creating a Replication User

Each replica in a MySQL replication environment connects to the source database with a username and password.

Replicas can connect using any MySQL user profile that exists on the source database and has the appropriate privileges.

Start by opening up the MySQL shell:

sudo mysql

Note: If you configured a dedicated MySQL user that authenticates using a password, you can connect to your MySQL with a command like this instead:

mysql -u username -p

and enter this user’s password when prompted.

Be aware that some operations throughout this guide, including a few that must be performed on the replica server, require advanced privileges.

Because of this, it may be more convenient to connect as an administrative user, as you can with the previous sudo mysql command.

If you want to use a less privileged MySQL user throughout this guide, though, they should at least be granted the CREATE USER, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, and REPLICATION_SLAVE_ADMIN privileges.

From the prompt, create a new MySQL user. The following example will create a user named replica_user, but you can name yours whatever you’d like.

Make sure to change replica_server_ip to your replica server’s public IP address and to change password to a strong password of your choosing:

CREATE USER 'replica_user'@'replica_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

After creating the new user, grant them the appropriate privileges.

At minimum, a MySQL replication user must have the REPLICATION SLAVE permissions:

GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'replica_server_ip';

Following this, it’s good practice to run the FLUSH PRIVILEGES command.

This will free up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements:

FLUSH PRIVILEGES;

With that, you’ve finished setting up a replication user on your source MySQL instance.

However, do not exit the MySQL shell. Keep it open for now, as you’ll use it in the next step to obtain some important information about the source database’s binary log file.

Step4: Retrieving Binary Log Coordinates from the Source

To make sure that no users change any data while you retrieve the coordinates, which could lead to problems, you’ll need to lock the database to prevent any clients from reading or writing data as you obtain the coordinates.

You will unlock everything shortly, but this procedure will cause your database to go through some amount of downtime.

You should still have your source server’s MySQL shell open from the end of the previous step.

From the prompt, run the following command which will close all the open tables in every database on your source instance and lock them:

FLUSH TABLES WITH READ LOCK;

Then run the following operation which will return the current status information for the source’s binary log files:

SHOW MASTER STATUS;

Step5: Configuring the Replica Database

Open up the MySQL configuration file, mysqld.cnf, on your replica server:

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

Each MySQL instance in a replication setup must have a unique server-id value.

Find the replica’s server-id directive, uncomment it, and change its value to any positive integer, as long as it’s different from that of the source:

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

server-id               = 2

Following that, update the log_bin and binlog_do_db values so that they align with the values you set in the source machine’s configuration file: /etc/mysql/mysql.conf.d/mysqld.cnf

. . .
log_bin                 = /var/log/mysql/mysql-bin.log
. . .
binlog_do_db            = db
. . .

Lastly, add a relay-log directive defining the location of the replica’s relay log file.

Include the following line at the end of the configuration file: /etc/mysql/mysql.conf.d/mysqld.cnf

. . .
relay-log               = /var/log/mysql/mysql-relay-bin.log

After making these changes, save and close the file. Then restart MySQL on the replica to implement the new configuration:

sudo systemctl restart mysql

After restarting the mysql service, you’re finally ready to start replicating data from your source database.

Step6: Starting and Testing Replication

At this point, both of your MySQL instances are fully configured to allow replication.

To start replicating data from your source, open up the the MySQL shell on your replica server:

sudo mysql

From the prompt, run the following operation, which configures several MySQL replication settings at the same time.

After running this command, once you enable replication on this instance it will try to connect to the IP address following SOURCE_HOST using the username and password following SOURCE_USER and SOURCE_PASSWORD, respectively.

It will also look for a binary log file with the name following SOURCE_LOG_FILE and begin reading it from the position after SOURCE_LOG_POS.

Be sure to replace source_server_ip with your source server’s IP address.

Likewise, replica_user and password should align with the replication user you created in Step 2; and mysql-bin.000001 and 899 should reflect the binary log coordinates you obtained in Step 3.

You may want to type this command out in a text editor before running it on your replica server so that you can more easily replace all the relevant information:

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='source_server_ip',
SOURCE_USER='replica_user',
SOURCE_PASSWORD='password',
SOURCE_LOG_FILE='mysql-bin.000001',
SOURCE_LOG_POS=899;

Following that, activate the replica server:

START REPLICA;

If you entered all the details correctly, this instance will begin replicating any changes made to the db database on the source.

You can see details about the replica’s current state by running the following operation.

The \G modifier in this command rearranges the text to make it more readable:

SHOW REPLICA STATUS\G;

Your replica is now replicating data from the source.

Any changes you make to the source database will be reflected on the replica MySQL instance.

You can test this by creating a sample table on your source database and checking whether it gets replicated successfully.

Begin by opening up the MySQL shell on your source machine:

sudo mysql

Select the database you chose to replicate:

USE db;

Then create a table within that database.

[Need a solution to another query? We are just a click away.]

Conclusion

Today, we saw steps followed by our Support Engineers for DigitalOcean MySQL replication