Configuring mysql for ssl replications (Debian, Ubuntu)

This article is for those who need to set up master-slave replication on the mysql server using a secure connection. When the need arose, I was faced with a lack of information in Russian about configuring replication and almost no information at all, how to do it using ssl. In the end, I managed to raise replication. I want to share my experience and broken links.

The following installation commands are examples of configuration files related to debian-like systems (including ubuntu). In other distributions they may differ. For example, to install programs in RH and CentOS instead of “apt-get install” you need to write “yum install”.
The article is relevant, starting from August 2009 until such time as the developers of the programs used here do not change anything.
Mysql-server-5.0, Ubuntu 8.04-9.04 and the corresponding Debian.

Preparatory work - make the keys

Further it will be told how to configure replications. If the safety of the connection does not bother you, this item is not for you.
The muscle settings file suggests using prog-tinika to create keys:
')
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".

I listened to it, although true Linux users are required to create keys from the command line. If you do not labor, then:

sudo apt-get install tinyca
sudo tinyca2

In the program you need:

1. Create CA certificate (set Common Name parameter in cacert). Next, create a certificate and key (created in pairs) for the server and the client (do not forget to enter the password of the CA certificate that we invented when we created it).
2. Export the CA certificate and server and client certificates to the PEM format with the names cacert.pem, my-server-cert.pem and my-client-cert.pem respectively.
3. Export the server and client keys ( note , the Without passphrase = Yes option, otherwise muscular will not be able to decrypt your certificate, it will require the CA certificate password) to the PEM format with the names my-server-key.pem and my-client-key.pem, respectively.
4. Give all 5 files read permissions to the owner and group, no write permissions. Well, make sure that Muskul was either the owner or was a member of the group.

Commands to change rights and owners in order of mention:

chmod 440 *.pem
chown root:mysql *.pem

A CA certificate is an author's certificate; it serves to bundle server and client certificates. The server will need a certificate and server key, on the client a certificate and a client key, and both will need the author's certificate - cacert.

Configure the master server

In debian-like systems (at least) the command is specified in the file my.cnf

!includedir /etc/mysql/conf.d/

which says include all configuration files (* .cnf) from the specified directory. Create a replica.cnf file there with the following content:

[mysqld]
log-bin = /var/log/mysql/mysql-bin.log
slave-compressed = 1
binlog-do-db = mydb
server-id = 1

# ssl params
ssl-ca=/etc/mysql/my-cacert.pem
ssl-cert=/etc/mysql/my-server-cert.pem
ssl-key=/etc/mysql/my-server-key.pem

• log-bin - the path to the binary logging database change file.
• slave-compressed - allows you to compress the data transmitted from the master to the slave
• binlog-do-db - the name of the database that we are going to replicate
• server-id - server identifier, must be unique within interconnected servers

The ssl parameters are specified if there is a desire to protect the connection for replications. The previously created keys are indicated. Put them in the muscle folder, next to my.cnf. My attempts to put them in the / etc / mysql / certs / folder resulted in errors like "I can not get a certificate from a file ...". Perhaps there are solutions for other folders, but I did not begin to look for them.

After the movements made, you need to overload the muscle, go into it and see

SHOW VARIABLES LIKE '%ssl%';

In the answer, the have_ssl and / or have_openssl parameter has the value YES in the case of sucks, and DISABLED in all bad other cases (the absence of the parameter indicates that your muscle assembly does not support ssl). If yes we did not find us, go to the logs to look for the cause: /var/log/mysql.log, /var/log/mysql.err, / var / log / syslog or somewhere else, depending on where the muscle is configured to log itself on your system.

We also need to set up a separate user for replications. This is solved by executing the request.

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%.slave.domain' IDENTIFIED BY 'slave_password' require SSL

In case security does not bother us, the words “require SSL” should be omitted. In the domain name “%” works the same as “*” in the entry “* .cnf”.

Now the same user needs to be given rights to read our database tables:

GRANT SELECT ON mydb.* TO 'slave_user'@'%.slave.domain' IDENTIFIED BY 'slave_password' require SSL

In other docks, there are instructions to give the rights SUPER or even ALL, but I did not do it, but it works all the same.

Transfer the DB to the slave

Before creating a database dump, you need to lock the tables - in order to avoid trouble:

USE mydb;
FLUSH TABLES WITH READ LOCK;

Next, look at the status of the wizard:

SHOW MASTER STATUS;

And we will write down the values ​​of the File and Position fields on the list - they will still be useful to us. I had these values ​​mysql-bin.000002 and 412 respectively. It is important not to get lost in zeros :)

Now we will dump the database:

mysqldump -u slave_user -pslave_password --opt mydb > mydb.sql

Due to the fact that we have given our slave few rights, this command may be cursed (I did not try it, did a dump via webmin ). In this case, either add permissions to the user, or dump from under another user.

Finally, unlock the tables:

UNLOCK TABLES;

And rewrite the dump to the server slave.

Connect client

On the client-slave server you need to fill in the data in the database:

mysql -u root -p
[create database mydb]
[use mydb]
source /path/to/mydb.sql
exit

In square brackets there are commands that are not necessary to execute if you already have database creation commands in your mydb.sql script.

Although it is written in other instructions, I did not create the user slave_user and give him any rights on the client server. If that - links at the end of the article to you in clicks.

Next, create the file /etc/mysql/conf.d/replica.cnf

[mysqld]
server-id = 2

[client]
# ssl params
ssl-ca=/etc/mysql/my-cacert.pem
ssl-cert=/etc/mysql/my-client-cert.pem
ssl-key=/etc/mysql/my-client-key.pem

The second client part is for secure connections. The main thing is not to forget to put the previously created keys in the specified folder.

Overload the muscle, go into it and execute a series of commands:

SLAVE STOP;

CHANGE MASTER TO
MASTER_HOST='master.host.ru',
MASTER_USER='slave_user',
MASTER_PASSWORD='slave_password',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=412,
MASTER_CONNECT_RETRY=30,

MASTER_SSL = 1,
MASTER_SSL_CA = '/etc/mysql/my-cacert.pem',
MASTER_SSL_CERT = '/etc/mysql/my-client-cert.pem',
MASTER_SSL_KEY = '/etc/mysql/my-client-key.pem'

START SLAVE;

• MASTER_HOST - host name (or IP address) of the master server
• MASTER_LOG_FILE and MASTER_LOG_POS - data received from the master server and recorded previously on the list
• MASTER_CONNECT_RETRY - time (in seconds) until the next attempt to connect with the master in the event of a disconnection
• MASTER_SSL - set to 1 if you want to use a secure connection, otherwise - 0. If you set 0, then do not specify the MASTER_SSL_CA, MASTER_SSL_CERT and MASTER_SSL_KEY parameters either.

At the moment, replication should already work :)
Check by changing the data on the server. Almost immediately (a few seconds difference) they should change on the client. If it does not change, go to the logs to look for reasons. Or we go to read other docks:

If not earned

The article describes how I raised replication on my own servers. Perhaps due to differences in settings or for other reasons you will not be able to make everything work. Then I can only leave links to resources that have helped me.

http://www.opennet.ru/tips/info/1696.shtml - rusish, without ssl, do not forget to read kamenty (there are useful)
http://www.webnext.ru/blog/2007/08/21/replication-mysql-master-slave.html - Russian, without ssl, at a gallop across Europe
http://dibaliklayar.blogspot.com/2006/10/replication-of-mysql-50-using-ssl.html - English, with ssl, was read without attention, because I found it already after hitting my forehead

Shl. Yes, I found a fairly detailed and useful description of setting up replications here . Without ssl, but a lot of interesting.

The second option is to ask in the comments, if not me, then other habra people will prompt. You look, and earn.
If there are explanations or comments - I ask you to write too, I myself am wondering why some things are needed, although it works without them.