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).
|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——-|
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).
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.
After all the files are in the desired directories it is time to set up the configuration files.
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).
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).
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.
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.