Wednesday 26 May 2010

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! 

Tuesday 18 May 2010

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.