Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 2 down vote favorite

We have 3 MySQL databases in a production environment on one Windows 2008 server. How do others schedule backups and how would you restore an individual database. I see there is a Data Dump project option but don't see how I would schedule it to run nightly.

share|improve this question

4 Answers

up vote -3 down vote

If its your own server in your office, you have many options. I assume you are using Wamp? If so in the folder of MySql is a folder called Data. In it are all your databases. I have used many programs to copy the files from there. You just don't want to do it during a write. I hope this helps.

share|improve this answer
1  
you shouldn't recommend copying the files of a running MySQL, and wamp has nothing to do with the fact that MySQL has (by default anyway) a folder called "data". – 3molo Apr 27 '11 at 6:55
I have copied files for years. Moreover I have restored files and run tests on the restored files regularly. My system works and is reliable in a office setting where there are hours on non uses. 11pm to 5am. – Joe in Michigan Apr 27 '11 at 12:02
just because it "works" doesn't mean that it's appropriate. You will run into issues eventually. – EEAA Apr 27 '11 at 19:25
up vote 0 down vote

Databases change faster then they can be copied and doing a file-level backup on a running MySQL database will result in a corrupt backup. you must either stop the database and preform a file-level backup or in most cases, backup the database to file using the program mysqldump.exe.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

share|improve this answer
The warning about not copying the files directly should be a comment to Joe in Michigans answer, not an answer to the actual question. – 3molo Apr 27 '11 at 6:54
That first line, with the needless capitals, is absolutely incorrect. Using mysqldump is perfectly safe on a running system and produces reliable backups. If you're going to make such wild statements at least make them in context. – John Gardeniers Apr 27 '11 at 8:06
Sorry, i work with databases daily and i was just trying to explain that you should never backup a running database using file-level tools, it has cause much pain for me and clients who thought that that they could backup the entire system using the average backup program. i apologize it could have been worded better. – Silverfire Apr 27 '11 at 15:18
up vote 1 down vote

Create a .bat file with mysqldump.exe (within bin directory), here you can find the command syntax.

share|improve this answer
up vote 2 down vote

Just to elaborate on lg's answer, mysqldump is the best and safest way to do your backups. Create your batch file(s) and run it/them via Task Scheduler.

If you ever get to the point where the backup process causes problems due to record or table locks (depending on the DB engine being used) it's time to set up master/slave replication. This can even be done by running multiple instances of MySQL on the same machine. The backups are then taken from the slave, which allows the master to run uninterrupted.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.