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

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.

-

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 !!!

-

Use grep to exclude databases you don't want:

candidates=$(echo "show databases" | mysql | grep -Ev "^(Database|mysql|performance_schema|information_schema)$")
mysqldump --databases $candidates  From looking at http://stackoverflow.com/questions/19354870/bash-command-line-and-input-limit it seems like you'll be able to handle long lines. Otherwise you can always candidates=$(echo "show databases" | mysql | grep -Ev "^(Database|mysql|performance_schema|information_schema)$") for candidate in$candidates[*]; do
mysqldump \$candidate
done

-

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.

-
i know this command .but it is very lengthy .i want to ignore 4 databases and need to take remaining dbs backup. –  ashuthosh Feb 20 '13 at 9:39

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!

-
lot of thanks@@Cybermatatu it was helpful for me. –  ashuthosh Feb 20 '13 at 11:00
You are welcome! –  Cybermatatu Feb 20 '13 at 11:03