Mysql – I couldn’t change root user password on thesql with –skip-grant-tables

MySQLUbuntu

I am very new at mysql and have struggled to change root user password in mysql.
(I have never set root password but it asks me root password.)

The below is what I have done.

One thing I suspect is that flush privileges results 0 rows affected.

Could you give me a suggestion?

$ sudo service mysql stop
$ sudo mysqld_safe --skip-grant-tables &
[1] 5395
sangmin@ubuntu:~$ 170201 00:32:55 mysqld_safe Logging to syslog.
170201 00:32:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

$ ps -e | grep mysql
  5396 pts/6    00:00:00 mysqld_safe
  5545 pts/6    00:00:00 mysqld

$ mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.29-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> 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
MariaDB [mysql]> update user set password=PASSWORD("root") where User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

MariaDB [mysql]> quit
Bye

$ sudo service mysql restart
[1]+  Done                    sudo mysqld_safe --skip-grant-tables
$ mysql -uroot -proot
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Best Answer

First of all, when you start your server with the --skip-grant-tables it's recommended that you use also --skip-networking, so no one can connect from the outside.

Then, once you enter to mysql you should first do:

mysql> FLUSH PRIVILEGES;

And to chance your password you should use the ALTER USER instruction:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('YourPass');

After that, disconnect again and restart your server, that should do it.

Related Question