Backing Up And Restoring Mysql Databases

[s2city 0]

If you're an Administrator on a Forum, you probably know the importance of regular data backups. My Forum is always being hacked by someone and they always delete our SQL Databases. Well this tutorial is for all of you who want to protect your data and restore it if necessary!

Okay, backing up your data is the first part.

I use Cron Jobs in my cPanel to automate the backup process.
Just use this code for backing up all your SQL Databases:

mysqldump -u root -psecret --all-databases > backup.sql

OR if you wish to backup only a single database:

mysqldump -u root -psecret stocksdb > stocksdb.sql

-u = your cpanel username
-p = your cpanel password
stocksdb = the database you wish to backup (if backing up only a single database)
backup.sql = the filename of the backup and it's location, in this case it would dump the backup into your root directory (outside public_html)

You can set the backup intervals in the Cron Task Manager, I usually have it backup every night at Midnight.

On to the Restoration process.

My Forum is rather large and runs vBulletin as the Forum Software. PHPMyAdmin is a great tool to use for importing small databases, but in my case, it times out the server. So I use Shell, you may have to ask your hosting provider for access to this feature, but trust me when I say that it's well worth it!

Once you've logged into your Shell Command Line, simply input this code to restore all the databases from your backup.sql file.

mysql -u root -psecret < backup.sql

OR if you wish to restore only a single database:

mysql -u root -psecret -D stocks2 < stocksdb.sql

-D = the database you wish to restore the backup into (you must create this yourself, I use PHPMyAdmin.)

Well, I hope this makes all the worrysome administrators out there sleep a little easier, knowing that if they should lose their data... they've got backups!

[s2city 0]

I prefer the Cron over GUI, but yes you can use that as well.

[areawe 0]

Thanks for this great information. I have always wondered how to do this. It will definitely come in handy for the web site I'm currently working on. Hmmm now I just need to decide which one is better to attempt, GUI or Cron Jobs. How difficult is this Cron Jobs to work with? It sounds like the GUI would be easier but also slower to use? I've heard of Cron Jobs, but don't really know what it is or how to use it. I'm very new to working with mySQL databases. Any info is much appreciated!

[snlildude87 0]

I've heard of Cron Jobs, but don't really know what it is or how to use it. I'm very new to working with mySQL databases. Any info is much appreciated!

159304[/snapback]

I didn't know what they were either, but OpaQue (admin here) explained it pretty nicely: http://forums.xisto.com/topic/6321-cron-jobs-cron-jobs-in-cpanel-cron-jobs-cron-jobs-using-php/

[s2city 0]

Yeah, Crons are really just a simple way of automating a shell command. That's pretty much it... it's like having someone else type in the command for you, so you don't have to remember!

[bureX 0]

Try phpMyBackupPro! ( http://www.phpmybackuppro.net/ )

It allows you to make backups at any time, at any place, with a very easy-to-use GUI! You can also use it to make cron jobs in no time!

[beeseven 0]

Do you have shell access here? Or are you hosted somewhere else?I want shell access ;_;

[bureX 0]

Yes, I think Xisto supports exec()-like PHP functions (executing shell commands)!(Yes, I'm hosted here at Xisto)

[areawe 0]

Yeah, Crons are really just a simple way of automating a shell command. That's pretty much it... it's like having someone else type in the command for you, so you don't have to remember!

160044[/snapback]

Ha! Thanks for the great explanation! You mean I wouldn't even have to touch finger to key and this Cron Jobs does it all for me? Sounds absolutely wonderful! LOL Where do I sign up?

[s2city 0]

Ha! Thanks for the great explanation! You mean I wouldn't even have to touch finger to key and this Cron Jobs does it all for me? Sounds absolutely wonderful! LOL Where do I sign up?

160303[/snapback]

Well, there is a little key touching involved... but only once. After that, you can sit back and relax. Just open your cPanel, look for Advanced Tools, and then click on Cron Jobs. I'd recommend clicking Standard, unless you know how to input time, Unix style. Where it says Command to run, type in whatever command you wish for it execute. In the case of this thread, that'd be backing up an SQL Database. So, if you want to backup all your databases, put this code in:

 

mysqldump -u root -psecret --all-databases > backup.sql

Make sure you fill in the appropriate entries for each option, you can scroll to my original post for the meanings of each part of the command.

 

If you want to backup only a single database, use this code:

 

mysql -u root -psecret -D stocks2 < stocksdb.sql

Again, make sure you fill in the appropriate entries for each option, you can scroll to my original post for the meanings of each part of the command.

 

Then set a time period, in which you'd like the command to execute. I usually have it run every night at midnight.

Minutes: 0

Hours: 0 = 12 AM/Midnight

Days: Every Day

Months: Every Month

Weekdays: Every Weekday

Okay... there you go.