mysql - root password missing

It is not strange thing that you have a Mysql running, but you miss the root password for it.. There are two ways of doing that: If you can restart your database or not, lets start at the first case which is the simplest way to do that:


With database restart

[root@quickstart bin]#  mysql -u root -p123
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

fisrt thing to do is to perform a ps -ef | grep mysqld in order to see mysqld startup options

[root@quickstart bin]# ps -ef | grep mysqld
root     10421  7905  0 05:29 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
mysql    10538 10421  0 05:29 pts/1    00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root     10914  7905  0 05:34 pts/1    00:00:00 grep mysqld

now, shutdown mysqld service:

[root@quickstart bin]# service mysqld stop
171011 05:42:31 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Stopping mysqld:                                           [  OK  ]
[1]+  Done                    /usr/bin/mysqld_safe --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

and start mysqld with skip-grant-tables option witch means that server starts without using the privilege system at all, gives anyone with access to the server unrestricted access to all databases..
To start mysqld we use mysqld_safe, with the same options as previous step but adding --skip-grant-tables option as you can see below:

[root@quickstart bin]# /usr/bin/mysqld_safe --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --skip-grant-tables & 
[1] 13429
[root@quickstart bin]# 171011 06:07:02 mysqld_safe Logging to '/var/log/mysqld.log'.
171011 05:44:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

and now, we can access to the database and change root password as you can see below:

[root@quickstart bin]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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>
mysql> use mysql
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>  select user, Host, Password from user where user='root';
+------+---------------------+-------------------------------------------+
| user | Host                | Password                                  |
+------+---------------------+-------------------------------------------+
| root | localhost           | *D997577481B722A2996B58BCE11EF3C312AC0B89 |
+------+---------------------+-------------------------------------------+
1 row in set (0.00 sec)


mysql> update user set password=PASSWORD("temporal") where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Finally we can run "mysqladmin reload" in order to make mysql applies privileges again...

[root@quickstart bin]# mysqladmin reload

So, now an attempt to logon into mysql without pass will fail...

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

but you can access with password you previously set:

[root@quickstart bin]#  mysql -u root -ptemporal
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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>

Without database restart


As you can see, i have a mysqld running with --datadir=/var/lib/mysql

[root@quickstart mysql]# ps -ef | grep mysqld
root     13429  7905  0 06:07 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --skip-grant-tables
mysql    13552 13429  0 06:07 pts/1    00:00:13 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --skip-grant-tables --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

So i´m going to create a new directory called /var/lib/mysql2 with same privileges as /var/lib/mysql

[root@quickstart lib]# mkdir /var/lib/mysql2
[root@quickstart lib]#  chown -R mysql:mysql /var/lib/mysql2/

Now, we intall another mysql instance in this directory by launching green command:

[root@quickstart mysql2]# mysql_install_db --datadir=/var/lib/mysql2/ --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h quickstart.cloudera password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

So now, we can start another mysqld daemon in our machine:

[root@quickstart mysql]# /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql2 --user=mysql --log-error=/var/log/mysqld2.log --pid-file=/var/run/mysqld/mysqld2.pid --socket=/var/lib/mysql/mysql2.sock  --skip-innodb --default-storage-engine=myisam  --port=3307 & 
[2] 19634
[root@quickstart mysql]# 171011  7:28:14 [Note] Plugin 'InnoDB' is disabled.
171011  7:28:14 [Note] Event Scheduler: Loaded 0 events
171011  7:28:14 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73'  socket: '/var/lib/mysql/mysql2.sock'  port: 3307  Source distribution

Please, be carefully modifing this parameters different from original instance:
datadir=/var/lib/mysql2
log-error=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld2.pid
socket=/var/lib/mysql/mysql2.sock
port=3307
and disable innodb engine by adding this parameter:
skip-innodb --default-storage-engine=myisam


so, after that a new mysqld engine is running on my system as you can see:

[root@quickstart mysql]# ps -ef | grep mysqld
root     13429  7905  0 06:07 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --skip-grant-tables
mysql    13552 13429  0 06:07 pts/1    00:00:10 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --skip-grant-tables --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
mysql    19634  7905  0 07:28 pts/1    00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql2 --user=mysql --log-error=/var/log/mysqld2.log --pid-file=/var/run/mysqld/mysqld2.pid --socket=/var/lib/mysql/mysql2.sock --skip-innodb --default-storage-engine=myisam --port=3307

At this point, we need to copy user.frm, user.MYD and user.MYI files from original location to the new started engine location, so we do:

[root@quickstart mysql]# cp /var/lib/mysql/mysql/user.* /var/lib/mysql2/mysql/
cp: overwrite `/var/lib/mysql2/mysql/user.frm'? y
cp: overwrite `/var/lib/mysql2/mysql/user.MYD'? y
cp: overwrite `/var/lib/mysql2/mysql/user.MYI'? y
[root@quickstart mysql]#

so now, we login into new started engine an change root password into it

[root@quickstart mysql]#  mysql --socket=/var/lib/mysql/mysql2.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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> use mysql
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>  flush tables;
Query OK, 0 rows affected (0.00 sec)


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>  flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host, password from user where user like 'root';
+------+---------------------+-------------------------------------------+
| user | host                | password                                  |
+------+---------------------+-------------------------------------------+
| root | localhost           | *4A3E3E742AC35CD2AD31EED8DCDC33A01C7C556F |
+------+---------------------+-------------------------------------------+
1 rows in set (0.00 sec)

 
mysql> update user set password=PASSWORD("temporal2") where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

So, we copy back to original destination the 3 files:

[root@quickstart mysql]# cp /var/lib/mysql2/mysql/user.* /var/lib/mysql/mysql/
cp: overwrite `/var/lib/mysql/mysql/user.frm'? y
cp: overwrite `/var/lib/mysql/mysql/user.MYD'? y
cp: overwrite `/var/lib/mysql/mysql/user.MYI'? y

at this point, we can shutdown the mysql temporal engine we started previously:

[root@quickstart mysql]# mysqladmin -u root -ptemporal2 --socket=/var/lib/mysql/mysql2.sock  shutdown
171011  7:40:00 [Note] /usr/libexec/mysqld: Normal shutdown

171011  7:40:00 [Note] Event Scheduler: Purging the queue. 0 events
171011  7:40:00 [Note] /usr/libexec/mysqld: Shutdown complete

This is very importan to launch this signal to mysqld engine. When you launch this signal to mysqld engine it will flush tables, logs, the thread cache, and the host cache and reload the grant tables (this point is we need in order to reload new privileges of the new added files). When you lauch this SIGHUP command database performace might be degraded...you do it in a low activity period...

kill -1 $(/sbin/pidof mysqld)

And finally you can logon into this engine with password you set previously:

[root@quickstart mysql]# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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.