Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.2Mb
PDF (A4) - 31.2Mb
PDF (RPM) - 30.4Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 183.8Kb
Man Pages (Zip) - 295.2Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Setting Up Replication to Use Secure Connections

17.3.8 Setting Up Replication to Use Secure Connections

To use a secure connection for encrypting 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 secure connections (because it has not been compiled or configured for them), replication through an encrypted connection is not possible.

Setting up secure 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 secure connections, see Section 6.4.4, “Configuring MySQL to Use Secure Connections”.

To enable secure 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 within the [mysqld] section of the master's my.cnf file, changing the file names as necessary:

[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 identifies the Certificate Authority (CA) certificate.

  • ssl-cert identifies the server public key certificate. This can be sent to the client and authenticated against the CA certificate that it has.

  • ssl-key identifies the server private key.

On the slave, there are two ways to specify the information required for connecting securely 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 CHANGE MASTER TO statement:

  • 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.cnf file, 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-start option to prevent the slave from connecting to the master. Use CHANGE MASTER TO to specify the master configuration, using the MASTER_SSL option to connect securely:

    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 TO statement, append the appropriate MASTER_SSL_xxx options:

    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 a secure connection was established successfully.

For more information on the CHANGE MASTER TO statement, see Section 13.4.2.1, “CHANGE MASTER TO Syntax”.

If you want to enforce the use of secure connections during replication, then create a user with the REPLICATION SLAVE privilege and use the REQUIRE SSL option for that user. For example:

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 REQUIRE SSL to it with this statement:

mysql> GRANT USAGE ON *.*
    -> TO 'repl'@'%.mydomain.com' REQUIRE SSL;

User Comments
Sign Up Login You must be logged in to post a comment.