Backing Up your Forum Database via SSH or Cron

Backups are very important. This will show you how to do safe backups of your database via SSH command line access or a cron job from your server control panel.

First the backup...

mysqldump --opt -h localhost -u DB_Username -pDB_Password DB_Name > /home/account_name/folder_name/backup_name_date.sql

If you use a remote DB server, change localhost to the IP or domain name. The reason I show the full path to the backup folder is for those without SSH access. That way they can run this as a one time cron job from cpanel and get a proper backup to the correct folder. There is no space between the -p and DB_password, they are the only option connected. There is the possibility of someone seeing your DB password when using a cron job. If you run this from the command line and want to be safe, remove the password leaving just the -p and you should be prompted for it.

Now the restore...

mysql -h localhost -u DB_USER_NAME -pDB_PASSWORD DB_NAME < /home/ACCOUNT_NAME/BACKUP_FILE_NAME.sql

Again we use the full path for those without SSH access to be able to run this as a one time cron job. There is the possibility of someone seeing your DB password when using a cron job. If you run this from the command line and want to be safe, remove the password leaving the -p and you should be prompted for it.

I have used the method with very large databases successfully. Holler if you need more clarification.

To run a cron job, go to your control panel cron job (advanced unix style) creation screen. Note the current time before you start as we will set the backup to run in a few minutes. After it runs, return to the cron job (advanced unix style) screen and delete the cron job so it does not keep running every hour.

In the cron job screen, there is an email field, put your email address there so you can be notified of any errors that may occur.

You will also see a list of fields to fill out for the job itself...

Minute - Hour - Day - Month - Weekday - Command

In the minute field put the minute of the hour that the cron job should run. For instance, if you checked the time before starting and it was 8:30, a good time to run the job may be in five minutes. Enter 35 in the Minute field.

Then in the Hour, Day, Month, and Weekday Fields, put a *. That's a lone asterisks.

In the Command Field, put the mysql command for either the backup or restore, whichever you are running.

Check your backup folder, or DB screen after a restore, when the time you selected has passed and see if the backup files exists or the DB is restored. If it was successfull, return to the Cron screen and delete the cron job. If you don't, the job will run every hour.

If the backup file is not there or the DB not restored, check your email for an error message. That should tell you what went wrong and enable you to adjust. Delete the cron job and start over.

That should ensure your reliable backups. You should download the backup via FTP to your local computer to be extra safe.

Good luck and backup often!

Great deal, we have been waiting for this for a good while, back up and forum security are the most important factors in protecting a forums greatest asset, it's threads.

Thanks for the service to the community Noppid!

If you are on a dedicated server and have full ssh then there may be a better way.

I use cron to run my sql dumps and do so every morning early at a given time. I save the sql dump to a non apache folder and always keep a week old copy and the last copy to be on the safe side.

I run a dual xeon backup machine at my house with 500 gb of drives and using ssh run an rsync (example rsync -avzP --rsh=ssh whoever@whereever.com:/var/www/html .) of the /var/www/html folders of my on line servers from the remote machine. I then backup using rsync the folders that contain the db backup copies. This also works good for setting up one of your forums on the backup machine if you need to try an update or change before you do it to the active server.

Using rsync means that it only copies changed data even going as far as to compare the sql dumps and only altering the changed parts of them. This is very secure and very fast since your only downloading changed data and all done via ssh. I suggest making a dummy name on your server for the ssh to make it even more secure with no root access.

To retrieve a backup copy you can simply reverse the rsync using the live server and get the copy from the backup, again it only loads the changed bytes so is very fast.

Max

noppid is there a way to make a backup using ssh onto another server? Right now all my backups are stored on my server but I want the backups to be stored on my personal server just in case main server crashes.

noppid is there a way to make a backup using ssh onto another server? Right now all my backups are stored on my server but I want the backups to be stored on my personal server just in case main server crashes.

I used to use a cron that ran a shell script and after the backup it logged into my other server and FTPd the files to it. You can have the files deleted from the main server after they TX too.

noppid is there a way to make a backup using ssh onto another server? Right now all my backups are stored on my server but I want the backups to be stored on my personal server just in case main server crashes.

The backup to another server using ssh is what I discribed. Simply login to the backup server and run the rsync command and it will copy everything from the original server. You can do it every day and it only looks for new data and doesn't require that you download the entire site again.