Is there a way to put several SQL to diffrente servers inside the same transaction?

Question

In my system we do action, and for reporting sake we log that action on another server (star schema if it interests). Obviously I need the action updates/inserts be in the same transaction as the logging.
So, is there a way to include two different sqls to two different servers in the same transaction?
Right now I manage that in the code level (php)

Answer 1

You can use the distributed transaction coordinator.

Configuring DTC.

Answer 2

This isn't possible with MySQL alone. While MySQL does have a Federated Storage Engine (ie: remote access to other MySQL servers) it doesn't support transactions. That means you can't coordinate a multi-server transaction directly through MySQL. MySQL does support XA for InnoDB tables, so you can use an external transaction manager with MySQL, but PHP doesn't have any support for XA. Generally this is considered an Enterprise-level feature, and you'd implement using Java or C#/.NET.

Answer 3

Complicated way is:

1. create same table on same server.
2. Make this server master
3. Set replica an this table

if you want that log table be not very big you can do:

1. first table name set _tmp
2. create on slave log_tmp trigger on insert and update to copy data to log table
3. after some time to delete from log_tmp in master some data.