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?

lonix
  • 185
  • 1
  • 5
  • 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
  • PS. Of course you may have some problems. For example your dump data may be incompatible with current server's SQL mode... or some constructions which were removed may be used. To minimize the amount of work for adoptation you may backup the structure and the data into 2 separate backup files. Or even backup data into CSV - I doubt it can become uncompatible. – Akina Oct 30 '19 at 11:46
  • What versions are you trying to import/export to/from – Jesus Uzcanga Oct 30 '19 at 19:50
  • @JesusUzcanga Latest version 10.4.8-bionic (docker image) – lonix Oct 31 '19 at 06:02
  • MySQL and mysqldump versions on both source and target. – Jesus Uzcanga Oct 31 '19 at 17:20
  • @JesusUzcanga Yes same version on source and target: 10.4.8-bionic docker image of mariadb – lonix Oct 31 '19 at 17:23
  • If they are the same version, why are you asking how to move data from an older version to a newer version? – Jesus Uzcanga Oct 31 '19 at 17:28
  • @JesusUzcanga I'm not asking that. 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. – lonix Oct 31 '19 at 18:58

1 Answers1

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.

Rick James
  • 78,038
  • 5
  • 47
  • 113
  • Thanks for this really comprehensive answer! I suppose this is true for any program's data, and for any dbms. – lonix Nov 02 '19 at 06:11