MySQL Backup Script

JRB Technology blog offers a script on how to backup a MySQL database. The described solution uses perl to call three commands:

@b1 = `mysqldump -h localhost -u mysql_username –password=mysql_password mysql_database > /path/to/put/mysql_database.sql`;@z1 = `zip -r -9 ~/path/to/mysql_database.zip ~/path/to/mysql_database.sql`;@d = `rm -f ~/path/to/mysql_database.sql`;

I have posted a comment directly to the origin blog with some (as I see it) improvements, but unfortunately the comment was deleted, so I will reproduce it here.

First of all I think that there is no need to create a temporarily SQL file with dump from the database and then zipping it. The zip command supports getting data from STDIN, so this way, instead of running 3 commands, only one is needed:

mysqldump -h hostname -u username –password=somepassword databasename | zip -9 mysql_database.zip

Then I offered to hide a password, since otherwise it is possible to see it by running the ps command under any user during script execution, and password will be seen in the arguments list. To hide a password, we can create a text file which is accessible only to us (chmod 600) and store a password string there, then modify the backup command accordingly:

mysqldump -h hostname -u username –password=`cat /path/to/securefile.txt` databasename | zip -9 mysql_database.zip

This way, even if someone will see that the password for accessing database is in the specified text file, it will be harder to get it, since file is readable only by owner user.

Finally, I would like comment a bit on Perl script itself, which author of the original script provides: first of all, I don\’t see any point of using Perl here, since nothing, except for calling standard commands is done during execution. A plain bash script would do a job fine. Anyway, even if you prefer using Perl, I would strongly recommend using -w switch and use strict; statement (even in such simple script, it is just good to use this every time)