In this second part of HTAULB we will be visiting the topic of how to automate MySQL database backups. Although I will be the first to admit this is completely simple and a very nice way of accomplishing this task, I will also advise that it is not the most secure. I would not recommend this to anyone sharing a server or its resources with other people. You will be hard coding your SQL password into a script and although you could restrict ‘Read’ permissions from anyone else seeing it, I will advise against it on any shared machine.
Now, on with the show!
If you haven’t read the first part of this series please go back and do so, I’ll wait….
Back? Took you long enough. I had you read that because we will be doing some of the same tasks in order to automate the SQL backups.
You will need to go to the location in which you are keeping your .sh scripts. (We use /bin here at IAmRoberson)
Use the editor of your choice to edit the file using the name of your choice for the script’s name:
Add the following line for each database you wish to make a sql dump from.
mysqldump -u username -h localhost -p(password) databasename | gzip -9 > /var/backups/nameoftargetfile-$(date +%Y%m%d).db.sql.gz
Exit from the editor and chmod the file to 766. (see previous post for exact syntax.)
Add an additional line in the crontab like you did for (part 1) of this exercise.
You are done! Go back up some stuff now!
Did this article help you any? Please feel free to leave feedback.