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.