I’m running out the door to a meeting, so my response will be a little brief for now.
But yes, reads are fast. And writes are done entirely independent of the remote database. Each database is setup to log all writes to a binary log that the remote server can access using a special account given replication access. So that way, no process has to wait until the remote side gives the “okay”. To make this safe, you can configure each server to a specific multiple for the auto-increment IDs, such as giving server 1 the 1/3/5/etc and server 2 will write all 2/4/6/8/etc … That way you do not have the issue of running into a clashing unique ID if two writes happened at the same time or if the databases were temporarily out of communication. When a connection is out for any period of time, the remote server (or both of them in a master-master configuration) will access the binary log when the communication is re-established and catch up. The MySQL configuration allows you to configure the maximum amount of binary logs to store, because they do take up quite a bit of space if you have lots of writes. So in the case where the two databases are disconnected for a period of time that exceeds the binary logs, you will have to re-create the configuration by dumping the most updated database, importing to the remote server, then setting up the master-master replication again. My configuration is set to keep logs for 10 days, which is generally about 3.5GB worth of binary logs. So not that bad. Rarely our servers are disconnected for longer than a few minutes at a time, so consistency has not been an issue. When it *IS* an issue, for example when there is a single database at a remote site with non-enterprise internet, that can be a big pain to get back in sync if the database size is very large. But for any legit datacenter or proper connection, I have never had a problem. If the databases do get out of sync and it is a serious problem, both databases will continue to serve out their reads/writes as if they were totally independent of each other. The admin will have to figure out how he will merge any inconsistent data manually, and then re-establish the replication setup after that is done. I haven’t had to do that (yet), but it is most definitely doable. So far, we have not had any simultaneous writes done to the same record at the same time without the ability to synchronize before the next write is done (from whichever side). Our MySQL-based databases are generally much friendlier than our SQL Server databases. Those are a total nightmare since each developer has a very different way of misconfiguring their entire database design, and I would never think of replicating in this fashion (though Microsoft seems to think it is doable without any problems). For a CipherMail configuration, I really do not think that there is much possibility to run into any clashing data. Anyhow, got to run. Take care Martijn! ~ Laz Peterson Paravis, LLC > On Jul 7, 2015, at 9:29 AM, Martijn Brinkers <[email protected]> wrote: > > On 07/07/2015 03:26 PM, Laz C. Peterson wrote: >> Cool, thanks Martijn! >> >> I have tested the initial release quite a bit over the past day or >> so. Actually thinking about going live tomorrow or Thursday. With >> of course a fallback to the existing CipherMail installation if any >> issues arise. >> >> The architecture goes like this … >> >> Two sites on two independent networks/datacenters, MySQL 5.5 database >> at each location with bidirectional master-master replication for the >> back-end. For the mail services, at each location, we have one >> primary MX for local delivery and one backup MX for store/forward. >> The primary MX delivers to Dovecot at its respective site, which then >> synchronizes using dsync (which works very well I might add). Our >> users are able to authenticate, send and receive from each site >> entirely independent of the other site. >> >> Now, with CipherMail able to use MySQL as its DB, we can configure >> each site's current primary MX as a relay destination, and allow >> CipherMail to take over as primary MX duty. CipherMail will be each >> site’s gateway in and out for both internal and external domains, >> relaying to the Postfix server that lives with Dovecot or sending to >> the outside world. >> >> We have tested sending encrypted email out from one site and having >> the external user access the web portal from the second site. >> Everything works great so far. Our particular setup is more for >> disaster recovery, but the exact same setup could be applied for >> failover and load balancing. >> >> (Of course, a single database using PostgreSQL would be sufficient >> for two CipherMail servers to share at a single site. But the >> replication process for PostgreSQL was a bit complicated and immature >> at this time to apply for WAN.) >> >> I will upgrade the packages later on today and give it a go. Thanks >> again. > > Sounds like a nice setup :) > > A couple of questions, since I'm not familiar with the HA options of MySQL. > > Reads will be fast since they can be read from the local database. With > writes I guess a write is blocked until all databases have finished the > transaction? If so the write speed will depend on the latency between > the datacenters. The majority of databases access is read only so that > should not be a problem. > > How does the system handle a connection failure? It can happen that the > databases get out of sync when a connection between the databases is > lost. Should you manually tell which server is the "good" server? > > Kind regards, > > Martijn Brinkers > > -- > CipherMail email encryption > > Open source email encryption gateway with support for S/MIME, OpenPGP > and PDF messaging. > > https://www.ciphermail.com > > Twitter: http://twitter.com/CipherMail > > -- > CipherMail email encryption > > Open source email encryption gateway with support for S/MIME, OpenPGP > and PDF messaging. > > https://www.ciphermail.com > > Twitter: http://twitter.com/CipherMail > _______________________________________________ > Users mailing list > [email protected] > https://lists.djigzo.com/lists/listinfo/users _______________________________________________ Users mailing list [email protected] https://lists.djigzo.com/lists/listinfo/users
