Has anyone out there figured out a good way to do database backups without
shutting down the database? We are starting a 24 X 7 (international)
service and are trying to figure out the best way to backups without
stopping (killing) the database for an hour. As I understand it, our
1. Scheduled replication using the log files. This is the best solution for
us (no stopping the service at all) but the problems is that it is very hard
to determine if something goes wrong in the middle of the replication
process so we still need to do a full backup weekly (at least).
2. Setting a flag in the database to signal that we are backing up the
database and coding all our write operations to check that flag before
writing to the db so we don't write during backup (file copying). Besides
all that extra bug prone code, we still have to halt the service while we
copy GBs of stuff across disks.
3. Using mysqldump to pump the database to a backup file. The problem again
is that the -lock-tables option will cause our UIs to hang for an hour or so
until the tables are unlocked.
Are these just the price I must pay?