将主DB复制到不同的从站

I have a master database which would be the cloud server that consisted of different schools.

我有一个主数据库,它将是由不同学校组成的云服务器。

Dashboard type that has the details of each school. Can edit their information and other data.

仪表板类型,包含每所学校的详细信息。可以编辑他们的信息和其他数据。

Now those schools are deployed to their corresponding school location which would be the local server.

现在,这些学校被部署到相应的学校位置,这将是本地服务器。

Dashboard type that can only edit the specific school deployed in the local server. Can edit their information and other data.

仪表板类型,只能编辑本地服务器中部署的特定学校。可以编辑他们的信息和其他数据。

Now what I want to happen is, to synchronize the cloud to local server on their corresponding school if something is changed. That also goes for local to cloud server.

现在我想要发生的是,如果发生了变化,将云同步到相应学校的本地服务器。这也适用于本地云服务器。

Note: If you guys ever tried Evernote, that can edit the notes information on whatever device you're using and still be able to synchronize when you have internet or manually clicked synchronize.

注意:如果您曾经尝试使用Evernote,可以在您正在使用的任何设备上编辑备注信息,并且在您拥有互联网或手动点击同步时仍能够同步。

When the local server doesn't have internet connection and edited some data in school. Once the internet is up, the data from local and cloud server should be synchronize.

当本地服务器没有互联网连接并在学校编辑了一些数据。一旦互联网启动,本地和云服务器的数据应该同步。

That's the logic that I'm pursuing to have.

这就是我追求的逻辑。

Would anyone shed some light for me where to start off? I couldn't think of any solution that fit my problem.

有人会为我开始从哪里开始?我想不出任何适合我的问题的解决方案。

I also think of using php to foreach loop all over the table and data that corresponds to current date and time. But I know that would be so bad.

我还想到使用php来遍历整个表和对应于当前日期和时间的数据。但我知道那会很糟糕。

Edited: I deleted references / posts of other SO questions regarding this matter.

编辑:我删除了有关此事的其他SO问题的参考/帖子。

The application pegs that I found are

我找到的应用程序挂钩

Evernote
Todoist

Servers:

Local Server Computer: Windows 10 (Deployed in Schools)

本地服务器计算机:Windows 10(部署在学校)

Cloud Server: Probably some dedicated hosting that uses phpmyadmin

云服务器:可能是一些使用phpmyadmin的专用主机

Not to be picky but, hopefully the answer would be you're talking to a newbie to master to slave database process. I don't have experience for this.

不要挑剔,但希望答案是你正在和新手谈论掌握到奴隶数据库的过程。我没有这方面的经验。

6 个解决方案

#1

When we used to do this we would:

当我们习惯这样做时,我们会:

Make sure every table we wanted to sync had datetime columns for Created; Modified; & Deleted. They would also have a boolean isDeleted column (so rather than physically delete records we would flag it to true and ignore it in queries). This means we could query for any records that have been deleted since a certain time and return an array of these deleted IDs.

确保我们要同步的每个表都具有Created的日期时间列;改性;删除。它们也有一个布尔的isDeleted列(因此,不是物理删除记录,我们会将其标记为true并在查询中忽略它)。这意味着我们可以查询自特定时间以来已删除的任何记录,并返回这些已删除ID的数组。

In each DB (Master and slave) create a table that stores the last successful sync datetime. In the master this table stores multiple records: 1 for each school, but in the slave it just needs 1 record - the last time it synced with the master.

在每个DB(主服务器和从服务器)中,创建一个存储上次成功同步日期时间的表。在master中,这个表存储了多个记录:每个学校1个,但在slave中只需要1个记录 - 最后一次与master同步。

In your case every so often each of the slaves would:

在你的情况下,每个奴隶经常会:

Call a webservice (a URL) of the master, lets say called 'helloMaster'. It would pass in the school name (or some specific identifier), the last time they successfully synced with the master, authentication details (for security) and expect a response from the master of whether the master had any updates for the school since that datetime provided. Really the point here is just looking for an acknowledgement that the master available and listening (ie. the internet is still up).

调用master的webservice(URL),叫做'helloMaster'。它将传递学校名称(或某些特定标识符),他们最后一次成功与主人同步,身份验证详细信息(为了安全),并期望主人回答主人是否有自该日期以来学校的任何更新提供。这里的重点只是寻找主人可用和收听的确认(即互联网仍在运行)。
Then, the slave would call another webservice, lets say called 'sendUpdates'. It would again pass in the school name, last successful sync, (plus security authentication details) & three arrays for any added, updated and deleted records since last sync. The master just acknowledge receipt. If a receipt was acknowledged then the slave to move to step 3, otherwise the slave would try step 1 again after a pause of some duration. So now the Master has updates from the slave. Note: it is up to the master to decide how to merge any records if there are conflicts with its pending slave updates.

然后,奴隶会调用另一个Web服务,比如称为“sendUpdates”。它将再次传入学校名称,上次成功同步(加上安全认证详细信息)和三个数组,用于自上次同步以来的任何添加,更新和删除的记录。主人只是确认收到。如果确认收据,则从属设备移动到步骤3,否则从属设备将在暂停一段时间后再次尝试步骤1。所以现在Master拥有来自奴隶的更新。注意:如果与其挂起的从属更新存在冲突,则由主服务器决定如何合并任何记录。
The slave then calls a webservice, lets say 'getUpdates'. It passes in the school name, last successful sync, security authentication details, & the master then return to it three arrays for any added, updated and deleted records it has which the slave is expected to apply to its database.

奴隶然后调用web服务,让我们说'getUpdates'。它传入学校名称,上次成功同步,安全认证详细信息,然后主服务器返回三个数组,用于任何添加,更新和删除的记录,其中奴隶应该应用于其数据库。
Finally once the slave tries to update its records it will then notifies the master of success/failure through another webservice, say 'updateStatus'. If successful then the master will return a new sync date for the slave to store (this will exactly match the date the master stores in its table). If it fails then the error is logged in the master and we go back to step 1 after a pause.

最后,一旦奴隶试图更新其记录,它将通过另一个网络服务通知主人成功/失败,比如'updateStatus'。如果成功,则主服务器将返回一个新的同步日期供从服务器存储(这将与主服务器在其表中存储的日期完全匹配)。如果失败则会在主服务器中记录错误,我们会在暂停后返回步骤1。

I have left out some detail out about error handling, getting the times accurate across all devices (there might be different time zones involved), and some other bits and pieces, but that's the gist of it.

我遗漏了一些关于错误处理的细节,让所有设备的时间准确(可能涉及不同的时区),以及其他一些零碎,但这就是它的要点。

I may make refinements after thinking on it more (or others might edit my post).

我可以在考虑更多(或其他人可能编辑我的帖子)之后进行改进。

Hope that helps at least.

希望至少有所帮助。

#2

I will suggest you to go with the Trivial Solution, which according to me is:

我建议你使用Trivial Solution,根据我的说法:

Create a SQLlite or any database (MySQL or your choice) in local server

在本地服务器中创建SQLlite或任何数据库(MySQL或您选择的)

Keep a always running thread which will be pinging (makes an API call) your Master database every 5 minutes (depends on how much delay is accepted)

保持一个始终运行的线程,它将每5分钟ping一次(进行一次API调用)你的主数据库(取决于接受多少延迟)

With that thread you can detect whether you're connected to the internet or not.

使用该线程,您可以检测您是否已连接到互联网。

If connected to internet

如果连接到互联网

a) Send local changes with the request to master server, this master server is an application server, which will be capable to update changes of local machines in school (you received this changes by an API call) to the master database after certain validations according to your application usage.

a)将请求发送到主服务器的本地更改,此主服务器是一个应用程序服务器,它将能够更新学校中的本地计算机的更改(您通过API调用收到此更改)到主数据库后,根据某些验证到您的应用程序使用。

b) Receive updated changes from the server after the API call, this changes are served after solving conflicts (like if data in school server was updated earlier than data updated in master database so which one you will accept based on your requirement).

b)在API调用之后从服务器接收更新的更改,在解决冲突之后提供此更改(例如,如果学校服务器中的数据早于主数据库中更新的数据更新,那么您将根据您的要求接受哪一个)。
If not connected to internet, keep storing changes in local database and reflect those changes in Application which is running in school, but when you get connected push those changes to master server and pull actual changes which is applicable from the master server.

如果未连接到Internet,请将更改存储在本地数据库中并反映在学校中运行的应用程序中的更改,但是当您连接时将这些更改推送到主服务器并从主服务器提取适用的实际更改。

This is complicated to do it by your own, but if the scale is small I will prefer to implement your own APIs for the database applications which will connect in this manner.

这是你自己做的很复杂,但如果规模很小,我宁愿为数据库应用程序实现自己的API,这将以这种方式连接。

Better solution will be to use Google Firebase, which is a real time database which is asynchronously updated whenever there is change in any machine, but can cost you higher if its really not required. But yes it will really give you Evernote type realtime editing features for your database systems.

更好的解决方案是使用Google Firebase,这是一个实时数据库,只要有任何机器发生变化,就会异步更新,但如果真的不需要,可能会花费更多。但是,它确实会为您的数据库系统提供Evernote类型的实时编辑功能。

#3

This is not a problem that can be solved by database replication.

这不是可以通过数据库复制解决的问题。

Generally speaking, database replication can operate in one of two modes:

一般来说,数据库复制可以以两种模式之一运行:

Master/slave replication, which is what MySQL uses. In this mode, all writes must be routed to a single "master" server, and all of the replica databases receive a feed of changes from the master.

主/从复制,这是MySQL使用的。在此模式下,所有写入操作必须路由到单个“主”服务器,并且所有副本数据库都会从主服务器接收更改。

This doesn't suit your needs, as writes can only be made to the master. (Modifying one of the replicas directly would result in it becoming permanently out of sync with the master.)

这不符合您的需求,因为只能对主设备进行写入。 (直接修改其中一个副本会导致它与主服务器永久不同步。)
Quorum-based replication, which is used by some newer databases. All database replicas connect to each other. So long as at least half of all replicas are connected (that is, the cluster has reached "quorum"), writes can be made to any of the active databases, and will be propagated to all of the other databases. A database that is not connected will be brought up to date when it joins the quorum.

基于仲裁的复制,由一些较新的数据库使用。所有数据库副本都相互连接。只要所有副本中至少有一半连接(即群集已达到“仲裁”),就可以对任何活动数据库进行写入,并将其传播到所有其他数据库。未连接的数据库将在加入仲裁时更新。

This doesn't suit your needs either, as a disconnected replica cannot be written to. Worse, having more than half of all replicas disconnect from the master would prevent the remaining databases from being written to either!

这也不适合您的需要,因为无法写入断开连接的副本。更糟糕的是,超过一半的所有副本与主服务器断开连接会阻止剩余的数据库被写入其中!

What you need is some sort of data synchronization solution. Any solution will require some logic -- which you will have to write! -- to resolve conflicts. (For instance, if a record is modified in the master database while a school's local replica is disconnected, and the same record is also modified there, you will need some way to reconcile those differences.)

您需要的是某种数据同步解决方案。任何解决方案都需要一些逻辑 - 你必须写出来! - 解决冲突。 (例如,如果在学校的本地副本断开连接时在主数据库中修改了记录,并且同样的记录也在那里被修改,则需要一些方法来协调这些差异。)

#4

No need for any complicated setup or APIs. MySQL allows you to easily replicate your database. MySQL will ensure the replication is correctly and timely done and whenever internet is available. (and its fast too)

无需任何复杂的设置或API。 MySQL允许您轻松复制数据库。 MySQL将确保在互联网可用时正确及时地完成复制。 (也很快)

There are:

Master - slave: Master edits slave reads or in other words one way synchronization from master to slave.

主站 - 从站:主站编辑从站读取或换句话说从主站到从站的单向同步。

Master - Master: Master1 edits master2 reads and edits or in other words two way synchronization. Both server will push and pull updates.

Master - Master:Master1编辑master2读取和编辑或换句话说双向同步。两个服务器都会推送和拉取更新。

assuming your cloud server has schema for each school and each schema is accessible by its own username and password. i.e db_school1, db_school2

假设您的云服务器具有每个学校的架构,并且每个架构都可以通过自己的用户名和密码访问。即db_school1,db_school2

now you have the option to replicate only a selected database schema from your cloud to local master. In your case, school one's local master will only "do replicate db_school1"

现在,您可以选择仅将选定的数据库架构从云复制到本地主站。在你的情况下,学校的本地大师只会“复制db_school1”

in case if you want to replicate only specific table, MySQL also has that option "replicate-do-table"

如果你只想复制特定的表,MySQL也有这个选项“replicate-do-table”

the actual replication process is very easy but can get very deep when you have different scenarios.

实际的复制过程非常简单,但是当你有不同的场景时可以变得非常深。

few things you want to take a note, server ids, different auto-increment value on each server to avoid conflicts with new records. i.e Master1 generates records on odd number, Master 2 on even numbers so there won't be a duplicate primary key issues. Server down alerts/monitoring, error skipping

您想要记录的几件事,服务器ID,每台服务器上的不同自动增量值,以避免与新记录冲突。即Master1生成奇数的记录,偶数的Master 2生成记录,因此不存在重复的主键问题。服务器关闭警报/监视,错误跳过

I'm not sure if you are on linux or windows, I've wrote simple c# application which checks if any of the master is not replicating or stopped for any reason and sends email. monitoring is crucial!

我不确定你是在linux还是windows上,我写了一个简单的c#应用程序,它会检查是否有任何master没有因任何原因复制或停止并发送电子邮件。监控至关重要!

here some links for master master replication: https://www.howtoforge.com/mysql_master_master_replication

这里有一些主集成复制的链接:https://www.howtoforge.com/mysql_master_master_replication

https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication

also worth reading this optimised tabl-level replication info: https://dba.stackexchange.com/questions/37015/how-can-i-replicate-some-tables-without-transferring-the-entire-log

还值得阅读这个优化的tabl级复制信息:https://dba.stackexchange.com/questions/37015/how-can-i-replicate-some-tables-without-transferring-the-entire-log

hope this helps.

希望这可以帮助。

#5

Edit:

The original version of this answer proposed MongoDB; but with further reading MongoDB is not so reliable with dodgy internet connections. CouchDB is designed for offline documents, which is what you need - although it's harder to get gong than MongoDB, unfortunately.

这个答案的原始版本提出了MongoDB;但随着进一步的阅读,MongoDB对于狡猾的互联网连接并不那么可靠。 CouchDB专为脱机文档而设计,这是您所需要的 - 尽管不幸的是,它比MongoDB更难获得。

Original:

I'd suggest not using MySQL but deploy a document store designed for replication such as CouchDB - unless you go for the commercial MySQL clustering services.

我建议不要使用MySQL,而是部署一个专为复制而设计的文档存储,例如CouchDB - 除非你选择商业MySQL集群服务。

Being a lover of the power of MySQL I find it hard to suggest you use something else, but in this case, you really should.

作为MySQL的强大爱好者,我发现很难建议你使用别的东西,但在这种情况下,你真的应该。

Here is why -

这就是为什么 -

Problems using MySQL replication

使用MySQL复制的问题

Why MySQL had good replication (and that's most likely what you should be using if you're synchronizing a MySQL database - as recommended by others) there are some things to watch out for.

为什么MySQL有良好的复制(如果你正在同步MySQL数据库,那么你最应该使用的是 - 正如其他人所推荐的那样)有一些需要注意的事项。

"Unique Key" *es will give you a massive headache; the most likely cause of this is "Auto Incrementing" IDs that are common in MySQL applications (don't use them for syncing operation unless there is a clear "read+write"->"read-only" relationship - which there isn't in your case.)

“独特的钥匙”冲突将给你带来巨大的麻烦;最可能的原因是MySQL应用程序中常见的“自动递增”ID(除非有明确的“读取+写入” - >“只读”关系,否则不要将它们用于同步操作 - 其中没有'在你的情况下。)

Primary keys must be generated by each server but unique across all servers. Possibly by adding a mix of a server identifier and a unique ID for that server (Server1_1, Server1_2, Server1_3 etc will not * with Server2_1)

主键必须由每个服务器生成,但在所有服务器上都是唯一的。可能通过为该服务器添加服务器标识符和唯一ID的混合(Server1_1,Server1_2,Server1_3等不会与Server2_1冲突)

MySQL sync only supports on-way unless you look at their clustering solutions (https://www.mysql.com/products/cluster/).

除非您查看其群集解决方案(https://www.mysql.com/products/cluster/),否则MySQL同步仅支持在线。

Problems doing it "manually" with time stamping the record.

使用时间戳记录“手动”执行此操作时出现问题。

Another answer recommends keeping "Time Updated" records. While I've done this approach there are some big gotchas to be careful of.

另一个答案建议保留“时间更新”记录。虽然我已经采用了这种方法,但仍有一些需要注意的重要问题。

"Unique Key" *es (as mentioned above; same problems - don't use them except primary keys, and generate primary keys unique to the server)

“唯一键”冲突(如上所述;相同的问题 - 除主键外不使用它们,并生成服务器特有的主键)

Multiple updates on multiple servers need to be precisely time-synced and *es handled according to rules. This can be a headache.

多个服务器上的多个更新需要精确时间同步,并根据规则处理冲突。这可能很头疼。

What happens when updates are received way out-of-order; which fields have been updated, which weren't? You probably don't need to update the whole record, but how do you know?

在无序接收更新时会发生什么;哪些字段已更新,哪些不是?您可能不需要更新整个记录,但您怎么知道?

If you must, try one of the commercial solutions as mentioned in answers https://serverfault.com/questions/58829/how-to-keep-multiple-read-write-db-servers-in-sync and https://community.spiceworks.com/topic/352125-how-to-synchronize-multiple-mysql-servers and Strategy on synchronizing database from multiple locations to a central database and vice versa (etc - Google for more)

如果必须,请尝试回答https://serverfault.com/questions/58829/how-to-keep-multiple-read-write-db-servers-in-sync和https://中提到的商业解决方案之一community.spiceworks.com/topic/352125-how-to-synchronize-multiple-mysql-servers和策略将数据库从多个位置同步到*数据库,反之亦然(等等 - Google更多)

Problems doing it "manually" with journalling.

使用日志记录“手动”执行此操作时遇到的问题。

Journalling is keeping a separate record of what has changed and when. "Database X, Table Y, Field Z was updated to value A at time B" or "Table A had new record added with these details [...]". This allows you much finer control of what to update.

Journalling正在单独记录已发生变化的内容和时间。 “数据库X,表Y,字段Z在时间B更新为值A”或“表A新记录添加了这些详细信息[...]”。这使您可以更好地控制要更新的内容。

if you look at database sync techniques, this is actually what is going on in the background; in MySQL's case it keeps a binary log of the updates

如果你看看数据库同步技术,这实际上是在后台发生的事情;在MySQL的情况下,它保留了更新的二进制日志

you only ever share the journal, never the original record.

你只分享期刊,而不是原始的记录。

When another server receives a journal entry, if has a much greater picture of what has happened before/after and can replay updates and ensure you get the correct details.

当另一台服务器收到日记帐分录时,如果能够更好地了解之前/之后发生的事情,可以重播更新并确保获得正确的详细信息。

problems arise when the journalling/database get out of Sync (MySQL is actually a pain when this happens!). You need to have a "refresh" script ready to roll that sits outside the journalling that will sync the DB to the master.

当日志/数据库失去同步时会出现问题(当发生这种情况时,MySQL实际上是一种痛苦!)。您需要准备好一个“刷新”脚本,该脚本位于日志记录之外,该日志将DB同步到主服务器。

It's complicated. So...

这很复杂。所以...

Solution: Using a document store designed for replication, e.g. MongoDB

解决方案:使用专为复制而设计的文档存储,例如MongoDB的

Bearing all this that in mind, why not use a document store that already does all that for you? CouchDB has support and handles all the journalling and syncing (http://docs.couchdb.org/en/master/replication/protocol.html).

考虑到这一切,为什么不使用已经为您完成所有这些的文档存储? CouchDB支持并处理所有日志记录和同步(http://docs.couchdb.org/en/master/replication/protocol.html)。

There are others out there, but I believe you'll end up with less headaches and errors than with the other solutions.

还有其他人,但我相信与其他解决方案相比,你最终会遇到更少的麻烦和错误。

#6

Master to master replication in MySQL can be accomplished without key violations while using auto_increment. Here is a link that explains how.

使用auto_increment时,可以在不违反密钥的情况下完成MySQL中的主控复制。这是一个解释如何的链接。

If you have tables without primary keys I'm not sure what will happen (I always include auto_increment primary keys on tables)

如果你有没有主键的表我不知道会发生什么(我总是在表上包含auto_increment主键)

http://brendanschwartz.com/post/12702901390/mysql-master-master-replication

The auto-increment-offset and auto-increment-increment effect the auto_increment values as shown in the config samples from the article...

自动增量偏移和自动增量增量会影响auto_increment值,如文章中的配置示例所示...

server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 1

server_id           = 2
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 2

#1