MySQL 数据库复制
场景是这样的：
- 我有一个带有数据库的 MySQL 服务器，我们将其称为 consolidateddb。 这个数据库整合了来自不同数据库的几个表
- 我有另一个MySQL服务器，带有原始数据库，这些数据库是生产数据库并且每天更新。
- 该公司希望将生产数据库中每个表的每次更新/插入/删除复制到 consolidateddb 中的相应表中。
复制能做到这一点吗？ 我知道复制是在数据库到数据库上完成的，而不是在属于不同数据库的表到一个目标数据库上完成的。
我希望我的解释很清楚。 谢谢。
编辑：将每个数据库中的所有表递归复制到单个从站是否有效？ 或者这是一个丑陋的解决方案？
This is the scenario:
- 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.
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.
Edit: Would a recursive copy of all tables inn each database to the single slave work? Or is it an ugly solution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
您可能需要查看 maatkit 工具包。 它是免费下载的，并且有许多专门用于优化归档表等内容的工具。 我在过去的项目中使用它来将某些数据复制到另一个数据库等。您可以根据时间或任何其他数量的因素来执行此操作。
You may want to check out the maatkit toolkit. It's a free download and has a host of tools that specialize in optimizing things like archiving tables. I've used it on past projects to duplicate certain data to another DB, etc. You can do it based on time or any other number of factors.
据我所知，您可以设置复制（MySQL 4+），并在 my.cnf 文件中让从属服务器仅处理某些表或让主服务器仅记录某些表，无论哪种方式都可以解决您的问题。
以下是一些技术的指南：
http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
我在复制设置方面遇到了很少的问题，我所有的问题都试图解决同步数据库，尤其是在重新启动等之后。
To the best of my knowledge you can set up replication (MySQL 4+) and in the my.cnf file have the slave either only process certain tables or have the master log only certain tables, either way will solve your problem.
Here is a guide to some techniques:
http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
I have very few problems with replication set-up, all my problems came trying to sync DBs, especially after a reboot etc.
为了澄清一些事情，让我们根据当前的 mysql 实践来命名。 数据库是数据库服务器。 模式是一个数据库实例。 一个数据库服务器可以有多个模式。 表存在于模式中。
如果您想要复制在主/生产服务器上定义的架构或表，复制将为您提供帮助。 复制的工作原理是将主服务器上运行的所有 sql 语句的二进制日志传送到从服务器，从服务器尽职尽责地运行它们，就好像它们在自身上顺序运行一样。
您可以选择复制所有数据，也可以选择某些架构甚至仅某些表。
您不能从不同模式中选择表并将它们复制到一个模式中，表属于特定模式。
顺便说一下，重要通知。 复制服务器不能是多个主服务器的从服务器。 您可以使用联合表来模拟这一点，但这永远不会将数据复制到整合服务器，只是显示它们，就好像来自不同服务器的数据位于一台服务器上一样。
复制的好处是您的整合服务器或多或少会一直更新数据。
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.
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.
您可以从每个主服务器获取二进制日志，使用 mysqlbinlog 解析它们，然后将其运行到整合的计算机中。
大约是这样的：
你需要某种简单的应用程序（我怀疑如果需要的话可以在 bash 中完成）来包装逻辑。
You could take the binary logs from each of the masters, parse them with
mysqlbinlogand then run that into the consolidated machine.Something very approximately like:
you'd need some kind of simple application (I suspect it could be done in bash if you needed) to wrap the logic.
查看将不同的数据库复制到不同的从站 ，看看它是否对您有任何帮助。
Check out Replicating Different Databases to Different Slaves, see if it helps you in any way.
MySQL 基于语句的复制（基本复制）通过在从属服务器上运行与主服务器上运行的完全相同的语句来工作。 这包括有关表所在数据库的信息。
我认为 MySQL 没有提供任何内置方法来在数据库之间移动复制语句（即“插入 db1.table1 ...”->“插入 db2.table1 ”）。 您也许可以通过手动动态更改复制日志来欺骗它，但这不会是不合时宜的 MySQL 复制。
MySQL statement-based replication (basic replication) works by running the exact same statements that were run on the master on the slave. This includes information about what database the table was in.
I don't think MySQL provides any built-in way to move replication statements between databases (i.e. "insert into db1.table1 ..." -> "insert into db2.table1"). You may be able to trick it by manually altering the replication logs on the fly, but it wouldn't be out-of-the-bod MySQL replication.
您也许可以使用 MySQL Proxy 来实现它
You might be able to pull it off with MySQL Proxy