So today, I received a 1TB hard drive for backup purposes and decided to backup all 131 MySQL databases. I did a mydqldump of all databases but that was not good enough. What if I wanted to restore only one database?

Not having the knowledge needed to perform the task, I went to Google to search. I came across a few scripts that did not work but then I came across the Stack Overflow forum (http://stackoverflow.com/questions/10867520/mysqldump-with-db-in-a-separate-file) and a script that worked. Oh thank you Jesus!

I tested it out on my VM and it worked (with a few tweaks. I did not need a zip file for every database, for example). I then transfered the working script to my server and BAM! Databases were backed up. Yay!! Here is my script below:

“mysql -u [USERNAME] -p[PASSWORD] -e 'show databases' | while read db; do mysqldump -u [USERNAME] -p[PASSWORD] $db -r ${db}.sql; done“

Copy this to Notepad++ or gEdit and save the file with a .sh extension. Assuming you saved your file to “/home/[USERNAME]/web” open terminal and simply type “/home/[USERNAME]/web/[FILENAME].sh” and let it run. Upon completion all your databases should be backup up.

If you have many databases like I do, then it’s just not acceptable to back them up one by one. Big thanks to the folks at StackOverflow.com for this solution.

Tags: mysql, server, ubuntu