4

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?

lonix
  • 205
  • 2
  • 5

1 Answers1

4
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.

Rick James
  • 80,479
  • 5
  • 52
  • 119