Can I use a mariadb/mysql dump from older version in newer version?

Posted on

Question :

I’m a mariadb/mysql newbie. I want to take a complete backup.

Apparently this is the way (please correct me if I’m wrong):

mysqldump 
  -h... -u... -p... 
  --hex-blob --routines --triggers 
  --all-databases 
  --add-drop-database --add-drop-table 
  | gzip > backup.sql.gz

But according to this answer:

The drawback is that mysqldumps created this way can only be reloaded into the same majot release version of mysql that the mysqldump was generated. In other words, a mysqldump from a MySQL 5.0 database cannot be loaded in 5.1 or 5.5. The reason ? The mysql schema is total different among major releases.

That was written in 2011 – is it still true?

Answer :

major   Release Candidate   General Availability
8.0     2017-09-21 8.0.3    2018-04-19 8.0.11
5.7     2015-04-08 5.7.7    2015-10-21 5.7.9
5.6     2012-09-29 5.6.7    2013-02-05 5.6.10
5.5     2010-09-13 5.5.6    2010-12-03 5.5.8
5.1     2007-09-24 5.1.22   2008-11-14 5.1.30
5.0     2005-09-22 5.0.13   

(I don’t have MariaDB’s GA dates.)

You can try to use a mysqldump on a different major version, but there may be incompatibilities.

Note that an “old” mysqldump may not understand a “new” db, but it is more likely that a “new” mysqldump will correctly read “old” data.

Some notable incompatibilities:

TYPE --> ENGINE  (4.1?)
fractional seconds (new in 5.6.4 / 10.0)
VISIBLE indexes  (8.0)

The 8.0 mysqldump apparently insists on including the default, but “new” option of VISIBLE. This makes a mess when moving a dump (of any version) taken by the mysqldump for 8.0 and loading that dump on any MySQL/MariaDB other than MySQL 8.0.

You may notice comments like this: This allows the dumps to (mostly) work across any version:

/*!50100 PARTITION BY ... */

That says “5.1 and newer can handle it, but if the target version is older than 5.1, treat this as a comment.” Caveat: since the MariaDB numbering diverged from MySQL, this style of comment somethings screws up.

I’m asking if it would create a problem in the future, when I will need to move to a newer version. If that’s the case, then I need to take it into account today and use a different approach, as specified in that linked question.

They will always allow dumping from one version to the next major version. It is a major way to upgrade. Skipping a version may cause hiccups, but even that rarely causes trouble.

Leave a Reply

Your email address will not be published. Required fields are marked *