I recently configured MySQL for replication after first enabling SSL connections between the two systems that would be involved with replication. I have to say that MySQL documentation is excellent and all these notes are simply based on what is available on the MySQL site. I have included links to as many of the relevant sections of the documentation as possible.
For reference, here is the MySQL manual on enabling SSL: 5.5.7.2. Using SSL Connections
Before beginning, it is a good idea to create a directory for the SSL output files and make sure all the files end up there.
MySQL’s RHEL5 packages from mysql.com support SSL by default, but to check you can run:
$ mysqld --ssl --help mysqld Ver 5.0.67-community-log for redhat-linux-gnu on i686 (MySQL Community Edition (GPL))Copyright (C) 2000 MySQL AB, by Monty and others This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Starts the MySQL database server Usage: mysqld [OPTIONS] For more help options (several pages), use mysqld --verbose --help
The command will create an error if there is no SSL support.
Next, check that the MySQL server has SSL enabled. The below output means that the server supports SSL but it is not enabled. Enabling it can be done at the command line or in the configuration file, which will be detailed later.
$ mysql -u username -p -e"show variables like 'have_ssl'" Enter password: +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_ssl | DISABLED | +---------------+----------+
Documentation on setting up certificates:
5.5.7.4. Setting Up SSL Certificates for MySQL
First, generate the CA key and CA certificate:
$ openssl genrsa 2048 > mysql-ca-key.pem Generating RSA private key, 2048 bit long modulus ............................................+++ ............+++ $ openssl req -new -x509 -nodes -days 356 -key mysql-ca-key.pem > mysql-ca-cert.pem You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [GB]:US State or Province Name (full name) [Berkshire]:California Locality Name (eg, city) [Newbury]:Burbank Organization Name (eg, company) [My Company Ltd]:Acme Road Runner Traps Organizational Unit Name (eg, section) []:Acme IRT Common Name (eg, your name or your server's hostname) []:mysql.acme.com Email Address []:acme-irt@acme.com
Create the server certificate:
$ openssl req -newkey rsa:2048 -days 365 -nodes -keyout mysql-server-key.pem > mysql-server-req.pem Generating a 2048 bit RSA private key .............................+++ .............................................................+++ writing new private key to 'mysql-server-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [GB]:US State or Province Name (full name) [Berkshire]:California Locality Name (eg, city) [Newbury]:Burbank Organization Name (eg, company) [My Company Ltd]:Acme Road Runner Traps Organizational Unit Name (eg, section) []:Acme IRT Common Name (eg, your name or your server's hostname) []:mysql.acme.com Email Address []:acme-irt@acme.com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: $ openssl x509 -req -in mysql-server-req.pem -days 356 -CA mysql-ca-cert.pem -CAkey mysql-ca-key.pem -set_serial 01 > mysql-server-cert.pem Signature ok subject=/C=US/ST=California/L=Burbank/O=Acme Road Runner Traps/OU=Acme IRT/CN= mysql.acme.com/emailAddress=acme-irt@acme.com Getting CA Private Key
Finally, create the client certificate:
$ openssl req -newkey rsa:2048 -days 356 -nodes -keyout mysql-client-key.pem > mysql-client-req.pem Generating a 2048 bit RSA private key ................+++ .................+++ writing new private key to 'mysql-client-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [GB]:US State or Province Name (full name) [Berkshire]:California Locality Name (eg, city) [Newbury]:Burbank Organization Name (eg, company) [My Company Ltd]:Acme Road Runner Traps Organizational Unit Name (eg, section) []:Acme IRT Common Name (eg, your name or your server's hostname) []:mysql.acme.com Email Address []:acme-irt@acme.com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: $ openssl x509 -req -in mysql-client-req.pem -days 356 -CA mysql-ca-cert.pem -CAkey mysql-ca-key.pem -set_serial 01 > mysql-client-cert.pem Signature ok subject=/C=US/ST=California/L=Burbank/O=Acme Road Runner Traps/OU=Acme IRT/CN=mysql.acme.com/emailAddress=acme-irt@acme.com Getting CA Private Key [nr@mysqld mysqlcerts]$ ls mysql-ca-cert.pem mysql-client-key.pem mysql-server-key.pem mysql-ca-key.pem mysql-client-req.pem mysql-server-req.pem mysql-client-cert.pem mysql-server-cert.pem
To enable SSL when starting mysqld, the following should be in /etc/my.cnf under the [mysqld] section. For this example, I put the files in /etc/mysql/openssl:
ssl-ca="/etc/mysql/openssl/mysql-ca-cert.pem" ssl-cert="/etc/mysql/openssl/mysql-server-cert.pem" ssl-key="/etc/mysql/openssl/mysql-server-key.pem"
To use any client, for instance mysql from the command line or the GUI MySQL Administrator, copy the client cert and key to a dedicated folder on the local box along with ca-cert. You will have to configure the client to use the client certificate, client key, and CA certificate.
To connect with the mysql client using SSL, copy the client certificates to a folder, for instance /etc/mysql, then under the [client] section in /etc/my.cnf:
ssl-ca="/etc/mysql/openssl/mysql-ca-cert.pem" ssl-cert="/etc/mysql/openssl/mysql-client-cert.pem" ssl-key="/etc/mysql/openssl/mysql-client-key.pem"
In MySQL Administrator, the following is an example you would put into the Advanced Parameters section if you want to connect using SSL.
SSL_CA U:/keys/mysql-ca-cert.pem SSL_CERT U:/keys/mysql-client-cert.pem SSL_KEY U:/keys/mysql-client-key.pem USE_SSL Yes
Replication
Before configuring replication, I made sure to review the MySQL replication documentation.
16.1.1.1. Creating a User for Replication
Because MySQL stores the replication user’s name and password using plain text in the master.info file, it’s recommended to create a dedicated user that only has the REPLICATION SLAVE privilege. The replication user needs to be created on the master so the slaves can connect with that user.
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.50' IDENTIFIED BY ‘password’;
16.1.1.2. Setting the Replication Master Configuration
Edit my.cnf to uncomment the “log-bin” line. Also uncomment “server-id = 1”. The server-id can be anything between 1 and 2^32 but must be unique.
Also add “expire_logs_days” to my.cnf. If you don’t, the binary logs could fill up the disk partition because they are not deleted by default!
expire_log_days = 4
16.1.1.3. Setting the Replication Slave Configuration
Set server-id to something different from the master in my.cnf. Although not required, enabling binary logging on the slave is also recommended for backups, crash recovery, and in case the slave will also be a master to other systems.
16.1.1.4. Obtaining the Master Replication Information
I flush the tables to disk and lock them to temporarily prevent changes.
# mysql -u root -p -A dbname mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec)
If the slave already has data from master, then you may want to copy over data manually to simplify things, 16.1.1.6. Creating a Data Snapshot Using Raw Data Files. However, you can also use mysqldump, as shown in Section 16.1.1.5, “Creating a Data Snapshot Using mysqldump”.
Once the data is copied over to the slave, I get the current log position.
Mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 16524487 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> UNLOCK TABLES;
16.1.1.10. Setting the Master Configuration on the Slave
Finally, configure the slave. The log file and log position tell the slave where to begin replication. All changes after that log position will be replicated to the slave.
mysql> CHANGE MASTER TO -> MASTER_HOST=’192.168.1.50’, -> MASTER_USER=’repl’, -> MASTER_PASSWORD='replication_password', -> MASTER_LOG_FILE=’ mysql-bin.000002’, -> MASTER_LOG_POS=16524487, -> MASTER_SSL=1, -> MASTER_SSL_CA = '/etc/mysql/openssl/mysql-ca-cert.pem', -> MASTER_SSL_CAPATH='/etc/mysql/openssl/', -> MASTER_SSL_CERT = '/etc/mysql/openssl/mysql-server-cert.pem', -> MASTER_SSL_KEY = '/etc/mysql/openssl/mysql-server-key.pem'; Query OK, 0 rows affected (0.74 sec) mysql> START SLAVE;
Replication can start!
The slave status can be checked via the following command:
mysql> show slave status;
