远程访问其他数据库管理系统
请教两个问题。
1）我正在阅读有关 FEDERATED 存储引擎的内容，但对我来说，并不清楚简单的远程连接是否有优势。有什么区别或者优势吗？
2）（真正的问题）在这种情况下：如果我有一个MySQL数据库，并且我需要从其他数据库访问和读取敏感数据，可能使用不同的DBMS，并且可能，我只有读取权限（无论如何，我没有需要更多权限来执行该任务）。
选项
- 中的问题
- FEDERATED存储引擎仅解决MySQL DBMS数据库抽象库（pdo，zend）
- 为每个外部数据库构建一个API
- 将我的数据库与其他数据库同步（对于这个建议可能有点过分）
我需要的是只是：“约翰在您的数据库中？是，否”
最好的选择是什么？
谢谢！
Two questions, please.
1) I am reading about FEDERATED storage engine, but for me, is not clear that there are advantages in relation a simple remote connection. There is any difference or advantage ?
2) (real question) In this situation: If i have a MySQL DB and I need to access and read sensitive data from other database, probably with different DBMS, and probably, I only have access to read (anyway, I don't need more privileges for the task).
Options
- FEDERATED storage engine only solve the issue in MySQL DBMS
- database abstraction library (pdo, zend)
- Build an API for each external database
- Sync my database with the others (maybe overkill for this propose)
What I need is just: "john is in your database? yes, no"
What's the best choice ?
thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
很难判断您是在尝试解决技术问题还是某种安全/管理问题。我将解决我认为您的实际技术问题 - 确定用户是否在您的数据库中 - 并描述每种可能性的权衡。让我们按照复杂性的顺序来解决可能的解决方案。
1.直接MySQL数据库连接
这将使用MySQL特定的数据库驱动程序直接连接到远程数据库并发出硬编码的SQL语句。如果您可以确定两端都具有相同的模式知识，如果两端最好位于同一内部网络上，并且您负责所有要连接并需要此信息的客户端，这对于内部使用来说是很好的。
2. PDO 数据库连接
如果您对架构达成一致，但与数据库供应商没有达成一致（即一方可能使用 PostgreSQL 而不是 MySQL），那么 PDO 是更好的选择。只要您的查询相当简单，您就不太可能遇到数据库兼容性问题。这是一个比#1 更好的主意，因为它的工作量相同，但更灵活。
3. 数据库复制
我建议在决定使用数据库复制之前要格外小心。复制的设置很复杂，需要监督和管理。不存在简单的复制设置这样的事情。但是，如果满足以下条件，则可能是合适的：
如果您需要其中部分或全部功能并且愿意支付维护价格，它可能是正确的选择。请记住，处理复制需要大量工作，并且会将双方绑定到同一数据库供应商。
4. FEDERATED 存储引擎
我建议不要使用它，原因如下：
FEDERATED只能工作在 MySQL 数据库之间，它引入了新的故障类型，而没有有意义地简化您的应用程序代码。我正在考虑这个引擎选择，想知道它有什么好处。我想，如果您将表从一个数据库移动到另一个数据库，但不想或无法更改查询它的应用程序代码，那么它可能是合适的，但我不会考虑将其用于预先设计。如果不提高清晰度或性能，它将变得脆弱。
5. 编写 API
到目前为止的所有选择都假设您可以信任每个需要数据库凭据的信息的人。如果您需要向第三方授予对此信息的访问权限，API 是一个不错的选择，因为：
API 也有缺点：
进一步的问题和注释
您提到这是“敏感”信息。让我指出，对于 PCI 合规性和 HIPAA 合规性以及您处理受法律保护的私人数据的其他情况，这些选项都不合适，因为它们都将涉及在不应访问数据的计算机之间解密和共享数据。当您从机器 B 查询机器 A 上的数据库时，很难确定内存中到底有多少数据副本。如果这是您的情况（私有、加密、受法律保护的数据），您将需要竭尽全力确保您的解决方案在法律上合理，我无法提供有关如何继续的建议，除了说上述内容还不够。
如果不是这种情况，我想说，就效率和简单性而言，内部使用的最佳解决方案是使用 PDO (#2)。否则，构建一个 API (#5)。
It's very hard to tell whether you're trying to solve a technical problem or some kind of security/management problem. I'm going to address what I believe is your actual technical problem—determining if a user is in your database—and describe tradeoffs of each of your possibilities. Let's address possible solutions in order of complexity.
1. Direct MySQL database connection
This would be to use a MySQL-specific database driver to connect directly to the remote database and issue your hard-coded SQL statement. This is fine for internal use if you can be sure that both ends have the same knowledge of the schema, if both ends are preferably on the same internal network, and you are responsible for all the clients that are going to connect and need this information.
2. PDO database connection
If you have agreement about the schema, but not the database vendor (i.e. one side may be using PostgreSQL instead of MySQL) then PDO is a better choice. As long as your queries are fairly simplistic you are unlikely to run into database compatibility issues. This is a better idea than #1 because it amounts to the same amount of work but is more flexible.
3. Database replication
I recommend extreme caution before deciding to use database replication. Replication is complex to set up and requires oversight and management. There is no such thing as a simple replication setup. However, it may be appropriate if:
If you need some or all of these features and are willing to pay the maintenance price, it may be the right choice. Bear in mind that it is a lot of work to deal with replication, and it will tie both sides to the same database vendor.
4. FEDERATED storage engine
I would recommend against using this for these reasons:
FEDERATEDonly works between MySQL databases, it introduces new kinds of failure without meaningfully simplifying your application codeI'm looking at this engine choice wondering what it could be good for. I suppose if you moved a table from one database to another but didn't want to or couldn't change the application code that queries it, it may be appropriate, but I would not consider this for an up-front design. It will be fragile without improving clarity or performance.
5. Write an API
All of the choices up to this assume that you can trust everyone who needs the information with database credentials. If you need to give access to this information to third-parties, an API is a good choice because:
APIs have disadvantages as well, though:
Further questions and notes
You mention that this is "sensitive" information. Let me point out that for PCI compliance and HIPAA compliance and other situations where you are dealing with private data protected by law, none of these options are appropriate, because they all will involve decrypting and sharing data across computers that should not have access to it. When you query a database on machine A from machine B, it is very difficult to be certain of exactly how many copies of the data you'll have in memory. If this is your situation—private, encrypted, legally protected data—you will need to go to great lengths to ensure your solution is legally sound and I am not in a position to offer advice as to how to proceed, other than to say that the foregoing is insufficient.
If that is not the case, I would say the best solution for internal use in terms of efficiency and simplicity is to use PDO (#2). Otherwise, build an API (#5).