Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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:

table1
table2
etc
...

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.

share|improve this question
add comment

4 Answers

up vote 2 down vote accepted
mysqldump -uuser -ppass database < show_tables.sql |
xargs -I TableName sh -c 'mysqldump -uuser -ppass database TableName > TableName.sql'
share|improve this answer
add comment

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

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

share|improve this answer
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
add comment
mysql database -e 'show tables' | while read table
do mysqldump -uuser -ppass database "$table" -r "$table.sql"
done

Works with spaces in the tables, too.

share|improve this answer
    
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
add comment

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
done
share|improve this answer
    
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
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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