kettle连接mysql
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 '127.0.0.1' 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 database 10 11 Error connecting to database: (using class org.gjt.mm.mysql.Driver) 12 null, message from server: "Host '127.0.0.1' 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 '127.0.0.1' 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 more 69 Caused by: java.sql.SQLException: null, message from server: "Host '127.0.0.1' 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
主机名 : localhost
端口 : 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从的主机连接到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为127.0.0.2的主机连接到mysql服务器的dk数据库，并使用123456作为密码
1 mysql> GRANT ALL PRIVILEGES ON dk.* TO 'root'@'127.0.0.2' 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)
授权完成后，再一次连接成功。
