1

We are running MySQL 5.6 and taking backups using XtraBackup 2.4. We want to migrate to MySQL 5.7 using that same backup as we have datasets well over 600GB.

Is it possible to do so and what are prons and cons that we need to worry about while we use the XtraBackup restore?

Improve this question

2 Answers 2

Reset to default
4

Xtrabackup is a physical backup tool, and is meant to backup and restore to the same MySQL (or MariaDB) version. It's not designed to work with migrations. It might work, it might not.

So if possible, if you want to restore the backup to a new instance, I think the best approach is to restore it to another MySQL 5.6 instance, and then upgrade that instance to 5.7 afterwards.

Improve this answer
1
  • Expanding the answer a little: Xtrabackup tool allows you to take multiple different backup types. Most commonly used is 'xtrabackup' which is a physical copy of the database that host (encrypted); there is another type of backup called mysqldump can be generated from Xtrabackup tool, which can be used to restore "data" to a new host, but you need to sort out the grants yourself on the host, but the restore can take a long time. One path you can take is restore a host using mysqldump, and then generate xtrabackups from the upgrade host with Xtrabackup tool.
    – LegitMe
    Commented Mar 6, 2020 at 22:01
2

GRANTS

Each major release of MySQL changes the MySQL schema in some respects

For example, in the post I answered (MySQL service stops after trying to grant privileges to a user), I mentioned how mysql.user has a different number of columns.

Here is the updated list of the column counts

When you run this query

SELECT COUNT(1) column_count FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

You should the following number

  • If you get 45, MySQL 5.7
  • If you get 43, MySQL 5.6
  • If you get 42, MySQL 5.5
  • If you get 39, MySQL 5.1
  • If you get 37, MySQL 5.0

When you restore a MySQL 5.6 backup into a MySQL 5.7 server, there may be a physical misalignment of grants to deal with.

MySQL SCHEMA FILES

The number of InnoDB tables in the MySQL schema also changes between major releases.

Back on Dec 09, 2017, I answered ERROR 1031 (HY000): Table storage engine for 'proc' doesn't have this option and discussed when InnoDB tables starting being used in the MySQL schema.

In MySQL 5.6, it was 5 tables. In MySQL 5.7, it went to 19. In MySQL 8.0, all 31 tables are InnoDB.

FINAL THOUGHTS

Your may have to run some kind of mysql_upgrade commands to perform the transformation of tables from MyISAM to InnoDB right after to xtrabackup is restored

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.