online prescription solutions
online discount medstore
pills online
buy lorazepam without prescription
xanax for sale
buy xanax without prescription
buy ambien without prescription
ambien for sale
buy modafinil without prescription
buy phentermine without prescription
modafinil for sale
phentermine for sale
lorazepam for sale
buy lexotan without prescription
bromazepam for sale
xenical for sale
buy stilnox without prescription
valium for sale
buy prosom without prescription
buy mefenorex without prescription
buy sildenafil citrate without prescription
buy adipex-p without prescription
librium for sale
buy restoril without prescription
buy halazepam without prescription
cephalexin for sale
buy zoloft without prescription
buy renova without prescription
renova for sale
terbinafine for sale
dalmane for sale
buy lormetazepam without prescription
nobrium for sale
buy klonopin without prescription
priligy dapoxetine for sale
buy prednisone without prescription
buy aleram without prescription
buy flomax without prescription
imovane for sale
adipex-p for sale
buy niravam without prescription
seroquel for sale
carisoprodol for sale
buy deltasone without prescription
buy diazepam without prescription
zopiclone for sale
buy imitrex without prescription
testosterone anadoil for sale
buy provigil without prescription
sonata for sale
nimetazepam for sale
buy temazepam without prescription
buy xenical without prescription
buy famvir without prescription
buy seroquel without prescription
rivotril for sale
acyclovir for sale
loprazolam for sale
buy nimetazepam without prescription
buy prozac without prescription
mogadon for sale
viagra for sale
buy valium without prescription
lamisil for sale
camazepam for sale
zithromax for sale
buy clobazam without prescription
buy diflucan without prescription
modalert for sale
diflucan for sale
buy alertec without prescription
buy zyban without prescription
buy serax without prescription
buy medazepam without prescription
buy imovane without prescription
mefenorex for sale
lormetazepam for sale
prednisone for sale
ativan for sale
buy alprazolam without prescription
buy camazepam without prescription
buy nobrium without prescription
mazindol for sale
buy mazindol without prescription
buy mogadon without prescription
buy terbinafine without prescription
diazepam for sale
buy topamax without prescription
cialis for sale
buy tafil-xanor without prescription
buy librium without prescription
buy zithromax without prescription
retin-a for sale
buy lunesta without prescription
serax for sale
restoril for sale
stilnox for sale
lamotrigine for sale

MySQL: How do I dump all tables in a database into separate files?

There have been numerous occasions where I needed to make backups of individual tables from selected database. Usually I can achieve this by typing:

mysqldump database_name table1 > table1.sql
mysqldump database_name table2 > table2.sql

This could be very painful if you have 10’s or 100’s of tables. Until today, I never ran into a situation where I had to deal with dumping more than few tables at a time. Today I had to do a dump of 181 tables. I was not going to sit there and type in that command with 181 table names. It is not just time consuming but it is also stupid. So I wrote this script to help me with this task. We still use mysqldump command as described above, except we do it programmatically to make it easier on us:

if [ "$db" = "" ]; then
echo "Usage: $0 db_name"
exit 1
mkdir $$
cd $$
for table in `mysql $db -e 'show tables' | egrep -v 'Tables_in_' `; do
echo "Dumping $table"
mysqldump --opt -Q $db $table > $table.sql
if [ "$table" = "" ]; then
echo "No tables found in db: $db"

You can also compress your files by adding bzip2, zip or any other compression commands after mysqldump command. Here is the same script with bzip2 command added:
if [ "$db" = "" ]; then
echo "Usage: $0 db_name"
exit 1
mkdir $$
cd $$
for table in `mysql $db -e 'show tables' | egrep -v 'Tables_in_' `; do
echo "Dumping $table"
mysqldump --opt -Q $db $table > $table.sql
bzip2 $table.sql
if [ "$table" = "" ]; then
echo "No tables found in db: $db"

I do not recommend doing compression on a production server since most compression program put descent amount of load on the server. Also note that this will delay your dump considerably. You may also want to use different parameters for running mysqldump. Type man mysqldump in your shell to read more.

Related posts:

DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.

19 Responses to “ MySQL: How do I dump all tables in a database into separate files? ”

  1. Stoner
    November 26th, 2007 | 5:57 pm

    Have you looked at Maatkit ( – with it’s parallel dump and restore?

  2. November 26th, 2007 | 6:11 pm

    I have in past. I didn’t like the fact I had to install perl modules to get it working. That may not be the case on all systems but since all our systems have bare minimum installed OS, it takes longer for me to get things working whereas I can just run this script and be done.

    Thanks for posting the link though. I am sure other people would appreciate it.

  3. November 26th, 2007 | 9:42 pm

    check out the mysqldump option -T or –tab option
    makes a unique *.txt file of the tables’ data and *.sql for the table definition

  4. November 27th, 2007 | 6:38 am

    The –skip-column-names option to the mysql cli would negate the need for your inverse egrep.

  5. November 27th, 2007 | 7:15 pm

    How do you resotre the dump files back into the database?
    Can you do all of them at once?

  6. November 28th, 2007 | 7:35 pm

    [...] After I wrote the post: How do I dump all tables in a database into separate files? I got emails from couple people asking how to import the individual table files back in to MySQL. [...]

  7. January 29th, 2008 | 7:09 pm

    [...] How do I dump all tables in a database into separate files? [...]

  8. Frank Daley
    April 3rd, 2008 | 6:25 pm

    Thanks for the script. Has been very very helpful.

    One addition I am trying to implement is to include the column names in the output TXT files.

    Is there a way to do this?

  9. April 28th, 2008 | 9:37 am

    Thanks for the script. Works fine for dumping the tables to files.

    Is there also a way to dump the records from the tables into separate files? I’ve been trying for months to figure out how to do this, with no luck. Seems should be a php script already in existence to export each record from a table to separate text files. But after hundreds and hundreds of searches I can’t find anything like that works.

    No real care of the naming convention, whether named according to the id field, primary key, or by date and time or some other method isn’t real important. Just as long as the data goes to separate files. Any clues how to do that?


  10. May 12th, 2008 | 1:54 am

    Andre, Sorry for the delay. Here is a post for you:

  11. May 13th, 2008 | 6:55 am

    Sunny, thanks for posting that script. I didn’t mean for you to go to that trouble, I was curious if a way to do that had already existed :-|

    In my case, a 3rd party app required each record to be a separate text file to be able to import the records into wordpress’ posting area.

    If I were going for all 200,000 records I would have looked for a script that would directly import .sql files into wordpress’ posting area. But none exists so this 3rd party app seemed to be the next best thing.

    Thanks again for the script.

  12. June 16th, 2008 | 2:28 am

    Another poster asked this, but assume I dump out 5000 different tables, how do you re-import all these files without having to give a password each time?

  13. June 16th, 2008 | 9:26 am

    You can put a file in your home directory called .my.cnf and put following content in it:

  14. Max
    November 15th, 2009 | 6:18 pm

    You wrote:

    ” Usually I can achieve this by typing:”

    Where are you typing this?

  15. November 15th, 2009 | 11:50 pm

    Max: at a command prompt. It’s a mysqldump command

  16. December 16th, 2009 | 2:48 pm

    [...] MySQL: How do I dump all tables in a database into separate files? [...]

  17. March 25th, 2010 | 11:11 pm

    was searching this long time :)

  18. backtick
    April 23rd, 2010 | 7:07 pm

    it would be great to see an extensive blog post that introduces shell scripting, linux etc and explains the script that you posted above.

    I have no idea what it does or how to make it run…but i definitely need to use it.

    If you have the time it would be great to see a whole introduction to linux, from the ground up …all with the ultimate goal of leaving the reader with a clear understanding of what each character in that script is doing.

    i live in hope. I don’t demand much do i? ;o)

  19. November 30th, 2010 | 10:38 am

    [...] MySQL: How do I dump all tables in a database into separate files? | Technology: Learn and Share (tags: backup database db mysql script mysqldump) [...]

Leave a reply

To prove that you're not a bot, enter this code
Anti-Spam Image