Wednesday, February 25, 2009

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.

No comments:

Post a Comment

Raspberry Pi Zero W - Wireless Configuation

create the file under "boot" folder wpa_supplicant.conf country=GB ctrl_interface=DIR=/var/run/wpa_supplicant GROUP=netdev u...