MySQL table replicated between servers

Question

Thats the problem I'm facing:

Given tree servers, each one offers some web services and have a table login like:

[id,username, password, email,...]

My target is to allow the access in each server to the users in the others, keeping the inter-server independence The desired behavior isn't complex:

• When a user is registered in one of the servers that user should be added to the other servers without taking too long.
• When a user change his pass in one server the others server must reflect that change too.
• If two changes collide then keep only the newest change

I have been asked to do this without spending much time so I wonder if there is any standard and easy-to-perform solution to this problem.

All the servers use REST web service with PHP and MySQL.

It is for a shared hosting so I can't perform admin actions like configuring the mySQL server

Answer 1

You can create two mysql users.

• first user will given write privileges and point to master
• second user will given read only privileges, can load balance between the three servers

Change your application when require write, connect mysql using first user.
If require read only, use the second user.

I don't think share hosting is an problem,
pay more money to ask the hosting company to do the necessary configuration (that's obvious)
Or seek for other hosting company that allow administrator access such as AWS.

Answer 2

You can replicate data between databases using MYSQL replication.

Usually it is used to replicate a whole DB but you can use do/ignore and rewrite rules to specify which tables to replicate.

replication filtering rules

replication logging

I have never used MYSQL replication this way so can't help further than this but I know it is possible.