最佳实践：mysql通过3G连接远程移动设备同步
目前，我们有一台主 mysql 服务器，每 1 小时通过 3G 连接连接到 100 个远程移动设备 [车辆] [不太可靠：每天在少数车辆同步时断开连接]。通过.net windows 服务工具完成同步。检查远程 mysql 状态后，master 开始执行同步。有时同步有效负载数据约为6-8 MB。仅使用非事务性方法对一张表执行同步。
使用的 mysql 服务器版本是：4.1.22
问题：
在知道只有一个表同步的情况下进行同步事务是否有用？或无附加值！
使用mysql语句将同步数据加载到远程机器：
加载数据本地INFILE
文件格式为 CSV。我如何以压缩格式发送数据？无需开发驻留在远程设备上的工具。
在同步域中部署远程应用程序（在发送数据后执行同步）是良好的实践或架构还是应该由主站直接完成？我的意思是，如果出现新的需求，驻留在远程计算机上的工具的开发将很难更新或修复。但它会为同步操作节省大量带宽，并且会消除在同步进行时发生断开连接时实时主同步可能引发的错误。因此，如果建议这样做，则仅发送压缩数据，然后通过使用某种校验和，我将验证是否发送了整个数据，否则将再次发起请求。
请分享您的想法和经验。
谢谢，
currently we have one master mysql server that connect every 1 hour to 100 remote mobile devices [vehicles] over 3G connection [not very reliable: get disconnect daily while sync in progress for few cars]. the sync done through .net windows service tool. after checking the remote mysql status the master start perform the sync. sometimes the sync payload data is about 6-8 MB. the sync performed for one table only using non-transactional approach.
mysql server version in use is: 4.1.22
Questions:
is it useful to make the sync transactional knowing that only one table getting sync? or no value added!
the sync data loaded to remote machine using mysql statement:
LOAD DATA LOCAL INFILE
the file format is CSV. how i can send the data in compressed format? without developing tool that reside on the remote device.
is it good practice or architecture in the sync domain to deploy remote application that will perform the sync after sending the data or it should be done directly by the master? i mean the development of tool that will reside on remote machine will be difficult to update or fix in case new requirements appear. but it will save a lot of bandwidth for the sync operation and it will eliminate the errors that could raise from the live master sync in case disconnection occur while the sync is in-progress. so if this is recommend then only compressed data will be sent, then by using some sort of check-sum I'll verify that the whole data sent otherwise the request will be initiated again.
please share your thoughts and experience.
thanks,
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先，我将更改客户端启动同步与服务器启动同步的方法。多对一与一对多方法将比您当前的设置更容易扩展。我的上述评论给出了所需的客户端到服务器同步的一些很好的示例。
其次，开启交易记录录入。没有理由不拥有它。这将保证信息及时输入，并且能够提供更多的“元数据”（例如哪些客户端更新缓慢等）。
最后，您可以通过不同的方式来“增强”此上传。如果您要在服务器端实现某种服务，通过来自客户端的 POST 接收响应，那么您将能够毫无问题地将数据发送到服务器端。这就像将文件“上传”到服务器一样。一旦您的 6-8 MB 文件被“上传”，它就会被放入数据库中。这样做的好处是，如果您的服务器是 APACHE（或者甚至在您的情况下是 IIS 服务器），您将能够让每个客户端同时上传数据，而不会出现太大问题。那时，通过插入上传到 mysql 服务器几乎不需要时间，并且您的过程将继续进行而不会出现问题。
这就是我处理你的情况的方式...
Firstly, I would change the approach to a client inited sync vs a server inited sync. A many to one vs one to many approach will expand much easier than your current setup. My above comments give a few good examples of a required client to server syncing.
Secondly, Turn on transactional record entry. There is no reason not to have it. This will guarentee that the information gets entered in a timely fashion and will be able to possibly provide even more 'meta-data' (such as which clients are slow to update, etc...).
Lastly, you can 'enhance' this uploading by taking a different look at it. If you were to implement a sort of service at the server side that takes in a response via a POST from the client, you'd be able to send the data to the server side with no issues. It would be just like 'uploading' a file to a server. Once your 6-8 MB file is 'uploaded' it is then put into the database. The great thing about this is if your server is an APACHE (or even in your case an IIS server), you'd be able to have every single client uploading data at the same time without much of an issue. At that point, uploading to the mysql server via an insert would take virtually no time and your process would continue on without a problem.
This is the way I'd handle your situation...