Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
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. –  SamK Jul 22 '11 at 14:30
add comment

2 Answers

up vote 1 down vote accepted

With bash:

for file in *.tar.gz; do 
  echo "processing $file";
  zcat $file | mysql;
done
share|improve this answer
1  
Will the mysql binary properly deal with tarred/gzipped data? –  EEAA 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. –  EEAA 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
add comment

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.

share|improve this answer
add comment

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.