如何在 mysql 中启用跨数据库连接？
我正在尝试使用如下查询将一些数据从生产数据库移植到沙箱：
INSERT `dbsandbox`.`SomeTable`(Field1, Field2, Field3)
SELECT t.Field1, t.Field2, t.Field3
FROM `dbprod`.`SomeTable` t;
当我尝试此跨数据库联接时，出现以下错误：
ERROR 1142 (42000): SELECT command returned to user 'myusername'@表“SomeTable”的“server.domain.tdl”
相关用户对两个数据库的相关表都有权限。 我已经在 unix mysql 客户端和 windows MySQL 查询浏览器应用程序中尝试过此操作，结果相同。
我缺少什么？
I am trying to port some data over from my production database to my sandbox using a query like this:
INSERT `dbsandbox`.`SomeTable`(Field1, Field2, Field3)
SELECT t.Field1, t.Field2, t.Field3
FROM `dbprod`.`SomeTable` t;
When I attempt this cross-database join I get the following error:
ERROR 1142 (42000): SELECT command denied to user 'myusername'@'server.domain.tdl' for table 'SomeTable'
The user in question has permission to the tables in question for both databases. I have tried this in both the unix mysql client and the windows MySQL Query Browser application with the same result.
What am I missing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
原来是权限问题。 源数据库需要我使用的用户名的密码才能访问任何表。 目标只需要用户名位于本地主机上。
即使我每次在跨数据库查询的上下文中都使用密码启动 MySQL 客户端，也会尝试另一个连接。 此连接不记得我最初使用密码对客户端进行了身份验证，因此从沙箱到生产数据库的连接失败。 显然，显式声明表的数据库名称有时也意味着需要另一个连接。
答案是从生产数据库启动查询，引用不带数据库限定符的本地表，然后插入到沙箱数据库表中。 这次成功了：
USE dbprod
INSERT
dbsandbox.SomeTable(Field1, Field2, Field3)选择 t.Field1、t.Field2、t.Field3
FROM
SomeTablet;It turns out it was a permissions problem. The source database required a password for the username I was using in order to access any tables. The target only required the username be on the localhost.
Even though I launch the MySQL client using a password every time within the context of a cross-database query another connection is attempted. This connection does not remember that I originally authenticated against the client with a password so connecting from the sandbox to the production database failed. Apparently explicitly stating the database name for a table sometimes implies the need for another connection, as well.
The answer was to initiate the query from with the production database, refer to the local tables without the database qualifier, then insert across to the sandbox database tables. This time it worked:
USE dbprod
INSERT
dbsandbox.SomeTable(Field1, Field2, Field3)SELECT t.Field1, t.Field2, t.Field3
FROM
SomeTablet;听起来像是权限问题。 通常，用户权限是以数据库方式在数据库上设置的，因此目标用户无权访问源。
首先确保您可以从源数据库中进行选择。
那样有用吗？ 如果没有，那么您需要授予用户对源数据库的选择权限。
两个数据库都位于同一服务器上，对吗？
It sounds like a permissions problem. Often user permissions are set up on a database in a database fashion, so the user for the destination doesn't have access to the source.
First make sure that you can do the select from the source database.
Does that work? If not, then you need to grant the user select permissions on the source db.
Both databases are on/in the same server right?