MySQL backup InnoDB

Question

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,

• Valery Viktorovsky: It's possible to configure database replication (master/slave) and then create backup on slave server at any time.

Answer 1

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.