kettle 连接数据库出现的问题

转载：http://www.cnblogs.com/zeppelin/p/5669988.html

kettle连接mysql时出现问题

错误信息如下：

 1 错误连接数据库 [test] : org.pentaho.di.core.exception.KettleDatabaseException: 2 Error occurred while trying to connect to the database 3  4 Error connecting to database: (using class org.gjt.mm.mysql.Driver) 5 null,  message from server: "Host '172.16.0.148' is not allowed to connect to this MySQL server" 6  7  8 org.pentaho.di.core.exception.KettleDatabaseException: 9 Error occurred while trying to connect to the database10 11 Error connecting to database: (using class org.gjt.mm.mysql.Driver)12 null,  message from server: "Host '172.16.0.148' is not allowed to connect to this MySQL server"13 14 15     at org.pentaho.di.core.database.Database.normalConnect(Database.java:459)16     at org.pentaho.di.core.database.Database.connect(Database.java:357)17     at org.pentaho.di.core.database.Database.connect(Database.java:328)18     at org.pentaho.di.core.database.Database.connect(Database.java:318)19     at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80)20     at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2734)21     at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:588)22     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)23     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)24     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)25     at java.lang.reflect.Method.invoke(Method.java:483)26     at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:313)27     at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157)28     at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141)29     at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:43)30     at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:136)31     at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)32     at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)33     at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)34     at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)35     at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)36     at org.eclipse.jface.window.Window.runEventLoop(Window.java:820)37     at org.eclipse.jface.window.Window.open(Window.java:796)38     at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:389)39     at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:318)40     at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:116)41     at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:60)42     at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.newConnection(SpoonDBDelegate.java:470)43     at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.newConnection(SpoonDBDelegate.java:457)44     at org.pentaho.di.ui.spoon.Spoon.doubleClickedInTree(Spoon.java:3051)45     at org.pentaho.di.ui.spoon.Spoon.doubleClickedInTree(Spoon.java:3021)46     at org.pentaho.di.ui.spoon.Spoon.access$2400(Spoon.java:347)47     at org.pentaho.di.ui.spoon.Spoon$27.widgetDefaultSelected(Spoon.java:6064)48     at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)49     at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)50     at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)51     at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)52     at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)53     at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1339)54     at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7939)55     at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9214)56     at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:653)57     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)58     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)59     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)60     at java.lang.reflect.Method.invoke(Method.java:483)61     at org.pentaho.commons.launcher.Launcher.main(Launcher.java:92)62 Caused by: org.pentaho.di.core.exception.KettleDatabaseException:63 Error connecting to database: (using class org.gjt.mm.mysql.Driver)64 null,  message from server: "Host '172.16.0.148' is not allowed to connect to this MySQL server"65 66     at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:572)67     at org.pentaho.di.core.database.Database.normalConnect(Database.java:443)68     ... 46 more69 Caused by: java.sql.SQLException: null,  message from server: "Host '172.16.0.148' is not allowed to connect to this MySQL server"70     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)71     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)72     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)73     at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1112)74     at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2486)75     at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2519)76     at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304)77     at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)78     at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)79     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)80     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)81     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)82     at java.lang.reflect.Constructor.newInstance(Constructor.java:408)83     at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)84     at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)85     at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)86     at java.sql.DriverManager.getConnection(DriverManager.java:664)87     at java.sql.DriverManager.getConnection(DriverManager.java:247)88     at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:554)89     ... 47 more

主机名       : 172.16.0.220
端口           : 3306
数据库名:mysql

 

在网上搜了好多都说mysql-connector-java-5.1.26-bin没有在/data-integration/lib或者/data-integration/libswt/win64/下

但是上图可以看出我的jar包已在/data-integration/lib下

最后，解决方法是：

1. root使用密码123456从任何主机连接到mysql服务器的话。

1 mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;2 Query OK, 0 rows affected (0.00 sec)3 4 mysql> FLUSH   PRIVILEGES;5 Query OK, 0 rows affected (0.00 sec)

 

2. 允许用户root从ip为172.16.0.148的主机连接到mysql服务器，并使用123456作为密码

1 mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;2 Query OK, 0 rows affected (0.00 sec)3 4 mysql> FLUSH   PRIVILEGES;5 Query OK, 0 rows affected (0.00 sec)

 

3. 允许用户root从ip为172.16.0.148的主机连接到mysql服务器的dk数据库，并使用123456作为密码

1 mysql> GRANT ALL PRIVILEGES ON dk.* TO 'root'@'172.16.0.148' IDENTIFIED BY '123456' WITH GRANT OPTION;2 3 Query OK, 0 rows affected (0.00 sec)4 5 mysql> FLUSH   PRIVILEGES;6 7 Query OK, 0 rows affected (0.00 sec)

授权完成后，再一次连接