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 3306Copy Code

Be sure to replace

replica_server_ipCopy Code

with your replica server’s actual IP address.

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

OutputRule 
addedCopy Code

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.cnfCopy Code

and can found in the

/etc/mysql/mysql.conf.d/Copy Code

directory.

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

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnfCopy Code

Within the file, find the

bind-addressCopy Code

directive. It will look like this by default:

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

. . .
bind-address = 127.0.0.1 . . .Copy Code

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.1Copy Code

with the source server’s IP address.

After doing so, the

bind-addressCopy Code

directive will look like this, with your own server’s IP address in place of

source_server_ipCopy Code

:

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

. . .
bind-address            = source_server_ip
. . .Copy Code

Next, find the

server-idCopy Code

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.cnfCopy Code

. . .
# server-id             = 1
. . .Copy Code

Uncomment this line by removing the pound sign (

#Copy Code

).

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

1Copy Code

:

/etc/mysql/mysql.conf.d/mysqld.cnfCopy Code

. . .
server-id               = 1
. . .Copy Code

Below the

server-idCopy Code

line, find the

log_binCopy Code

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.cnfCopy Code

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

Lastly, scroll down to the bottom of the file to find the commented-out

binlog_do_dbCopy Code

directive:

/etc/mysql/mysql.conf.d/mysqld.cnfCopy Code

. . .
# binlog_do_db          = include_database_nameCopy Code

Remove the pound sign to uncomment this line and replace

include_database_nameCopy Code

with the name of the database you want to replicate.

This example shows the

binlog_do_dbCopy Code

directive pointing to a database named

db.Copy Code

. . .
binlog_do_db          = db
Copy Code

. . .
binlog_do_db          = db
binlog_do_db          = db_1
binlog_do_db          = db_2Copy Code

Alternatively, you can specify which databases MySQL should not replicate by adding a

binlog_ignore_dbCopy Code

directive for each one:

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

. . .
binlog_ignore_db          = db_to_ignoreCopy Code

After making these changes, save and close the file.

Then restart the MySQL service by running the following command:

sudo systemctl restart mysqlCopy Code

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 mysqlCopy Code

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 -pCopy Code

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 mysqlCopy Code

command.

If you want to use a less privileged MySQL user throughout this guide, though, they should at least be granted the

CREATE USERCopy Code

,

RELOADCopy Code

,

REPLICATION CLIENTCopy Code

,

REPLICATION SLAVECopy Code

, and

REPLICATION_SLAVE_ADMINCopy Code

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_ipCopy Code

to your replica server’s public IP address and to change

passwordCopy Code

to a strong password of your choosing:

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

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

At minimum, a MySQL replication user must have the

REPLICATION SLAVECopy Code

permissions:

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

Following this, it’s good practice to run the

FLUSH PRIVILEGESCopy Code

command.

This will free up any memory that the server cached as a result of the preceding

CREATE USERCopy Code

and

GRANTCopy Code

statements:

FLUSH PRIVILEGES;Copy Code

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;Copy Code

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

SHOW MASTER STATUS;Copy Code

Step5: Configuring the Replica Database

Open up the MySQL configuration file,

mysqld.cnfCopy Code

, on your replica server:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnfCopy Code

Each MySQL instance in a replication setup must have a unique

server-idCopy Code

value.

Find the replica’s

server-idCopy Code

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               = 2Copy Code

Following that, update the

log_binCopy Code

and

binlog_do_dbCopy Code

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
. . .Copy Code

Lastly, add a

relay-logCopy Code

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.logCopy Code

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

sudo systemctl restart mysqlCopy Code

After restarting the

mysqlCopy Code

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 mysqlCopy Code

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_HOSTCopy Code

using the username and password following

SOURCE_USERCopy Code

and

SOURCE_PASSWORDCopy Code

, respectively.

It will also look for a binary log file with the name following

SOURCE_LOG_FILECopy Code

and begin reading it from the position after

SOURCE_LOG_POSCopy Code

.

Be sure to replace

source_server_ipCopy Code

with your source server’s IP address.

Likewise,

replica_userCopy Code

and

passwordCopy Code

should align with the replication user you created in Step 2; and

mysql-bin.000001Copy Code

and

899Copy Code

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;Copy Code

Following that, activate the replica server:

START REPLICA;Copy Code

If you entered all the details correctly, this instance will begin replicating any changes made to the

dbCopy Code

database on the source.

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

The

\GCopy Code

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

SHOW REPLICA STATUS\G;Copy Code

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 mysqlCopy Code

Select the database you chose to replicate:

USE db;Copy Code

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