MySQL / MariaDB Change Root Password Step by Step

mysql-change-root-pass.md

MySQL / MariaDB Change Root Password

The initial root password on install can be found by running

grep 'temporary password' /var/log/mysqld.log

1. Stop mysql:

systemctl stop mysqld

2. Set the mySQL environment option

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

3. Start mysql usig the options you just set

systemctl start mysqld

4. Login as root

mysql -u root

5. Update the root user password with these mysql commands

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword')
    -> WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit

*** Edit *** Note that for 5.7.6 and later, you should use

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Or you'll get a warning

^^^ Not working!!! use commands as below

mysql> select user(), current_user();
+--------+-----------------------------------+
| user() | current_user()                    |
+--------+-----------------------------------+
| root@  | skip-grants user@skip-grants host |
+--------+-----------------------------------+
1 row in set (0.02 sec)

mysql> set password for root@localhost = 'haha';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
Query OK, 0 rows affected (0.16 sec)

mysql> set password for root@localhost = 'haha';
Query OK, 0 rows affected (0.03 sec)

6. Stop mysql

systemctl stop mysqld

7. Unset the MySQL envitroment option so it starts normally next time

systemctl unset-environment MYSQLD_OPTS

8. Start mysql normally:

systemctl start mysqld

Try to login using your new password:

7. mysql -u root -p

You can use the following sql to change your root password.

mysql  Ver 15.1 Distrib 10.11.6-MariaDB

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');

SET PASSWORD