Create a trigger for doing a insert from a remote database/server

[riahc3]

Hello

Using MySQL, I have DB 1 with table T1 on Server 1 and DB 2 with table T1 on Server 2. Not on the same network and T1 contain the same columns with the same datatypes.

I want to create a trigger on DB 1 so when there is a insert on T1 on DB 1, all the data inserted in T1 is copied (or inserted) into T1 but the T1 that is on DB 2 on Server 2.

Thanks for the help,

[smantscheff]

I don't think there is an API or function to connect to an external database from within a stored procedure.
You could alternatively set up a complete database replication or execute the DML statements from your scripting environment by use of transactions which you rollback if one command fails.

[riahc3]

I don't think there is an API or function to connect to an external database from within a stored procedure. You could alternatively set up a complete database replication or execute the DML statements from your scripting environment by use of transactions which you rollback if one command fails.

(Considering everything is the same as the first post. and ignoring sintaxis errors.....)

I cant do something like:

create trigger x after insert on db1.t1
for each row
being
insert * from db1.t1 into db2.t1
end;

(quick and dirty example just to show what I mean)

[smantscheff]

Yes you can, but only if db1 and db2 share the same connection parameters: are on the same server and have the same user credentials (username, password).

[riahc3]

Yes you can, but only if db1 and db2 share the same connection parameters: are on the same server and have the same user credentials (username, password).

The only thing different is as I said: Different servers.

I cant do this if they are on different servers?

If not (which would slow things down alot....), any way I can emulate it in a Java servlet at least?

[smantscheff]

I cant do this if they are on different servers?

AFAIK, no.If not (which would slow things down alot....), any way I can emulate it in a Java servlet at least?
Slow down how many things? How many transactions per second do you expect? Why can't you use standard mysql replication? And as I said,execute the DML statements from your scripting environment by use of transactions which you rollback if one command fails
If you do it with Java or PHP or whatever, does not matter. You just have two connections to which you feed the same statements.

[riahc3]

Slow down how many things? How many transactions per second do you expect? Why can't you use standard mysql replication? And as I said,

I ment slow down in development....

The situation is that there is a server in one place (our host) with Magento installed which has its own database with tables for a application we need. Our application server in Java which interacts with tables but (for some reason) we cant modify the Magento database (thats going to be on another server: thats what we have been told and recommended) to add those tables so they can all be in the same database. We've been told to have two databases: The one in Magento needed for the application on that server (which runs PHP) and the one on our server for (for example) logins and such (which runs Java: We are also told that they cant be on the same server).

My original idea was to have it all in one database but we have been told that for security reasons it is not advised so

[smantscheff]

If your idea is to replicate the development steps on the production server by paralleling database statements, then I too deem this as highly insecure. Your development path should instead include a routine which runs all necessary database commands against the production database after they have been thoroughly tested in the development environment. Otherwise you wouldn't need a development environment at all and could develop directly on the production server - bugs and downtime included.
Still, to answer your question, you could install the database on the production server as db2 with the same credentials as db1 and then duplicate your transactions.

[riahc3]

It seems the solution Ive found is federated tables:

http://en.wikipedia.org/wiki/MySQL_Federated

I can excute the triggers on my local side (Server 2) as if they were Server 1.

This is as close as it can get right?

[smantscheff]

Oh, that looks promising. Thanks for sharing.

[riahc3]

Oh, that looks promising. Thanks for sharing.

No problem :) And great to hear I have another recommendation on it being a good idea. Reenforces it a alot so thanks :)

[riahc3]

More information:

http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html