There is any option for mysqldump to ignore databases for backup in mysql

Question

1. we have 40 databases in our server.

2. we want to take 36 databases backup using mysqldump.

3. how can i ignore remainig 4 databases in mysqldump command.

4. there is any option for mysqldump to ignore databases for backup in mysql

5. i know general mysqldump command but it is very lengthy .i want to ignore only 4 databases and need to take remaining dbs backup.

Answer 1

Back on Dec 16, 2011, I answered the question How do you mysqldump specific table(s)?

I collected all tables not including a certain set of table names.

Using the same principles, you could have collect all the database names from the metadata table information_schema.schemata that you want mysqldump'd, create a query to return that list, then use that list of databases to formulate the mysqldump command.

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

All you need to do is put the databases you do not want mysqldump'd into DATABASES_TO_EXCLUDE

Give it a Try !!!

Answer 2

I don't think it is possible, but you can try these solution which you will have to type the names of all the databases you want to dump.

mysqldump -u root -password --databases db1 db2 db3 ... db36 > mydb_dump.sql

Let me know if the solution helps.

Answer 3

Alternatively, you can look at the following links;

Mysqldump Ignore Database Option

            OR

A few hacks to simulate mysqldump --ignore-database

Implementing mysqldump –ignore-database

Let me know if the solution helps.

Goodluck!