Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  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.

share|improve this question

4 Answers 4

up vote 4 down vote accepted

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

share|improve this answer

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.

share|improve this answer
    
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!

share|improve this answer
    
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

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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