1

hello this is my mysql table

table_clients

  client_id
  client_username (UNIQUE)
  client_password

now i want to get 5 servers and install mysql on each server and use this table on each 5 servers.

my question is: is there any way that all 5 servers connected to each other (by 1 request) and converted to 1 big server? i mean if there is one user with username (hiworld) in server 2, can not create it this user again in other servers !. (connect all 5 server with same table together and make them 1 big server) .. is it possible?

my queries are too big (2-3 bilion) and i want to share them between 3-4 servers but (make servers united) like when use 1 server )

cmnardi
  • 1,051
  • 1
  • 13
  • 27
Samethings
  • 31
  • 7
  • Wouldn't MySQL Cluster been a better option? – MarcHoH Sep 06 '16 at 19:51
  • Can you explain more ? – Samethings Sep 06 '16 at 19:59
  • The wiki explains it. https://en.wikipedia.org/wiki/MySQL_Cluster This would keep full copies of all your data on each of the 5 servers. It changes your design plans but this makes more sense I think that what you were planning. Not sure if there is a reason one copy of the database should not have the other copies of the database data in it. – M T Head Sep 06 '16 at 20:33
  • You have 2-3 billion clients? Please clarify and elaborate. – Rick James Sep 06 '16 at 20:48
  • I think what the 2-3 billion client response was related to. Is you don't need a big data solution unless your dealing with volumes in the billions. If you are not you should work to make good use of a Sql based data solution that should solve your issues with the assumption that you don't have a billion clients and actually need a big data solution. – M T Head Sep 08 '16 at 17:08

1 Answers1

1

How to create linked server MySQL

"Cross Linked Servers" a functionality that exists on Ms Sql is the type of thing your looking for.

You would need to do some sort of insert trigger that checked to see if the name existed on the other server.

But this will be slow plus there is a risk of two servers getting the same name at the same time and allowing the insert because when it checked the other name was not already there.

No real good way of doing this. One idea may be to have only one master table for the table_clients. Make it a master to slave relationship. Any time you have to do an insert you insert to the master table/database, then copy that data to the slave instances. But you would still have to cross link the servers for this to work. What you are describing would require waiting on 5 servers to tell if the name has already been used. This way you only have to check on one server.

Community
  • 1
  • 1
M T Head
  • 1,085
  • 9
  • 13
  • some websites like facebook and ... what are they doing for heavy clients !!! all clients info are exists in one server ?? – Samethings Sep 06 '16 at 20:02
  • Websites like Facebook that are doing this are not using SQL compliant databases. They are using "Big Data" Solutions. This transaction isolation requirements of Sql are broken. They are using a "Cloud" database of sorts. These do not comply with the Sql specifications on transaction isolation. Their cloud database solution allows you to insert to your local copy on the cloud. Then that data gets propagated on other servers till it is on all of them. There is a risk of duplicate inserts on different parts of the world at the same time, both will be accepted. Facebook has many flaws. – M T Head Sep 06 '16 at 20:22
  • You are trying to follow "Sql rules" you have to drop Sql rules and follow "Big Data" Solutions if you want to get their speed. You have to also accept duplicates and find a way to remove them. This is one of the many trade offs for the greater speed of a cloud, big data solution. – M T Head Sep 06 '16 at 20:25