10

I am creating a trigger in MySQL and I need a little help.

I have 2 websites, 2 databases (same name) on 2 different web servers, S1 & S2.

These databases have the same tables names.

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.

If a user registration information is updated on S1, the same information should be updated on S2.

And the same applies for 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.

And every time there is an insert / update / delete in database on S2, then the user table on S1 also get automatically updated.

Is this possible? Could you provide some examples?

3
  • I think you are after some sort of replication, not a trigger - to my knowledge the latter works only inside the same MySQL instance, that is, it is possible to define triggers to affect more than one database but they have to reside on the same server.
    – dezso
    Jun 18, 2013 at 13:22
  • Ok Thanks dezso. Is there any other way I can do the above in MySQL?
    – mkb
    Jun 18, 2013 at 17:06
  • 1
    In addition - be carefull. THis type of updates is only best done with something decoupled, otherwise the second server down will kill updates on the first database.
    – TomTom
    Aug 18, 2013 at 8:49

4 Answers 4

13

To accomplish what you are wanting to do, it is possible to use the FEDERATED storage engine on both servers, in conjunction with triggers, to allow each server to update the other server's database.

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 CONTINUE HANDLER to suppress errors (isolation tolerance).

But here is an extremely simplified example.

Each server would have the identical configuration.

The local user table:

CREATE TABLE user (
  username varchar(64) NOT NULL,
  password varbinary(48) NOT NULL, /* encrypted of course */
  PRIMARY KEY(username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A local table that is federated to the user table on the other server.

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:pass@the_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 BEFORE 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... as opposed to creating a user here with what would be a conflicting username. This is, of course, one of the tradeoff decisions you'll need to make.

DELIMITER $$

CREATE TRIGGER user_bi BEFORE INSERT ON user FOR EACH ROW
BEGIN
  INSERT INTO remote_user (username,password) VALUES (NEW.username,NEW.password);
END $$

CREATE TRIGGER user_bu BEFORE UPDATE ON user FOR EACH ROW
BEGIN
  UPDATE remote_user 
     SET username = NEW.username,
         password = NEW.password
   WHERE username = OLD.username;
END $$

CREATE TRIGGER user_bd BEFORE DELETE ON user FOR EACH ROW
BEGIN
  DELETE FROM remote_user
   WHERE username = OLD.username;
END $$

DELIMITER ;

This is not a perfect solution and is not a high-availability solution, because it relies on solid connectivity between the two systems and even if you are using InnoDB and transactions, the actions you take against the target table are not part of your local transaction and cannot be rolled back.

I use the FEDERATED engine quite a bit; it comes in handy for a number of creative purposes in my environment, including one situation where I used a federated query launched by a trigger to impose foreign key constraints against a foreign data source; however, I restrict its use to back-end processes where unexpected issues such as timeouts, coding errors, or server-to-server network/outage/isolation events cannot result in the end user on one of our web sites experiencing any kind of problem. Your ability to tolerate such a situation would be a major determining factor into whether this is an appropriate solution.

An alternative would be to configure your two servers in master/master replication. For this, you would need to use different database names on each server, so that for most events that replicate, the two servers could not possibly conflict with each other. In the worst-case scenario, if you lose connectivity or encounter a replication error, the two sites would still be running independently and you could resynchronize and recover. Configuration would look something like this:

database_a database for site A
database_b database for site B
database_c database for only the shared table(s)

Then, in database_a and database_b:

CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW user AS SELECT * FROM c.user;

MySQL will treat database_a.user and database_b.user as aliases for the "real" user table, database_c.user, so you would not have to change your application other than to use its designated database (i.e, you wouldn't have to configure it to understand that the user table was actually in a different schema, because the view will function pretty much transparently with this configuration). If the schemas have foreign keys against the user table, you would declare those against the true base table database_c.user.

Configure the two servers to replicate everything, but set auto_increment_increment and auto_increment_offset appropriately so you do not have conflicting auto-increment values on the shared table(s), if your tables use auto-increment. (Note, the documentation says that these variables are for NDB tables only, but that's not accurate).

An extra advantage of this setup is that your two servers would then have a complete duplicate of the other site's data that you could potentially use to your advantage for recovery from hardware failure in one of the servers.

1

It's only one table from many which should be equal, isn't it?

So, triggers won't work as they can only be executed in the same schema on the same server.

Guess you have 3 options:

  1. Synchronize manually, e.g. by invoking a script which executes all queries done on user data on S1 on the other server. Kinda ugly.

  2. Direct all queries on this table to one server, e.g. S1 contains the usertable and your application, whatever it is, on server 2 doesn't query the local usertable on S2, but remotely on S1. Not that better.

  3. Use the FEDERATED engine. Store the user data on S1, create the same table structure with FEDERATED on S2. This means, S2 does not hold the physical user data, but a connection to the table on S1. All actions taken on S2 are done on S1. See http://en.wikipedia.org/wiki/MySQL_Federated and http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html to find out if this suits your needs.

In all 3 scenarios it is hard to give a statement about the delays between the syncs. Option 3 is the only one where you'd need to adjust only the database, not the application, but I really don't know if that's the big thing.

0

Create a linked server between your servers and create a stored procedure on both servers.

Example:

CREATE proc p_insert(your parameters)

...your logic

INSERT INTO table values ()
INSERT INTO [AnotherSERVERNAME].[DATABASE NAME].dbo.[Table] values()

It may solve your problem.

0

Firstly a quick caveat - id probably go down the route of the other answers and use federation for your specific setup. That said it's worth a bit of thought on your behalf as to whether splitting the user data out into a third db would be a better solution.

It'll remove duplication from the 2 dbs but I'd anticipate that it would also require a fair amount of reworking to get the 2 sites working from one central authentication db

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.