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 twoNow 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 twoThat'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:
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
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')"
Much better solution
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')"
thumbs up on the last comment! i had to use that too...
select schema_name from information_schema.schemata where schema_name not in...
Post a Comment