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?

  • MySQL 5.0/5.1/5.5 were not altered since 2011 - so this statement is true yet. – Akina Oct 30 '19 at 11:31
  • @Akina Wow! No major revision in 8 years. – lonix Oct 30 '19 at 11:37
  • No. 5.6, 5.7 and 8.0 already exists. Practice: I have succesfully imported 5.6.27 (? don't remember exactly) dump to 8.0.17. – Akina Oct 30 '19 at 11:39
  • @Akina LOL... You're right I just checked I'm using version 10.4.8 of mariadb and 8.0.18 of mysql! – lonix Oct 30 '19 at 11:45
  • @Akina so if backup is made with modern version, it should be possible to restore in version+1 - I hope?! – lonix Oct 30 '19 at 11:46
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.

  • Thanks for this really comprehensive answer! I suppose this is true for any program's data, and for any dbms. – lonix Nov 2 '19 at 6:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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