How to setup MySQL Replication using SSL on Ubuntu

Let’s setup MySQL replication using SSL on Ubuntu. We’ll be using Ubuntu 22.04 LTS and whatever version of MySQL it wants to install by default.

To be fair, the differences in MySQL versions won’t really change how we approach this.

Throughout this article, I will be referring to the MySQL servers as MySQL1 and MySQL2. MySQL1 will be the master database, and MySQL2 will be the replication database.

I’ve also written this on Medium!

Terms

How to setup MySQL Replication using SSL on Ubuntu

To setup MySQL replication on Ubuntu using SSL, we need to run through a few steps. However, I will assume that you already have two Ubuntu servers, and that they are on the same network.

Let’s get started!

Both Systems

Install MySQL

First, run this command on both systems to install and configure MySQL. I’ll leave explanations throughout this guide. If you have any specific questions, just ask in the comments.

sudo apt install mysql-server

Once MySQL has been installed, we need to:

  • Stop the MySQL service
  • Initialise MySQL with the defaults
  • Start the MySQL service

To do that,run these commands:

sudo service mysql stop
sudo mysqld --defaults-file=/etc/mysql/my.cnf --user=mysql --console
sudo service mysql start

MySQL Root User + Config

Now that MySQL has been installed on both systems, we need to set a more secure root password.

This could be different for you, so first check the error logs and look for a temporary password:

sudo grep 'temporary password' /var/log/mysql/error.log

We now need to login to MySQL using the temporary password from the above command, or using a blank password.

sudo mysql -u root -p

Once logged in, change the root password to something more secure:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

Exit MySQL and run the secure installation wizard, making sure to select the options that best fit your deployment:

sudo mysql_secure_installation;

MySQL1

Now let’s focus on MySQL1. Before we touch MySQL, we need to make sure that the connection from the future replication host will be able to talk to this system. We can do that by allowing the MySQL port of 3306 through the firewall:

sudo ufw allow 3306

MySQL Config

Now let’s focus back on MySQL. Edit the MySQL config file using this command:

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

Comment out both bind-address values in the center of the file, then add the following to the bottom of the file:

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = mixed
sync_binlog = 1

ssl
ssl-ca = /etc/mysql/certs/ca-cert.pem
ssl-cert = /etc/mysql/certs/server-cert.pem
ssl-key = /etc/mysql/certs/server-key.pem
How to setup MySQL Replication using SSL on Ubuntu - MySQL1 config

Now you can save and exit this file.

Certificates

In order to have an SSL connection from the replication server to the master server, we need to generate some SSL certificates.

Create a new directory for our certs and set full access for now:

sudo mkdir /etc/mysql/certs
sudo chmod 777 /etc/mysql/certs/
cd /etc/mysql/certs

The next step is VERY important. We’re going to generate 3 certificate pairs. This is for the CA (Certificate Authority), Server and Client. For this, we will be using OpenSSL. It’s very important that each pair has a unique common name:

sudo openssl genrsa 4096 > ca-key.pem
    - common name: mysql-ca
sudo openssl req -new -x509 -nodes -days 9999 -key ca-key.pem > ca-cert.pem


sudo openssl req -newkey rsa:4096 -days 9999 -nodes -keyout server-key.pem > server-req.pem
    - common name: mysql-master
sudo openssl x509 -req -in server-req.pem -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

sudo openssl req -newkey rsa:4096 -days 9999 -nodes -keyout client-key.pem > client-req.pem
    - common name: mysql-client
sudo openssl x509 -req -in client-req.pem -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

Now that the certificates have been created, we can fixed the permissions on the certificate directory and restart MySQL:

sudo chown -R mysql:mysql /etc/mysql/certs
sudo chmod -R 555 /etc/mysql/certs
sudo systemctl restart mysql

Replication User

We now need to create a user inside MySQL for the MySQL2 replication system to connect with.

You can do this by running the following commands. Just make sure to update the PASSWORD_HERE value to something secure.

sudo mysql -u root -p
CREATE USER 'replication'@'%' IDENTIFIED BY 'PASSWORD_HERE' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

exit

MySQL Dump

We now need to generate a MySQL database dump so that it can be copied over to the MySQL2 system. This will be imported later on.

You can use a ton of different parameters when running a mysqldump, but this is what I ran to output a .sql file into /tmp/

sudo mysqldump -u root -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --source-data=2 -A > /tmp/snapshot.sql

After this, we can use scp to copy this file over to our MySQL2 host

sudo scp /tmp/snapshot.sql MySQL2USER@MySQL2IP:/tmp

MySQL2

Now that we’re moving over to the replication system, the process is fairly similar to the master server. With some slight changes.#

MySQL Config

Let’s edit the MySQL config file:

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

And add the following config to the bottom of the file. For the replication server, we don’t need to comment out the bind address values as nothing is connecting to this system:

[mysqld]
server-id = 2
log-bin = mysql-bin
relay-log = mysql-relay-bin
log-slave-updates = 1
read-only = 1
binlog-format = mixed
MySQL2 MySQL config

Certificates

Now restart MySQL, check it’s still running and create the new certs folder with full permissions. We’ll fix the permissions like we did with MySQL1 shortly:

sudo systemctl restart mysql
sudo systemctl status mysql
sudo mkdir /etc/mysql/certs
sudo chmod 777 /etc/mysql/certs

Hop back to the MySQL1 and copy the ca-cert.pem, client-cert.pem, and client-key.pem to MySQL2. To copy the certificates, I used scp but you could also use copy and paste. If you do use scp, use the following commands, making sure to replace the username and ip address for MySQL2:

sudo scp /etc/mysql/certs/ca-cert.pem MySQL2USER@MySQL2IP:/etc/mysql/certs
sudo scp /etc/mysql/certs/client-cert.pem MySQL2USER@MySQL2IP:/etc/mysql/certs
sudo scp /etc/mysql/certs/client-key.pem MySQL2USER@MySQL2IP:/etc/mysql/certs

Go back to MySQL2 and fix the certificate permissions, followed by a restart of MySQL:

sudo chown -R mysql:mysql /etc/mysql/certs
sudo chmod 555 /etc/mysql/certs
sudo systemctl restart mysql

MySQL Import

Since we’ve already copied the database dump from MySQL1 in this step, all we need to do now is import the dump, find the master log position information and build our slave command.

Import the MySQL dump file using:

sudo mysql -u root -p -f < /tmp/snapshot.sql

Once that’s imported, get the log file and position. We will need these values soon!

sudo head /tmp/snapshot.sql -n80 | grep "MASTER_LOG_POS"

Once you have both those values from the MySQL dump file, log in to MySQL:

sudo mysql -u root -p

And run a command similar to this. You will need to update the MASTER_HOST, MASTER_PASSWORD, MASTER_LOG_FILE and MASTER_LOG_POS values before running inside MySQL. I would recommend building this command inside NotePad and copying it over once complete:

CHANGE MASTER TO MASTER_HOST='MySQL1IP',
MASTER_USER='replication',
MASTER_PASSWORD='REPLICATION_PASSWORD',
MASTER_LOG_FILE='LOG_FILE (e.g mysql-bin.000002)',
MASTER_LOG_POS=LOG_POS (e.g 157),
MASTER_SSL=1,
MASTER_SSL_CA = '/etc/mysql/certs/ca-cert.pem',
MASTER_SSL_CERT = '/etc/mysql/certs/client-cert.pem',
MASTER_SSL_KEY = '/etc/mysql/certs/client-key.pem';

Once that has been entered succesfully, you can start the slave service and then check the slave status:

start slave;

SHOW SLAVE STATUS \G;

If you see Slave_IO_State: Waiting for master to send event at the top of the output, then you’ve done everything right and your MySQL1 server is successfully replicating changes over to MySQL2.

Testing The Connection

Now you’ve reached the end of this guide, you can try creating a database on MySQL1 and checking it successfully gets created on MySQL2.

On MySQL1, run this whilst logged into MySQL:

CREATE DATABASE replicationtest;

On MySQL2, run this whilst logged into MySQL:

SHOW DATABASES;

In the list that’s outputted on MySQL2, you should see the replicationtest database that was created on MySQL1. Now that’s you’ve tested this, you can drop the database from MySQL1:

DROP DATABASE replicationtest;
image 5

Thanks for reading everyone! This was a big guide to put together. Enjoy! 🎉

email popup image
Mark Harwood
NEVER miss a blog post again! Subscribe for email notifications whenever a new post is live!
Subscribe
NEVER miss a blog post again! Subscribe for email notifications whenever a new post is live!
Fill Out This Form, And I Will Be In Touch Shortly
Contact form image
I'll Be In Touch Soon