无法实现与远程计算机上的MySQL数据库的连接 [英] cannot achieve connectivity with MySQL db on remote machine
问题描述
笔记本电脑和服务器都有一个MySQL版本5的实例。 xx
笔记本电脑和服务器都有一个root @ localhost用户（duh）
服务器还有另外三个用户：
zzdb_admin @％
zzdb_admin @ localhost
zzdb_admin @ mybiz.com
全都使用相同的密码remotepw，并且所有密码都拥有相应的权限并被刷新。
这两个MySQL实例都有一个名为zzdb的数据库。
MySQL的两个实例都将@@ session.old_passwords，@@ global.old_passwords和@@ global.secure_auth设置为0;在所有情况下，密码的哈希值为41个字符。
在直接登录到远程服务器时，我可以手动登录到两台计算机上的所有帐户。
b$ b
mysql --user = root --password = remoterootpw
mysql --user = zzdb_admin --password = remotepw
mysql --host = localhost --user = zzdb_admin --password = remotepw
使用
mysql --user = root --password = localrootpw
mysql --host = mybiz.com --user = zzdb_admin --password = remotepw
所以所有用户和密码都是正确的。他们的哈希全部是41个字符。重要提示：注意最后证明可以通过远程机器上的实例进行连接。
web服务'pom的版本是5.1.8的mysql-connector-java。
现在变得很奇怪。在webservice的属性文件中使用这些行：
pre $ h $ c $ hibernate.connection.url = jdbc mysql：// localhost：3306 / zzdb
hibernate.connection.username = root
hibernate.connection.password = localrootpw
webapp可以连接到本地数据库实例，并且都很好用。但只将这三行改为
hibernate.connection.url = jdbc：mysql：//mybiz.com：3306 / zzdb
hibernate.connection.username = zzdb_admin
hibernate.connection.password = remotepw
抛出可怕的访问拒绝用户'zzdb_admin'@'localhost'错误
这让我拔出了剩下的几根头发。看起来不像我错过任何东西，一切都拼写正确。任何人都知道发生了什么事情？
TIA，
静态学习史蒂夫
附录：尝试一种不同的，更简单的方法WORKED！
Class.forName（com .mysql.jdbc.Driver）;
String connectionUrl =jdbc：mysql：//mybiz.com/zzdb？user = zzdb_admin& password = remoteapw;
连接conn = DriverManager.getConnection（connectionUrl）;
现在怎么样？只有改变是连接的方法。 Crazy
解决了这个问题，以及它是一个多么细微的bug！
Web服务基础的applicationContext.xml使用Atomikos bean作为数据源
< bean id =dataSourceServerAclass =com.atomikos.jdbc.AtomikosDataSourceBeaninit-method =initdestroy-method =close>
< property name =uniqueResourceNamevalue =XADBMS_A/>
< property name =xaDataSourceClassNamevalue =com.mysql.jdbc.jdbc2.optional.MysqlXADataSource/>
< property name =xaProperties>对于不同的数据源，即MySQL / HSDB / DB2等，属性将会不同 - >
<道具>
< prop key =uri> $ dbServerA {hibernate.connection.uri}< / prop>
< prop key =user> $ dbServerA {hibernate.connection.username}< / prop>
< prop key =password> $ dbServerA {hibernate.connection.password}< / prop>
< /道具>
< / property>
< property name =poolSize>< value> 20< /值>< / property>
< property name =testQueryvalue =SELECT 1/>
EntityManagerFactor bean。只要我指向MySQL的本地实例，该配置就可以工作得很好。不管我做了什么，改变配置指向远程实例都保持失败，引用了zzdb_admin @ localhost。
因此，我创建了第二个更简单的连接性测试程序，仅使用 Class.forName（com.mysql.jdbc.Driver）;
String connectionUrl =jdbc：mysql：//mybiz.com/zzdb？user = zzdb_admin& password = removepw;
theApp.conn = DriverManager.getConnection（connectionUrl）;
和EUREKA！它用于建立远程连接。所以这让我想到这个问题必须与Atomikos bean配置有关。
结果发现有一个关键的xaProperty我没有设置 - serverName，当它没有明确设置时默认为 - 你猜对了 - localhost。 Atomikos不够聪明，无法推断出uri传递给它的主机名。
因此，仅仅切换到
< bean id =dataSourceServerAclass =com.atomikos.jdbc.AtomikosDataSourceBeaninit-method =initdestroy-method =close>
< property name =uniqueResourceNamevalue =XADBMS_A/>
< property name =xaDataSourceClassNamevalue =com.mysql.jdbc.jdbc2.optional.MysqlXADataSource/>
< property name =xaProperties>对于不同的数据源，即MySQL / HSDB / DB2等，属性将会不同 - >
<道具>
< prop key =serverName> mybiz.com< / prop>
< prop key =port> 3306< / prop>
< prop key =databaseName> zzdb< / prop>
< prop key =user> $ dbServerA {hibernate.connection.username}< / prop>
< prop key =password> $ dbServerA {hibernate.connection.password}< / prop>
< /道具>
< / property>
< property name =poolSize>< value> 20< /值>< / property>
< property name =testQueryvalue =SELECT 1/>
所以问题是配置之一。真是太微妙了！
感谢所有回复！
封闭！
静止学习史蒂夫
I'm developing a Java/Spring/Hibernate/CXF/MySQL SOAP webservice and corresponding web Spring MVC client on my laptop. The plan is to eventually move the two resulting war-files over to my remote server hosted at mybiz.com .
Both laptop and server have an instance of MySQL version 5.x.x Both laptop and server have a root@localhost user (duh) The server also has three other users: zzdb_admin@% zzdb_admin@localhost zzdb_admin@mybiz.com all with the same password remotepw and which have all had assorted privileges granted and flushed.
Both instances of MySQL have a database named zzdb. Both instances of MySQL have @@session.old_passwords, @@global.old_passwords and @@global.secure_auth set to 0; In all cases hashes of passwords are 41 characters wide.
While logged in to the remote server directly I can manually log in to all accounts on both machines
mysql --user=root --password=remoterootpw
mysql --user=zzdb_admin --password=remotepw
mysql --host=localhost --user=zzdb_admin --password=remotepw
On the laptop I can log in to the local mysql with
mysql --user=root --password=localrootpw
mysql --host=mybiz.com --user=zzdb_admin --password=remotepw
So all users and passwords are correct. And their hashes are all 41 characters. Important: note this last proves that connection can be made with the instance on the remote machine.
The webservice' pom has version 5.1.8 of mysql-connector-java.
Now it gets weird. With these lines in the webservice' properties file:
hibernate.connection.url=jdbc:mysql://localhost:3306/zzdb
hibernate.connection.username=root
hibernate.connection.password=localrootpw
the webapp can connect to the local db instance and all is peachy. But changing only these three lines to
hibernate.connection.url=jdbc:mysql://mybiz.com:3306/zzdb
hibernate.connection.username=zzdb_admin
hibernate.connection.password=remotepw
throws the dreaded "Access denied for user 'zzdb_admin'@'localhost' " error
This has got me pulling out what few hairs I have left. Doesn't look like I'm missing anything and everything is spelled correctly. Anybody have an idea of what's going on?
TIA,
Still-learning Steve
Addendum: trying a different, simpler approach WORKED!
Class.forName("com.mysql.jdbc.Driver");
String connectionUrl = "jdbc:mysql://mybiz.com/zzdb?user=zzdb_admin&password=remoteapw";
Connection conn = DriverManager.getConnection(connectionUrl);
Now how about that? Only change is the method of connecting. Crazy
Solved the problem, and oh what a subtle bugger it was!
The applicationContext.xml at the base of the web service uses an Atomikos bean for a data source
<bean id="dataSourceServerA" class="com.atomikos.jdbc.AtomikosDataSourceBean" init-method="init" destroy-method="close">
<property name="uniqueResourceName" value="XADBMS_A" />
<property name="xaDataSourceClassName" value="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource" />
<property name="xaProperties"> <!-- properties will be different for different data sources ie MySQL/HSDB/DB2 etc -->
<props>
<prop key="uri">$dbServerA{hibernate.connection.uri}</prop>
<prop key="user">$dbServerA{hibernate.connection.username}</prop>
<prop key="password">$dbServerA{hibernate.connection.password}</prop>
</props>
</property>
<property name="poolSize"><value>20</value></property>
<property name="testQuery" value="SELECT 1" />
which in turn gets used in the EntityManagerFactor bean. This configuration works great as long as I'm pointing to the local instance of MySQL. Changing the configuration to point to the remote instance kept failing with references to zzdb_admin@localhost no matter what I did.
So I created a second, much simpler connectivity tester using only
Class.forName("com.mysql.jdbc.Driver");
String connectionUrl = "jdbc:mysql://mybiz.com/zzdb?user=zzdb_admin&password=removepw";
theApp.conn = DriverManager.getConnection(connectionUrl);
and EUREKA! It worked to make a remote connection. So that got me thinking the problem had to lie with the Atomikos bean configuration.
Turns out there's one crucial xaProperty I wasn't setting - serverName, which when not explictly set defaults to - you guessed it - localhost. Atomikos isn't "smart" enough to infer the hostname out of the uri passed to it.
So merely switching to
<bean id="dataSourceServerA" class="com.atomikos.jdbc.AtomikosDataSourceBean" init-method="init" destroy-method="close">
<property name="uniqueResourceName" value="XADBMS_A" />
<property name="xaDataSourceClassName" value="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource" />
<property name="xaProperties"> <!-- properties will be different for different data sources ie MySQL/HSDB/DB2 etc -->
<props>
<prop key="serverName">mybiz.com</prop>
<prop key="port">3306</prop>
<prop key="databaseName">zzdb</prop>
<prop key="user">$dbServerA{hibernate.connection.username}</prop>
<prop key="password">$dbServerA{hibernate.connection.password}</prop>
</props>
</property>
<property name="poolSize"><value>20</value></property>
<property name="testQuery" value="SELECT 1" />
did the trick. So the problem was one of configuration. Fiendishly subtle indeed!
Thanks to all who replied!
CASE CLOSED!
Still-learning Steve
这篇关于无法实现与远程计算机上的MySQL数据库的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
