Multi Master MySQL Replication

[pilot830]

If I'm understanding it correctly, the script currently does not have support for the ability to have multiple mysql servers (for if your site is popular and busy) to be able to write to the database.. i.e big sites, they don't operate with just ONE mysql server... and I think everyone here is aspiring to have a site that is so busy, that it is necessary to have more than one mysql server..... So i feel this is a very important RFC that everyone should be wanting..

Otherwise, if we are limited to one mysql server, that is limiting our success and I don't think that is a good thing. So provided I am right that the script does not support multiple mysql servers writing into the db at the same time...... please add this support..

 

[japmedia]

Usually this kind of problem is solved from the host with load balancing with fail over ip. It's how large website works (facebook, google, CNN)
more infos: https://en.wikipedia.org/wiki/Load_balancing_%28computing%29

 

[enricodias4654]

If I'm understanding it correctly, the script currently does not have support for the ability to have multiple mysql servers (for if your site is popular and busy) to be able to write to the database.. i.e big sites, they don't operate with just ONE mysql server... and I think everyone here is aspiring to have a site that is so busy, that it is necessary to have more than one mysql server..... So i feel this is a very important RFC that everyone should be wanting..

Otherwise, if we are limited to one mysql server, that is limiting our success and I don't think that is a good thing. So provided I am right that the script does not support multiple mysql servers writing into the db at the same time...... please add this support..

You can't make multimaster with the free version of mysql. If you do, you will have several problems. What you can do is have one master and several slaves. Having slaves will solve problems regarding reads, but not writes.

As far as I can see, YS makes way more inserts and updates than selects. So multiple mysql servers will not help you. If your mysql can't handle your site you should work on your mysql config and make minor changes on the script to make it more efficient. You can also move your mysql to a dedicated server with lots of ram and ssd disks. A server with 64gb ram, 2x 512gb SSD in raid 1 should be able to handle millions of unique visitors daily.

 

[pilot830]

You can't make multimaster with the free version of mysql.

I cannot find any information online stating that you can't do multi master mysql for free. Can you show me where it says we're required to pay money and use commercial version? I am being told that it was always available in ALL VERSIONS since the very first implementation of the replication..

If you do, you will have several problems.

What problems would those be? That things might overlap and so forth? If so, that's precisely why i'm asking yetishare to add support for multi master. If that's not what you mean, let me know what the problems would be, i'd be interested in hearing them

you should work on your mysql config and make minor changes on the script to make it more efficient. You can also move your mysql to a dedicated server with lots of ram and ssd disks.

Well of course that is what I would do first, but sure, you are probably right that a server with 64gb ram, 2x 512gb SSD in raid 1 should be able to handle millions of unique visitors daily.

The main thing is, if I just stay with one mysql server, if my mysql server goes down, then my site is toast. So if i had more than one, wouldn't that keep the site online, for redundancy sake? For this reason I feel we should not be limited to one mysql server

 

[enricodias4654]

I cannot find any information online stating that you can't do multi master mysql for free. Can you show me where it says we're required to pay money and use commercial version? I am being told that it was always available in ALL VERSIONS since the very first implementation of the replication..

The free version just replicate the date. It doesn't support transactions between several servers. Quick example: 2 users register in your website at the same time (or few seconds apart), the script send the insert of the first user to mysql server 1 and the second user to mysql server 2. Both mysql servers will perform an insert in the user's table with the same userId (primary key, auto increment). Then you have a problem, 2 servers with different users with the same id.

This is just a silly example to ilustrate. There are many other implications.

The main thing is, if I just stay with one mysql server, if my mysql server goes down, then my site is toast. So if i had more than one, wouldn't that keep the site online, for redundancy sake? For this reason I feel we should not be limited to one mysql server

Mysql replication will not solve this problem. A high availability cluster will. If your mysql server is on a separate server accessible only by your servers, using hardware raid and a redundant power supply it's unlikely it will go down without warning. What you can do is use the mysql replication with one slave server and switch this slave to master if necessary. In that case you will only loose the data created in the last few seconds.

 

[sukhman21]

personally, i just started using a openstack cloud server and so far its working great, you get low downtime and high availability, basically you wont be worried about hardware issues, failed HDD's, etc, etc..