Encrypted Replication for MySQL

Encrypted connections for MySQL replication are the default as of 8.0. The following simple example shows a basic setup for encrypted replication using Ubuntu 20.04 and MySQL Community 8.0.27. The steps may be similar for other Linux distributions.

The necessary files for this example come installed with this version of MySQL and are found in the data directory (Figure 1). They are five different .pem files and two belong on the source server, two will be copied over to the replica server, and one will exist on both the source and replication servers (Table 1).

VDBA Encrypted Replication 1 — *Figure 1: Source data directory contents. Note the .pem files that are automatically included with a fresh install of MySQL.*

File	Original Location	Needed on Source	Needed on Replica	Permissions
ca.pem	Source – data dir	Yes	Yes	-rw-r–r–
client-cert.pem	Source – data dir	No	Yes	-rw-r–r–
client-key.pem	Source – data dir	No	Yes	-rw——-
server-cert.pem	Source – data dir	yes	No	-rw-r–r–
server-key.pem	Source data dir	yes	No	-rw——-

Table 1: List of necessary .pem files with the permissions

Once the necessary files have been located in the default data directory, the directory can be noted and client files can be copied to the replication server.

For this example, a separate directory was created on the source and replication servers to better organize the .pem files, but this is not necessary. On the source this directory is called “/var/lib/mysql/repl_security” (Figure 2). For the replication .pem files the directory is called “/var/lib/mysql/repl_sec” (Figure 3).

Now the files can be distributed appropriately.

For the source the files ca.pem, server-cert.pem, and server-key.pem are moved locally into the repl_security directory (Figure 2).

VDBA Encrypted Replication 2 — *Figure 2: The directory (on source) for ssl server files necessary for replication. Check file permissions to ensure they can be used by MySQL.*

Any files that are needed on the replication server must be sent over from the source to the replication server. The necessary files (shown in Table 1) are: ca.pem, client-cert.pem, and client-key.pem. In this case the linux command “scp” was used to move the client .pem files to the replication server. Once on the server the files were moved to the repl_sec directory. After that the file owner and permissions were changed (via chown, chgrp, and chmod) to those shown in Figure 3.

VDBA Encrypted Replication 3 — *Figure 3: The replica files with their permissions in the directory found in the configuration file. Be sure the permissions match what is shown here.*

After all the files are in the desired directories it is time to set up the configuration files.

Source:

Figure 4 shows the default directory containing the configuration file to be edited, and Figure 5 shows the changes to be made. It is important to note that the .pem file directories be located under the [mysqld] section (as seen in Figure 5).

Replica:

Figure 6 shows the default directory containing the configuration file to be edited, and Figure 7 shows the changes to be made. It is important to note that the .pem file directories be located under the [client] section (as seen in Figure 7).

VDBA Encrypted Replication 7 — *Figure 7: The replica configuration file. The necessary ssl files are under [client] and begin with “ssl.”*

After both configuration files are set be sure to restart the MySQL instance on both servers.

Now the encrypted replication connection is setup and ready to use.

Summary

A quick reference to the steps involved in the process of setting up encrypted replication for MySQL using the automatically created .pem files:

Locate necessary .pem files
Distribute client files to replication server
Ensure correct permissions and owners on all .pem files
Edit configuration files for source and replica to point ssl options at appropriate directories
Restart source and replication MySQL instances.