1

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'
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.