MySQL Replication over SSL

##Tutorial for MySQL Replication over SSL (one-way)##
#####################################################

##Check that SSL has been compiled in SSL on both the master and the client:

SHOW VARIABLES LIKE ‘have_openssl';
or
mysql –ssl –help
If it says disabled or yes then its fine.  If it says no then an SSL enabled version of MySQL needs to be installed.

##Create the SSL certificates on the master

Change to the mysql ssl directory (you may need to create it)
mkdir /etc/mysql/ssl
cd /etc/mysql/ssl/
Create your own Certification Authority (CA) if you do not already have one (e.g. for signing web or mail server certificates)

openssl req -x509 -new -days 9999 -newkey rsa:2048 -nodes \
-keyout ca-key.pem -out ca-cert.pem

Create the server certificate request

openssl req -new -newkey rsa:2048 -nodes -keyout server-key.pem -out server-csr.pem

(optional) Remove the passphrase from the key

openssl rsa -in server-key.pem -out server-key.pem

Sign this server request with the CA key to make a proper server certificate.

openssl x509 -req -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial \
-CAserial ca-srl.txt -in server-csr.pem -out server-cert.pem

Adjust the following lines (such as the paths) in /etc/mysql/my.cnf as necessary (note to self – the server keys go in the [mysqld] section, the client keys go in the [client] section):

[mysqld]
ssl-key=/etc/mysql/ssl/server-key.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-ca=/etc/mysql/ssl/ca-cert.pem

###Create the client certificates on the master

Create the client certificate request

openssl req -new -newkey rsa:2048 -nodes -keyout client-key.pem -out client-csr.pem

(OPTIONAL) Remove a passphrase from the key

openssl rsa -in client-key.pem -out client-key.pem

Sign this server request with the CA key to make a proper server certificate

openssl x509 -req -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial \
-CAserial ca-srl.txt -in client-csr.pem -out client-cert.pem

Copy the client files client-key, client-cert and ca-cert.pem to $PATH on the client machine and adjust your ~/.my.cnf or /etc/mysql/my.cnf:

[client]
ssl-ca=$PATH/ca-cert.pem
ssl-key=$PATH/client-key.pem
ssl-cert=$PATH/client-cert.pem

##Grant the replicate user privileges

mysql> grant replication slave, super, reload, select on *.* to ‘replicate’@’%’ \
identied by ‘password’ require SSL;

or:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replicate@’%’ \
IDENTIFIED BY ‘password’ require SSL;

After creating the account, verify that the settings are correct:

mysql> SHOW GRANTS FOR replicate;

FLUSH PRIVILEGES;

Test from the client that the connection works:

mysql –ssl -h MySQL_SERVER -u SSL_CLIENT -p

##Configure Replication
##########################

On Server:

Change /etc/mysql/my.cnf:

server-id=1
log-bin=/var/log/mysql/mysql-bin.log

mysqld restart

On Slave:

Change /etc/mysql/my.cnf:

server-id               = 2
log-bin                 = /var/log/mysql/mysql-bin.log
relay-log               = /var/log/mysql/hostname-relay-bin
relay-log-index         = /var/log/mysql/hostname-relay-bin.index

replicate-do-db = db1#list and databases/tables to be replicated
replicate-do-db = db2

mysqld restart

##Copy data over
#################
On master lock tables so they cannot be written:

mysql> flush tables with read lock;

stop slave on slave if running:
mysql > stop slave;
mysql > reset slave;

On master, clear master binary log:

mysql > reset master;

grab a mysqdump of the database(s), scp it to the slave and import it.

##Start The Replication
#######################

On slave setup the master replication settings:

mysql > change master to master_host=’85.111.222.000′,  master_user=’replicate’,  master_password=’xxxxxxxxx’, master_ssl=1, MASTER_SSL_CA = ‘/etc/mysql/ssl/ca-cert-pem’, MASTER_SSL_CERT = ‘/etc/mysql/ssl/client-cert.pem’, MASTER_SSL_KEY = ‘/etc/mysql/ssl/client-key.pem';
mysql > start slave;

On master, unlock the tables:

mysql > unlock tables;

##Check all working ok:
########################

On slave:

mysql > show slave status

Should show “waiting for master to send event”

Useful errors may log to /var/log/daemon.log aswell as mysqld log

references:

http://dearsmk.wordpress.com/2008/04/21/how-to-set-up-mysql-replication/

http://www.option-c.com/xwiki/MySQL_Replication_with_SSL

http://bugs.mysql.com/bug.php?id=2143

You can leave a response, or trackback from your own site.

Leave a Reply