MySQL数据库复制 [英] MySQL database replication
问题描述
这是场景：
- 我有一个带有数据库的MySQL服务器，我们称之为 consolidateddb 。
- 我有另一个MySQL服务器，包含原始数据库，这些数据库是生产数据库，每天更新。
- 公司希望将生产数据库中每个表格上的每个更新/插入/删除都复制到 consolidateddb 中的相应表格。
- I have a MySQL server with a database, let's call it consolidateddb. This database a consolidation of several tables from various databases
- I have another MySQL server, with the original databases, these databases are production databases and are updates daily.
- The company wants to copy each update/insert/delete on each table in the production databases to the corresponding tables in consolidateddb.
复制会完成吗？我知道复制是在databas到数据库，而不是属于不同数据库的表到一个目标数据库。
Would replication accomplish that? I know that replication is done on a databas to database, but not on tables that belong to different databases to one target database.
我希望我的解释清楚。非常感谢。
I hope my explanation was clear. Thanks.
编辑：每个数据库的所有表递归到单个从属的工作？
Would a recursive copy of all tables inn each database to the single slave work? Or is it an ugly solution?
推荐答案
为了清除一些东西，让我们为当前的mysql做法命名。数据库是数据库服务器。模式是数据库实例。数据库服务器可以有多个模式。表存在于模式中。
To clear up some things, let's name things accordingly to current mysql practice. A database is a database server. A schema is a database instance. A database server can have multiple schemas. Tables live within a schema.
如果您要在主/生产服务器上重复定义的模式或表格，则复制将帮助您。复制的工作方式是将在主服务器上运行的所有sql语句的二进制日志传送到从服务器，从服务器尽可能地运行它们，就好像它们在自身上顺序运行一样。
Replication will help you if you want to duplicate schemas or tables as they are defined on the master/production server. The replication works by shipping a binary log of all the sql statements that are run on the master to the slave which dutifully runs them as if they run sequentially on itself.
您可以选择复制所有数据，也可以选择一些模式，甚至只选择一些表。
You can choose to replicate all data, or you can choose some of the schemas or even just some of the tables.
您不能从不同模式中选择表，并将它们复制到一个模式中，表属于特定模式。
You can not choose tables from different schemas and have them replicated into one schema, a table belongs to a specific schema.
顺便说一句，重要的通知。复制服务器不能成为多个主设备的从属设备。您可以使用联合表来模拟这种情况，但是它绝不会将数据复制到合并服务器，只是将它们显示为来自不同服务器的数据在一个服务器上。
By the way, important notice. A replication server can not be a slave to multiple masters. You could mimic this using federated tables, but that would never copy the data to the consolidation server, just show them as if the data from different servers were on one server.
复制的优势在于，您的合并服务器将或多或少地拥有更新的数据。
The bonus of replication is that your consolidation server will more or less have updated data all the time.
这篇关于MySQL数据库复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
