Any option for mysqldump to ignore databases for backup?

Question

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.

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

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

Answer 3

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 4

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

Answer 5

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

Answer 6

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!

Answer 7

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.

Answer 8

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)

Answer 9

You can use this one for production
It excludes 'performance_schema\|information_schema\|mysql\|sys'...modify for your needs

MYSQL_USER=
MYSQL_PASS=
MYSQL_HOST=
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST}"
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"

DBLIST=`mysql -s --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASS \
--execute="SHOW DATABASES;" | grep -v \
'performance_schema\|information_schema\|mysql\|sys' | awk '{printf("\"%s\" ",$0)}'`

mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} | gzip >all-dbs.sql.gz

Answer 10

I'm able to ignore certain database name using the below script. I'm excluding information_schema database while running mysqldump. You may replace "information_schema" with database name you want to exclude.

for DB in $(mysql -u db_username -p'db_password' -e 'show databases' -s --skip-column-names); do
  if [ "$DB" != "information_schema" ]
  then
  mysqldump --single-transaction --skip-lock-tables -u db_username -p'db_password' $DB | gzip > $DB.sql.gz;
  fi
done