0

How to mysqldump All Databases Excluding Information_Schema

Posted in Database

If you want to back up your MySQL databases you’ll be familiar with

1
mysqldump -uroot -p --all-databases > dump.sql

However this includes the information_schema, mysql and performance_schema databases. These are often not only unwanted in the dump, but can potentially cause issues on import.

To exclude these databases from your dump use the following script courtesy of user RolandoMySQLDBA on the StackOverflow forums.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MYSQL_USER=rootMYSQL_PASS=rootpasswordMYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"
 
DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
 
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
 
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > all-dbs.sql