Solved

mysql triger to update table on multiple servers.

Posted on 2011-03-15
8
537 Views
Last Modified: 2012-05-11
I have a pbx system that has 4 mysql and 2 ms sql server data bases.  The is a table on each server/data base that is the same table (and must be kept in sync).. on all the server/dbases.

i'm writting a .net web app to update one table on one server, and then I need to make the insert, update, delete (via a trigger) replicate onto the other pbx dbase table on the other servers.

I want to know if I can write a trigger on one of the tables in one server/dbase that would replicate the insert, update, deletes from one to the other ms sql and my sql data base.  What would it take to do this?  Is there a better way.  We are using mysql 5.02.
0
Comment
Question by:jparlato
  • 5
  • 2
8 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 35143910
what about other way... one table in one db, the rest will use the table via db link...
so you do not need to do any replication... do you really need table in all servers?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 35143915
if you need tables in all servers, create trigger in one db (or copy the trigger to all servers but comment out the individual sections that replicates data on that server)
or create individual sp and insert/delete/update on all servers, an alternative to trigger...
0
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 35146628
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 51

Expert Comment

by:HainKurt
ID: 35148992
lets say we have 4 db

db1, db2, db3 and dbm (master db)

are you running dml on dbm only, or it may run on any database?
if it is running on dbm, then you can add dblinks from db1, db2 and db3 to dbm and add an alias to the table

create alias myTable for dbm.myTable

so on db1, db2, db3 we will have an object with the same name as table in dbm. and all applications use that as if it is on the same db

and you run your dml statements on master db only... or if you give full permission to aliased table, you can run dml on any database, but probably running on master would be best for performance issues...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 35149013
with federated solution, you create 3 federated tables on db1, db3, db3 showing dbm.myTable

similar to what I described above... but it may perform better... you should test both...
0
 

Author Comment

by:jparlato
ID: 35151537
I really like the alias solution... as I don't want to actually change the tables.  This is for a pbx system and it is a vendor system.  The problem is that we have no easy way to update all 4 mysql tables as well as the to ms sql server tables.  So we have an asp.net application to create, delete, and update the table, but we need all updates to go to all 6 table... This is just the way the pbx system is designed.  The tables have to all have the same data.   So, if you could give me just a little more clarification as to how this alias thing will work - and will it also work on the two ms sql servers - I think I would be ok with this.  So, I pick one server and call it my master.  My asp.net app updates that table on that server, then I create alias in each other server (my  sql and ms sql) to alias them to the master table (being updated) and then all updates will occur on all server/tables?  Is that what you are saying.  Thanks for any clarifications and sample ddl commans as I'm not a dba.. just a developer.
Thanks again in advance.
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 35151668
right now you have an asp app, and it is inserting/updating/deleting from one master db only
and you want this to be replicated to all databases automatically...

1. if you can update the asp app, just call the code multiple times for all servers (one insert will be multiple insert
)
2. if you do not want to touch app, create trigger on master table. create aliases (dblinks to other dbs, say db1, db2,...) and in this trigger do the same thing on each linked server

like

if inserting then
  insert into db1.myTable select * from inserted;
  insert into db2.myTable select * from inserted;
...
end if;

if deleting then
  delete from db1.myTable where id in (select id from deleted);
  delete from db2.myTable where id in (select id from deleted);
...
end if;

similar thing for update...
0
 

Author Closing Comment

by:jparlato
ID: 35153380
Great - and gave me some starter samples.  This is what I needed.
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now