Migrating MySQL databases

I was tasked with migrating mysql server between two cloud providers, and boy, this is less polished than in postgresql.

Here are some notes:

  • The best way to do backups is to use mysql-workbench graphical tool, so I can recommend it to you. I couldn't use it as server was (almost literally) on the other side of the world, and latency really kills mysql-workbench usability.

  • To dump users to sql file you need to create a custom script (since I had only handful of users I decided to just do it by hand).

    You script would need to:

    1. Read all users and store their login, host, and password hash to mysql.users table.
    2. For each user execute SHOW GRANTS 'user'@'host, and store them to a file.

    And if you are writing such tool than:

    1. Let me know ;)
    2. There is a ton of bash monstrosities that dump grants, like this one: https://serverfault.com/q/8860/3441.
  • Under no circumstances try to restore mysql database, it does contain users, passwords, and grants (which are kinda hard to backup otherwise) but it also contains a lot of stuff that is very intimately tied to exact version of mysql you use.

    Dumping and restoring it will be pain.

    Probably this could work if you have exactly the same server version, but I wouldn't risk it (and if you have exactly the same server version just use physical backup --- will explain later).

  • mysqldump --all-databases is useless as it dumps all databases including the mysql one, which is a world of pain.

    To dump everything except mysql (and users!) just do some grotesque bash oneliner that loops over all databases.

  • Especially dont mysqldump --all-databases if you are also moving data between versions.

If this was feasible I'd probably do physical backup, that is: copy data dir between servers, I couldn't do it as I was using managed db on the one side, and I didn't have direct access to the server.