6

I tried multiple ways to install mySql and then access the database on a fresh installation. OS is RHEL 7. package used is mysql-5.7.7-0.3.rc.el7.x86_64.rpm-bundle.tar

installation process: 
[root@host1 mysql]# rpm -ivh mysql-community-common-5.7.7-0.3.rc.el7.x86_64.rpm mysql-community-libs-5.7.7-0.3.rc.el7.x86_64.rpm mysql-community-client-5.7.7-0.3.rc.el7.x86_64.rpm mysql-community-server-5.7.7-0.3.rc.el7.x86_64.rpm
 Preparing...                          ################################# [100%]
 Updating / installing...
1:mysql-community-common-5.7.7-0.3.################################# [ 25%]
2:mysql-community-libs-5.7.7-0.3.rc################################# [ 50%]
3:mysql-community-client-5.7.7-0.3.################################# [ 75%]
4:mysql-community-server-5.7.7-0.3.################################# [100%]

then I start the server

 systemctl start mysqld

After this I try to access the server with no password for root. I get error as:

[root@host1 mysql]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

As I have not set any password, not very sure why this error comes. After this I tried various options and mone could help me.

option 1 : asks to make a file and change password by using an init file. I tried using that and got output as:

    [root@host1 mysql]# mysqld_safe --init-file=/home/me/mysql-init & [1]28469
    -bash: mysqld_safe: command not found

Option 2 : set the password using mysqladmin command

[root@host1 mysql]# /usr/bin/mysqladmin -u root password
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Option 3 : using mysql_secure_installation

[root@host1 mysql]#  mysql_secure_installation

Securing the MySQL server deployment.

Enter password for root user:
Error: Access denied for user 'root'@'localhost' (using password: NO)

Option 4 : mysql -u root 

[root@host1 mysql]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

and various others. Can you please suggest what can be issue behind this.

maximus
  • 608
  • 3
  • 7
  • 17

4 Answers4

11

“MySQL v 5.7 or higher generates a temporary random password after installation and stored that in mysql error log file, located at

/var/log/mysqld.log

for an installation by the MySQL Yum repository on CentOS 6/7. use below command to see the password:

cat /var/log/mysqld.log |grep "temporary password"
ρss
  • 5,115
  • 8
  • 43
  • 73
Dumb
  • 111
  • 1
  • 3
8

For those ever in a pickle over this:

  1. Stop MySql server

  2. Create a text file onetime.sql that contains something like:

set password for 'root'@'localhost' = PASSWORD('password789');

Path that file so it is safe and other users can't get to it, perhaps /home/jason

  1. Start MySql server with --init-file option:

mysqld_safe --init-file=/home/jason/onetime.sql &

  1. Wait happily while password is changed. Wait won't be long.

  2. delete that file ... rm /home/jason/onetime.sql

  3. Stop MySql server

  4. Start MySql server normal way

Edit this may be necessary :

Once MySQL has stopped,

edit your /etc/my.cnf file to add the line

skip-grant-tables

restart it

Connect to MySQL using the root user.

mysql -u root

Once logged in, you should see the following prompt:

mysql>

Enter the following commands:

mysql> use mysql;
mysql> UPDATE user SET password=PASSWORD("YOUR NEW PASSWORD HERE") WHERE User='root';
mysql> flush privileges;
mysql> quit

Now stop MySQL again:

service mysqld stop

If you edited your /etc/my.cnf file, delete the skip-grant-tables line.

Now restart MySQL and test your new login.

service mysqld restart

mysql -u root -p

Drew
  • 24,851
  • 10
  • 43
  • 78
  • thanks for your comment, but I tried this and that's my first option. I got error as "mysqld_safe : command not found" – maximus Jul 13 '15 at 22:24
  • `find / -name mysqld_safe` – Drew Jul 13 '15 at 22:41
  • It's not in your path – Drew Jul 13 '15 at 22:42
  • Ya I tried to find that earlier, no such file in whole system. Am I missing something while installation. – maximus Jul 13 '15 at 22:55
  • I was googling around trying to find a conf setting but am on an Android kinda limited. Will keep looking – Drew Jul 13 '15 at 22:59
  • 2
    edit your /etc/my.cnf file to add the line "skip-grant-tables" did the thing. Thanks a lot – maximus Jul 13 '15 at 23:36
  • Seems that was not end of things, now i'm into the database, but when I run command **UPDATE user SET `password`=PASSWORD("root") where User='root';** I get error as _ERROR 1054 (42S22): Unknown column 'password' in 'field list'_ – maximus Jul 13 '15 at 23:53
  • Did u do `use mysql` ? You need to get in that special db first – Drew Jul 13 '15 at 23:55
  • yes I did. I'm able to see columns of table user by _select * from user_. Also I dont see any column in the table user named password. Strange? – maximus Jul 13 '15 at 23:58
  • Sorry check this out you are close https://dev.mysql.com/doc/refman/5.0/en/set-password.html – Drew Jul 14 '15 at 00:00
  • Set password=password("fffff") – Drew Jul 14 '15 at 00:03
5

Just do it:

cat /var/log/mysqld.log |grep "temporary password"

[Note] A temporary password is generated for root@localhost: dI&2-&FteI8a

sudo mysql_secure_installation
Securing the MySQL server deployment. Enter password for user root: > type here temporary password
The existing password for the user account root has expired. Please set a new password.
New password: > type here your new password  
Re-enter new password:

credits to user .pss

Kurt Van den Branden
  • 11,995
  • 10
  • 76
  • 85
Carlos Konig
  • 51
  • 1
  • 1
-1

For those getting an error that column password doesn't exist do the following:

mysql> use mysql;
mysql> UPDATE user SET authentication_string=PASSWORD("YOUR NEW PASSWORD HERE") WHERE User='root';
mysql> flush privileges;
mysql> quit
ThaSaleni
  • 370
  • 1
  • 7
  • 21