Row cannot be located for updating mysql server
I have 2 websites, 2 databases (same name) on 2 different web servers, S1 & S2. I want both the user data on both the websites to be the same.So if one user registers on S1, then that user registration information should be passed to S2.
anything will do), each one responding to a specific IP address: they don’t need to be identical, as long as they can both properly run the same version of My SQL Server (v5.1 or above is strongly suggested).
If a user registration information is updated on S1, the same information should be updated on S2. How can I create a trigger so that every time there is an insert / update / delete in database on S1, then the user table on S2 also gets automatically updated. storage engine on both servers, in conjunction with triggers, to allow each server to update the other server's database.
And every time there is an insert / update / delete in database on S2, then the user table on S1 also get automatically updated. This is not exactly a simple out-of-the-box solution, because it requires additional precautions and requires you to decide whether consistency or isolation tolerance is more important and allow the queries to fail when the other server isn't available (more consistency) or use a CREATE TABLE remote_user ( username varchar(64) NOT NULL, password varbinary(48) NOT NULL, /* encrypted of course */ PRIMARY KEY(username) ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://username:[email protected]_other_host:port/schema/user'; Selecting from remote_user on one server will retrieve the records from the other server, and insert/update/delete on that table will change data on the other server.
So, we create triggers do accomplish the purpose of updating the distance server.
They are written as triggers, with the idea being that we don't want to do something to ourselves that we can't do to the other server -- for example, if a username already exists on the other server, but not here, we want the insert on the other server to throw an error that prevents us from creating the user here...