Opened 16 months ago
Last modified 16 months ago
#114 new defect
Update TLS certs for MySQL
| Reported by: | chris | Owned by: | chris |
|---|---|---|---|
| Priority: | major | Milestone: | Maintenance |
| Component: | crin1 | Version: | |
| Keywords: | Cc: | russell, peter | |
| Estimated Number of Hours: | 0 | Add Hours to Ticket: | 0 |
| Billable?: | yes | Total Hours: | 1.75 |
Description
The TLS certs for MySQL connections between the servers are due to expire on 7th May 2017 so need to be updated.
Peter, Russell, I have added you just so you know this is happening, it'll involve restarting MySQL when the new keys and certs are in place, so there might be a few moments of site downtime, but I'll do this on the dev/staging sever first.
Change History (2)
comment:1 Changed 16 months ago by chris
- Add Hours to Ticket changed from 0 to 0.5
- Total Hours set to 0.5
comment:2 Changed 16 months ago by chris
- Add Hours to Ticket changed from 0 to 1.25
- Total Hours 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:
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)
Note: See
TracTickets for help on using
tickets.
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:
And these are the files we have on Crin1:
And these are the files on Crin2:
And on Crin2 we have this /root/.my.cnf file (passwd redacted)
And on Crin4 the /root/.my.cnf file:
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.netSo what needs to be done: