Backups are useful, and a must have feature of anyone’s site or product if you are going to be successful. Backup as often as you can, as much as you can. In this short post I’m going to quickly outline Linux CRON Scheduling with MySQL to backup your databases.
Once logged into your server run:
crontab -e
This is like your CRON configuration file and will show you all scheduled tasks that you currently have running in your system. Here is an example of what part of my config file looks like:
# m h dom mon dow command
# db Backups - Every day at 4AM. - Write to _backups folder.
0 4 * * * /usr/bin/mysqldump -u'root' -p'[password]' dbname1 | /bin/gzip -9 > /path/to/backups/dbname1-`date +\%m-\%d-\%Y`.gz
0 4 * * * /usr/bin/mysqldump -u'root' -p'[password]' dbname2 | /bin/gzip -9 > /path/to/backups/dbname2-`date +\%m-\%d-\%Y`.gz
0 4 * * * /usr/bin/mysqldump -u'root' -p'[password]' dbname3 | /bin/gzip -9 > /path/to/backups/dbname3-`date +\%m-\%d-\%Y`.gz
# db backup cleanup - Every day at 3AM. - Remove backups that are 30+ days old (this is based on last time the file was modified)
0 3 * * * /usr/bin/find /var/www/html/_backups/ -mtime 30 -exec rm {} \;
These are some pretty basic setups, so I’ll explain the very first line:
0 4 * * * /usr/bin/mysqldump -u'root' -p'[password]' dbname1 | /bin/gzip -9 > /path/to/backups/dbname1-`date +\%m-\%d-\%Y`.gz
In this cron schedule I am backing up a database named “dbname1” using the mysqldump executable. Then I am getting a little fancy and zipping the dump (as to save space) using the gzip executable. This new zip file is being moved to a backups folder of my choice and I’m naming the file with the days date, since these are occurring daily.
You may be puzzled by now at what the “0 4 * * *” does. Well there are explanations out there to explain it more thoroughly but here is a quick explanation. The first 0 is for minutes. If you want the task to occur on any given minute, for example every 5 minutes, then you would do 5 * * * *. The second 4 is for hour of the day. This uses the 24-hour system so a 4 would be 0400 or 4AM. So in my case, I have my task running at 0 minutes past 4AM, every day.. or 4AM every day. The third entry is for a specific day of the month, 0-30 or so. So if you wanted the task to run on the 15th of every month, at 4AM you would do 0 4 15 * *. The fourth entry is for actual months, 0-12. If I wanted to run a task on the 15th of October at 4AM, each year, I would do: 0 4 15 10 *. Lastly the fifth entry is the day of the week, 0-7. Again using this format if I wanted to run a scheduled task at 4AM on Tuesday, every Tuesday, I would do 0 4 * * 2.
I hope this helps explain the CRON scheduling tab a little more to those just getting started. One flaw to note is that whenever you are making backups you, if at all possible, want to have them backing up “Off-site” instead of within the same server. What good is a backup if the server goes down and you can’t access them. I’ll write a part 2 on this for how to move backups off-site after you’ve done this much leg-work to make them happen!
If you find any errors in this post please let me know.
@brandongreen
Brandon
[mc4wp_form id=”277″]