## ## Oracle Linux 6 ## Build MySQL 5.6.10 cmake -DCMAKE_INSTALL_PREFIX=/home/ushastry/mybuilds/mysql-5.6.10 -DWITH_DEBUG=ON -DWITH_SSL=system -DENABLE_DOWNLOADS=1 make && make install ## Sample certs/key etc as per doc - http://dev.mysql.com/doc/refman/5.6/en/creating-ssl-certs.html [root@ushastry 5610_]# rm -rf newcerts/ [root@ushastry 5610_]# su mysql [mysql@ushastry 5610_]$ mkdir newcerts && cd newcerts [mysql@ushastry newcerts]$ openssl genrsa 2048 > ca-key.pem Generating RSA private key, 2048 bit long modulus .......................................................................+++ .....+++ e is 65537 (0x10001) [mysql@ushastry newcerts]$ 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]:IN State or Province Name (full name) []:KA Locality Name (eg, city) [Default City]: Organization Name (eg, company) [Default Company Ltd]: Organizational Unit Name (eg, section) []:MySQL Common Name (eg, your name or your server's hostname) []:umesh Email Address []:ushastry@gmail.com [mysql@ushastry newcerts]$ [mysql@ushastry newcerts]$ 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]:IN State or Province Name (full name) []:KA Locality Name (eg, city) [Default City]:Bangalore Organization Name (eg, company) [Default Company Ltd]:Oracle Organizational Unit Name (eg, section) []:Support Common Name (eg, your name or your server's hostname) []:shastry Email Address []:umesh.shastry@oracle.com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:who are you An optional company name []: [mysql@ushastry newcerts]$ openssl rsa -in server-key.pem -out server-key.pem writing RSA key [mysql@ushastry newcerts]$ 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=IN/ST=KA/L=Bangalore/O=Oracle/OU=Support/CN=shastry/emailAddress=umesh.shastry@oracle.com Getting CA Private Key [mysql@ushastry newcerts]$ 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]:IN State or Province Name (full name) []:MH Locality Name (eg, city) [Default City]:Pune Organization Name (eg, company) [Default Company Ltd]:Oracle Organizational Unit Name (eg, section) []:support Common Name (eg, your name or your server's hostname) []:mytest Email Address []:ushastry@yahoo.com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:who am i An optional company name []: [mysql@ushastry newcerts]$ [mysql@ushastry newcerts]$ [mysql@ushastry newcerts]$ openssl rsa -in client-key.pem -out client-key.pem writing RSA key [mysql@ushastry newcerts]$ 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=IN/ST=MH/L=Pune/O=Oracle/OU=support/CN=mytest/emailAddress=ushastry@yahoo.com Getting CA Private Key [mysql@ushastry newcerts]$ [mysql@ushastry newcerts]$ openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OK [mysql@ushastry newcerts]$ ### Bringing up MySQL Enterprise server 5.6.10 [root@ushastry mysql-5.6.10]# bin/mysqld_safe --defaults-file=./my.cnf --user=mysql & [1] 23004 [root@ushastry mysql-5.6.10]# 130417 15:44:44 mysqld_safe Logging to '/tmp/5610_/ushastry.err'. 130417 15:44:44 mysqld_safe Starting mysqld daemon with databases from /tmp/5610_ ## Creating SSL required user account GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'ssluser' REQUIRE SSL; GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'%' IDENTIFIED BY 'ssluser' REQUIRE SSL; ##### MySQL Enterprise 5.6.10 client with the user who require SSL and non-SSL using --ssl-ca, --ssl-cert, --ssl-key can connect without any issues [root@ushastry mysql-5.6.10]# bin/mysql -u ssluser -p --port=3306 --protocol=TCP --ssl-ca=/tmp/5610_/newcerts/ca-cert.pem --ssl-cert=/tmp/5610_/newcerts/server-cert.pem --ssl-key=/tmp/5610_/newcerts/server-key.pem Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.10-debug-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW STATUS LIKE 'Ssl_cipher'; +---------------+--------------------+ | Variable_name | Value | +---------------+--------------------+ | Ssl_cipher | DHE-RSA-AES256-SHA | +---------------+--------------------+ 1 row in set (0.01 sec) mysql> show global variables like 'have%'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | have_compress | YES | | have_crypt | YES | | have_dynamic_loading | YES | | have_geometry | YES | | have_openssl | YES | | have_profiling | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | YES | | have_symlink | YES | +----------------------+-------+ 10 rows in set (0.01 sec) mysql> show global variables like '%ssl%'; +---------------+-------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /tmp/5610_/newcerts/ca-cert.pem | | ssl_capath | | | ssl_cert | /tmp/5610_/newcerts/server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /tmp/5610_/newcerts/server-key.pem | +---------------+-------------------------------------+ 9 rows in set (0.00 sec) mysql> \s -------------- bin/mysql Ver 14.14 Distrib 5.6.10, for Linux (x86_64) using EditLine wrapper Connection id: 3 Current database: Current user: ssluser@localhost SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.10-debug-log Source distribution Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 46 sec Threads: 1 Questions: 6 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.130 -------------- mysql> mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | innodb_version | 1.2.10 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.10-debug-log | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------+ ##### 5.6.10 client with SSL enabled user using --ssl-ca can connect without any issues ##### 5.6.10 client with non-SSL enabled user can connect without any issues [root@ushastry mysql-5.6.10]# bin/mysql -u ssluser -p --port=3306 --protocol=TCP --ssl-ca=/tmp/5610_/newcerts/ca-cert.pem Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.10-debug-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \s -------------- bin/mysql Ver 14.14 Distrib 5.6.10, for Linux (x86_64) using EditLine wrapper Connection id: 4 Current database: Current user: ssluser@localhost SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.10-debug-log Source distribution Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 8 min 21 sec Threads: 1 Questions: 14 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.027 -------------- mysql> SHOW STATUS LIKE 'Ssl_cipher'; +---------------+--------------------+ | Variable_name | Value | +---------------+--------------------+ | Ssl_cipher | DHE-RSA-AES256-SHA | +---------------+--------------------+ 1 row in set (0.00 sec) mysql> ## SSL user with 5.5.30 client to 5.6.10 server with --ssl-ca [ushastry@ushastry mysql-5.5.30]$ bin/mysql -u ssluser -p --port=3306 --protocol=TCP --ssl-ca=/tmp/5610_/newcerts/ca-cert.pem Enter password: ERROR 2026 (HY000): SSL connection error: ASN: bad other signature confirmation [ushastry@ushastry mysql-5.5.30]$ ## Non-SSL user with old client can connect to 5.610 server without any problem [ushastry@ushastry mysql-5.5.30]$ bin/mysql -u root -p --port=3306 --protocol=TCP Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.6.10-debug-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> STATUS; -------------- bin/mysql Ver 14.14 Distrib 5.5.30, for linux2.6 (x86_64) using readline 5.1 Connection id: 18 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.10-debug-log Source distribution Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 1 hour 7 min 13 sec Threads: 1 Questions: 36 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.008 -------------- mysql> ### SSL user trying with MySQL community client 5.6.10 [ushastry@ushastry mysql-com-5.6.10]$ bin/mysql -u ssluser -p --port=3306 --protocol=TCP --ssl-ca=/tmp/5610_/newcerts/ca-cert.pem Enter password: ERROR 2026 (HY000): SSL connection error: ASN: bad other signature confirmation [ushastry@ushastry mysql-com-5.6.10]$ [ushastry@ushastry mysql-com-5.6.10]$ bin/mysql -u root -p --port=3306 --protocol=TCP Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27 Server version: 5.6.10-debug-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> STATUS; -------------- bin/mysql Ver 14.14 Distrib 5.6.10, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 27 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.10-debug-log Source distribution Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 1 hour 59 min 44 sec Threads: 1 Questions: 47 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.006 -------------- mysql> SELECT VERSION(); +------------------+ | VERSION() | +------------------+ | 5.6.10-debug-log | +------------------+ 1 row in set (0.00 sec) mysql>