How to Backup Your MySql Database

[Greg]

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 -Q -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!

[AnthonyCea]

This seems much more complex than the response you gave in this thread about me doing a back up remotely to a different server!

http://www.cpurigs.com/forums/showth...5701#post15701

What I am trying to learn is how I can back up my forums at separate data center server where I can buy space to store all my back ups on!

You also have to realize that I have never done this before Noppid.

So what specifically do I need to do to accomplish the above before I get really confused and give up?

[Greg]

Since I've not done it, I could only guess. I don't have a turnkey solution I know about to suggest.

[AnthonyCea]

Well think about it, because a lot of forum owners have the same problem that needs to be solved

[Zero Tolerance]

Ive been using the above method for a long time....and when I want a backup to my local media....I just use my forum AdminCP and dump it to a CD...then the CD goes into my fire safe.

I dont personally see the need to pay for data assurance. Waste of money in my opinion. But also you need to realize that backing up your database is only part of having a disater recovery system. You obviously will need to backup any directories that contain user upload files....or else you only have half the horse.

[Greg]

Problem with PHPadmin Is that not all folks have it and some servers time out on databases of 100 MB or more when downloading often.

Anthony. I have no interest in writing a how to that sends business to other people. If I provided that service, perhaps I'd venture an opinion.

[AnthonyCea]

Well PM me something or start the service Noppid.

[Big Dan]

Thanks for the how to.

[AnthonyCea]

Even with instructions some of us still can't figure out how to do it!

[Greg]

Originally Posted by AnthonyCea

Even with instructions some of us still can't figure out how to do it!

Please be specific and I'll be glad to try and help you sort it out.

Thanks