How we migrated from MySQL 5.0 to Google Cloud SQL MySQL 5.6

It’s one of those things that most developers fear, maybe even hate. Yet, it’s inevitable; migrating and/or updating databases. Every once in a while there comes a time that you are forced to do it. And when that day comes, you are gonna fall. You’ll want to throw your computer out of the window. Hard. You’ll also spend countless hours on some 1990 webpages containing a ‘maybe-useful’ answer in the bottom comments. You’ll ask yourself: “why is such an easy job so difficult? “. That’s the reason for this article. There were some things we learned which could help other developers when they are trying to migrate or update databases in the future. My fellow developers: this one’s for you.

Our ‘old’ database was running MySQL 5.0. Our ‘new’ Google Cloud SQL database would be running on MySQL 5.6. At first we even started with a new database that was running MySQL 5.7, but we soon found out that there are some bigger restrictions to 5.7 (related to default values of columns) which caused us to postpone that update for just a little while. A developer can only handle so much, right? ;)


Migrating the data

To migrate all data, we used the tool MySQL Workbench. With that tool you can easily connect to your database server and, using the ‘Database Migration’ utility, migrate that data to a new database. It involves somewhere around 8 times of clicking the ‘next’ button, the only real important step is that you select the correct source and target databases in step 1 and 2.


Converting MyISAM tables to InnoDB

We ran into the problem that our new database only supported InnoDB tables. We were still using MyISAM tables, so we needed to convert them. First, we needed to identify which tables should be converted. Then, they should be converted. Our bash script looked a little something like this:

This loops through all the databases and their tables, and checks if they should be converted into InnoDB

Changing the db.ini

We then changed our db.ini file to match the new IP address of the Google Cloud SQL database, and their user and password credentials, et voila!


Common (or not so common) pitfalls

While writing this article, it seems to be so simple. However, we went through some (hellish) steps before we came to this short list. Here are some common pitfalls.

  • Do not use your own mysqldump scripts to export/import your datasets, use a tool like MySQL Workbench which provides you feedback on what’s going on.
  • Take small steps while migrating or updating databases. We wanted to go from 5.0 to 5.7 at once, but this causes greater incompatibility issues then when taking smaller increases.
  • Always keep a backup of your database
  • Reserve time. For big datasets, it will take up time. Our final migration process took about 4 to 5 hours in total.

Hopefully this article helps you a little bit towards successfully migrating/updating your database. If you have any questions, feel free to leave a comment.

www.brandbox.io