How can I take a full backup from MySQL without any System tables?

Question

We are downgrading our version of MariaDB, and are looking to take a full backup of all of the databases. I do not want to include system tables since they would be incompatible between the two versions. What would be the best way to accomplish this?

After reading through http://mysqlserverteam.com/creating-and-restoring-database-backups-with-mysqldump-and-mysql-enterprise-backup-part-1-of-2/, it seems like everyone is saying that you have to manually exclude them, however I don't want to manually have to type in all of the system databases. What is the best way to accomplish this?

Currently I have the following options -

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction --gtid --master-data --dump-slave --add-drop-database"

This is the current method I have tried -

SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

echo $SQL

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

Dblistfile includes all of my database names that I want to backup. Is there a better way to do this?

Answer 1

Four and a half years ago in my first post to Any option for mysqldump to ignore databases for backup?, I collected the database names to memory rather than to disk.

Here is the code from my first answer

DATABASES_TO_EXCLUDE="db1 db2 db3"
EXCLUSION_LIST="'information_schema','mysql'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql

This could be adjusted to collect the names in SQL rather than in the shell

DATABASES_TO_EXCLUDE="db1 db2 db3"
EXCLUSION_LIST="'information_schema','mysql'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYDBLIST1=`mysql -ANe"${SQLSTMT}"`
MYDBLIST2=`echo "${MYDBLIST1}" | sed 's/,/ /g'`
MYSQLDUMP_DATABASES="--databases ${MYDBLIST2}"
MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql

A couple of months ago, I briefly mentioned using mysqlpump (See my second post to Any option for mysqldump to ignore databases for backup?) which now has --exclude-databases.