Accessing Mysql DB on my Mac via Sqoop

[#1]

Hi,

I am trying to perform the following task: 'Import data from a table in a relational database into HDFS'.

To get a working example of this - I have created a mySQL database (hostname=127.0.0.1 and port=xxxx) on my Mac.

I have then logged in to ssh (http://127.0.0.1:4200/) root within a terminal and type:

sqoop list-databases --connect jdbc:mysql://127.0.0.1:xxxx --username root --password abcabcabc

Instead of a list of databases, I get the below output. Can anyone advise?

Warning: /usr/hdp/2.3.2.0-2950/accumulo does not exist! Accumulo imports will fail.        
Please set $ACCUMULO_HOME to the root of your Accumulo installation.                      
16/01/11 18:44:42 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.3.2.0-2950              
16/01/11 18:44:42 WARN tool.BaseSqoopTool: Setting your password on the command-line is ins
ecure. Consider using -P instead.                                                          
16/01/11 18:44:43 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. 
SLF4J: Class path contains multiple SLF4J bindings.                                        
SLF4J: Found binding in [jar:file:/usr/hdp/2.3.2.0-2950/hadoop/lib/slf4j-log4j12-1.7.10.jar
!/org/slf4j/impl/StaticLoggerBinder.class]                                                
SLF4J: Found binding in [jar:file:/usr/hdp/2.3.2.0-2950/zookeeper/lib/slf4j-log4j12-1.6.1.j
ar!/org/slf4j/impl/StaticLoggerBinder.class]                                              
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.          
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]                      
16/01/11 18:44:43 ERROR manager.CatalogQueryManager: Failed to list databases              
java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)    
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)                  
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)                      
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)                      
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:935)                      
        at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4101)                        
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1300)                          
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2337)            
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)      
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)            
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)                  
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)                  
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)          
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorI
mpl.java:57)                                                                              
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorA
ccessorImpl.java:45)                                                                      
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)                
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)                            
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)              
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)      
        at java.sql.DriverManager.getConnection(DriverManager.java:571)                    
        at java.sql.DriverManager.getConnection(DriverManager.java:215)                    
        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:885)        
        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.jav
a:52)                                                                                      
        at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.j
ava:57)                                                                                    
        at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)          
        at org.apache.sqoop.Sqoop.run(Sqoop.java:148)                                      
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)                      
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)                                
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)                                  
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)                                  
        at org.apache.sqoop.Sqoop.main(Sqoop.java:244)                                    
16/01/11 18:44:43 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeExceptio
n: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)  
java.lang.RuntimeException: java.sql.SQLException: Access denied for user 'root'@'localhost
' (using password: YES)                                                                    
        at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.j
ava:73)                                                                                    
        at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)          
        at org.apache.sqoop.Sqoop.run(Sqoop.java:148)                                      
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)                      
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)                                
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)                                  
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)                                  
        at org.apache.sqoop.Sqoop.main(Sqoop.java:244)                                    
Caused by: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password
: YES)                                                                                    
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)                  
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)                      
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)                      
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:935)                      
        at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4101)                        
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1300)                          
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2337)            
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)      
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)            
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)                  
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)                  
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)          
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorI
mpl.java:57)                                                                              
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorA
ccessorImpl.java:45)                                                                      
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)                
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)                            
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)              
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)      
        at java.sql.DriverManager.getConnection(DriverManager.java:571)                    
        at java.sql.DriverManager.getConnection(DriverManager.java:215)                    
        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:885)        
        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.jav
a:52)                                                                                      
        at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.j
ava:57)                                                                                    
        ... 7 more                            

[#2]

@Revlin Abbi

you're confusing your local filesystem with Sandbox filesystem. You can create a mysql database in Sandbox as it already comes with mysql server, you can create a db in postgres as well, Ambari db is one such database. Unless you specify host-guest networking so that guest can access the host's services, you won't be able to achieve what you're doing. It's a lot simpler to work with mysql that is inside Sandbox guest machine.

[#3]

Hi Artem, sure I understand.

If I take the example database xademo (which I can see when viewing from Hive in Ambari) - how can I use sqoop to list this database?

[#4]

you typically wouldn't use sqoop to query Hive table. Login to mysql and list databases there. Once you know which db you'd like to import then you can use the command you'd used before. For exporting hive data, you'd use hive commands, pig scripts, hdfs commands, etc.

[#5]

@Artem Ervits: I have the exact same problem. MySQL is hosted on an altogether different system which is not a cluster in my hadoop system but only hosts the mySQL database. I have user root@'IP' granted with all permission and I can connect through the command mysql -u -p -h but it is not throws the mentioned error whenever I try to connect via sqoop.What else could possibly be the problem?How do I fix this?

[#6]

@simran kaur check virtualbox networking configuration, you may need to port forward Mysql port in Virtualbox network settings.

[#7]

I have to same environment set up and same issue. Within Virtual box using the bridged adapter (so no port forward set up there) Using my network router which has 3306 forwarded the my server with mysql instance. This all seems to be working since we can connect to the database using the command mysql -u -p -h. Any other ideas on why sqoop fails and throws the access denied? Maybe driver or config related? I also have granted root access from any host '%' on mysql so don't believe that is the issue, esp since connection with command line works.

[#8 · Accepted solution]

@Revlin Abbi

try this : grant all privileges on *.* to 'root'@'localhost' identified by 'abcabcabc' with grant option;

sqoop list-databases --connect jdbc:mysql://127.0.0.1:xxxx --username root --password abcabcabc

root user does not have read/list permissions.

ERROR manager.CatalogQueryManager: Failed to list databases java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

[#9]

I have the exact same problem. MySQL is hosted on an altogether different system which is not a cluster in my hadoop system but only hosts the mySQL database. I have user root@'IP' granted with all permission and I can connect through the command mysql -u -p -h  but it is not throws the mentioned error whenever I try to connect via sqoop.What else could possibly be the problem?How do I fix this?

@neeraj

[#10]

here's a scenario that works on Sandbox

# Create Hive table

drop table if exists export_table;

create table export_table (

key int,

value string

)

row format delimited

fields terminated by ",";

# populate Hive with dummy data

insert into export_table values("1", "ExportedValue");

# confirm Hive table has data

select * from export_table;

# display the values in hive as hdfs files

hdfs dfs -cat /apps/hive/warehouse/export_table/000000_0

# export table to MySQL

# MySQL table must exist

su mysql

mysql -u root

create database export;

use export;

create table exported (rowkey int, value varchar(20));

exit;

# on HDP 2.3.2 Sandbox, SQOOP-1400 bug, use --driver com.mysql.jdbc.Driver to overcome the problem

# sqoop export from a Hive table into MySQL

sqoop export --connect jdbc:mysql://127.0.0.1/export --username hive --password hive --table exported --direct --export-dir /apps/hive/warehouse/export_table --driver com.mysql.jdbc.Driver

# login to Mysql and check the table

su mysql

mysql -u root

use export;

select * from exported;

exit;