mysql default password first time sandbox login

[#1]

Hi I am a newbie to HDP, I downloaded the HDP and run with virtualbox. However, when i tried to access the mysql database for the first time after ssh into the sandbox VM, I encountered the following error:

[root@sandbox-hdp ~]# mysql -uroot

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

Originally I thought that maybe mysql does require any password for the first time login, therefore i tried to use "hadoop" as the password as suggested in some of the posts i googled, but again the access was denied:

[root@sandbox-hdp ~]# mysql -uroot -phadoop

mysql: [Warning] Using a password on the command line interface can be insecure.

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

I have also followed this post https://community.hortonworks.com/questions/86949/error-1045-28000-access-denied-for-user-root.html but the suggested solution was not working for me:

1. # mysql -u root -p
2. Enter password: hadoop
3. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I even tries other means such as

[root@sandbox-hdp ~]# mysql -root -h 0.0.0.0

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

I am using the latest HDP sandbox which is version 2.6.5

[root@sandbox-hdp ~]# sandbox-version

== Sandbox Information ==

Platform: hdp-security

Build date: 06-18-2018

Ambari version: 2.6.2.0-155

Hadoop version: Hadoop 2.7.3.2.6.5.0-292

OS: CentOS Linux release 7.5.1804

Any help will be appreciated

[#2]

@Xianshun Chen I tried on my sandbox and this password works for me.

[root@sandbox-hdp ~]# mysql -uroot -phadoop -hlocalhost
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.39 MySQL Community Server (GPL)

You can try the following link to reset mysql password.

https://www.howtoforge.com/setting-changing-resetting-mysql-root-passwords

[#3]

As i agree with Eric it should work or also you can enforce by the below settings using the link and let me know if you need any help on this.

https://saisrinivase.wordpress.com/category/mysql/

[#4 · Accepted solution]

Many thanks for the suggestions from Eric and Sai, I think there may be something wrong with my mysql root access as Eric's suggestion not working for me either:

[root@sandbox-hdp ~]# mysql -uroot -phadoop -hlocalhost

mysql: [Warning] Using a password on the command line interface can be insecure.

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

I ended up reset the password using the following approach

systemctl stop mysqld

systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"

systemctl start mysqld

mysql -uroot

Once in the mysql, run:

FLUSH PRVILEGES;

UPDATE PASSWORD FOR root@'localhost'=PASSWORD('hadoop')

FLUSH PRIVILEGES;

QUIT;

Once log out from mysql, clear the environment MYSQLD_OPTS and restart mysql server

systemctl unset-environment MYSQLD_OPTS

systemctl restart mysqld

mysql -uroot -phadoop

[#5]

My solution was a similar:

in command line as a root

systemctl stop mysqld
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"
systemctl start mysqld
mysql -u root

in mysql shell

use mysql;
update user set authentication_string=PASSWORD("hadoop") where User='root';
flush privileges;
quit

After this change, I got trouble hive and ranger due to connection error to MySQL. Then 

I had to change hive and ranger mysql database root user password from Ambari.

It is ok now.

[#6]

My hive services wont start after running these commands.

[#7]

Hello @Xianshun Chen, thanks a lot. Your answer helped me. May I ask you how did you found your solution?

[#8]

mysql -u root -p
hortonworks1

[#9]

This solution works well for me. I hope such a basic information exists somewhere in MySql setup in Cloudera documentation to help new users to do not have to waist time to look around.

The password Saga provided is correct. "hortonworks1"

Thanks Sagar for helping newbie like us. 

Here is the screen shot.

[#10]

After mysql root password changed, hive metastore service not works. Before password change it worked. When I start the service i get below error

SQLException : SQL state: 28000
java.sql.SQLException: Access denied for user 'root'@'sandbox-hdp.hortonworks.com' (using password: YES) ErrorCode: 1045
2018-08-30 17:13:22,761 [E] Can't establish db connection.

,

After reset mysql root password , hive metastore and ranger admin not works (before changing MySQL root password the hive service was working). Below error shows

SQLException : SQL state: 28000
java.sql.SQLException: Access denied for user 'root'@'sandbox-hdp.hortonworks.com' (using password: YES) ErrorCode: 1045
2018-08-30 17:13:22,761 [E] Can't establish db connection.