MySQL 8.4 Replication with SSL on Ubuntu 22.02

So I recently wrote a post and made a YouTube video on setting up MySQL replication with SSL on Ubuntu, but that was using MySQL 8.0. You can find that here!

But I recently had a comment on that YouTube video to show MySQL 8.4 replication with SSL on Ubuntu 22.02. I know, it’s quite specific but I do aim to please. So thank you Mohammed for suggesting this!

Similar to last time, I will be referring to the 2 MySQL servers as MySQL1 and MySQL2. With these being the master and replication slave respectively.

You can also check this out on Medium!

I think it’s important that we cover off the main terms that will be used throughout this article.

Setting up MySQL replication on version 8.4 of MySQL is slightly different as some of the config and commands have been changed. Not necessarily made better, just different.

You’ll also need 2 or more systems to setup this up, 1 to be the master MySQL system and then more systems to be replication slaves. All the replication slave systems will listen to changes from the MySQL master system.

I will assume that you already known your way around Ubuntu and have 2 systems setup! 😁

Let’s jump in!

Perform the following actions on all of your systems. I’ve titled this section as “both systems” as I’m only using 2.

First we need to download the specific version of MySQL 8.4 since its not brought down automatically with the previously used sudo apt install mysql-server command. To do this, we use wget:

sudo wget https://dev.mysql.com/get/mysql-server_8.4.4-1ubuntu22.04_amd64.deb-bundle.tar

Next, we unzip that tar bundle:

sudo tar -xf mysql-server_8.4.4-1ubuntu22.04_amd64.deb-bundle.tar

Then, we install some utility programs that make installing the files easier:

sudo apt install mecab-ipadic-utf8 libaio-dev

Now, we can install the .deb files we just unzipped using the dpkg utility:

sudo dpkg -i *.deb

You should be asked to set a root password during the above operation. Set this to something secure in your production environment.

Once that installation is complete, you should have MySQL 8.4.4 installed on your system. You can check the version of MySQL a couple of ways, I’ll leave these below:

sudo mysql --version -- Check using the Linux CLI

sudo mysql -u root -p -- Check using MySQL
SELECT version(); -- Use the root password you created before

Now that you’ve installed and confirmed MySQL 8.4.4, we can now clean up the install files using:

sudo rm *.deb
sudo rm mysql-server_8.4.4-1ubuntu22.04_amd64.deb-bundle.tar

After this, you can run the MySQL secure installation script using:

sudo mysql_secure_installation

Now that MySQL has been installed and verified on both systems, we can move over to just MySQL1 for now.

The first thing to do is to allow remote connections through the firewall to our MySQL database. MySQL typically uses port 3306, so we can run this command:

sudo ufw allow 3306

Moving onto the MySQL config, open this file using:

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

And make the following changes after the [mysqld] section:

[mysqld]
server-id = 1 #This must be unique to this MySQL server. I've set it to 1 on the master as it's the first MySQL system in this setup. Subsequent MySQL replica servers will increment this number.
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

We now need to create a location for the SSL certificates and create them using OpenSSL. We’ll mimic the directories set in the MySQL config file above. First, create the directory and set full permissions whilst we run through the certificate creation steps:

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

Now run these commands to create the certificates. Make sure to set different common names for each of the 3 we create:

#Creates the CA key and cert
sudo openssl genrsa 4096 > ca-key.pem
sudo openssl req -new -x509 -nodes -days 9999 -key ca-key.pem > ca-cert.pem
    - common name: mysql-ca
 
#Creates the Server key and cert
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
 
#Creates the Client key and cert
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

Let’s now replace the permissions on this certificate directory to something more appropriate and restart the MySQL server:

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

In order for the replica slave servers to connect to the master server, we need to create a user for their authentication. We can do this by creating a new user in our MASTER MySQL database and giving replication privileges to all databases and tables:

-- Login to MySQL
sudo mysql -u root -p 

-- Create the Replication user
CREATE USER 'replication'@'%' IDENTIFIED BY 'PASSWORD_HERE' REQUIRE SSL; 
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

We need to generate a MySQL database dump which will later be imported into the replica slave servers. There are a ton of different parameters you can use on the mysqldump utility, so please research ahead of time to find something that best fits your needs.

First login to the MySQL database an flush the tables with read lock:

sudo mysql -u root -p 

FLUSH TABLES WITH READ LOCK;

-- Grab the current binary logs status
SHOW BINARY LOG STATUS\G

exit

I like to grab the binary logs status twice just to confirm that nothing has changed. I grab it once in the command section above, and on the replica slave system from the back that we’re about to take:

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

Now quickly hop back into MySQL and unlock the tables:

sudo mysql -u root -p

UNLOCK TABLES;

exit;

After this, we need to move some of the certificates we generated before plus the MySQL dump file to the replica slave server. We can do this using scp:

#Copy the MySQL Dump file
sudo scp /tmp/snapshot.sql MySQL2USER@MySQL2IP:/tmp

#Copy the CA cert, Client cert and Client key
sudo scp /etc/mysql/certs/ca-cert.pem MySQL2USER@MySQL2IP:/tmp
sudo scp /etc/mysql/certs/client-cert.pem MySQL2USER@MySQL2IP:/tmp
sudo scp /etc/mysql/certs/client-key.pem MySQL2USER@MySQL2IP:/tmp

After this, we can move onto the MySQL2 system which just to confirm, will be our second MySQL system, and the first MySQL replica slave, in this setup.

Now let’s move onto the MySQL2 system which will be the first replica slave system:

First, we need to edit the MySQL config file. We can do this using:

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

#Add for edit the following
[mysqld]
server-id = 2 #Remember, this has to be unique. I've set this to 2 as it's the second MySQL system in this setup
log-bin = mysql-bin
relay-log = mysql-relay-bin
log-slave-updates = 1
read-only = 1
binlog-format = mixed

After that, we can make the directory for the certificates and set full permissions for now:

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

Once this directory has been created, move the certificates that were copied from the MySQL1 system into the /tmp directory:

sudo mv /tmp/ca-cert.pem /etc/mysql/certs
sudo mv /tmp/client-cert.pem /etc/mysql/certs
sudo mv /tmp/client-key.pem /etc/mysql/certs

Now we can update the ownership and permissions on the certificates directory, restart MySQL and check the status to confirm everything is okay thus far:

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

sudo systemctl restart mysql
sudo systemctl status mysql

Importing the MySQL dump file is surprisingly easy. All you need to run is:

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

Once that’s done, we are almost ready to setup the automated replication function. But first, we need to grab the SOURCE_LOG_POS and the SOURCE_LOG_FILE. These will be needed later on when we build the MySQL replication command.

To get these values, you can run this command:

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

It might be worth double checking the values match from this point in the guide. It’s not the end of the world if they don’t match though, just continue!

To finalise the replication setup, we need to build a command that is entered into MySQL. So first, login to MySQL and build a command that looks like this:

sudo mysql -u root -p

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.0.1', -- Enter the IP address of your MASTER MySQL server
SOURCE_USER='replication', -- Enter the replication username
SOURCE_PASSWORD='PASSWORD_HERE', -- Enter the replication password
SOURCE_LOG_FILE='mysql-bin.000008', -- Update to the values retrieved above
SOURCE_LOG_POS=158, -- Update to the values retrieved above
SOURCE_SSL=1,
SOURCE_SSL_CA = '/etc/mysql/certs/ca-cert.pem',
SOURCE_SSL_CERT = '/etc/mysql/certs/client-cert.pem',
SOURCE_SSL_KEY = '/etc/mysql/certs/client-key.pem';

If that command completed successfully, we can now stop the replica and start it again. Then check the status to confirm there are no errors:

STOP REPLICA;

START REPLICA;
SHOW REPLICA STATUS\G

The status you want at this stage is: Waiting for updates

To test that MySQL replication is functioning, first go onto the MySQL2 system and check which databases actually exist using:

sudo mysql -u root -p

SHOW DATABASES;

Next, go to your MySQL1 server and create a new dummy database using:

sudo mysql -u root -p

SHOW DATABASES;
CREATE DATABASE replicationtest;
SHOW DATABASES;

If replication is setup, you can now go back to MySQL2 and see that the replicationtest database has been automatically created using:

SHOW DATABASES;

Setting up MySQL replication with SSL certificates on MySQL 8.4 has changed from the MySQL 8.0 days. But, those changes are mainly decorative and the bulk of the process remains the same.

It was slightly frustrating having to read all the documentation again to update my commands though. I don’t understand why they did this as the previous commands worked fine.

But hey-ho at least this is working now. Enjoy! 🎉