2

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.

Improve this question

4 Answers 4

Reset to default
2

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.

Improve this answer
1

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

Improve this answer
0

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

Improve this answer
3
  • 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, 2011 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, 2011 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, 2011 at 15:18
-3

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.

Improve this answer
3
  • 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, 2011 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, 2011 at 12:02
  • just because it "works" doesn't mean that it's appropriate. You will run into issues eventually.
    – EEAA
    Apr 27, 2011 at 19:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .