To use an encrypted connection for the transfer of the binary log required during replication, both the master and the slave servers must support encrypted network connections. If either server does not support encrypted connections (because it has not been compiled or configured for them), replication through an encrypted connection is not possible.
Setting up encrypted connections for replication is similar to doing so for client/server connections. You must obtain (or create) a suitable security certificate that you can use on the master, and a similar certificate (from the same certificate authority) on each slave. You must also obtain suitable key files.
For more information on setting up a server and client for encrypted connections, see Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.
To enable encrypted connections on the master, you must create or
obtain suitable certificate and key files, and then add the
following configuration options to the master's configuration
[mysqld] section of the master's
my.cnf file, changing the file names as
[mysqld] ssl-ca=cacert.pem ssl-cert=server-cert.pem ssl-key=server-key.pem
The paths to the files may be relative or absolute; we recommend that you always use complete paths for this purpose.
The options are as follows:
--ssl-ca: The path name of the Certificate Authority (CA) certificate file. (
--ssl-capathis similar but specifies the path name of a directory of CA certificate files.)
--ssl-cert: The path name of the server public key certificate file. This can be sent to the client and authenticated against the CA certificate that it has.
--ssl-key: The path name of the server private key file.
On the slave, there are two ways to specify the information
required for connecting using encryption to the master. You can
either name the slave certificate and key files in the
[client] section of the slave's
my.cnf file, or you can explicitly specify
that information using the
To name the slave certificate and key files using an option file, add the following lines to the
[client]section of the slave's
my.cnffile, changing the file names as necessary:
[client] ssl-ca=cacert.pem ssl-cert=client-cert.pem ssl-key=client-key.pem
Restart the slave server, using the
--skip-slave-startoption to prevent the slave from connecting to the master. Use
CHANGE MASTER TOto specify the master configuration, using the
MASTER_SSLoption to connect using encryption:
mysql> CHANGE MASTER TO -> MASTER_HOST='master_hostname', -> MASTER_USER='replicate', -> MASTER_PASSWORD='password', -> MASTER_SSL=1;
To specify the certificate and key names using the
CHANGE MASTER TOstatement, append the appropriate
mysql> CHANGE MASTER TO -> MASTER_HOST='master_hostname', -> MASTER_USER='replicate', -> MASTER_PASSWORD='password', -> MASTER_SSL=1, -> MASTER_SSL_CA = 'ca_file_name', -> MASTER_SSL_CAPATH = 'ca_directory_name', -> MASTER_SSL_CERT = 'cert_file_name', -> MASTER_SSL_KEY = 'key_file_name';
After the master information has been updated, start the slave replication process:
mysql> START SLAVE;
You can use the
SHOW SLAVE STATUS
statement to confirm that an encrypted connection was established
If you want to enforce the use of encrypted connections during
replication, create a user with the
REPLICATION SLAVE privilege and use
REQUIRE SSL option for that user. For
mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass'; mysql> GRANT REPLICATION SLAVE ON *.* -> TO 'repl'@'%.mydomain.com' REQUIRE SSL;
If the account already exists, you can add
SSL to it with this statement:
mysql> GRANT USAGE ON *.* -> TO 'repl'@'%.mydomain.com' REQUIRE SSL;