Resetting Root MySQL password for MariaDB on CentOS

February 20, 2018

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