As you might have noticed my blog was down last night for about 1 hour and 45 minutes between 22:20 and 00:10. A bit longer than I wanted but I needed more time do deal with the upgrade of MySQL as part of the routine maintenance I do on my WordPress blog server.
In the environments under my care I take care to take the time to do routine maintenance to avoid falling behind to much in firmware, drivers, patches, etc. This takes some effort but as it helps prevent bigger issues in the long run it’s worth while to do so. I take the same approach with my blog as much as possible. Most of this maintenance goes by without you ever noticing. The windows updates reboots being the exception. WordPress upgrades, plugin upgrades, PHP upgrades, etc. … all go swiftly usually which means I’m pretty well covered there, frequently.
Upgrading MySQL however is always a bit of a time consuming effort and depending on what version you’re upgrading from and to witch one it can actually mean multiple sequential upgrades (5.1 to 5.5.44 to 5.6.25).
I practiced this upgrade on a copy of the VM in azure to make sure I could handle whatever came up and still I had to deal with some challenges I did not encounter in the test environment. That show that I’m not a full time hard core MySQL guru I guess.
Anyway after getting to MySQL 5.6.25 from 5.5.44 and fixing some issues with TIMESTAMP with implicit DEFAULT value is deprecated (easy fix) and dealing with the error in MySQL Workbench:
An unhandled exception occurred (Error executing ‘SELECT t.PROCESSLIST_ID,
IF (NAME = ‘thread/sql/event_scheduler’,’event_scheduler’,t.PROCESSLIST_USER) PROCESSLIST_USER,t.PROCESSLIST_HOST,t.PROCESSLIST_DB,t.PROCESSLIST_COMMAND,
t.INSTRUMENTED,t.PROCESSLIST_INFO,a.ATTR_VALUE FROM performance_schema.threads t
LEFT OUTER JOIN performance_schema.session_connect_attrs a ON t.processlist_id = a.processlist_id AND (
a.attr_name IS NULL OR a.attr_name = ‘program_name’) WHERE t.TYPE <> ‘BACKGROUND”
Native table ‘performance_schema’.’threads’ has the wrong structure.
SQL Error: 1682). Please refer to the log files for details.
which I fixed by running run mysqld –performance_schema I’m rocking everything up to date once more.
Always have good backups, make exports of your database schema, data and structures in MySQL and have multiple ways out when things go south. In Azure I’m relying on Backup Vault where I protect my virtual machine with schedules backup jobs. I also backup my WordPress with the data via a plug in and export the database via MySQL Workbench.
Those dumps are copied out of the VM to where ever I want (Azure, One Drive, home PC, a VM running in AWS …) to make sure I have multiple options to recover.
VEEAM FastSCP for Microsoft Azure comes in very handy for this by the way. You might want to check it out if you’re in need of an automated and secure way to get data out of a VM running in Microsoft Azure!