1

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?

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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