MariaDB / MySQL Reset Root Forgotten Password on Linux
mysql reset root password.
Oops I can't remember my MySQL root password!
[root@centos7test etc]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Stop logger if running/existing:
Check if logger is running for mysqld, if so we have to kill it, otherwise when we kill mariadb it may just restart and then our skip-grant-tables later on has no effect.
ps aux|grep mysqld
root 16235 0.0 0.0 5796 1108 pts/0 S+ 05:22 0:00 logger -t mysqld -p daemon error
We kill the pid of logger which in our example is 16235. Replace with your pid
kill -9 16235
Then we need to stop mariadb:
systemctl stop mariadb
Now we need to restart it with skip-grant-tables which disables all authentication allowing us to login as root with no password.
mysqld_safe --skip-grant-tables &
[1] 1355 Now login as root with no password:
[root@centos7test etc]# 200108 15:34:30 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
200108 15:34:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@centos7test etc]# mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Issue the following commands and queries:
Make sure you set "yournewpassword" to whatever you want the new password to be.
Don't forget the "flush privileges" at the end or the new password will not be applied.
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("yournewpassword") WHERE USER='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> exit
Bye
Now login again with your new root password:
mysql -u root -p
Tags:
mariadb, mysql, reset, password, centos, etc, user, localhost, systemctl, restart, disables, authentication, allowing, login, mysqld_safe, logging, var, mysqld, daemon, databases, lib, commands, server, copyright, oracle, corporation, ab, input, queries, quot, yournewpassword, flush, privileges, applied, completion, column, feature, quicker, startup, database, update, query, ok, rows, matched, warnings,