Activate SSL connection in MySQL

Secure connection? Today we will explore an interesting task about activating and using SSL connection in MySQL. We will provide all necessary scripts and commands to re-play all steps. So assume that we have CentOS 6.5 with OpenSSL and MySQL already installed:

[root@linuxsrv3 ~]# mysql --version 
mysql Ver 14.14 Distrib 5.6.17, for Linux (x86_64) using EditLine wrapper
[root@linuxsrv3 ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.6.17-log MySQL Community Server (GPL) 
[root@linuxsrv3 ~]# openssl version 
OpenSSL 1.0.1e-fips 11 Feb 2013
[root@linuxsrv3 ~]# rpm -qf `which openssl` 
openssl-1.0.1e-16.el6_5.7.x86_64 

Our test host is a Virtual Machine with static ip address: 192.168.1.77. The rest of contents of this article is quite straight just follow commands:

    ### Create Environment ### 
    [root@linuxsrv3 ~]# cd /etc/ 
    [root@linuxsrv3 etc]# mkdir mysql_ssl_certs 
    [root@linuxsrv3 etc]# ls -ld mysql_ssl_certs/ 
    drwxr-xr-x. 2 root root 4096 2014-05-27 17:33 mysql_ssl_certs/ 
    ### Create CA certificates ### 
    [root@linuxsrv3 etc]# cd mysql_ssl_certs/ 

    # 1 
    [root@linuxsrv3 mysql_ssl_certs]# openssl genrsa 2048 > ca-key.pem
    Generating RSA private key, 2048 bit long modulus ..............+++ .......................+++ e is 65537 (0x10001) 

    # 2 [root@linuxsrv3 mysql_ssl_certs]# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out 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) [XX]:AZ 
    State or Province Name (full name) []:BAKU 
    Locality Name (eg, city) [Default City]:BAKU 
    Organization Name (eg, company) [Default Company Ltd]:Student
    Organizational Unit Name (eg, section) []:Student 
    Common Name (eg, your name or your server's hostname) []:shahriyar
    Email Address []:rzayev.sehriyar@gmail.com 

    # 3 ### Create server certificate # 

server-cert.pem = public key, server-key.pem = private key

    [root@linuxsrv3 mysql_ssl_certs]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem 
    Generating a 2048 bit RSA private key ...+++ ..........................+++ writing new private key to '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) [XX]:AZ 
    State or Province Name (full name) []:BAKU 
    Locality Name (eg, city) [Default City]:BAKU 
    Organization Name (eg, company) [Default Company Ltd]:Student
    Organizational Unit Name (eg, section) []:Student 
    Common Name (eg, your name or your server's hostname) []:shahriyar
    Email Address []:rzayev.sehriyar@gmail.com 
    Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:Sh@rzstt01 An optional company name []:Student 

    # 4 [root@linuxsrv3 mysql_ssl_certs]# openssl rsa -in server-key.pem -out server-key.pem writing RSA key 

    # 5 [root@linuxsrv3 mysql_ssl_certs]# openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
    Signature ok subject=/C=AZ/ST=BAKU/L=BAKU/O=Student/OU=Student/CN=shahriyar/emailAddress=rzayev.sehriyar@gmail.com Getting CA Private Key 

    # 6 ### Create client certificate ### 

client-cert.pem = public key, client-key.pem = private key

    [root@linuxsrv3 mysql_ssl_certs]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem 
    Generating a 2048 bit RSA private key .............................................................+++ ......................+++ writing new private key to '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) [XX]:AZ 
        State or Province Name (full name) []:BAKU 
        Locality Name (eg, city) [Default City]:BAKU 
        Organization Name (eg, company) [Default Company Ltd]:Student
        Organizational Unit Name (eg, section) []:Student 
        Common Name (eg, your name or your server's hostname) []:shahriyar
        Email Address []:rzayev.sehriyar@gmail.com 
        Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:Sh@rzstt01 An optional company name []:Student 

        # 7 [root@linuxsrv3 mysql_ssl_certs]# openssl rsa -in client-key.pem -out client-key.pem 
        writing RSA key 

        # 8 
        [root@linuxsrv3 mysql_ssl_certs]# openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem 
        Signature ok subject=/C=AZ/ST=BAKU/L=BAKU/O=Student/OU=Student/CN=shahriyar/emailAddress=rzayev.sehriyar@gmail.com Getting CA Private Key

The last step is to verifying certificates:

[root@linuxsrv3 mysql_ssl_certs]# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OK 

So let’s summurize that our certificates are stored in /etc/mysql_ssl_certs:

[root@linuxsrv3 mysql_ssl_certs]# ls -l /etc/mysql_ssl_certs/ 
total 32 
-rw-r--r--. 1 root root 1424 2014-05-27 17:37 ca-cert.pem 
-rw-r--r--. 1 root root 1679 2014-05-27 17:36 ca-key.pem 
-rw-r--r--. 1 root root 1298 2014-05-27 17:46 client-cert.pem 
-rw-r--r--. 1 root root 1675 2014-05-27 17:45 client-key.pem 
-rw-r--r--. 1 root root 1127 2014-05-27 17:45 client-req.pem 
-rw-r--r--. 1 root root 1298 2014-05-27 17:41 server-cert.pem 
-rw-r--r--. 1 root root 1679 2014-05-27 17:41 server-key.pem 
-rw-r--r--. 1 root root 1127 2014-05-27 17:40 server-req.pem

Going on we must modify MySQL Configuration file(my.cnf) to reflect our generated certificates. In my case my.cnf located in /etc But Before this check if MySQL installation supports SSL:

mysql> SHOW VARIABLES LIKE 'have_ssl'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| have_ssl | YES | 
+---------------+-------+ 
1 row in set (0,10 sec)

If you get “YES” open my.cnf and type under the [mysqld] catalog following:

[mysqld] 
# SSL 
ssl-ca=/etc/mysql_ssl_certs/ca-cert.pem
ssl-cert=/etc/mysql_ssl_certs/server-cert.pem
ssl-key=/etc/mysql_ssl_certs/server-key.pem 

Just NOTE that, this on server side (our virtual machine). Restart MySQL and create user and force it to only SSL connection:

create user 'ssluser'@'%' IDENTIFIED BY 'Pass@123#'; 
GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'%' IDENTIFIED BY 'Pass@123#' REQUIRE SSL;

And let’s try to connect:

[root@linuxsrv3 ~]# mysql -u ssluser -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'ssluser'@'localhost' (using password: YES)

Now, by logic we have a user that reuquires SSL, but our mysql client which we used to connect does not know about this. So we must again edit my.cnf but now under [client] catalog:

[client] 
ssl-cert=/etc/mysql_ssl_certs/client-cert.pem
ssl-key=/etc/mysql_ssl_certs/client-key.pem

Restart MySQL and test again:

[root@linuxsrv3 ~]# mysql -u ssluser -p 
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g. 
Your MySQL connection id is 2 Server version: 5.6.17-log MySQL Community Server (GPL) 
mysql> SHOW STATUS LIKE 'Ssl_cipher'; 
+---------------+--------------------+ 
| Variable_name | Value | 
+---------------+--------------------+ | 
Ssl_cipher | DHE-RSA-AES256-SHA | 
+---------------+--------------------+ 
1 row in set (0,08 sec)

That’s all. As you see our client Connection over ssluser is using SSL connection. In Final stage of our article o want to show some coding examples for developers. I use Python 2.7.6 with official mysql-connector package installed. To make article little i paste this codes on gist. So here is the first file is mysql_connection_without_ssl.py: mysql_connection_without_ssl.py
Note: There is an infinite loop inside code just for showing what is unsecured connection like.
Run this Python script(it will select from country table) and on another terminal run ngrep to read packages:

sh@sh-work:~$ sudo ngrep 
649.San Miguel.SLV.San Miguel.127696-....650.Nueva San Salvado r.SLV.La Libertad.98400!....651.Apopa.SLV.San Salvador.88800.....652.Asmara .ERI.Maekel.431000.....653.Madrid.ESP.Madrid.2879052$....654.Barcelona.ESP. Katalonia.1503451!....655.Valencia.ESP.Valencia.739412!....656.Sevilla.ESP. Andalusia.701927!....657.Zaragoza.ESP.Aragonia.603367!....658.M..laga.ESP.A ndalusia.530553.....659.Bilbao.ESP.Baskimaa.3575899....660.Las Palmas de Gr an Canaria.ESP.Canary Islands.354757.....661.Murcia.ESP.Murcia.353504)....6 62.Palma de Mallorca.ESP.Balears.326993-....663.Valladolid.ESP.Castilla and Le..n.319998"....664.C..rdoba.ESP.Andalusia.311708.....665.Vigo.ESP.Galici 

As you see we can see what is retrieved from our select statement. And know lets try The another script which uses SSL: mysql_connection_with_ssl.py Run This Python script. To do this you will have to copy certificates from remote server to local and ofcourse edit my.cnf on local machine to reflect to new certificates. So as you see from Python code we specify paths where certificates reside on our local machine.

root@sh-work:/home/sh/mysql-ssl# ls -l 
total 32 
-rw-r--r-- 1 root root 1424 May 27 18:39 ca-cert.pem 
-rw-r--r-- 1 root root 1679 May 27 18:39 ca-key.pem 
-rw-r--r-- 1 root root 1298 May 27 18:39 client-cert.pem 
-rw-r--r-- 1 root root 1675 May 27 18:39 client-key.pem 
-rw-r--r-- 1 root root 1127 May 27 18:39 client-req.pem 
-rw-r--r-- 1 root root 1298 May 27 18:39 server-cert.pem 
-rw-r--r-- 1 root root 1679 May 27 18:39 server-key.pem 
-rw-r--r-- 1 root root 1127 May 27 18:39 server-req.pem

Run Python script and on another terminal run ngrep again to read packages:

sh@sh-work:~$ sudo ngrep 
.OV.*z.S..~8j6.`.. W..j...f.X......0...S....#..d...E%.R.7..?3...<..d..C..T.....D...T.....=_......V..UI+.0s...- .....-.P...B.N,.&.....EF.t..E.e....im.P.....v.A.H...d.h..T..,.H..h.+..........(....F.....K.^.Y..).R.g.........Lh.Zq.0..........|S.......4z .-X.LWN.......q....N.D....^8r...p..x+si.e..`.,P.t..7...i.{c...>)...~...7..X..EC.E...,..W+..ik.58...%r.K..lwG8t'../..(A.A...u...'.VygP&..o ...(!..)..RH....B.Y.[..nD~S....~.*kUC.1....'.g.........k.......D.......^%4.^u....!U.gY.@.hGK&.-..Z8N."./..........1....5.g..N.........2t ..c.U.....W{.&g'...m...........<5d.x.=..$@..0x#.Y.%.......M.........!.....V,.k.2...,.r..:..1..CU.4.y.._.4#.z=.R..t....F......'z~....Lf.ks5 .tX..I...Z...]....)|Q.(.]$....v.P.U.bS..9..l...*].AY...K..o..* .C.%..q...}[.>..3.[..D....v.s.....$..q_.f...E...P8.Sg.Y..~)N..j#.$A..j.[Gm( ....A..`..._.]+.....S....&.k....v...}.f.....4...H.`..%.....$K.....=.V..^.A..ck......_>.s.[XE....:.O.!.S.k..J<..r....Y%..ZU....A...or.....D |l.^[.......H?.........._.NO.m..i.2*Kl

I think you see the difference 🙂 Thank you for reading if you have any questions feel free to ask.

Activate validate_password Plugin

Recently i encounter an uprising among PHP Developers that , they must write a script additionally checking the strength of MySQL user password at creation time. So, just for note that there is ready to use plugin in MySQL named: validate_password.so The file location is default in plugin_dir in Linux:

mysql> select @@plugin_dir; 
+--------------------------+ 
| @@plugin_dir | 
+--------------------------+ 
| /usr/lib64/mysql/plugin/ | 
+--------------------------+ 
1 row in set (0,00 sec) 

List this directory:

[root@linuxsrv3 plugin]# ls 
adt_null.so auth_test_plugin.so innodb_engine.so mypluglib.so qa_auth_server.so validate_password.so auth.so daemon_example.ini libdaemon_example.so qa_auth_client.so semisync_master.so auth_socket.so debug libmemcached.so qa_auth_interface.so semisync_slave.so

As you see there is a validate_password.so. So what is the defaults of this plugin and how it works? In General if this plugin activated, User will only be able to create passwords with following specifications: 1. Greater or equal to 8 char length. 2. At least 1 number 3. At least 1 non-letter non-number (eg. #, % ,!) 4. At least 1 Upper case letter So the sample password will be: “Sh@rkp45!” or something similar. All weak passwords such as “12345” , “acbsg54” will not allowed to create:

mysql> create user "nc"@"%" identified by '12345'; 
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 

Question is, will i be able to connect with already created user before activating plugin? Answer is: YES. So how to activate plugin. Simply add this to my.cnf file under [mysqld] catalog:

    # Password Validation # 

    plugin-load=validate_password.so 
    validate-password=FORCE_PLUS_PERMANENT 

Restart Server. And that’s all. Force + Permanent will guarantee that plugin can not be disabled at run time or while running MySQL. If you want to change default values for this plugin Read documentation: Validate Password Plugin

How to Solve Replication lag? quick solution

This topic is not for all situations but in my situation it helps. The problem is with Slave is lagging behind master in very large numbers and the relay logs are continously growing.

Slave is using: Relay_Log_File: mysql-relay-bin.000031.
But in folder where relay log resides, was created up to: mysql-relay-bin.000135 And it is continously growing.
So what i decide to do, again step-by-step i edited my.cnf file as follows:

1. I have 13 database so give to slave paralel workers 13: slave-parallel-workers = 13

2. Disabled sync_binlog: sync_binlog = 0

3. Changed at_trx_commit from 1 to 0: innodb_flush_log_at_trx_commit = 0

4. Gave log_at_timeout (only from 5.6.6>) to 10: innodb_flush_log_at_timeout = 10

5. Disabled slow query log( or commented out)

So Slave lag was somewhat: Seconds_Behind_Master: 250350 And it was continously increasing but now it is dicreasing: Seconds_Behind_Master: 178860

Again be carefull with this options. And After Slave catchs up Master undo these changes.

Audit MySQL? no just Crash it :)

Auditing maybe the main part of a corporate structure that wants to know everything. Searching for MySQL auditing plugins we come up to 3 known plugins: 1. MySQL Enterprise Audit (Official one from Oracle) – commercial 2. MariaDB audit plugin for MySQL (version 1.1.6) – free 3. McAfee MySQL audit plugin – free
With a great happiness tried to test this plugins. But wait i don’t want to crash my MySQL just want to install and use these plugins. Let’s begin with MariaDB audit plugin for MySQL — at this time the latest version is 1.1.6 With a great effort in SkySQL site they provide us with a tutorial of how to activate this plugin. Check it: SkySQL Tutorial about Plugin Followed all instructions from tutorial and got a crash of MySQL 5.6.17. So there was an error messages with 2 statement:

    INSTALL PLUGIN server_audit SONAME 'server_audit.so';
    ERROR 2013 (HY000): Lost connection to MySQL server during query 

And

    SET GLOBAL server_audit_logging=ON; 
    ERROR 2013 (HY000): Lost connection to MySQL server during query 

From Web APP side the error message is something more interesting:

    SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'reading initial communication packet',

This is a same error message that we saw after Errcode: 28 – No space left on device.
Reported but still waiting as open 🙂 — 72437.

So The State of my server is very critical and actually i lost my lovely MySQL 😦 Killed all mysql processes. Restarted MySQL looked at error log and here you are:

Version: '5.6.17-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 140430 11:21:52 server_audit: MariaDB Audit Plugin version 1.1.6 STARTED. 06:21:52 UTC - mysqld got signal 11 ; . . . Thread pointer: 0x18428270 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f63a0d78e18 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8d6475]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x663084]
/lib64/libpthread.so.0[0x385780f500] /lib64/libc.so.6[0x3a2173259f]
/usr/sbin/mysqld[0xabdd60] /usr/sbin/mysqld[0xabe0a0]
/usr/lib64/mysql/plugin/server_audit.so(logger_open+0x10b)[0x7f639bdf806b]
/usr/lib64/mysql/plugin/server_audit.so(+0x4202)[0x7f639bdf8202]
/usr/lib64/mysql/plugin/server_audit.so(+0x5ccf)[0x7f639bdf9ccf]
/usr/sbin/mysqld(_Z23initialize_audit_pluginP13st_plugin_int+0x4d)[0x68f6ad]
/usr/sbin/mysqld[0x6ea481]
/usr/sbin/mysqld(_Z20mysql_install_pluginP3THDPK19st_mysql_lex_stringS3_+0x43f)[0x6eee7f] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0xf04)[0x6deb14]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x32f)[0x6e2b3f]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x140b)[0x6e403b] 
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xcf)[0x6b0f3f]
/usr/sbin/mysqld(handle_one_connection+0x47)[0x6b1067]
/usr/sbin/mysqld(pfs_spawn_thread+0x12a)[0xabd8da]
/lib64/libpthread.so.0[0x3857807851] /lib64/libc.so.6(clone+0x6d)[0x3a216e811d] . . . 
    2014-04-30 11:21:54 19033 [Note] InnoDB: Starting crash recovery.
    2014-04-30 11:21:54 19033 [Note] InnoDB: Reading tablespace information from the .ibd files... 
    2014-04-30 11:21:55 19033 [Note] InnoDB: Restoring possible half-written data pages 2014-04-30 11:21:55 19033 [Note] InnoDB: from the doublewrite buffer... InnoDB: Doing recovery: scanned up to log sequence number 69056068973 InnoDB: Transaction 246648984 was in the XA prepared state. InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 0 row operations to undo InnoDB: Trx id counter is 246649344 
    2014-04-30 11:21:55 19033 [Note] InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed [/code] Result is was: MariaDB Audit Plugin Crashed my MySQL 5.6.17... Will i use it? Last step was: 140430 14:40:53 server_audit: STOPPED

with all necessary steps that stated here: McAfee Audit Plugin So no more words and just pasting error log portion:

2014-04-30 19:00:48 14571 [Note] Audit Plugin: starting up. Version: 1.0.4 , Revision: 459 (64bit). AUDIT plugin interface version: 769 (0x301). 
2014-04-30 19:00:48 14571 [Note] Audit Plugin: hot patch for: open_tables (0x69bd00) complete. Audit func: 0x7fb366fcad30, Trampoline address: 0x7fb366fcc060 size: 15.
2014-04-30 19:00:48 14571 [Note] Audit Plugin: Done initializing sql command names. status_vars_index: [141], com_status_vars: [0x1215400]. 
2014-04-30 19:00:48 14571 [Note] Audit Plugin: Init completed successfully. 2014-04-30 19:00:48 14571 [Note] Recovering after a crash using /var/lib/mysql/data/mysql-bin 
2014-04-30 19:00:48 14571 [Note] Starting crash recovery... 
2014-04-30 19:00:48 14571 [Note] Crash recovery finished. 
2014-04-30 19:00:48 14571 [Note] Server hostname (bind-address): '*'; port: 3306 2014-04-30 19:00:48 14571 [Note] IPv6 is available. 
2014-04-30 19:00:48 14571 [Note] - '::' resolves to '::'; 
2014-04-30 19:00:48 14571 [Note] Server socket created on IP: '::'. 14:00:48 UTC - mysqld got signal 11 ; Thread pointer: 0x26e2b70 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7fff90422a78 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8d6475]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x663084]
/lib64/libpthread.so.0[0x35b6e0f710]
/usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x0)[0x69bd00] 
/usr/sbin/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LISTbjP19Prelocking_strategy+0x44)[0x69cb74] /usr/sbin/mysqld(_Z10acl_reloadP3THD+0x28f)[0x67c9cf]
/usr/sbin/mysqld(_Z8acl_initb+0x120)[0x67e9d0]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0xa26)[0x59fc96]
/lib64/libc.so.6(__libc_start_main+0xfd)[0x35b6a1ed1d] /usr/sbin/mysqld[0x591949] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0): is an invalid pointer Connection ID (thread ID): 0 Status: NOT_KILLED

Conclusion: 2 of 3 Audit Plugins crashed MySQL 5.6.17. And now Triggers will make us happy or Purchase a commercial product