2

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?

12
  • MySQL 5.0/5.1/5.5 were not altered since 2011 - so this statement is true yet.
    – Akina
    Oct 30, 2019 at 11:31
  • @Akina Wow! No major revision in 8 years.
    – lonix
    Oct 30, 2019 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, 2019 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, 2019 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, 2019 at 11:46

1 Answer 1

3
Oracle (MySQL):    
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   

MariaDB:
MAJOR VERSION    GA         DATE        5 YEAR BOUNDARY DATE
MariaDB Database 5.1        Feb 2010    Feb 2015
MariaDB Database 5.2        Nov 2010    Nov 2015
MariaDB Database 5.3        Feb 2012    Mar 2017
MariaDB Database 5.5        Apr 2012    Mar 2020
MariaDB Database 10.0       Mar 2014    Mar 2019
MariaDB Database 10.1       Oct 2015    Oct 2020
MariaDB Database 10.2.40    May 2017    May 2022
MariaDB Database 10.3.31    May 2018    May 2023
MariaDB Database 10.4.21    Jun 2019    Jun 2024
MariaDB Database 10.5.12    Jun 2020    Jun 2025
MariaDB Database 10.6.4     Jul 2021    Jul 2026  LTS (switching to Linux-like)
MariaDB Database 10.7.?     Feb 2022    Feb 2023
MariaDB Database 10.8.?     May 2022    May 2023

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.

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

Your Answer

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

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