15 September, 2009

MySQL replication on RHEL

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;

06 September, 2009

Two years

It has been two years since I started this blog. Here is a quick recap of notable posts that consistently get a substantial number of page views.

IR/NSM:

System Administration: