I have a mysql database on a shared web host. It's grown too large, and a single dump process with zipping is getting killed. I want to dump and zip each table in individually. With this command

mysqldump -uuser -ppass database < show_tables.sql

I can get it to return all the names of the tables like so:


How can I feed this into a bash script so that I can feed each line into a command of this syntax

mysqldump -uuser -ppass database table_name_goes_here > and_here.sql


Edit Sorry folks, I made a mistake in the original formatting. The output from the show tables query is not a single line with space-delimited table names; it is actually one table name per line, as it is formatted now. I apologize for the confusion.

mysqldump -uuser -ppass database < show_tables.sql |
xargs -I TableName sh -c 'mysqldump -uuser -ppass database TableName > TableName.sql'

Assuming the table names cannot contain newlines,

mysqldump -uuser -ppass database < show_tables.sql | 
while IFS= read -r table; do
        mysqldump -uuser -ppass database "$table" > and_here.sql
  • That's only any good if the output is null-delimited. – Mikel Apr 16 '12 at 5:50
  • @Mikel Sorry, I'm not sure what you mean. The output of the first command has to be newline delimited. The output of the second one can be anything it likes. – jw013 Apr 16 '12 at 7:04
  • 2
    @Mikel: It won't work if each table name is null-delimited. read reads up to a newline, regardless of how IFS is set ..IFS only tells read how to split up what it reads (based on -d which is \n by default).. read doesn't need IFS at all when reading just a single var ($table).. Really, for just a single var, the only time you need IFS= is when you want to cater for embedded \n, in which case you must have null-delimited input and IFS= read -d $'\0'.. The following works fine without a modified IFS: printf "a b\nx y\n" |while read -r x; do echo $((i+=1))"$x="; done – Peter.O Apr 16 '12 at 9:59
  • 3
    IFS= prevents read from stripping leading and trailing whitespace (try read -r a <<<' foo ' vs IFS= read -r a <<<' foo '). If the table names don't have such things then it won't make a difference, but I threw it in for safety in case they do. – jw013 Apr 16 '12 at 11:24
  • 1
    Thanks jw013, I had forgotten all about the lead/trail whitespace. I wonder if that behaviour existed originally for interactive input... "for safety"; a good ethos... I do usually use -r, and even set -f to disable globbing. I'll now make sure I usually use IFS= – Peter.O Apr 16 '12 at 13:08

The manual says

   Arrays are assigned to using compound assignments of the form name=(value1 ... valuen),

so you just need a way put your command's output where it says value1 ... valuen.

You can do that like this

databases=( $(mysqldump -uuser -ppass database < show_tables.sql) )

and then iterate over them like this

for database in ${databases[*]}; do

but just in case your table name is really weird and contains some newlines, I'd recommend using mysql -Bse to list the databases and/or tables, and use while read IFS= like jw013 suggests (possibly without the -r option).

  • 2
    It should be mentioned that the command substitution approach requires the table names to not contain any whitespace characters. – jw013 Apr 16 '12 at 7:17
mysql database -e 'show tables' | while read table
do mysqldump -uuser -ppass database "$table" -r "$table.sql"

Works with spaces in the tables, too.

  • That would include some header lines like ----------. Add the -B option to turn those off. Also, how is your answer different from jw013's? – Mikel Apr 17 '12 at 4:28
  • If you have an improvement to make, edit the post. – laebshade Apr 18 '12 at 2:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.