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,

| improve this question | |
  • It's possible to configure database replication (master/slave) and then create backup on slave server at any time. – Valery Viktorovsky May 14 '13 at 19:19
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.

| improve this answer | |
  • Hi Rolando, thanks for help. I may manage to get the DB server migrate on Linux. what would you suggest then? Is that a better idea? – level3 Mar 15 '13 at 21:51
  • 2
    You could setup replication from Windows to Linux (although it is a little messy). I've done that twice at my job (1st time about 3 years, last time 3 days ago). – RolandoMySQLDBA Mar 15 '13 at 22:23
  • 2
    Once you migrate to Linux, setup Circular Replication between two Linux servers and ditch Windows for MySQL. – RolandoMySQLDBA Mar 15 '13 at 22:24
  • 1
    88% of survey respondants say they use MySQL on Linux. mysqlperformanceblog.com/2012/11/30/… – Bill Karwin Dec 11 '13 at 5:52
  • 1
    One caveat about backing up from a slave: make sure the slave has no data drift. Use pt-table-checksum. – Bill Karwin Dec 11 '13 at 5:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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