Backing Up Your Database
This is a wiki article created by HostingRails users. Please login or signup to make edits.
After learning the hard way why it is important to have backups to your database, I wrote a small shell script for that purpose that you might find useful. The script is started once a day by cronjob and saves a database dump to a backup directory and gzips it. Being a pit paranoid I also copy this file by ftp to a server in another country (ok the last part is probably being very paranoid).
To be sure that the backup couldn't change anything in my database, I went to cPanel and added a new user "backup" giving him only the "select" and "lock tables" privileges. Then I created the directory "backup" in the root of my account.
Using a text editor I made the file backup.sh in the root of account:
#!/bin/sh
/usr/bin/mysqldump -h localhost Dbname -uUsername_backup -pDbpassword > /home/username/backup/`date +%Y%m%d`.sql
/usr/bin/gzip /home/username/backup/`date +%Y%m%d`.sql
cd /home/username/backup
ftp -n ftp.backupserver.com <<EOT
quote user Ftpuser
quote pass Ftppass
binary
put `date +%Y%m%d`.sql.gz
quit
EOT
In the second line the dump is zipped to reduce the file space. If you don't have a second server you can delete the rest. Otherwise replace ftp.backupserver.com with the ftp-Account of your second system and Ftpuser and Ftppass with the username.
After you have saved the file, make sure you set it as executable (chmod 755 backup.sh). You can test it by typing "./backup.sh" at the command line. If everything works, you can set up a cronjob to start the script every night:
Got to "Cron job" in your cPanel. Click on "Advanced (Unix Style)". Add a time for the backup (0 for hour and minute will make it start at midnight) and add "*" for day, month and weekday. The command should be "/home/username/backup.sh" with your username.
If you ever need to restore a backup, you can download it to your computer and upload it in the mySQL-Admin."
***
This seems to blow up if you have any views defined. When I try I get the message:
"mysqldump: Couldn't execute 'show create table `xxxx`':
SHOW VIEW command denied to user 'xxxx'@'localhost'
for table 'xxxx' (1142)"
Patrick
Kumar
Peedee