I wrote a simple fast and flexible perl script called xmysqldump.pl to extract databases/tables from mysqldump sql file, you may download it from here.
Usage: ./xmysqldump.pl -h|--help This page ./xmysqldump.pl -l|--list [file.sql ...] List databases and tables ./xmysqldump.pl -a|--all-tables [file.sql ...] split to one .sql file per table ./xmysqldump.pl [-s|--separate] -d|--databases db1,dbn... [file.sql ...] Extract selected databases: ./xmysqldump.pl [-s|--separate] -t|--tables tbl1,... [file.sql ...] Extract selected tables(the first table name match): ./xmysqldump.pl [-s|--separate] -t|--tables db1.tbl1,db2.tbln,... [file.sql ...] Extract selected tables(full qualified): Options: --debug print some debug information to STDERR -o|--output-dir directory to output sql files -s|--separate output separate sql files named like DB.TBL.sql -v|--version show program version NOTE: This program requires running myqldump with --comments option(eanbled by default) If no source sql file specified, it will read from stdin
Some example:
List all databases and tables in t.sql
./xmysqldump.pl --list t.sql
Output:
mysql columns_priv db func help_category help_keyword help_relation help_topic host proc procs_priv tables_priv time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type user t_curu t t2
Extract ‘test’ databse from all.sql
./xmysqldump.pl -d test all.sql > test.sql
Split t.sql by table, save all file to ‘sql’ directory
./xmysqldump.pl --all-tables --output-dir=sql t.sql
ls -l sql
Output:
[curu@linuxplayer] ls -1 sql
mysql.columns_priv.sql
mysql.db.sql
mysql.func.sql
mysql.help_category.sql
mysql.help_keyword.sql
mysql.help_relation.sql
mysql.help_topic.sql
mysql.host.sql
mysql.proc.sql
mysql.procs_priv.sql
mysql.tables_priv.sql
mysql.time_zone.sql
mysql.time_zone_leap_second.sql
mysql.time_zone_name.sql
mysql.time_zone_transition.sql
mysql.time_zone_transition_type.sql
mysql.user.sql
t_curu.t.sql
t_curu.t2.sql
Extract mysql.user, t_curu.t2 table from t.sql, save to mysql.user.sql and t_curu.t2.sql
./xmysqldump.pl --separate -t mysql.user,t_curu.t2 t.sql
If you are sure that no duplicate table named user, then you can just run the above command like this:
./xmysqldump.pl --separate -t user,t2 t.sql
./xmysqldump.pl -d test all.sql > test.sql
Split t.sql by table, save all file to ‘sql’ directory
./xmysqldump.pl --all-tables --output-dir=sql t.sql
ls -l sql
Output:
[curu@linuxplayer] ls -1 sql
mysql.columns_priv.sql
mysql.db.sql
mysql.func.sql
mysql.help_category.sql
mysql.help_keyword.sql
mysql.help_relation.sql
mysql.help_topic.sql
mysql.host.sql
mysql.proc.sql
mysql.procs_priv.sql
mysql.tables_priv.sql
mysql.time_zone.sql
mysql.time_zone_leap_second.sql
mysql.time_zone_name.sql
mysql.time_zone_transition.sql
mysql.time_zone_transition_type.sql
mysql.user.sql
t_curu.t.sql
t_curu.t2.sql
Extract mysql.user, t_curu.t2 table from t.sql, save to mysql.user.sql and t_curu.t2.sql
./xmysqldump.pl --separate -t mysql.user,t_curu.t2 t.sql
If you are sure that no duplicate table named user, then you can just run the above command like this:
./xmysqldump.pl --separate -t user,t2 t.sql
./xmysqldump.pl --all-tables --output-dir=sql t.sql ls -l sql
Output:
[curu@linuxplayer] ls -1 sql mysql.columns_priv.sql mysql.db.sql mysql.func.sql mysql.help_category.sql mysql.help_keyword.sql mysql.help_relation.sql mysql.help_topic.sql mysql.host.sql mysql.proc.sql mysql.procs_priv.sql mysql.tables_priv.sql mysql.time_zone.sql mysql.time_zone_leap_second.sql mysql.time_zone_name.sql mysql.time_zone_transition.sql mysql.time_zone_transition_type.sql mysql.user.sql t_curu.t.sql t_curu.t2.sql
Extract mysql.user, t_curu.t2 table from t.sql, save to mysql.user.sql and t_curu.t2.sql
./xmysqldump.pl --separate -t mysql.user,t_curu.t2 t.sql
If you are sure that no duplicate table named user, then you can just run the above command like this:
./xmysqldump.pl --separate -t user,t2 t.sql
Note, duplicate non qualified table name may cause the program to behave unexpectedly, in case of error, just supply only 1 table will do, eg:
./xmysqldump.pl --table user t.sql > user.sql
Awesome script, really supports one of my tasks. Thanks a lot.
Thanks for this!!!
Thanks very much for this. I use this script all the time to help with my MySQL tasks!!
This script is brilliant – I had a 500MB .sql dump with over a hundred databases in it – and it pulled out the one I needed in under a minute…. can’t say thank you enough.
my pleasure!