2

I have a VoIP server running 24x7. At low peak hour at lease 150+ users are connected. My server has MySQL running with InnoDB engine on Windows 2008 platform. I like to take at least 2 times full database backup without shutting down my service.

As per Peter Zaitsev - the founder of percona, mysqldump –single-transaction is not always good.

read here if you are interested

As I'm not a DBA, I like to know in my scenario, which would be best solution to take a database backup?

Thanks,

1
  • It's possible to configure database replication (master/slave) and then create backup on slave server at any time. May 14, 2013 at 19:19

1 Answer 1

2

As your link points out, ALTER TABLE can break a transaction

In fact, according to Page 418 Paragraph 3 of MySQL 5.0 Certification Study Guide

sx

the following commands can break a transaction

  • ALTER TABLE
  • BEGIN
  • CREATE INDEX
  • DROP DATABASE
  • DROP INDEX
  • DROP TABLE
  • RENAME TABLE
  • TRUNCATE TABLE
  • LOCK TABLES
  • UNLOCK TABLES
  • SET AUTOCOMMIT = 1
  • START TRANSACTION

Thus, you cannot run any of these and have a stable mysqldump

SUGGESTION

You badly need to create a MySQL Replication setup

That way, the Master and stay up 24/7.

On the Slave, you can do the following:

  • STOP SLAVE;
  • mysqldump --single-transaction
  • START SLAVE;

and never disturb the Master.

CAVEAT

Since setting up Replication requires a stable mysqldump, you may need downtime for the one-time setup. My answer would be a lot different if your MySQL was in Linux.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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