backup and zip all mysql databases shell script
This isn't rocket science but probably something good to share for anyone making the move to Linux for web hosting. Below is a quick script that I run daily to back up all MySQL DB's which then have placed somewhere safe.
Open Nano/Vi/Gedit and create a file called mysql_backup.sh then add the following lines:
1#!/bin/bash
2cd /tmp/
3echo "creating mysqldump in /tmp/all-database.sql"
4mysqldump -u YOUR_USERNAME -pYOUR_PASSWORD --all-databases > all-database.sql
5echo "Next we tar/zip the file"
6tar cvf db_dump.tar all-database.sql
7echo "All done!"
2cd /tmp/
3echo "creating mysqldump in /tmp/all-database.sql"
4mysqldump -u YOUR_USERNAME -pYOUR_PASSWORD --all-databases > all-database.sql
5echo "Next we tar/zip the file"
6tar cvf db_dump.tar all-database.sql
7echo "All done!"
At the moment this is just a text file which cannot be executed via the terminal, so to fix this run the following:
1chmod 755 mysql_backup.sh
Then you can run this as any normal shell script
1./mysql_backup.sh
There is a lot more you can do with this like moving the file to another location, delete the dump behind you etc, but this should give you a good starting ground.
Comments
You can simplify the process by making the gz in the same command:
mysqldump -uYOUR_USERNAME -pYOUR_PASSWORD --all-databases | gzip > output all-database.sql.gz
mysqldump -uYOUR_USERNAME -pYOUR_PASSWORD --all-databases | gzip > output all-database.sql.gz
I've been doing exactly this process to back up databases (and transfer them between servers) for a long time, and I'd like to share just a few tips I've picked up along the way.
First, MySQL dumps are full of text so they're highly compressible. I would suggest adding the "z" switch to the tar command, and changing the file extension of the resulting file to either ".tgz" or ".tar.gz". That's going to save you a lot of space (and, in the case of moving the backup, a ton of transfer time).
Second, since mysqldump sends the dump file to STDOUT, you can avoid the need to clean up the temporary file by not ever creating it in the first place. Simply pipe the output of mysqldump directly to tar (which works because tar looks to STDIN for its input file if no file name is specified).
Finally, be careful with the -p version of the password parameter to mysqldump. I tend to use randomly-generated database passwords, which are more secure but also more likely to include shell metacharacters like spaces or backslashes. This is particularly a problem if you parameterize the script. So I switched to always using the --password version of the parameter, which allows you to quote the password to protect against shell metacharacters.
In summary, the "business end" of your script would be something more like this:
mysqldump -u YOUR_USERNAME --password="YOUR_PASSWORD" --all-databases | tar czvf db_dump.tar.gz
Hope this helps.
First, MySQL dumps are full of text so they're highly compressible. I would suggest adding the "z" switch to the tar command, and changing the file extension of the resulting file to either ".tgz" or ".tar.gz". That's going to save you a lot of space (and, in the case of moving the backup, a ton of transfer time).
Second, since mysqldump sends the dump file to STDOUT, you can avoid the need to clean up the temporary file by not ever creating it in the first place. Simply pipe the output of mysqldump directly to tar (which works because tar looks to STDIN for its input file if no file name is specified).
Finally, be careful with the -p version of the password parameter to mysqldump. I tend to use randomly-generated database passwords, which are more secure but also more likely to include shell metacharacters like spaces or backslashes. This is particularly a problem if you parameterize the script. So I switched to always using the --password version of the parameter, which allows you to quote the password to protect against shell metacharacters.
In summary, the "business end" of your script would be something more like this:
mysqldump -u YOUR_USERNAME --password="YOUR_PASSWORD" --all-databases | tar czvf db_dump.tar.gz
Hope this helps.
cheers for the comments guys! Off to change my script now
