User Login

Help Community Login:

Question regarding backup & restore of sql database

3 replies [Last post]
Evil Monkey's picture
Evil Monkey
Moderator (Watching Over The Masses)Premium Member (Silver)The Steel CurtainI use FirefoxI use Google ChromeI use Internet ExplorerI use SafariI'm Here To Help, & Have Proven It!Windows UserMember of VileThe JokerSomeone thinks you're a Rotten Tomato!STaRDoGG <3's you ;)
Joined: 01/22/2009
Posts: 234
Drops: 350

Hey Dogg,

I want to be able to schedual automatic backups of my sql database. Right now I have phpmyadmin handling the db and I have successfully been able to manually backup the dB, restore it to new DB, then copy the site into new directory and point to the new DB.

I would like however to be able to schedule backups of the database when this server is live wich I don't belive you can do with phpmyadmin. Also I am concerened because I don't belive phpmyadmin can handle backing up and resotreing a large database. After time the database can get quite large and I don't want to run into trouble with restoreing it if ever I had a problem.

Do you know of a way to handle this? Can I go download something like mysqladmin and connect it to my database?

I Averaged: 0 | 0 votes


Read More ...





Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
STaRDoGG's picture
From:
Olympus
STaRDoGG
Head Mucky MuckJoined the Dark SidePremium Member (Gold)I'm a Code Monkey!The Steel CurtainI use FirefoxI use Google ChromeI use Internet ExplorerI use SafariLinux UserMac UserWindows UserI donated to GeekDrop simply because I love it!Booga Booga BoogaI took a bite of the AppleFormer Phrozen Crew MemberI'm MagicMember of VileThe Dr. put the stem on the apple!The JokerSomeone thinks you're udderly delightful!
Relationship Status:
Single & Not Looking
Joined: 01/14/2009
Posts: 2595
Drops: 3066
Mood: Energetic
Re: Question regarding backup of sql server

For backing up your database the easiest way is to setup a cron job with crontab. Since you mentioned before that you use Appserve which has Apache as it's webserver I'm guessing it'll also have a crontab server in it too (both are Linux)?

Here's the cron job I setup to automatically backup my database every night, to a folder on my server. You can also have it sent to other places, like a DropBox folder instead if you want, that way if your server takes a ish you'll have the database backed up offsite.

mysqldump -u<USER NAME> -p<PASSWORD> <DATABASE NAME> > /home/geekdrop/public_html/backupeddb/GeekDrop-backup.sql


And you'll need to set a time for the cron job to run, for example, every night at midnight. Note that there's no space between your username and password after the -u / -p switches, and the last part is the full internal server path to the folder you want to backup to. Also note that this will overwrite the previous version each night. It's possible to have a random number added before the name of the DB, (like the date and time) so it won't overwrite it, and you'll have multiple copies. BlueHost, which is the host GD is on currently frowns upon basically having anything at all stored on your server other than what is actually "live", which includes database backups (strange, eh?) but I think they're sort of flexible with that since it's you know, a db backup and not a porn movie. Plus that it's only a single db backup. If you were to start storing a bunch of huge db backups they might call you out on it. I mention this because I'm not sure how other hosts handle that so it migh tbe something to be aware of later on when you choose one for your site.

As for the big database importing, PHPMyadmin does complain and sometimes choke when trying to. It's kind of hit and miss. It'll ask you if you want to keep trying after a certain point and you can, but sometimes it ends ok, sometimes it doesn't import correctly, so I just use the actual MySQL command to do it via MySQL itself.

For Windows the trick is to make sure you're at the plain Windows command prompt (not the Mysql command prompt), and have the DB you plan to import sitting in the same folder. Then just enter:

mysql --user root --password=<PASSWORD> <DATABASE NAME> < <BACKED UP DATABASE NAME>.sql



Shhh.. dont tell anyone, but we also have a private forum area with the really good stuff, see?

Evil Monkey's picture
Evil Monkey
Moderator (Watching Over The Masses)Premium Member (Silver)The Steel CurtainI use FirefoxI use Google ChromeI use Internet ExplorerI use SafariI'm Here To Help, & Have Proven It!Windows UserMember of VileThe JokerSomeone thinks you're a Rotten Tomato!STaRDoGG <3's you ;)
Joined: 01/22/2009
Posts: 234
Drops: 350
Re: Question regarding backup & restore of sql database

hmmm... don't seem to have a cron server with this. It's linux based so can I download and run one on a windows box?  this sure is turning into a pita to figure out a way to do this on a windows box.



I believe in making the world safe for our children, but not our children's children, Because I don't think children should be having sex.

Evil Monkey's picture
Evil Monkey
Moderator (Watching Over The Masses)Premium Member (Silver)The Steel CurtainI use FirefoxI use Google ChromeI use Internet ExplorerI use SafariI'm Here To Help, & Have Proven It!Windows UserMember of VileThe JokerSomeone thinks you're a Rotten Tomato!STaRDoGG <3's you ;)
Joined: 01/22/2009
Posts: 234
Drops: 350
Re: Question regarding backup & restore of sql database

OK, Think I'm starting to understand this.  With the installation of mysql I have mysqldump.  So I made a batch file that will run the dump command and copy it to a local dir. I can create a windows task to run that batch file at night each night.  That should work I'm guessing.

EDIT:  works. I wrote a batch file that backs the db up to a local dir and a netowrk shared directory.  Created a task so windows will run it every night at 10pm.  So that way I'll have a copy of the DB on the local server but if the actual server takes a dump it will be on a remote server as well.  both of wich will be included along with the actual website to our nightly backups to tape.  So that should providing I can restore the DB without any issues down the road if ever needed cover backing up this website.

Thanks for the advice!



I believe in making the world safe for our children, but not our children's children, Because I don't think children should be having sex.

Who's New

OkwuteAgu's picture
Dollaronewebhosting's picture
CB_Latin's picture
GrimRapper's picture
glrobins's picture
hady's picture
Jessica Davis's picture
CRowmAN's picture
redd993's picture
Austeen Legitt's picture
JimGuth's picture
ethan121's picture
gmailerror's picture
fmonras's picture
brandbugle's picture
facebook codes exploits tips tricks Phrozen Crew
All contents ©Copyright GeekDrop 2009-2019
TOS | Privacy Policy