23

We have 40 databases in our server.

We want to take 36 databases backup using mysqldump. How can i ignore remaining 4 databases in mysqldump command? Is there any option for mysqldump to ignore databases for backup in MySQL?

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

16

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

  • There's no need to exclude information_schema it isn't being dumped anyways. mysqldump does not dump the INFORMATION_SCHEMA or performance_schema database by default. To dump either of these, name it explicitly on the command line and also use the --skip-lock-tables option. You can also name them with the --databases option. Before MySQL 5.5 mysqldump silently ignores INFORMATION_SCHEMA even if you name it explicitly on the command line. dev.mysql.com/doc/refman/5.5/en/mysqldump.html – Luka Jan 28 at 13:50
15

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 https://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
  • One should add ` | paste -sd " " -` after the grep in order to pass from multiline output to a single line with each database name seperated by a space, which is required by mysqldump. So the command will be candidates=$(echo "show databases" | mysql | grep -Ev "^(Database|mysql|performance_schema|information_schema)$" | paste -sd " " -) – Anthony O. Sep 7 '18 at 12:17
8

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.

  • 1
    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
2

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!

2

Even here so many excellent answers, so this post is just to add one more choice. By below 2 lines in script, you can take your servers all DB's backup ignoring some DB.

DB_LIST=`mysql -Nse "SELECT GROUP_CONCAT(SCHEMA_NAME SEPARATOR ' ') FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql','information_schema','performance_schema','sys');"`

mysqldump -R -B $DB_LIST > BACKUPNAME.sql
  • 1
    Thank you for including sys since that is part of MySQL 5.7 – RolandoMySQLDBA Dec 27 '16 at 15:29
  • thanks @RolandoMySQLDBA, I always gets help from your excellent posts/blogs...thanks again for your compliment... – Zafar Malik Dec 28 '16 at 11:24
1

Many have always wanted mysqldump to ignore databases.

Would you believe that option exists now ??? No, not in mysqldump.

Oracle (Yuck, toowee, still doesn't roll off the tongue) has DataPump (expdb impdp) to dump Oracle databases. Since MySQL 5.7 in the Oracle family (still hurting), the new backup utility program is called mysqlpump, which comes with --exclude-databases and other neat options. Like its older stepbrother datapump, mysqlpump also features parallelism to help speed up dumps and divide work. At this time, I have not incorporated into at the job, but it looks very promising. When I deep dive into mysqlpump I might find that it brings the same look and feel as Oracle's datapump.

If there is anyone in the MySQL Parallel Universe with stories about it, please post it here.

0

Rolando's answer is pretty great, but I wanted a script that can be reused across projects. So, I took his script and modified it so that you can do things like:

$ mysqldump-ignore-databases.sh 'myuser' 'mypwd' 'information_schema mysql performance_schema' '--skip-lock-tables' > mydbs.sql

Here is the modified script:

#!/usr/bin/env bash

# This is an adapted version of Rolando's awesome script at
# https://dba.stackexchange.com/a/35104/94046

# Usage: mysqldump-ignore-databases.sh username password dbs-to-exclude mysqldump-options
# e.g.
#   mysqldumpIgnoreDatabases.sh 'myuser' 'mypwd' 'information_schema mysql performance_schema'
#                               '--skip-lock-tables' > dbs.sql
#
#   mysqldumpIgnoreDatabases.sh 'myuser' 'mypwd' 'mysql' '-h example.com' '-h example.com' > dbs.sql

USERNAME=$1
PASSWORD=$2
DATABASES_TO_EXCLUDE=$3
MYSQLDUMP_OPTIONS=$4
MYSQL_OPTIONS=$5

EXCLUSION_LIST=""
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
  if [ "${EXCLUSION_LIST}" != "" ]
  then
    EXCLUSION_LIST="${EXCLUSION_LIST},"
  fi
  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 -u${USERNAME} -p${PASSWORD} ${MYSQL_OPTIONS} -ANe"${SQLSTMT}"`
do
  MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done

mysqldump -u${USERNAME} -p${PASSWORD} ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES}

(I'm still hoping that an --ignore-database option will be added to a future version of mysqldump)

0

Since MySQL 5.7.8, you can use mysqlpump (which is NOT the same as mysqldump) as follows:

mysqlpump --user=user --password --exclude-databases=db1,db2,db3,db4 --result-file=partial_backup.sql

Just replace db1,db2,db3,db4 with the four databases you want to exclude.

Source: MySQL Server Blog

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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