MySQL Backup Script

In one of my previous ProTips I mentioned my shell script that performs a backup of my MySQL database and then gzips it for a quicker download. I thought I'd share it with you today.

# backup-dbname

NOW=$(date +"%Y-%m-%d.%H-%M-%S")
mysqldump -uroot -p dbname > dbname.$NOW.sql
gzip dbname.$NOW.sql

You can then add the script to your PATH (usually ~/myusername/bin/ or ~/bin) so that all you have to do in your terminal is run backup-dbname, or you could run the script absolutely (e.g. /path/to/backup-tablename).

DISECTION

NOW=$(date +"%Y-%m-%d.%H-%M-%S")

In the above code, we declare the variable NOW. Notice how there is no space either side of the equals sign. If the current date is the 17th of September 2014 at 14:56:30, then the variable NOW will be equal to 2014-09-17.14-56-30.

mysqldump -uroot -p dbname > dbname.$NOW.sql

This is the usual MySQL dump syntax. The > means pipe the results in this direction, i.e. from the mysqldump function, to the file named dbname.$NOW.sql. Change dbname to the name of the table that you want to dump. If you only want to dump certain tables, do the following

# backup-dbname

NOW=$(date +"%Y-%m-%d.%H-%M-%S")
mysqldump -uroot -p dbname table1 table2 table3 tableN > dbname.$NOW.sql
gzip dbname.$NOW.sql

Obviously, replace tableX with the names of the tables that you want to dump.

gzip dbname.$NOW.sql

The last line of code simply gzips the file.

Happy backing up! :v