CRIN Trac: Ticket #114: Update TLS certs for MySQL

hours changed; totalhours set

chris — Fri, 05 May 2017 12:47:16 GMT

hours changed from 0 to 0.5
totalhours set to 0.5

The TLS certs were originally set up on #8, looking at the existing config files, this is what we have on Crin1 in /etc/mysql/my.cnf:

ssl=on
ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=/etc/ssl/cacert/cacert.pem
ssl-cert=/etc/ssl/cacert/crin1_cert.pem
ssl-key=/etc/ssl/cacert/crin1_yassl_privatekey.pem

And these are the files we have on Crin1:

ls -lah /etc/ssl/cacert/
total 60K
drwxr-xr-x 3 root mysql 4.0K May  8  2015 .
drwxr-xr-x 6 root root  4.0K Jan 27 20:16 ..
-rw-r--r-- 1 root mysql 5.1K May  3  2015 cacert.pem
-rw------- 1 root mysql 2.6K May 23  2011 class3.crt
-rw-r--r-- 1 root mysql 2.0K May  8  2015 crin1_cert.pem
-rw-r--r-- 1 root mysql 1.1K May  8  2015 crin1_csr.pem
-rw-r--r-- 1 root mysql 1.7K May  8  2015 crin1_privatekey.pem
-rw-r--r-- 1 root mysql 1.7K May  8  2015 crin1_yassl_privatekey.pem
-rw-r--r-- 1 root mysql 2.0K May  8  2015 crin2_cert.pem
-rw-r--r-- 1 root mysql 1.1K May  8  2015 crin2_csr.pem
-rw-r--r-- 1 root mysql 1.7K May  8  2015 crin2_privatekey.pem
-rw-r--r-- 1 root mysql 1.7K May  8  2015 crin2_yassl_privatekey.pem
drwxr-xr-x 2 root root  4.0K May  8  2015 old
-rw------- 1 root mysql 2.6K Oct 11  2004 root.crt

And these are the files on Crin2:

ls /etc/ssl/cacert/ -lah
total 68K
drwxr-xr-x 3 root www-data 4.0K May  8  2015 .
drwxr-xr-x 6 root root     4.0K Jan 27 20:17 ..
-rw-r--r-- 1 root www-data 5.1K May  3  2015 cacert.pem
-rw-r--r-- 1 root www-data 2.6K May 23  2011 class3.crt
-rw-r--r-- 1 root www-data 2.0K May  8  2015 crin1_cert.pem
-rw-r--r-- 1 root www-data 1.1K May  8  2015 crin1_csr.pem
-rw-r--r-- 1 root www-data 1.7K May  8  2015 crin1_privatekey.pem
-rw-r--r-- 1 root www-data 1.7K May  8  2015 crin1_yassl_privatekey.pem
-rw-r--r-- 1 root www-data 7.1K May  8  2015 crin2_cert.chained.pem
-rw-r--r-- 1 root www-data 2.0K May  8  2015 crin2_cert.pem
-rw-r--r-- 1 root www-data 1.1K May  8  2015 crin2_csr.pem
-rw-r--r-- 1 root www-data 1.7K May  8  2015 crin2_privatekey.pem
-rw-r--r-- 1 root www-data 1.7K May  8  2015 crin2_yassl_privatekey.pem
drwxr-xr-x 2 root root     4.0K May  8  2015 old
-rw-r--r-- 1 root www-data 2.6K Oct 11  2004 root.crt

And on Crin2 we have this /root/.my.cnf file (passwd redacted)

[client]
host=crin1
user = root
password = XXX
ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=/etc/ssl/cacert/cacert.pem
ssl-cert=/etc/ssl/cacert/crin1_cert.pem
ssl-key=/etc/ssl/cacert/crin1_yassl_privatekey.pem

And on Crin4 the /root/.my.cnf file:

[client]
host=crin1
ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=/etc/ssl/cacert/cacert.pem
ssl-cert=/etc/ssl/cacert/crin1_cert.pem
ssl-key=/etc/ssl/cacert/crin1_yassl_privatekey.pem

Checking the names in certs:

aptitude install gnutls-bin
certtool -i < crin1_cert.pem
        Subject: CN=crin1.crin.org
                Subject Alternative Name (not critical):
                        DNSname: crin1.crin.org
                        XMPP Address: crin1.crin.org
                        DNSname: *.crin1.crin.org
                        XMPP Address: *.crin1.crin.org
                        DNSname: *.crin.org
                        XMPP Address: *.crin.org
                        DNSname: crin1.webarch.net
                        XMPP Address: crin1.webarch.net
                        DNSname: *.crin1.webarch.net
                        XMPP Address: *.crin1.webarch.net
certtool -i < crin2_cert.pem
        Subject: CN=crin2.crin.org
                Subject Alternative Name (not critical):
                        DNSname: crin2.crin.org
                        XMPP Address: crin2.crin.org
                        DNSname: *.crin2.crin.org
                        XMPP Address: *.crin2.crin.org
                        DNSname: *.crin.org
                        XMPP Address: *.crin.org
                        DNSname: crin2.webarch.net
                        XMPP Address: crin2.webarch.net
                        DNSname: *.crin2.webarch.net
                        XMPP Address: *.crin2.webarch.net
certtool -i < crin4_cert.pem
        Subject: CN=crin4.crin.org
                Subject Alternative Name (not critical):
                        DNSname: crin4.crin.org
                        XMPP Address: crin4.crin.org
                        DNSname: *.crin4.crin.org
                        XMPP Address: *.crin4.crin.org
                        DNSname: dev.crin.org
                        XMPP Address: dev.crin.org
                        DNSname: stage.crin.org
                        XMPP Address: stage.crin.org
                        DNSname: crin4.webarch.net
                        XMPP Address: crin4.webarch.net
                        DNSname: *.crin4.webarch.net
                        XMPP Address: *.crin4.webarch.net

So what needs to be done:

Create a /etc/ssl/cacert/new directory for all the new files
Generate new keys and certs with matching Subject and subjectAltNames
Generate the yassl and chained versions
rsync everything between all the servers
switch the old files for the new files
restart mysqld on Crin1

hours, totalhours changed

chris — Fri, 05 May 2017 14:03:38 GMT

hours changed from 0 to 1.25
totalhours changed from 0.5 to 1.75

So generating certs on Crin1:

cd /etc/ssl/cacert/
mkdir new
cd new
/root/bin/csr
Short Hostname (ie. imap big_srv www2): crin1
FQDN/CommonName (ie. www.example.com) : crin1.crin.org
Type SubjectAltNames for the certificate, one per line. Enter a blank line to finish
SubjectAltName: DNS:crin1.crin.org
SubjectAltName: DNS:*.crin1.crin.org
SubjectAltName: DNS:*.crin.org
SubjectAltName: DNS:crin1.webarch.net
SubjectAltName: DNS:*.crin1.webarch.net
vi crin1_cert.pem
mv /root/crin1_privatekey.pem .
/root/bin/csr
Short Hostname (ie. imap big_srv www2): crin2
FQDN/CommonName (ie. www.example.com) : crin2.crin.org
Type SubjectAltNames for the certificate, one per line. Enter a blank line to finish
SubjectAltName: DNS:crin2.crin.org
SubjectAltName: DNS:*.crin2.crin.org
SubjectAltName: DNS:*.crin.org
SubjectAltName: DNS:crin2.webarch.net
SubjectAltName: DNS:*.crin2.webarch.net
vi crin2_cert.pem
mv /root/crin2_privatekey.pem .
/root/bin/csr
Short Hostname (ie. imap big_srv www2): crin4
FQDN/CommonName (ie. www.example.com) : crin4.crin.org
Type SubjectAltNames for the certificate, one per line. Enter a blank line to finish
SubjectAltName: DNS:crin4.crin.org
SubjectAltName: DNS:*.crin4.crin.org
SubjectAltName: DNS:dev.crin.org
SubjectAltName: DNS:stage.crin.org
SubjectAltName: DNS:crin4.webarch.net
SubjectAltName: DNS:*.crin4.webarch.net
vi crin4_cert.pem
mv /root/crin4_privatekey.pem .
openssl rsa -in crin1_privatekey.pem -out crin1_yassl_privatekey.pem
openssl rsa -in crin2_privatekey.pem -out crin2_yassl_privatekey.pem
openssl rsa -in crin4_privatekey.pem -out crin4_yassl_privatekey.pem
wget https://www.cacert.org/certs/root.crt --no-check-certificate
wget https://www.cacert.org/certs/class3.crt --no-check-certificate
cat root.crt > cacert.pem
cat class3.crt >> cacert.pem
cat crin2_cert.pem > crin2_cert.chained.pem
cat cacert.pem >> crin2_cert.chained.pem
cat crin4_cert.pem > crin4_cert.chained.pem
cat cacert.pem >> crin4_cert.chained.pem
chown root:mysql *.*
chmod 644 *.*

And now we should have a full set of files:

root@crin1:/etc/ssl/cacert/new# ls -lah
total 76K
drwxr-xr-x 2 root root  4.0K May  5 13:23 .
drwxr-xr-x 4 root mysql 4.0K May  5 12:50 ..
-rw-r--r-- 1 root mysql 5.1K May  5 13:22 cacert.pem
-rw-r--r-- 1 root mysql 2.6K May 23  2011 class3.crt
-rw-r--r-- 1 root mysql 2.0K May  5 13:09 crin1_cert.pem
-rw-r--r-- 1 root mysql 1.7K May  5 12:52 crin1_privatekey.pem
-rw-r--r-- 1 root mysql 1.7K May  5 13:23 crin1_yassl_privatekey.pem
-rw-r--r-- 1 root mysql 7.1K May  5 13:22 crin2_cert.chained.pem
-rw-r--r-- 1 root mysql 2.0K May  5 13:12 crin2_cert.pem
-rw-r--r-- 1 root mysql 1.7K May  5 13:11 crin2_privatekey.pem
-rw-r--r-- 1 root mysql 1.7K May  5 13:23 crin2_yassl_privatekey.pem
-rw-r--r-- 1 root mysql 7.2K May  5 13:22 crin4_cert.chained.pem
-rw-r--r-- 1 root mysql 2.1K May  5 13:18 crin4_cert.pem
-rw-r--r-- 1 root mysql 1.7K May  5 13:18 crin4_privatekey.pem
-rw-r--r-- 1 root mysql 1.7K May  5 13:23 crin4_yassl_privatekey.pem
-rw-r--r-- 1 root mysql 2.6K Oct 11  2004 root.crt

Sync them between the servers:

rsync -av /etc/ssl/cacert/new/ crin2:/etc/ssl/cacert/new/
rsync -av /etc/ssl/cacert/new/ crin4:/etc/ssl/cacert/new/

So first stop the MySQL server on Crin1:

/etc/init.d/mysql stop

And now the commands to make them live on each server:

cd /etc/ssl/cacert
mv crin* old/
mv new/* .

And restart MySQL on Crin1:

/etc/init.d/mysql start

And to test from Crin2:

mysql -h crin1 -u root newprod

And in the event of that not working, we might need to revert, if we do the commands:

cd /etc/ssl/cacert
mv crin* new/
mv old/crin* .

But thankfully that wasn't needed and it restarted OK and the site is fine.

Checked the Munin stats:

https://munin.crin.org/munin/crin.org/crin1.crin.org/index.html#mysql2

And the results of mysqltuner:

mysqltuner
 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.55-0+deb8u1-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 7G (Tables: 1569)
[--] Data in MyISAM tables: 1G (Tables: 13)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 1570
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1m 52s (11K q [104.268 qps], 331 conn, TX: 11M, RX: 2M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 7.5G global + 2.7M per thread (151 max threads)
[!!] Maximum possible memory usage: 7.9G (101% of installed RAM)
[OK] Slow queries: 0% (54/11K)
[OK] Highest usage of available connections: 35% (54/151)
[OK] Key buffer size / total MyISAM indexes: 1.0G/458.3M
[OK] Key buffer hit rate: 100.0% (39 cached / 0 reads)
[OK] Query cache efficiency: 68.1% (7K cached / 10K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 897 sorts)
[!!] Temporary tables created on disk: 31% (186 on disk / 594 total)
[OK] Thread cache hit rate: 79% (68 created / 331 connections)
[!!] Table cache hit rate: 4% (64 open / 1K opened)
[OK] Open file limit used: 0% (3/1K)
[OK] Table locks acquired immediately: 100% (5K immediate / 5K locks)
[!!] InnoDB  buffer pool / data size: 6.0G/7.5G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 64)
    innodb_buffer_pool_size (>= 7G)