In our company we run six MySQL database servers with several schemas and a mix of InnoDB and MyISAM tables. Until recently we still ran MySQL version 5.5, it was time to update. I have written our experiences during our upgrade process in this blog post to possibly save you some time and share the knowledge we gained.
Worth noting is our servers all run in a replication topology for redundancy and hold about 1.5TB of data.
Caveat 1: To upgrade to MySQL version 5.7 you have to first update all servers to 5.6 otherwise your replication breaks. You can’t replicate between servers who differ more than one major release.
Caveat 2: In MySQL 5.6.13 and later you can’t have invalid dates in your timestamp, datetime and date columns. It will break your replication if you do statement based replication from a server running 5.5 to 5.6. Before this version you could have invalid ‘0000-00-00’ dates. See the incompatible change described in the documentation. So you have to check your tables for such dates.
Our webservers run Ruby on Rails and therefor basically each and every single table we have has at least two datetime columns (created_at and updated_at). Because we have over 400 tables I created a little Rails script to check all our tables for zero dates. We did find several records with this ‘zero’ date and just set them to ‘1900-01-01’. We did this on the master server which replicated this down to all slaves.
With the zero dates fixed we started upgrading from 5.5 to 5.6. We started with the last replication slaves and worked up to the masters. We have a master – master setup and each master has multiple slaves chained.
Caveat 3: MySQL version 5.6 sets the binlog_checksum default to CRC32. As we started upgrading the standby master replication between them failed.
Since the masters are both slave and master to each other replication started flowing from a 5.6 version to a 5.5 version. The master still running on version 5.5 could not cope with the CRC32 checksums.
Setting the binlog_checksum setting to NONE in the 5.6 (standby) master solved it. In our case we could flush the logs and have the 5.5 (active) master skip replication to the last point on the standby master and pick up from there. After also upgrading the active master we could set the binlog_checksum back to CRC32.
Now the tricky part starts. Before upgrading to 5.7 we need to deal with the new temporal storage type.
Caveat 4: Since MySQL 5.6.4 temporal types can hold fractional parts. This means the storage of columns with time changes. To ‘upgrade’ your table you just can run this command on your tables:
ALTER TABLE <table_name> FORCE;
As part of the 5.7 upgrade this will be done automatically.
Now do we want to risk our database to choke on such upgrade while processing 1.5TB and tables with over a billion records?… nah don’t think so.
To show tables having the old or new temporal storage type you can set a global variable
SET GLOBAL show_old_temporals = 'ON';
When you now use a describe table instruction or query the INFORMATION_SCHEMA you will see it if the old format is used in the table.
SELECT table_schema, table_name, column_name, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE (data_type LIKE '%date%' OR data_type LIKE '%stamp%');
To do this upgrade on our master server in full production on these big tables we made use of the Percona pt-online-schema-change script. Here is how we used it
pt-online-schema-change --alter "FORCE" D=archive,t=device_usage_2016 --ask-pass --no-drop-old-table --recursion-method none
The script does all kinds of checks to not be too intrusive and hog down on resources. One of these checks is to see if replica’s are not lagging too much behind. Because we did not care about this lagging we disabled this constraint. And we wanted to keep the old tables for now, just in case.
Merge tables: We also have merge tables and handling these was very tricky. You can’t have tables with the old date format and tables with the new format in a merge table. In order to upgrade your table you have to take it out of the merge table. But you can’t put it back.
Make a mistake and your merge table will be unusable until all underlying tables are converted. When that happens there is nothing else to do then dropping and re-creating your merge table. But your merge table definition will then also have the new temporal format and will not work with tables having the old format.
The best startegy with tables part of a merge table is to first convert all underlying tables into new tables. Then have rename scripts ready to do the switch for all tables at once. Therefore use 2 extra options with the percona script to not swap and not drop new tables:
--no-drop-new-table --no-swap-tables
A pretty cool feature of the script is that it creates triggers on the old table that will copy changes to the new table while they are not swapped. So when the tables are converted you are ready to drop the merge table, swap the underlying tables and recreate the merge table
-- drop merge table -- rename tables to old -- rename new tables to normal -- recreate merge table
This whole process of converting every table (we have over 400 of them) to the new temporal format took a long time to finish due to the amount of tables. Small tables can be done using the simple ALTER TABLE <table_name> FORCE; instruction but larger tables not.
What is a small or large table? It depends on your hardware and your table and index structure. For us we defined a large table to be handled with the Percona script as one with about 100k records or more and several GB’s of data or more. And every table actively written to of course.
We have one schema with about 1.2TB of mostly archive data in total in many tables. Most tables are organised in merge tables. This schema has only MyISAM tables. The other two schema’s have each about 100GB to 150GB of data and have InnoDB tables actively written to.
Once all tables were converted we made make sure to drop all the old tables that were still left by using the –no-drop-old-table option. Otherwise the database would still choke in them 😉
Now we were ready to run the upgrade of 5.6 to 5.7 and it went fine. Again starting with last replica’s in the chain working up to the masters. No surprises this time anymore.
Good luck.
PS Next change will be to enable MySQL GTID’s for replication.
Jerry Logic 