up vote 0 down vote favorite
share [g+] share [fb]

I have 200+ mysql database dump in tar.gz format. Restoring all of them one by one is very time consuming. Is there any way so I can restore all these database in a single transaction.

Thanks for your help in advance!

link|improve this question
54% accept rate
No need to tar a single file, just gz compression is enough. Then something like zcat $file | mysql in a loop would have done the trick. – SamKrieg Jul 22 '11 at 14:30
feedback

2 Answers

up vote 1 down vote accepted

With bash:

for file in *.tar.gz; do 
  echo "processing $file";
  zcat $file | mysql;
done
link|improve this answer
1  
Will the mysql binary properly deal with tarred/gzipped data? – ErikA Jul 22 '11 at 12:32
@ErikA zcat is supposed to take care of that, yet tar.gz probably means it was tarred first for some reason, don't know if that is also handled by zcat. – Xeross Jul 22 '11 at 17:37
@Xeross - Hubert didn't include zcat in his initial answer, which is when I posted that comment. – ErikA Jul 22 '11 at 18:55
@ Hubert, thanks for the tips mate. I'm getting this error "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) processing dcv.sql.gz zcat: %file.gz: No such file or directory". If I try with mysql -uroo -pPASSWORD, getting this error "zcat: %file.gz: No such file or directory". – Saif Jul 25 '11 at 11:26
use "$", not "%", in windows cmd you write a loop differently and use double %: %varname% – Hubert Kario Jul 25 '11 at 23:57
feedback
up vote 1 down vote

No, because transactions don't cross database boundaries. On the other hand, if you want to load 200+ databases with a single command, just script the process.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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