PHP/PDO MariaDB Galera 集群
我正处于配置可从四个全球位置访问的服务的最后阶段(计划稍后添加更多位置).我将在带有 MariaDB 的 Ubuntu 12.04 机器上运行服务器.我最初的想法是使用 4 个不同的数据库创建彼此独立运行的服务器，并遵守用户只能登录到他们最初注册的服务器的约束.
I am in the final stages of configuring a service that is accessible from four global locations (with plans to add more later). I will be running the servers on an Ubuntu 12.04 box with MariaDB. My initial thought was to create servers that run independently of each other with 4 distinct databases and live with the constraint that users would only be able to login to the server where they were initially registered.
但是，我刚刚遇到 这篇文章让我思考... .
However, I have just run into this article that has got me thinking... .
根据我的阅读情况，如果我按照文章中的建议设置具有主-主复制的 Galera 集群，我可以迁移一个大型数据库，该数据库在所有四台服务器上始终可用.我已经收集(并且希望)在集群设置正确且运行良好的情况下，我几乎不需要在我的 PHP 代码中做任何事情(四个 MariaDB 实例将有相同的用户来访问数据库)——甚至不需要更改 PDO 连接字符串.
From my reading of things if I set up a Galera cluster with master-master replication as suggested in the article I can move have the luxury of one large database that is consistently available across all four servers. I have gathered (and am hoping) that with the cluster setup correctly and functioning well I need do pretty much nothing in my PHP code (the four MariaDB instances will have the same user to access the database) - not even alter the PDO connection string.
但是，这听起来好得令人难以置信.我的问题是:
However, this sounds almost too good to be true. My questions are:
- 这里是否涉及其他导致复杂化的问题?
- PHP PDO 连接字符串是否需要随时更改?
- 我的应用程序已经构建以确保两台服务器尝试同时写入同一行的可能性绝对为零这一事实是否有帮助?
- 最后，阅读 MariaDB 文档，这不适用于 TokuDB 存储引擎?
- 有没有办法专门停止所选表的复制?事实上，我是否可以利用only InnoDB/XtraDB"约束并在我不想复制的表上使用另一个存储引擎?
- are there other issues involved here that make for complications?
- Do the PHP PDO connection strings need to be altered in anway?
- Does the fact that my application is already structured to ensure that there is absolutely zero chance of two servers attempting to simultaneously write the same row help?
- And finally, reading from the MariaDB docs, that this will not work with the TokuDB storage engine?
- Is there a way to specifically stop the replication of a selected table? Could I in fact exploit the "only InnoDB/XtraDB" constraint and use another storage engine on the table I do not want to have replicated?
推荐答案
这里是否涉及其他导致复杂化的问题?
are there other issues involved here that make for complications?
您应该了解一些已知限制意识到.通常，对于集群，理想情况下您应该拥有奇数个节点以防止出现脑裂情况，但偶数个节点通常也可以正常工作.
There are some Known Limitations that you should be aware of. Generally, with clusters, you should ideally have an odd number of nodes to prevent split brain conditions, but an even number will usually work just as well.
PHP PDO 连接字符串是否需要随时更改?
Do the PHP PDO connection strings need to be altered in anway?
没有.您现有的连接字符串应该可以工作.
No. Your existing connection strings should work.
我的应用程序已经构建以确保两台服务器尝试同时写入同一行的可能性绝对为零这一事实是否有帮助?
Does the fact that my application is already structured to ensure that there is absolutely zero chance of two servers attempting to simultaneously write the same row help?
查看已知的限制并确保您的应用程序仍能做到这一点.如果您使用命名锁，则需要更改您的应用程序.
Look at the known limitations and make sure your application will still do that. If you're using named locks, you'll need to change your application.
最后，从 MariaDB 文档中了解到，这不适用于 TokuDB 存储引擎?
And finally, reading from the MariaDB docs, that this will not work with the TokuDB storage engine?
在最近的 galera 集群发行版中添加了对 TokuDB 的支持.我使用了一些，它确实像 InnoDB 一样复制，但我不会依赖它，因为它是 galera 集群构建中的新功能.
TokuDB support was added in the recent galera cluster distribution. I have used some and it does replicate just like InnoDB but I wouldn't rely on it since it's new in the galera cluster build.
有没有办法专门停止所选表的复制?事实上，我是否可以利用only InnoDB/XtraDB"约束并在我不想复制的表上使用另一个存储引擎?
Is there a way to specifically stop the replication of a selected table? Could I in fact exploit the "only InnoDB/XtraDB" constraint and use another storage engine on the table I do not want to have replicated?
我听到很多人问他们是否可以从复制中省略表或数据库，但我仍然没有听到很好的理由.Galera 复制提供 HA 并且便宜且容易，因此即使某些表不重要，我也找不到任何不复制数据的现实理由.话虽如此，您可以使用 MyISAM/Aria 复制未复制的数据.
I've heard a lot of people ask if they can omit tables or databases from replication but I still haven't heard a good reason why. Galera replication provides HA and is cheap and easy so even if some tables aren't important I can't find any realistic reason to not replicate the data. That being said, you could have data not replicated by using MyISAM/Aria.
我一直在多个中等规模的项目中使用 MariaDB 和 galera，这是我为 HA 找到的最佳解决方案，它还提供了性能优势.其他解决方案通常价格昂贵或不成熟.您应该考虑的一件事是设置一个代理来连接到数据库服务器，如 HA 代理、mysql-proxy 或 glbd(我使用)，以提供更好的冗余和连接平衡以提高性能.
I've been using MariaDB with galera in multiple moderately sized projects and it is the best solution I've found for HA and it also provides performance benefits. Other solutions are generally expensive or not mature. One thing you should consider is setting up a proxy for connecting to the database servers like HA Proxy, mysql-proxy, or glbd (which I use) to provide better redundancy and connection balancing for performance.
针对 DroidOS 的以下评论:
In response to DroidOS's comment below:
集群中的每次写入都需要得到每个节点的同意，因此节点之间的任何延迟都会添加到每次写入中.因此，基本上，每次写入都将在写入服务器和添加到它的其他节点之间具有最长的往返时间.
Every write in the cluster needs to be agreed upon by every node so any latency between nodes is added to every write. So, basically, every write will have the greatest round trip time between the writing server and the other nodes added to it.
没有.Galera 复制是整个集群的全部或全部.如果任何节点在写入数据时出现问题(如果表没有主键，则可能发生这种情况)，该节点将优雅地杀死自己，因为它不能保证其数据与集群的其余部分一致.如果发生这种情况，集群的其余部分将继续正常运行.如果出现网络问题，如果其中一个段有仲裁，它将继续正常运行.任何没有仲裁的段将等待更多节点获得仲裁，但不会接受查询.通过这种行为，您可以确保您能够查询的任何节点都与集群的其余部分保持一致.
No. Galera replication is all or nothing across the entire cluster. If any node has a problem writing the data, which can happen if a table doesn't have a primary key, the node will gracefully kill itself since it can't guarantee its data is consistent with the rest of the cluster. If that happens, the rest of the cluster will continue to operate normally. If there is a network issue, if one of the segments has quorum, it will continue to operate normally. Any segments without quorum will wait for more nodes to get quorum but will not accept queries. With this behavior, you can be sure that any node that you are able to query is consistent with the rest of the cluster.
相关文章