how to access mysql in hadoop VM from host machine?

[#1]

Hello guys,

I am newbie to hadoop. I have installed sandbox vmware and now i want to access mysql DB which is installed in my host machine into hive through sqoop. after writing following command

mysql -u root -p

I am getting following error. any help?

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)                   

[#2]

@Inam Ur Rehman

Try specifying the password as "hadoop" if you are using sandbox.

# mysql -u root -p
Enter password: hadoop

.

- But if you have changed mysql root password then you might want to look at the following link which talks about If you assigned a root password previously but have forgotten it, you can assign a new password. https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

[#3]

I want to access mysql schemas which is installed in my host machine.

Using password: hadoop i have accessed DB but it is only showing information shcema when I enter

show databases

Command it only showing information schema. how can i access other databases?

@Jay SenSharma

[#4]

@Inam Ur Rehman

After logging in to the mysql you can switch to the desired database schema using "mysql> use $SCHEMA_NAME;"

Example: Shows how to use "ranger" db schema.

# mysql -u root -p
Enter password: hadoop

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3126
Server version: 5.6.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| EmpDB              |
| hive               |
| mysql              |
| performance_schema |
| ranger             |
+--------------------+
6 rows in set (0.01 sec)

mysql> use ranger;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+--------------------------------+
| Tables_in_ranger               |
+--------------------------------+
| vx_trx_log                     |
| x_access_type_def              |
| x_access_type_def_grants       |
| x_asset                        |
| x_audit_map                    |

.

[#5]

@Jay SenSharma Thanks brother.. I'll try this and will update you.

[#6]

@Jay SenSharma

hey brother.. i can access the following schema in mysql in ssh (see attached screen shot)..but i want to access host machine's mysql db..in which i have another schema named test.. how i access that? plz help i am stuck here..

[#7]

@Inam Ur Rehman

Your latest query is not very clear to me. Can you please elaborate more.

If you want to access the DB schema of a remote mysql DB in your local mysql then one option will be to export the DB dump of the remote DB and then import the DB dump to your local machine.

Example: importing DB dump:

mysql -u ambari -pbigdata ambari < ambari_mydb_current.sql

Here assuming that the DB username is "ambari" and password is "bigdata" and the database name is 'ambari" which may be different in your case.

Example: Exporting DB dump:

mysqldump ambari > /tmp/mydir/ambari_mydb_current.sql

.

[#8]

@Jay SenSharma

I am using virtualbox sandbox in my host machine which is window 10. i have mysql db installed in windows which i want to access in virtualbox sandbox.. i have tried your given command

mysql -u root -padmin123 hadoop_test 

user: root Password: admin123 database Name : hadoop_test

it gives me error 1045 (28000): Access denied for user root'@'localhost (using password: yes)

According to my concept defeult mysql db in sandbox and host machien ( window 10) db is different thing..am I right? apology for taking too much of your time

[#9]

@Inam Ur Rehman

Make sure that you are entering correct password. Else try the following:

# mysql -u root -padmin123
mysql> use hadoop_test ;

[#10]

@Jay SenSharma

the basic problem is it is not accepting -padmin123 password which is the password for windows mysql db rather it accepts -phadoop password for root..

[#11]

@Inam Ur Rehman

Unlike your original query which was related to Sandbox based mysql (that uses 'hadoop' as root user password by default) , Your current query is very specific to MySQL instead of HDP Sandbox specific settings.

You should refer to standard MySQL documentation in order to know how to reset the root passwords: https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

- If you will use default sandbox then the mysql root user password will be 'hadoop'.

[#12]

the thing is that you have a passed your localhosts to your VM sandbox. the sandbox itselfs contains its own mysql instance. within sqoop you are not pointing to the mysql on your windows but on your vm..

[#13]

but i am looking to the same solution to bypass this issue. How can i access my local mysql instance on windows when running sqoop on a VM box

[#14]

HI,

used cmd like 

mysql -u root -p cloudera