extract databases or tables from mysqldump

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

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

./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