MySQL dual wizard replication - is this script safe?

Question

I currently have MySQL double replication (A ↔ B) installed and everything seems to work smoothly. I used the basic ideas here and here ,

Server A is my web server (VPS). User interaction with the application updates several fields in table X (which are replicated to server B). Server B is a weightlifter where all the big calculations are done. The cron task on server B regularly adds rows to table X (which are replicated to server A).

So server A can update (but never add) rows, and server B can add rows. Server B can also update fields in X, but only after that the user is no longer able to update this line.

What potential disasters can I expect with this scenario if I work with it? Or does it seem OK? I ask mainly because I am not aware that the simultaneous operation of anyone in the table (from copy A or copy B) can cause problems or if it's just operations on the same line that get hairy.

Answer 1

Double replication wizards are messy if you are trying to write to the same database on both wizards.

One of the biggest points of view (and high blood pressure) is the use of auto-increment keys .

Until you forget to set auto_increment_increment and auto_increment_offset , you can search for any data and retrieve the auto_incremented identifiers.

You just need to remember this rule: if you are reading the id from serverX, you should look for the necessary data from serverX using the same identifier.

Here is one saving grace for using two-repeater replication.

Suppose you have

• two databases (db1 and db2)
• two database servers (serverA and serverB)

If you impose the following restrictions

• all db1 entries on serverA
• all db2 entries on serverB

then you do not need to set auto_increment_increment and auto_increment_offset.

Hopefully my answer will clarify the good, bad and ugly use of the dual replication wizard.

• Here is a graphic example of 4 wizards using automatic increment settings
• Good article from Percona on this subject

Answer 2

I think you can depend on Percona XtraDB Cluster Feature 2: Multi-Master replication than MySQL regular replication

They promise the following:

By means of Multi-Master, I mean the ability to write to any node in your cluster and do not worry about the fact that you end up with a situation of lack of synchronization, since this happens regularly with regular MySQL replication if you inadvertently write to the wrong server.

Using a cluster, you can write any node, and the cluster guarantees consistent records. That is, the recording is either performed on all nodes, or not performed at all.

Two important implications of the Muti-master architecture.

First: we can have several applications running in parallel. This gives us true parallel replication. A slave can have many parallel threads, and you can set it to the wsrep_slave_threads variable

Secondly: there may be a short period of time when the subordinate is disconnected from the master. This is because the master can apply the event faster than the subordinate. And if you are reading from a slave, you can read data that has not yet changed. You can see it from the diagram. However, you can change this behavior using the wsrep_causal_reads = ON variable. In this case, the read on the slave will wait until the event is applied (this, however, will increase the response time of the read. This gap between the slave and the master is the reason why this replication is called " virtually synchronous replication ", and not real " synchronous replication "

The COMMIT behavior described is also of second serious importance. If you perform write transactions on two different nodes, the cluster will use an optimistic locking model. This means that the transaction will not check for possible lock conflicts during individual requests, but rather at the COMMIT stage. And you can get an ERROR response to COMMIT. I emphasize this as it is one of the inconsistencies with regular InnoDB that you may experience. In InnoDB, usually DEADLOCK and LOCK TIMEOUT errors occur in response to a specific request, but not to COMMIT. Well, if you follow good practice, you still check the error code after asking for “COMMIT”, but I have seen many applications that do not.

 So, if you plan to use Multi-Master capabilities of XtraDB Cluster, and run write transactions on several nodes, you may need to make sure you handle response on "COMMIT" query. 

You can find it here along with picturesque text.

Answer 3

Master-master replication can be very complex, are you sure this is the best solution for you? It is usually used for load balancing (for example, round-robin connects to your db servers), and sometimes when you want to avoid the effect of replication delay. The big problem is the auto_increment problem, which is supposedly solved using different offsets and increment values.

I think you need to change your configuration to a simple master-slave, making A a master and B a slave, if I'm not mistaken about the requirements of your system.

Answer 4

and thank you for sharing my cluster article Master-Master Mysql. As Rolando explained, this configuration is not suitable for most production conditions due to limited support for auto-increment.

The most suitable way to get a MySQL cluster is to use NDB, which requires at least 4 servers (2 controls and 2 data nodes).

I wrote a detailed article to run this work on only two servers, which is very similar to my previous article, but uses NDB instead.

http://www.hbyconsultancy.com/blog/mysql-cluster-ndb-up-and-running-7-4-and-6-3-on-ubuntu-server-trusty-14-04.html

Please note that I always recommend analyzing your needs and finding the most suitable solution, rather than just looking for affordable solutions and trying to find out if they fit your needs or not.

-Hatem

Answer 5

From my fairly extensive experience on this topic, I can say that someday you will regret writing to more than one teacher. It may soon be short, but it will. You will have two servers, each of which has the correct data and some incorrect data, and you either choose it as an authoritative source, or throw away the other (perhaps not knowing what you are throwing out), or you will reconcile these two, No matter As you develop it, you cannot rule out the possibility of this event, so the mathematical certainty is that this will happen someday.

Percona (my employer) handled probably a few hundred cases of recovery after you do what you are trying. Some of them take hours, some take weeks, one of which I helped by taking several months - and this is with great tools for help.

Use a different replication technology or find another way to do what you want to do. MMM will not help - it will bring disaster earlier. You cannot do this with standard MySQL replication, with or without external tools. You need new replication technology like Continuent Tungsten or Percona XtraDB Cluster.

It is often easier to simply solve a real need in a different way and refuse to record with multiple masters if you want to use MySQL vanilla replication.

Answer 6

I highly recommend learning a tool that will manage this for you. Replicating with multiple masters can be very difficult if things go wrong.

I would suggest something like Percona XtraDB Cluster . I am following this project and it looks really cool. I definitely think this will be a game change in the world of MySQL. This is still in beta.