Jump to content
xisto Community
s2city

Backing Up And Restoring Mysql Databases

Recommended Posts

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! :unsure:

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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/

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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? :P

Share this post


Link to post
Share on other sites

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? :P

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.