Lewin.nu MySQL Information

Upgrade checklist

Because my MySQL servers run on other ports than the default, extra care should be taken when upgrading Ubuntu to make sure the proper MySQL upgrade steps are taken.

• Determine the MySQL version in the new Ubuntu version.
• Ideally, read release notes for upgrades between versions.
• Ideally, backup each database using mysqldump.

  There is a script in /home/erl/backup/mysql called backup-all.sh which will backup all databases. Run with sudo, enter MySQL root password 4 times.

  It uses the following command for backups. max_allowed_packet is needed for database 2, which contains big image BLOBs:

  	    mysqldump --all-databases >backup-1.sql --max_allowed_packet=200M --socket=/var/run/mysqld/mysqld1.sock -p
            

• Upgrade Ubuntu
• Run mysql_upgrade as follows, for sockets from 1 to 4:

               sudo mysql_upgrade --socket=/var/run/mysqld/mysqld1.sock -p
            

SSL Certificates

We use SSL to communcate between MySQL servers.

• Certificate Authority. Must be the same on server and client. We're currently using the cacert.org CA.
• Server public key
• Server private key
• Client public key
• Client private key

Renewing certificates

cacert.org certificates expire after a year.

• Generate a certificate request:

  If you find an old csr (certificate request) file for the same server, I think you can re-use it.

  openssl req -new -out cert-fractus-2018.csr -newkey rsa:2048 -nodes

  Answer all questions by just pressing enter, except the CN (Common Name) field, where you fill in the name of the server with a '.lewin.nu' domain.

  This will createa a certificate request in the file cert-fractus-2018.csr.

• Submit the CSR

  Log into the cacert.org site, select 'Nytt' (new) under the 'Servercertifikat' heading.

  Paste the csr.

  Submit it

• You will be show the new certificate. Copy and paste it to a file called something like 'cert.pem'

Replication

Copying data after replication can be done with a command like this on cumulus:

mysqldump -S /var/run/mysqld/mysqld2.sock --password=<password&rt; --opt --single-transaction --comments --hex-blob --dump-date --no-autocommit --databases drupal_mmbot | mysql -h cirrus.stenius.org --password=<password> --ssl --ssl-ca=/etc/mysql/ca-cert.pem &>dump-dump &