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.