Perhaps I somehow got confused that I did not take the transaction part as the bottleneck of the task.
I try to explain it in more detail.
1. Master and Slave Let's name the A mssql local database as the Master and B mysql database as the slave. Any kind of update on A will be "sychronized" updated on B. I put the double quotes here because it could never be the real sychronized which is simliar as credit card that assumes you live in Beijing and use ccard to get 100 Yuan from 1000 km away, what the bank does is not to wait until your main account of Beijing got 100 Yuan cut, which would take perhaps one day time, but it would simply check that you have enough money so far and give you the money first.
2. Trigger and On Update/Change of Table AFAIK, mssql has the trigger stuff similar as Oracle. I thought it would be done by a programming language to get the SQL Statement. Actually it should be done by the Trigger stuff which possessed by the DB itself. IMO, most basic operations to the A DB is not so vulnerable like "update" and "delete" to the B DB, becuase such statement could be run thousands of times without destroying anything of B DB. However, some other operations like "insert" is kind of fatal, which forcing us to focus on the network transaction side.
If you ever awared the cashier of Kenturky Fried Chicken or other fast food chain, you could know that the organization there are quite interesting. At the moment that the saler confirms a deal, it will not only record the ammount of hot-wing, pummies and cola just sold into the local system, but also post the info to the Kenturky main center of the city/province/china/or even the top depa. usa. In a word, it stores the record not only in the locale DB but also in the remote server. So, now the question comes. how to promise the transaction to be safe?
First of all, in the A DB side, we have to design one more table, i.e named as A.order.
A.order should at least coarse-grained contain such fields or even more elaborate. a. unique ID b. statement c. timestamp d. flag (for state like "ready to post", "have posted", ? "no response for 1 hour so that post failed", "post failed", etc...)
Now, the workflow would be, a. no matter in which way and who, the A DB is changed. The trigger organization awares that and record the statement into the A.order table. b. There must be a daemon running background to fetch each record from A.order, immigrate mssql statement into mysql statement, post to the socket of the B DB side which could be done by any http/https post or soap if you prefer a sledgehammer. c. After the socket of the B DB side gets the post, it executes the statement towards B DB and response a msg to the socket of A DB side. If it works successfully, A side will update the flag of the record of A.order. ? Advantage: even the network was down or in traffic jam, the system would work in somehow late. Disadvantage: this is a master-slave model. If the slave changes and gotta influence the master, hmmmm.... the system has to be redesign somehow.
hope this helpssandy
Addition:The msg just posted is still not complete...The slave end should have B.model same as A.model to achieve three-handshake...
--不可能太理想!yaya, however, after optimizing the system design it could be fine, since there are already various products available.
哦。要支持事务啊。那sandyde2(sandy)的英文帖差不多就是解决方法啦。 local database & remote database. 很多大商场的结帐系统都是使用此结构.不过具体细节上就看自己的了.
难搞,实时更新比较难做到。 不过可以使用job来一段时间更新远程的数据。
用类似的存储过程EXEC sp_addlinkedserver 'rServer','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=221.7.144.98;UID=userxxx;PWD=pwdxxx;' --rServer是链接服务器的名字 declare @aa numeric --当前服务器表中最大的id set @aa=0 select top 1 @aa=id from bookbuy order by id desc --查询链接服务器本插入到本地表中 --注意,本地的BookBuy的id列不能自动增。 insert into fangyan.dbo.BookBuy select * from rServer.guolianweb.fangyang.BookBuy where id>@aa
我没用过mysql
1,通过网页
2,通过本地script如果你直接改数据库,还要实现这个功能,我不会。。1。无论通过网页还是script,你肯定能在A端克隆下mssql statement
2。写个程序(java, php,asp, 哪个熟。。)把mssql语句转成mysql,不是很困难。。
3.1 在上面的程序里接着写,如果内容不大,可以直接post到B端,写另一个程序做
接口接收post来的内容,加载到mysql里
3.2 或把转化好的mysql statement存为个文件 a.csv,然后ftp到B端,启动
B端的程序加载到mysql里。每个文件起名为时戳,避免覆盖。如果用3.2,不太可能同步,如果A和B间是vpn连接就更成问题了。用3.1有transaction问题,建议用J2EE,看给多少钱了。。
the transaction part as the bottleneck of the task.
I try to explain it in more detail.
1. Master and Slave
Let's name the A mssql local database as the Master
and B mysql database as the slave. Any kind of update
on A will be "sychronized" updated on B. I put the
double quotes here because it could never be the real
sychronized which is simliar as credit card that assumes
you live in Beijing and use ccard to get 100 Yuan
from 1000 km away, what the bank does is not to wait
until your main account of Beijing got 100 Yuan cut,
which would take perhaps one day time, but it would
simply check that you have enough money so far and
give you the money first.
2. Trigger and On Update/Change of Table
AFAIK, mssql has the trigger stuff similar as Oracle.
I thought it would be done by a programming language
to get the SQL Statement. Actually it should be done
by the Trigger stuff which possessed by the DB itself.
IMO, most basic operations to the A DB is not so
vulnerable like "update" and "delete" to the B DB,
becuase such statement could be run thousands of times
without destroying anything of B DB. However, some
other operations like "insert" is kind of fatal,
which forcing us to focus on the network transaction side.
If you ever awared the cashier of Kenturky Fried
Chicken or other fast food chain, you could know that the
organization there are quite interesting. At the moment that
the saler confirms a deal, it will not only record the
ammount of hot-wing, pummies and cola just sold into the
local system, but also post the info to the Kenturky main
center of the city/province/china/or even the top depa. usa.
In a word, it stores the record not only in the locale DB
but also in the remote server. So, now the question comes.
how to promise the transaction to be safe?
First of all, in the A DB side, we have to design one more
table, i.e named as A.order.
A.order should at least coarse-grained contain such fields
or even more elaborate.
a. unique ID
b. statement
c. timestamp
d. flag (for state like "ready to post", "have posted",
? "no response for 1 hour so that post failed", "post failed", etc...)
Now, the workflow would be,
a. no matter in which way and who, the A DB is
changed. The trigger organization awares that and
record the statement into the A.order table.
b. There must be a daemon running background to
fetch each record from A.order, immigrate mssql
statement into mysql statement, post to the socket
of the B DB side which could be done by any http/https
post or soap if you prefer a sledgehammer.
c. After the socket of the B DB side gets the post,
it executes the statement towards B DB and response
a msg to the socket of A DB side. If it works
successfully, A side will update the flag of the record
of A.order.
? Advantage: even the network was down or in traffic jam,
the system would work in somehow late.
Disadvantage: this is a master-slave model. If the slave
changes and gotta influence the master, hmmmm.... the system
has to be redesign somehow.
hope this helpssandy
A.model to achieve three-handshake...
it could be fine, since there are already various
products available.
其实也就是将表格里的数据分别更新到sqlsever和mysql.不过使用odbc连接mysql好像有问题.用dbx试试看.我很少用dbx连接sqlserver。问问其他人。
我知道我的建议有无可行性.继续gz。
local database & remote database.
很多大商场的结帐系统都是使用此结构.不过具体细节上就看自己的了.
不过可以使用job来一段时间更新远程的数据。
--rServer是链接服务器的名字
declare @aa numeric
--当前服务器表中最大的id
set @aa=0
select top 1 @aa=id from bookbuy order by id desc
--查询链接服务器本插入到本地表中
--注意,本地的BookBuy的id列不能自动增。
insert into fangyan.dbo.BookBuy select * from rServer.guolianweb.fangyang.BookBuy where id>@aa
我的客户在本地使用的是MS SQL-Server,网上的web服务器是resin,数据库是MySQL3.23。
客户在本地进行添加、修改、删除操作,一定时间后需要使远程MySQL保持数据同步。我的初步想法如下:1:在MS SQL-Server中对需要保持同步的表(假设:student表)放上一个触发器,当用户对student进行添加、修改、删除的同时,触发器将此变动信息(从inserted和deleted表中可以获得)插入到一个历史变动表(假设:history表),此history表与student表具有相似的表结构,但还多几个字段(a)operate字段,用来记录操作行为add/del(注:对触发器来说,修改操作就是先删除,后添加,所以需要增加两条记录)
(b)flag,值默认为0(0:未更新远程MySQL,1:已更新)2:用PB做一个EXE,用PB的数据窗口对象和数据窗口控件按升序读取MS SQL-Server中的student表,(如果不按升序读取,对于修改操作来说,因为是先增加一条删除信息,后增加一条添加信息,则在更新远程MySQL的时候,会导致主键不唯一,数据覆盖等等异常),然后数据窗口对象根据operate字段用modify函数修改当前行成为add/del状态,使之模拟进行了添加或删除操作,在数据窗口对象相应事件中循环截获PB生成相应的insert into /delete 语句,将其存入连接成strsql=strsql+截获的sql+"#@#",(#@#用来分割各条sql语句)可用PB中的PostURL()函数,将最终strsql字符串发送到远程的服务器上。
注:此功能我们已经实现了
[函数详解:
servicereference.PostURL ( urlname, urldata, headers, data )
servicereference:Inet对象实例或引用urlname:String类型,指定要发送请求的URL(统一资源定位器)urldata:Blob类型,指定发送给urlname参数指定的URL的参数
headers:String类型,指定HTML首部。对Netscape浏览器来说,每个HTTP首部之后需要个换行符(~n),在所有首部之后再跟一个换行符(~n)data:InternetResult实例变量,用于保存返回的HTML
]3:在MySQL服务器端可以写一个servlet接收客户端传过来的数据包。将接受的字符串分割成数组ArraySQL,循环执行ArraySQL()中的SQL语句,即可更新MySQL中的student表。全部成功后发回一个标志给客户端,客户端接受标志后,将history中已发送的记录的flag更新为1。如果失败,回滚
注:此功能我们已经实现
还有一点非常重要,就是事务处理中的回滚功能,因为MySQL3.23不支持,所以我现在只能用MySQL4.0。因为我用的是resin所以,还需要和IIS来配合才能接受全部数据。否则接受能力有限。
it is already one month pass. He could achieve a bigger
project than this one in 30 days :)
call me Steven
修改 /etc/my.cnf 添加这是 salve 机器的配置文件 server-id = 2
master-host=192.168.0.119 # 这是 master 机器地址
master-user=root # master 的用户
master-password=123 #master 上存在并且可以有权访问你要备份的数据库
master-port=3306
master-connect-retry=5
# 下面就写你要镜像的数据库
replicate-do-db=test
replicate-do-db=test1
replicate-do-db=test2
replicate-do-db=test3然后把 master 上的 数据库和合该库的表结构在 slave 上面建一个,然后它就自动地为 master 做镜像了。当 master 的数据作更改的时候 slave 自动就更新了。
因为在MSSQL中能执行的SQL语句在MySQL中不一定能通过!
记录的无非就是一些delete from .... ; insert into ...... ;update
这些语句,都是通用的
做个数据管道就ok了!