Thanks for the good input, especially from Paul and Dirk. It may help people to understand that we have approximately 500 instances of MySQL distributed across a dozen servers in 6 pacemaker+drbd clusters. Each cluster consists of 2 nodes, with 40 instances of MySQL per node. Each node is primary for its own drbd device and replicates to the other node. If a node fails, the surviving node becomes primary for both drbd devices and temporarily runs all 80 MySQL instances. Running that many MySQL instances on one server has never been a significant problem for us and performance has been good. However, if we switch to innodb, then the disk and memory footprint will change and I don't think the same model could be maintained.
Secondly, we backup each mysql instance at night by performing two rsyncs of the live running database to a separate archive server.The first rsync copies the changes that have occurred during the production day. Then we do a 'flush tables with read lock' and perform a second rsync, which gets the changes that occurred while the first rsync was in progress. The databases are read locked for about 1 second, often less. In this manner, we get a 100% binary backup of the databases, but the system remains accessible to users 24x7. This is way faster and more efficient than use mysqldump. However, again, this technique will apparently not work with innodb, for some reason that I still do not understand. Apparently there is no 'flush tables with read lock' in innodb, or if there is, it still does not work the way it does with myisam, which is what I am struggling to understand. To answer Jacek's question about why we don't use built in mysql replication, it is because it is inefficient and error prone. There are two many conditions that lead to the slave not being a 100% exact copy of the master, and for that reason many db admins do not consider mysql slaves to be safe for using as a backup source. Verifying slave reliability can only be properly done using checksums, which is an expensive process. Re-syncing the slave to the master is painful. And finally, doing so would require us to run twice as many instances of MySQL (1000 instead of 500) and our cluster node failover strategy described above would be more financially expensive due to the requirement for more hardware. I realize that all this mysql chatter is dangerously close to being off-topic, so I appreciate everyone's patience. Thanks again. For what it's worth, we run MySQL+InnoDB on DRBD and use Heartbeat+Pacemaker for failover between nodes at different sites. This supports our Moodle VLE which is an important part of our course provision. As yet we have had no problems. We also use mysqldump to back up databases. To quote from Wikipedia: " InnoDB recovers from a crash or other unexpected shutdown by replaying its logs" These logs, by nature of the fact they're on a DRBD device, will be identical on both nodes, so if, say, node-A fails, node-B can assume Primary role for the DRBD device and mysql can start up, replaying its logs to may sure the data is up-to-date. I think whoever told you mysql would not start properly was possibly referring to what would happen if you simply copied all the files (with, for instance, rsync) whilst the database was active. -- Paul Walsh Disclaimer - June 13, 2013 This email and any files transmitted with it are confidential and intended solely for 'Paul Walsh',[email protected]. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
_______________________________________________ drbd-user mailing list [email protected] http://lists.linbit.com/mailman/listinfo/drbd-user
