How to Exclude Specific Databases from an –all-databases MySQL Dump

There may come a time where you need to make a complete database dump on your MySQL server, yet for whatever reason, you need to exclude databases. In my case, I needed to exclude a certain database so that when I combined multiple databases into one, another database’s tables didn’t get overwritten during the conversion.

If you’re familiar with the grep command and piping, you can exclude as many databases as you want using grep’s -v option.

To keep this simple, here’s a script which you can use (modify to fit your needs) to make this work (by default, I have it skipping the main MySQL, Information Schema, and Test databases).

USER=kris
PASS=myPassword
HOST=database-server.mydomain.com

# Databases to exclude
DBEXCLUDE=db_name_i_dont_want

MYSQL=$(mysql -N -u${USER} -p${PASS} -h${HOST} <<<"SHOW DATABASES" | grep -v ${DBEXCLUDE} | grep -v mysql | grep -v information_schema | grep -v test | tr "\n" " ")

mysqldump -v -u${USER} -p${PASS} -h${HOST} --databases  --skip-lock-tables ${MYSQL} > DB-DUMP.sql

6 Thoughts on “How to Exclude Specific Databases from an –all-databases MySQL Dump

  1. Nice script… however, you declare variables that are not used in the script : MYUSERNAME, MYPASSWORD…

    Also, be careful with the grep -v test … If you have any database that has the word test in it it will be excluded from the DB export.

  2. thank youi

  3. Shouldn’t the grep be
    “grep -v ${DBEXCLUDE}”
    rather than
    “grep -v ${EXCLUDE}”

  4. José Daniel Peña on September 17, 2012 at 10:14 am said:

    Excelente, Muchas gracias !!!

Post Navigation