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?
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.
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!
Olympus
Single & Not Looking
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 you'll have the database backed up offsite.
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: