Crontab scripts for MySQL

So if your like most web programmers you have a mysql database running your site. You need to backup your database or get a copy of the database each night. Most providers give you backups already and this is probably the script they use.

Make sure that you have a user that can do the following permissions (Select + Lock_tables + Show_view). If these minimum permission are available the mysqldump will fail.
0 1 * * * mysqldump --opt DB_NAME -u USER --password=PASSWORD > /path/to/file/DB_NAME_`date +\%m`-`date +\%d`-`date +\%Y`.dump
The nice thing about this is that it will append the data and time to the end of the file for archive. Be careful with trying to dump all of the databases at once. I have found that it is best to space the CRONTABs about 5 minutes apart each night during slow time.

Now if you want to automate this completely then check out my rsync jobs which will then copy the mysql dump to an archival system. Also check out the script that you can use to send information from one server to another via ssh.


Popular posts from this blog

Console over SSH (ONTAP)

Brocade Zone Setup

vFoglight Purge Stale Objects (Groovy Script)