From what I understand, there are at least three ways one can go about replicating/mirroring MySQL databases to different servers overs the Internet
1. Use MySQL Replication.
All incoming data will be sent to a master which will then read the binary logs to the slaves. The replication can be “circular” or “one-master-to-many-slaves”
2. Use MySQL Clusters
Pros: Delivers “99.999%” availability
Cons: Need a lot more RAM and hardware for heavy load system
3. Through Application (Redundant)
Application first sends the data / operation to a master for replication. Following the success of the above, the application then sends data to a third independent server (that is not replicated)
Since 2004, I have been using replication and it works fine except when for some reason the slaves get behind the master.
I would like to ask the MySQL gurus here a few questions.
What is the best way in your experience to ensure 100% data mirroring, since Replication can break and slaves can, at rare but possible instances, fall behind the master (for e.g. when file based operations are performed on master)?
Can MySQL use UDP? I know using the option –master-port, one can easily specify the TCP/IP port number where the master is listening. Is there any way to use UDP for replication or for clusters to avoid TCP/IP overhead? I believe MySQL uses TCP/IP for all its operations over the Internet. Can someone kindly confirm or deny that? Also, What are the options if someone insists on using UDP to send data to MySQL for their application?
Can MySQL triggers be used to update a database on a separate host? My research so far tells me “no” as there is no way (that I see) to connect to a remote machine using triggers? Am I right in assuming that?
Am I right to assume that “circular” replication is more suitable for a mission critical, large scale application where 100% data mirroring needs to be ensured?
Many thanks for your valuable comments/suggestions/insights.
Frank
7 comments:
If you are on Linux and you use a cold standby server, you should also take a look at DRBD, which provides mirroring a block device via the network to another machine. This replication is done on a block device level, so you may have perform a check of the table files for integrity before you can failover to the standby system, in case your primary node fails.
Hi!
1) Circular yes. One master used by many slaves, yes.
2) 5.1 will have on disk operations. RAM is cheap I might point out.
3) When I have seen slaves fall behind, most of the time it had to do with changes in configuration between master and slave.
4) No UDP support.
5) Yes they can, through federated tables.
Cheers,
-Brian
mysql can not use udp for replication. you wouldn't really gain anything -- it would just need to reimplement all the stuff that tcp gives you over udp. there's value to that overhead.
and i bet you could use triggers to update a table on a foreign database by using the federated storage engine.
Hello Lenz,
Thank you for your comment and giving the tip for DRBD. I will check it out.
The application for which I am inquiring has heterogenous operating systems that will all send data that needs to be replicated.
Frank
Hello Brian and Jim,
Thank you for confirming that MySQL cannot use UDP and for pointing me to federated tables direction.
So what's the verdict in your opinion? I am tending to believe that clustering is the best option, followed by replication.
Do you agree?
Thanks
Frank
Slaves falling behind often happens when they are too highly loaded. Also happens because slaves update in a single thread and master in multiple, so slaves can't keep their disk systems as busy as a master. Single-threaded means that a single query which takes a while to run causes all others to lag until it completes. This also blocks InnoDB's ability to commit multiple concurrent transactions with a single fsync, so slave transaction commit rate may be lower for this reason.
James Day
Thank you James for your insightful comment.
Frank
Post a Comment