Using SSL Connections with MySQL

It is very important to secure connection between MySQL Server and Clients. This can be achieved by using SSL connections between MySQL server and client program.

Assumptions:

1. MySQL that has been built with SSL support, and you are going to use OpenSSL.

2. Reader has basic Unix/MySQL Skills

3. Client program is running on windows machine (IP:192.168..0.5)

4. Server program is running on Centos/Redhat machine (IP:192.168.0.4)

Setting Up SSL Certificates for MySQL:

The following openssl commands will prompt you for several inputs. For testing, you can press Enter to all prompts.

1. Create new folder

shell> mkdir -p /etc/mysql

shell> chown -R mysql:mysql /etc/mysql/

shell> cd /etc/mysql/

2. Create CA certificate

shell> openssl genrsa 2048 > ca-key.pem

shell> openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

3. Create server certificate

shell> openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

shell> openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

4. Create client certificate

shell> openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem

shell> openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

5. Add these lines into MySQL option file (i.e. /etc/my.cnf)

[mysqld]

..

ssl-ca=/etc/mysql/ca-cert.pem

ssl-cert=/etc/mysql/server-cert.pem

ssl-key=/etc/mysql/server-key.pem

6. Restart MySQL Instance

shell> /etc/init.d/mysql restart

7. Connect to MySQL server using mysql client program and check whether SSL support is enabled

shell> mysql -uroot -pxxxx

mysql> SHOW VARIABLES LIKE 'have_ssl'; 

+---------------+-------+ 

| Variable_name | Value | 

+---------------+-------+ 

| have_ssl      | YES   | 

+---------------+-------+

8. Create a user with REQUIRE SSL option

mysql> GRANT ALL ON test.* TO 'ssl_user'@'192.168.0.5' IDENTIFIED BY 'password' REQUIRE SSL;

9. Copy following files from /etc/mysql/ onto windows client on c:\ssl\

ca-cert.pem

client-cert.pem

client-key.pem

10. Test connectivity from windows O/S. From command prompt run this command

C:\>mysql -h192.168.0.4 -ussl_user  -ppassword --ssl-ca c:\ssl\ca-cert.pem --ssl-cert

c:\ssl\client-cert.pem --ssl-key c:\ssl\client-key.pem 

11. Once connected successfully, run this command to verify SSL connection

mysql> SHOW STATUS LIKE 'Ssl_cipher';

 +---------------+--------------------+ 

| Variable_name | Value              |

 +---------------+--------------------+ 

| Ssl_cipher    | DHE-RSA-AES256-SHA | 

+---------------+--------------------+

Done! 

Replication consistency check

How to verify that slaves are in sync with the master. There is an easy way to achieve this is to use mk-table-checksum. In this article I'll explain, how to verify slave database with master database.

Assumption:

=========

Master database server: 192.168.168.1

Slave database server: 192.168.168.2

Downloading

=========

we need to download mk-table-checksum onto slave database i.e. 192.168.168.2

wget http://www.maatkit.org/get/toolname/mk-table-checksum

chmod +x mk-table-checksum

Once downloaded, it is ready to use, no installation is needed.

Run this command: this will print connection information and exit

./mk-table-checksum 192.168.168.1 192.168.168.2 --user=root --ask-pass --chunk-size 500000 --databases test --explain-hosts

Output:

Server 192.168.168.1:

h=192.168.168.1,u=root

Server 192.168.168.2:

h=192.168.168.2,u=root

mk-table-checksum assumes the first server is the master and others are slave.

--user: user for login,

--ask-pass: prompt for a password when connecting to mysql servers, in this example it will prompt for both database servers

--chunk-size: Approximate number of rows or size of data to checksum at a time. In this example it's 50000

--database: checksum this comma-separated list of databases.In this example we decided to checksum only test database.

Now it's the time to run above command without --explain-hosts

./mk-table-checksum 192.168.168.1 192.168.168.2 --user=root --ask-pass --chunk-size 500000 --databases test

Output:

DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG

allsaints table_1 0 192.168.168.2 MyISAM 2 53fa3b06 0 0 NULL NULL

allsaints table_1 0 192.168.168.1 MyISAM 2 53fa3b06 0 0 NULL NULL

DATABASE

The database the table is in.

TABLE

The table name.

CHUNK

The chunk (see --chunk-size). Zero if you are not doing chunked checksums.

HOST

The server's hostname.

ENGINE

The table's storage engine.

COUNT

The table's row count, unless you specified to skip it.

CHECKSUM

The table's checksum, unless you specified to skip it or the table has no rows. some types of checksums will be 0 if there are no rows; others will print NULL.

TIME

The time the actual checksum and/or counting took.

WAIT

How long the checksum blocked before beginning.

STAT

The return value of MASTER_POS_WAIT().

LAG

How far the slave lags the master, as reported by SHOW SLAVE STATUS.