Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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,
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
the following commands can break a transaction
ALTER TABLEBEGINCREATE INDEXDROP DATABASEDROP INDEXDROP TABLERENAME TABLETRUNCATE TABLELOCK TABLESUNLOCK TABLESSET AUTOCOMMIT = 1START TRANSACTIONThus, you cannot run any of these and have a stable mysqldump
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-transactionSTART SLAVE;and never disturb the Master.
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.
