DigitalOcean MySQL replication

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
 
 
PREVENT YOUR SERVER FROM CRASHING!
 
Never again lose customers to poor server speed! Let us help you.
 
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
 
GET STARTED
 
 
 
  
Related posts:
 
Install MySQL on Linode
 
Backup MySQL to DigitalOcean Spaces
 
Change datadir mysql DigitalOcean
 
mysqldump error 1045 – Advise from our Database Experts