Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

how to access mysql in hadoop VM from host machine?

how to access mysql in hadoop VM from host machine?

New Contributor

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)                   
12 REPLIES 12

Re: how to access mysql in hadoop VM from host machine?

Super Mentor

@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

Re: how to access mysql in hadoop VM from host machine?

New Contributor

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

Re: how to access mysql in hadoop VM from host machine?

Super Mentor

@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                    |

.

Highlighted

Re: how to access mysql in hadoop VM from host machine?

New Contributor

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

Re: how to access mysql in hadoop VM from host machine?

New Contributor

@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..


capture.jpg

Re: how to access mysql in hadoop VM from host machine?

Super Mentor

@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

.

Re: how to access mysql in hadoop VM from host machine?

New Contributor
@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

Re: how to access mysql in hadoop VM from host machine?

Super Mentor

@Inam Ur Rehman

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

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

Re: how to access mysql in hadoop VM from host machine?

New Contributor

@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..