MySQL新手...... [英] New to MySQL......
问题描述
我有一个客户希望在不同城市的几个商店中使用新的应用程序。他想要实时访问所有商店数据和
能够继续在本地运行即使互联网连接下降
。
它在我看来，如果网络出于任何原因停机，本地MySQL实例肯定应该在每个商店中继续工作。但是，什么
是在所有商店之间同步/共享数据的最佳方式？
从我在MySQL网站上看到的内容，你需要MySQL Cluster要做同步复制，但它不能在网络连接上工作，而不是100亿美元。
是否有使用免费版本的
MySQL进行同步复制的希望。并且，如果免费版（它的前沿是
代码等）有多稳定？
你给的任何帮助都将是非常感谢。
谢谢！
I have a client that desires a new application for use at several stores in
different cities. He wants "real time" access to all store data and the
ability to continue to run locally even if the internet connection goes
down.
It occurs to me that a local MySQL instance should definitely be
in each store to continue work if the net is down for any reason. But, what
is the best way to synchronize/share data among all of the stores?
From what I have read on the MySQL site, you need MySQL Cluster to do
synchronous replication, but it won''t work over a network connection less
than 100 mb.
Is there any hope of synchronous replication using the free version of
MySQL. And, just how stable if the free edition (with it''s bleeding-edge
code and such)?
Any help that you give will be greatly appreciated.
Thanks!
推荐答案
>我有一个客户需要一个新的应用程序在
>I have a client that desires a new application for use at several stores in
不同城市的几家商店使用。他想要实时访问所有商店数据和
即使互联网连接断开也能继续在本地运行的能力。
我发现本地MySQL实例肯定应该<如果因任何原因导致网络停机，则每个商店都要继续工作。但是，什么
是在所有商店之间同步/共享数据的最佳方式？
从我在MySQL网站上看到的，你需要MySQL Cluster来做
同步复制，但它不能超过100 mb的网络连接。
different cities. He wants "real time" access to all store data and the
ability to continue to run locally even if the internet connection goes
down.
It occurs to me that a local MySQL instance should definitely be
in each store to continue work if the net is down for any reason. But, what
is the best way to synchronize/share data among all of the stores?
From what I have read on the MySQL site, you need MySQL Cluster to do
synchronous replication, but it won''t work over a network connection less
than 100 mb.
这不是唯一的复制形式。普通的旧MySQL可以进行
复制，虽然它没有集群的所有功能。
如果网络出现故障，你可能需要手动切换主机。 br />
根据查询量以及从站必须达到的最新状态，1200bps调制解调器连接可能就足够了。
我的雇主尝试使用MySQL Cluster设置邮件服务器，使用
MySQL来维护邮箱所在位置和用户选项的信息，
跨多台计算机。它没有很好地工作。我们会得到
不一致的数据和存储节点崩溃，这些崩溃本质上将是b $ b取出整个设置。最终我们使用了传统的复制，
，更改了主人和每台机器使用其本地
副本进行读取。也许MySQL Cluster从那时起就有所改进。
您需要的数据设置是什么？每个商店都掌握了自己的
数据，而总公司也想要一个实时（只读？）的副本
呢？或总部是否需要进行更改，而商店需要分享彼此的数据？我不知道怎么做传统的
复制，例如，总公司机器从每个商店主人那里奴役一个
数据库。
Gordon L. Burditt
That''s not the only form of replication. Plain old MySQL can do
replication although it doesn''t have all the features of the cluster.
You might have to manually switch masters if the net goes down.
Depending on the query volume and how up to date the slaves have to
be, a 1200bps modem connection might be sufficient.
My employer tried a mail server setup with MySQL Cluster, using
MySQL to maintain info on where the mailbox was and user options,
across several machines. It didn''t work very well. We''d get
inconsistent data and storage node crashes that would essentially
take out the whole setup. Eventually we used conventional replication,
with changes going to the master and each machine using its local
copy for reads. Perhaps MySQL Cluster has improved since then.
What is your required data setup? Each store is master of its own
data and the head office also wants a real-time (read-only?) copy
of it? Or does the head office need to make changes, and stores
need to share each other''s data? I don''t know how to do conventional
replication with, for example, the head office machine slaving one
database off of each store master.
Gordon L. Burditt
" Gordon Burditt" <去*********** @ burditt.org>在消息中写道
news：12 ************* @ corp.supernews.com ...
"Gordon Burditt" <go***********@burditt.org> wrote in message
news:12*************@corp.supernews.com...
我有一个客户希望在不同城市的几家商店使用新的应用程序。他想要实时访问所有商店数据和
即使互联网连接断开也能继续在本地运行的能力。
我发现本地MySQL实例肯定应该<如果因任何原因导致网络停机，则每个商店都要继续工作。但是，
是什么
是在所有商店之间同步/共享数据的最佳方式？
从我在MySQL网站上看到的，你需要MySQL Cluster来做
同步复制，但它不会超过100 mb的网络连接。
I have a client that desires a new application for use at several stores
in
different cities. He wants "real time" access to all store data and the
ability to continue to run locally even if the internet connection goes
down.
It occurs to me that a local MySQL instance should definitely be
in each store to continue work if the net is down for any reason. But,
what
is the best way to synchronize/share data among all of the stores?
From what I have read on the MySQL site, you need MySQL Cluster to do
synchronous replication, but it won''t work over a network connection less
than 100 mb.
这不是唯一的复制形式。普通的旧MySQL可以进行复制，虽然它没有集群的所有功能。
如果网络出现故障，你可能需要手动切换主机。
取决于查询量如何更新奴隶必须，1200bps调制解调器连接可能就足够了。
我的雇主尝试使用MySQL Cluster设置邮件服务器，使用MySQL维护有关邮箱的位置和用户选项的信息，
跨多台计算机。它没有很好地工作。我们会得到不一致的数据和存储节点崩溃，这些崩溃基本上会取消整个设置。最终我们使用传统复制，
将更改发送到主服务器，每台计算机使用其本地
副本进行读取。从那时起MySQL Cluster可能已经有所改进。
您需要的数据设置是什么？每个商店都掌握着自己的数据，而总公司也想要一个实时（只读？）的副本吗？或总部是否需要进行更改，而商店需要分享彼此的数据？我不知道如何进行传统的复制，例如，总公司的机器从每个商店主人那里奴役一个数据库。
That''s not the only form of replication. Plain old MySQL can do
replication although it doesn''t have all the features of the cluster.
You might have to manually switch masters if the net goes down.
Depending on the query volume and how up to date the slaves have to
be, a 1200bps modem connection might be sufficient.
My employer tried a mail server setup with MySQL Cluster, using
MySQL to maintain info on where the mailbox was and user options,
across several machines. It didn''t work very well. We''d get
inconsistent data and storage node crashes that would essentially
take out the whole setup. Eventually we used conventional replication,
with changes going to the master and each machine using its local
copy for reads. Perhaps MySQL Cluster has improved since then.
What is your required data setup? Each store is master of its own
data and the head office also wants a real-time (read-only?) copy
of it? Or does the head office need to make changes, and stores
need to share each other''s data? I don''t know how to do conventional
replication with, for example, the head office machine slaving one
database off of each store master.
他们提出的概念是，他们想要实时数据
访问进行商店到商店的库存查询和转移，并跟踪
来自中央办公室的商店活动。
他们还想分享客户数据（如果客户访问另一家商店或客户有没有完成支付
商品并支付金额。
他们还希望在发生灾难性事件时备份所有商店数据/>
商店（如盗窃IT资源或火灾）。
他们还想要（至少）存储'数据的本地商店 - 或（如果
可能）所有商店数据，直到与中央数据库的连接是
丢失。
而且，我们不是忘记一种方法回发到中央数据库（如果使用了
），一旦与组或中央数据库建立连接后，在断开连接的商店完成的任何本地交易恢复。
简单，呵呵？
JH
The concept that they came up with was that they wanted real-time data
access to do store-to-store inventory lookups and transfers and to track the
store activities from the central office.
They also want to share customer data (in case a customer visits another
store or in the event that a customer has not finished paying for
merchandise and is behind on payments).
They also want all store data backup up in case of a catastrophic event at a
store (like theft of IT resources or fire).
They also want a local store of (at least) that store''s data - or (if
possible) all store data until the connection to the central database was
lost.
And, let''s not forget a way to post back to a central database (if one is
used) any local transactions done at a disconnected store once the
connection to the group or central database is restored.
Simple, huh?
JH
Jim Hubbard写道：
Jim Hubbard wrote:
他们还想要一个本地商店（至少）存储'的数据 - 或者（如果可能的话）所有商店数据，直到与中央数据库的连接是
丢失了。
并且，让我们不要忘记一种方法回发到中央数据库（如果使用了一个），那么在一个断开连接的商店完成的任何本地交易>恢复与群组或中央数据库的连接。
简单，呵呵？
They also want a local store of (at least) that store''s data - or (if
possible) all store data until the connection to the central database was
lost.
And, let''s not forget a way to post back to a central database (if one is
used) any local transactions done at a disconnected store once the
connection to the group or central database is restored.
Simple, huh?
这是我如何设计这个系统：
1.他们对家庭办公室的主数据库
数据库的数据库连接执行写操作。
2. Th本地站点保留主数据库的副本，该数据库通过标准复制定期更新
。
3.在此本地数据库上执行读取操作。
4.如果与master数据库的连接断开，应用程序需要检测到这个错误，而是将更改写入本地数据库。 br />
我建议将_not_作为master数据库的本地副本，但
a单独的实例。我将其称为故障转移写入数据库。
5.因此，当他们对
主人的本地只读副本进行查询时，他们会应该将查询结果与存储在其本地故障转移写入数据库中的任何更改结合起来。希望这将是最小的，
但应用程序代码必须考虑它。
7.当连接恢复时，故障转移的内容写
数据库被发送到master数据库。然后本地系统删除故障转移写入数据库的内容
。等一下
master来处理新数据，更新密钥等。然后重新同步
主服务器的只读副本。因此，故障转移更改已将
移至主数据库，并通过复制再次返回。但现在他们已经分配了他们自动生成的主键，以及其他数据
清理任务。您可能希望让本地应用程序拒绝用户在这些时刻进行任何进一步更改。
。另请注意，其他
站点可能已经排队更改，因此初始复制同步后，可能包含比平常更多的数据量。
如果两个独立的网站
为同一个真实世界的实体创建记录（比如调用了
二的客户），这仍然会出现问题。不同的网站）。在任何RDBMS系统中，解决这些案例都不是100％可自动化的事情。
您必须为一些活人计划审查新数据并执行
在家庭办公室清理。一些数据清理是自动化的，但是没有人类就无法处理所有情况。
问候，
比尔K.
Here''s how I would design this system:
1. They perform write operations to a database connection to the master
database, which is at the home office.
2. The local sites keep a copy of the master database, which is updated
periodically with standard replication.
3. Read operations are performed on this local database.
4. If the connection to the master database goes down, the apps need to
detect this error and instead write their changes to a local database.
I''d recommend this _not_ be their local copy of the master database, but
a separate instance. I''ll call this the failover write database.
5. Thus when they do queries against their local read-only copy of the
master, they should combine the query results with any changes stored in
their local failover write database. Hopefully this will be minimal,
but the application code has to account for it.
7. When the connection is restored, the contents of the failover write
database are sent to the master database. Then the local system deletes
the contents of the failover write database. Wait a few moments for the
master to process the new data, update keys, etc. Then re-synchronize
the read-only copy of the master. Thus the failover changes have moved
to the master database, and back again via replication. But now they
have had their auto-generated primary keys assigned, and other data
cleanup tasks. You might want to make the local app deny the user from
making any further changes during these moments. Also note that other
sites may have queued up changes, so the initial replication sync after
an outage may contain more volume of data than usual.
This still leaves the problem of what happens if two separate sites
create records for the same real-world entity (like a customer who calls
two separate sites). Resolving these cases is something which is not
100% automatable, in any RDBMS system.
You must plan for some living person to review new data and performs
cleanup at the home office. Some data cleanup is automatable, but it is
simply not possible to handle all cases without a human.
Regards,
Bill K.
这篇关于MySQL新手......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
