During the summer months (of 2016) we upgraded our main databases from MySQL 5.5 to 5.6. We’d like to share our learnings.
We were very happy with MySQL 5.5 (running on FreeBSD 10 with ZFS), but we wanted to utilize the online DDL capabilities of MySQL 5.6. So we decided to start planning for an upgrade.
Before doing the upgrade we:
- Looked for the changes Affecting Upgrades to MySQL 5.6
- Ensured our MySQL client drivers had full 5.6 support.
- Added MySQL 5.6 support for our binlog replication library.
- Verified that MySQL 5.6 could replicate from a MySQL 5.5 instance.
- Simulated the upgrade on small VM instances with identical software but not the full data set.
Since the simulated upgrade couldn’t use the full data sets, we upgraded our replica MySQL database first to have a chance to see how it performed with a full data set. By starting with the replica we kept the option to abort/revert the upgrade for as long as possible. A revert on the replica would be as simple as just promoting a snapshot and waiting a few minutes for the replica machine to catch up. Once upgraded on the primary instance however, it would be much harder to abort.
Advice 1: Ensure the binlog clients can read old binlogs
When it was time for the primary MySQL instance, we had an issue with our binlog replication system causing a delay in replicating new data to our search index (outage incident). Basically it was caused by having old 5.5 binlog events without CRC data on the server, which caused the checksum code in our binlog reader to choke on the events written before the switch.
We skipped the old binlog events to minimize the indexing delays (the indices are kept up-to-date by listening on the binlog), then backfilled the skipped rows manually to the downstream services.
There’s an issue at our binlog library with some more details about this problem.
Advice 2: Remember to rewrite date time columns
Once all servers ran MySQL 5.6 we needed to rewrite tables with time-data. Since most of our tables have at least one timestamp field we needed to rewrite most of our data. To plan for this we found this great query in a blog comment, and improved it slightly to see a row count estimate.
For our large tables we had to first migrate the replica, promote the replica to primary, then migrate the other instance. Small tables could be migrated on the primary machine by just pausing the applications using it (gracefully degrading some services).
That’s it, the upgrade went pretty smooth even though we had lots of different MySQL clients (versions, Ruby, .NET etc.) and a pretty tight integration in the binlog client library.