Resetting Root MySQL password for MariaDB on CentOS
July 9, 2016
You've lost the root password for your MariaDB database – now what do you do? You can't recover your existing password, but you can get into your database to create a new root password. You just need root privileges on the Linux server that is running MariaDB.
Stop the database using the systemctl command:
systemctl stop mariadb.service
Now we're going to start up the SQL server but with the
--skip-grant-tables instruction. This allows anyone to log into the SQL server with no password.
mysqld_safe --skip-grant-tables --skip-networking &
The --skip-networking option is insurance against someone sneaking in over the network while your database is open to everyone. Don't use this if you're logging in remotely.
Now you can reset the root password by using the mysql shell. Log in to your database as root, select the mysql database, reset the MariaDB root password, and then exit:
mysql -u root mysql> use mysql; mysql> update user set password=PASSWORD("new-password") where User='root'; mysql> flush privileges; mysql> exit
Start up SQL again normally:
systemctl restart mariadb.service
Written By Dillon