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.

INNODB Full Table Scan

Full table scan is faster on MyISAM tables but it has been discourged to use with INNODB tables, the reason for which is that innodb buffer pool is not table scan resistant. A full table scan on INNODB can have the affects of evicting a large portion of the useful pages out of the INNODB buffer pool . However there are times when it is desirable to fill the buffer pool with full table scans. On other hand, if you use mysqldump to backup INNODB tables then you have experienced performance issues. Because it perfroms full table scans and thus blow out buffer pool cache.
INNOBASE has offered fix for it in innodb plugin 1.0.4

1) innodb_old_blocks_time
2) innodb_old_blocks_pct

1) Just before performing table scans and dumps to prevent eviction of a large portion of usefull pages from buffer pool. You can set this variable at runtime

mysql>SET GLOBAL innodb_old_blocks_time=1000;
.. perfrom table scans
mysql> SET GLOBAL inndob_old_blocks_time=0;


2) Similarly we can decide how much space should be available for old blocks, the default value for which is 37% (approximatley i.e. 3/8 of the pool). This behavior can be controlled with innodb_old_blocks_pct runtime variable. The value for this variable should be altered carefully, as you might have lots of unwanted old pages in buffer pool.

Customized Logging Using MySQL Proxy

If you are using MySQL 5.0, it is not possible to enable general query log without restarting mysql instance. However there are number of ways to capture what is being sent to mysql server. For instance
a) DTrace- this is the option available if you are using Solaris.
b) MySQL Proxy- you can use this on all major platforms including microsoft windows

Today I'll focus on MySQL Proxy.

Assumptions:
=========
1) Reader has basic skills of unix
2) Reader will carry out these steps on Redhat5/Centos5 only.
3) Download location is /root/
4) MySQL Server is running on 127.0.0.1 ( i.e. localhost)

Installation:
========

1. Download latest version of MySQL Proxy from here
wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.0-linux-rhel5-x86-64bit.tar.gz/from/http://mirrors.dedipower.com/www.mysql.com/
2. Decompress it
tar -xvzf mysql-proxy-0.8.0-linux-rhel5-x86-64bit.tar.gz
3. rename it
mv mysql-proxy-0.8.0-linux-rhel5-x86-64bit myqsql-proxy

Setup configuration file
=======================
Create text file using using your faviroute editor e.g. vi
shell# vi /etc/mysql-proxy

copy following text into /etc/mysql-proxy.conf

[mysql-proxy]
daemon=true
pid-file=/tmp/mysql-proxy.pid
log-file=/var/log/mysql-proxy.log
log-level=debug
proxy-backend-addresses=127.0.0.1:3306
proxy-lua-script=/root/mysql-proxy/include/custom_logging.lua

Permissions (Make it sure that mysql-proxy script should be able to read it)
=====================================================

shell# chmod 650 /etc/mysql-proxy.conf

Setup Lua script
================
copy following text into /mysql-proxy/include/customer_logging.lua

--------------------------------------------------------------------
local log_file = '/var/log/mysql_query.log'

local fh = io.open(log_file, "a+")

local tokenizer = require("proxy.tokenizer")

function read_query( packet )
if string.byte(packet) == proxy.COM_QUERY then
query = string.sub(packet, 2)
proxy.queries:append(1, packet, {resultset_is_needed = true} )
return proxy.PROXY_SEND_QUERY
else
query = ""
end
end

function read_query_result (inj)
local row_count = 0
local res = assert(inj.resultset)

local num_cols = string.byte(res.raw, 1)

if num_cols > 0 and num_cols < 255 then
for row in inj.resultset.rows do
row_count = row_count + 1
end
end

local error_status =""

if not res.query_status or res.query_status == proxy.MYSQLD_PACKET_ERR then
error_status = "[ERROR]"
end

if (res.affected_rows) then
row_count = res.affected_rows
end
--
-- write the query, adding the number of retrieved rows
--
local tokens = tokenizer.tokenize(inj.query:sub(2))
local norm_query = tokenizer.normalize(tokens)

fh:write( string.format("%s %6d --%s --%s query-time(%d) rows{%d} %s\n",
os.date('%Y-%m-%d %H:%M:%S'),
proxy.connection.server.thread_id,
proxy.connection.client.default_db,
query,
inj.query_time,
row_count,
error_status))
fh:flush()
end
---------------------------------------------------

Starting MySQL Proxy Server
=====================
Shell# /root/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.conf

Testing
=====
shell# mysql -h127.0.0.1 -uroot -pxxx -P 4040 mysql> SHOW DATABASES;
mysql> SHOW TABLES FROM TEST;
mysql> exit;

See what appears in log file
===================
shell# tail -f /var/log/mysql_query.log
2010-03-16 16:14:38 58123 -- --SELECT DATABASE() query-time(128) rows{1}
2010-03-16 16:15:11 58123 --test --show tables query-time(252) rows{5}

Backup using mysqlhotcopy script

I needed to setup a script to backup myisam tables, so I thought I shall publish it on web too

########################

# Create Backup Folder #

########################

NOW=$(date +"%m_%d_%Y")

BACKUP_LOCATION="/u02/backup/dev/$NOW/"

mkdir -p `echo $BACKUP_LOCATION`

cd /u02/backup/dev/$NOW

########################################

# Array of databases we want to backup #

########################################

ARRAY="bugs my_company knowledge_base mysql wikidb"

MYSQL_HOTCOPY="/usr/bin/mysqlhotcopy -p xxxxx -q "

echo "----BACKUP STARTED @`date`"

#################################################

# Iterate through Array and Backup each of them #

################################################

for DATABASE in `echo $ARRAY`

do

echo "Taking backup of $DATABASE.."

$MYSQL_HOTCOPY $DATABASE $BACKUP_LOCATION

echo "Done.."

echo "Compressing backup directory using tar"

/bin/tar cfP $DATABASE.tar $BACKUP_LOCATION/$DATABASE

echo "Done.."

echo "Delete backup directory after tar command"

rm -fR $DATABASE

echo "Done.."

echo "Compressing $DATABASE.tar file using gzip"

/bin/gzip $DATABASE.tar

echo "done"

done

echo " ----- END -----@`date` "

##################################

Backup MySQL Replication Database Server

Here is the script, which I use to backup MySQL Replication Slave database server.

HOST=localhost

USR=root

PASS=xxxxx

SOCKET=/tmp/mysql.sock

NOW=$(date +"%m_%d_%Y")

BACKUP_LOCATION=/path/to/backup/$NOW

mkdir -p `echo $BACKUP_LOCATION`

MYSQL="/usr/bin/mysql -h$HOST -u$USR -p$PASS -S $SOCKET -v"

MYSQLDUMP="/usr/bin/mysqldump -u$USR -p$PASS -S $SOCKET --all-databases"

# STOP_SLAVE

echo 'STOP SLAVE SQL_THREAD; FLUSH TABLES;'| $MYSQL

# GET_SLAVE_STATUS

echo "SHOW SLAVE STATUS\G" | $MYSQL > $BACKUP_LOCATION/bin_log_pos.log

# BACKUP_TABLE_STRUCTURES

$MYSQLDUMP --no-data --routines --result-file=$BACKUP_LOCATION/table_structure.sql

# BACKUP_RECORDS

$MYSQLDUMP --result-file=$BACKUP_LOCATION/all_records.sql

# START_SLAVE

echo "START SLAVE" | $MYSQL

####################

if you prefer, you can automate this script, add following line into crontab

0 4 * * * /path/to/script/backup.sh

MyISAM Table Maintenance

There are numerous ways to check/repair MyISAM tables

1. mysqlchk

2. mysqlcheck

3. myisam_recover

One should not use mysqlchk with online database, unlike mysqlcheck it directly accesses tables and may damage them. There are two alternative solutions

1. MYISAM_RECOVER option: quick option to automate recovery but it should be used very carefully. As all live tables are accessed quite frequently, enabling this option may trigger many check/repair at once.
2. CHECK TABLE: it is safer compared to myisam_recover option. Unlike myisam_chk it does not directly access tables.

It is not recommend that one should run extended table checks regularly. However it is highly recommended using medium/quick check options to ensure that all tables are okay. Furthermore one should be optimizing tables regularly, as this reduces risks associated with table errors. Unlike check table operations, optimize table operation takes much longer but the good news is that we can easily identify the table(s) that need optimization.