Monday, December 06, 2010

Excluding databases from mysqldump

A question that came up during the MySQL track at the UKOUG conference in Birmingham was "Can I exclude only a few databases from mysqldump? Let's say that I have 50 databases, and I want to dump all of them, except a few."
As many know, mysqldump has an option to ignore specific tables. SO if you have 1,000 tables in a databases, you can tell mysqldump to dump all the tables except a few ones.
There is no corresponding option to exclude one or more databases.
However, if you know your command line tools, the solution is easy:
First, we get the list of all databases:
mysql -B -N -e 'show databases'
information_schema
employees
five
four
mysql
one
performance_schema
six
test
three
two
-B forces batch mode (no dashes box around the data), while -N gets the result without the headers.
Now, let's say that we want to exclude databases four, five, and six. And since we want to avoid unpleasant side effects, also information_schema and performance_schema.
Thus, we pipe the previous data through a filter. I use Perl, but sed or grep could get the job done.
mysql -B -N -e 'show databases' | \
  perl -ne 'print unless /\b(?:four|five|six|_schema)\b/' 
employees
mysql
one
test
three
two
Now that we have the list of databases that we need, we can tell mysqldump to backup the databases from such list. All we need is converting the vertical list into a horizontal one using xargs

mysql -B -N -e 'show databases' | \
  perl -ne 'print unless /\b(?:four|five|six|_schema)\b/' \
  xargs echo mysqldump -B 
mysqldump -B employees mysql one test three two
That's it. The last line is the resulting command. Once you are sure that it is what you want, remove the "echo" after xargs, and the command will be executed.

Update: Thanks to Shantanu, who pointed that the regexp does not filter properly. So I added the boundary checks (\b) to make my words match the result.

6 comments:

shantanu said...
This comment has been removed by the author.
shantanu said...

This does not seem to work if the DB name is four and four_report. If I want to exclude four, five_report, but keep four_report and five then egrep should do the job.

# mysql -BNe"show databases"
information_schema
five
five_report
four
four_report
mysql
test

# mysql -BNe 'show databases' | perl -ne 'print unless /five|four|_schema/'
mysql
test

# mysql -BNe"show databases" | egrep -vw 'four|five_report|information_schema'
five
four_report
mysql
test

Scott said...

Or skip the perl bit with mysql -B -N -e "select schema_name from information_schema.schemes where information_schema not in ('four', 'five', 'six', 'information_schema', 'performance_schema')"

jacob said...

Much better solution

Anonymous said...

In my case I had to use "select schema_name from information_schema.schemata where schema_name not in ('four', 'five', 'six', 'information_schema', 'performance_schema')"

barry said...

thumbs up on the last comment! i had to use that too...

select schema_name from information_schema.schemata where schema_name not in...