Home / server / Questions / 149351
Accepted
Justin Dearing
Asked: 2010-06-09 10:45:58 +0800 CST

Is an NTBackup of a MySQL data directory reliable

  • 772

This question was asked on the MySQL forums in 2004 with no answers.

I'm installing MySQL 5.0.x on a Windows 2003 Server for use with Drupal. I began to configure the backup with mysqldump when it occurred to me that an ntbackup taken using shadow copying should be reliable enough for backing up the database. Is there any flaw in my logic?

windows-server-2003 mysql ntbackup

3 Answers

  1. Best Answer

    It entirely depends on the state of the mysql service at the time of the backup. But the simple answer is no, it is not a safe or good way to make a mysql backup. You should use the included mysqlbackup to make periodic backups of the database. You can make backups to the local machine and then backup the backups with ntbackup.

    • 6

  2. I guess the mysql db is in use. Otherwise it would not make sense.

    • It is not possible to compare the files after backup. They could have changed in the meantime.
    • And also you do not get a consistent snapshot.

    Maybe you want to set up a replicated mysql db and disconnect the slave from the master during you backup the slave db and then reconnect, so the slave can catch up again until your next backup. You can do this with some scripting.

    Edit: If you take read only snapshots using nt shadow copy, this does not change much. It will help you with the comparing problem but not with the consistency.

    If you want a consistent backup you should do it on mysql level, for example as I described above. Mysql will use the binary log to replicate the databases, and you should have maximum consistency. The binary log will respect the transactions.

    But on the application level there is something to consider: http://dev.mysql.com/doc/refman/5.0/en/replication-features-transactions.html

    • 1

  3. As a general rule, never backup the data directory (directories) of a running database. Only backup the directory if you can shut the database down first. If the database is running and supports hot backups, you can backup with the database running. This usually requires communication between the backup software and the database software.

    Possibly, but I wouldn't count on it. Data may be cached in memory or modified while you are copying it. If you shut down MySQL before backing up the data then it should be reliable. There are administration tools to do backups. These will create reliable backups in another directory.

    • 1