Tuesday, May 17, 2011

Dumping Multiple Selected MySQL Tables as SQL


Not sure if anybody already figured out how to dump the selected mysql tables from a particular database. But after googling I found some way which helped me how to proceed and adding my thought little & attained what I need. Ok, not bugging you too much, here is my requirement:

I need to dump 5 tables (employee, address, salary, attendance, payslip) & the tables whose name starts with map_* in a single command.

Solution
What I know from the syntax is that I can append as many table name with the mysqldump to get the dump of all the tables.mysqldump -u{user} -p {database} {table1} {table2} {table3} > {destination}.sql
but what here missing is that I can add map_* tables which is really important for me to proceed further.

As said, after googling I got the site and understood how to get map_* tables include with the mysqldump.

The referred site provided me something like this..
mysql databasename -u [root] -p[password] -e 'show tables like "wp_153_%"' | grep -v Tables_in | xargs mysqldump [databasename] -u [root] -p[password] > [target_file]

but what I modified to get my requirement complete is
mysql {databasename} -u {root} -p{password} -e 'show tables like "wp_153_%"' | grep -v Tables_in | xargs mysqldump {databasename} -u {root} -p{password} {table1} {table2} {table3} > {destination}.sql

So my final output was
mysql employeeDB -u root -p -e 'show tables like "map_%"' | grep -v Tables_in | xargs mysqldump employeeDB -u root -p employee address salary attendance payslip > destination.sql

Hope NO other better option than this.


Note: I have remove {password} from my command as I think we should not execute the password in the command line instead asking us

No comments:

Post a Comment