27

I want to backup all 200+ databases on a MySQL server to all-dbs.sql.

I want to exclude the mysql schema.

How can I do that?

Improve this question
1
  • No doubt about the possible - it's a dupe! Vote to close and point users to the original answer.
    – Vérace
    Aug 28, 2015 at 15:51

1 Answer 1

Reset to default
44

You need to collect all the database names into a space delimited list. Use that for mysqldump

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_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

Give it a Try !!!

Improve this answer
14
  • 1
    Just for anyone here reading that is in a similar position to me - you can also just dump everything and manually remove the mysql schema from the dump file, to save time writing/running scripts
    – Leonard Challis
    Jul 2, 2015 at 11:27
  • 1
    "Why should we care implementing a simple --ignore-database argument, look at this tiny script, it will do just fine."
    – Gras Double
    Aug 28, 2015 at 4:45
  • 2
    It says mysqldump: unknown option '--ignore-database' don't know why, but when I used --ignore-table=information_schema.* it worked.
    – Hussain Behestee
    Nov 13, 2015 at 9:38
  • 4
    It's always lovely to read the comments :)))
    – Ivan Nikolchov
    May 25, 2016 at 7:01
  • 3
    @TheStoryCoder It's written in english, but the programming language is bash :D
    – Denys Vitali
    Jun 27, 2016 at 13:35

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