MySQL - What issues should I be aware of when se

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

MySQL - What issues should I be aware of when setting up two-way replication?

MySQL replication currently does not support any locking protocol between master and slave to guarantee the atomicity of a distributed (cross-server) update. In in other words, it is possible for client A to make an update to co-master 1, and in the meantime, before it propagates to co-master 2, client B could make an update to co-master 2 that will make the update of client A work differently than it did on co-master 1. Thus when the update of client A will make it to co-master 2, it will produce tables that will be different than what you have on co-master 1, even after all the updates from co-master 2 have also propagated. So you should not co-chain two servers in a two-way replication relationship, unless you are sure that you updates can safely happen in any order, or unless you take care of mis-ordered updates somehow in the client code.

You must also realize that two-way replication actually does not improve performance very much, if at all, as far as updates are concerned. Both servers need to do the same amount of updates each, as you would have one server do. The only difference is that there will be a little less lock contention, because the updates originating on another server will be serialized in one slave thread. This benefit, though, might be offset by network delays.

(Continued on next question...)

Other Job Interview Questions

• MySQL - Speed of INSERT Queries
• How would you change a column from VARCHAR(10) to VARCHAR(50)?
• What are some good ideas regarding user security in MySQL?
• Functionality Missing from MySQL - SELECT INTO TABLE
• Have you ever used MySQL Administrator and MySQL Query Browser?
• MySQL - What should I do to prepare my client code to use performance-enhancing replication?
• Have you ever used MySQL Administrator and MySQL Query Browser?
• What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?
• Use mysqldump to create a copy of the database?
• MySQL - Drawbacks to Creating Large Numbers of Tables in the Same Database
• More...