Author Photo

Ahmer's SysAdmin Recipes is a blog for Linux System Administrators. This blog provides recipes for Installation & Configuration of Linux, Ubuntu, Oracle Database, MySQL, Apache, Nginx, Oracle Cloud Control, FreeIPA, Kerberos & OpenLDAP, Subversion, PXE, DevOps, etc. over Red Hat Enterprise Linux (RHEL), CentOS, Ubuntu and Windows.

Please give me your feedback and help me improve this blog. Please let me know If you want me to write on a specific topic.

Saturday, 16 February 2019

Reset MariaDB root password on CentOS 7

Reset MariaDB root password on CentOS 7MariaDB the famous fork of MySQL database. It is free and open-source under GNU GPL license. Due to the acquisition of MySQL by Oracle corporation, Red Hat decided to include MariaDB as the default RDBMS in their RHEL 7 operating system.

We have already wrote an article on Installation of MariaDB 10.3 database on CentOS 7. Here, we are considering a very common scenario in which the root (superuser of MySQL databases) password has been forgotten. We will cover the complete step by step procedure to reset a new password of MariaDB root user on CentOS 7.

Although, we are using MariaDB 10.3 on CentOS 7.6, but the same procedure is good enough for other forks of MySQL (such as Percona) as well. And the same procedure can be used on other distros of Linux (like Ubuntu, Fedora, etc.) with minor variations according to the platform.

Reading Advice:  Learning MySQL and MariaDB: Heading in the Right Direction with MySQL and MariaDB

 

System Specification:

We are using the same virtual machine that we have configured in our previous article Installing MariaDB 10.3 Server on CentOS 7.

Hostname: mariadb-01.example.com
IP Address: 192.168.116.130/24
Operating System: CentOS 7.6
MariaDB Server: 10.3.12

 

Reset MariaDB 10.3 root password on CentOS 7:

Check the version of our MariaDB Server.

[root@mariadb-01 ~]# mysql --version mysql Ver 15.1 Distrib 10.3.12-MariaDB, for Linux (x86_64) using readline 5.1

We have enabled the mariadb.service, therefore MariaDB instance has been started automatically during system start-up. During this auto-start process, privileges tables have been loaded to memory.

To reset the password of root user we need to start the MariaDB instance without loading privileges tables.

Therefore, first of all we must stop MariaDB service.

[root@mariadb-01 ~]# systemctl stop mariadb.service

Now, we can start MariaDB database in safe mode without loading the privileges tables as follows.

[root@mariadb-01 ~]# mysqld_safe --skip-grant-tables --skip-networking & [1] 7444 [root@mariadb-01 ~]# 190215 22:59:09 mysqld_safe Logging to '/var/lib/mysql/mariadb-01.example.com.err'. 190215 22:59:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Connect with MariaDB instance as root user.

[root@mariadb-01 ~]# mysql -u root Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.3.12-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. MariaDB [(none)]>

This time, it didn't ask for any password.

We are successfully login to MariaDB instance as root user without any password.

Before we can set the new password for root user, we have to load the privileges tables into memory.

MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.002 sec)

Now, we can set a new password for MariaDB root user.

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'a'; Query OK, 0 rows affected (0.001 sec)

Reload the privileges tables again.

MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.000 sec)

New password for MariaDB root user has been set.

Exit from MariaDB prompt.

MariaDB [(none)]> exit Bye

Currently, MariaDB instance is running in safe mode, therefore, we must restart it in normal mode.

Kill MariaDB instance process as follows.

[root@mariadb-01 ~]# kill $(cat /var/lib/mysql/mariadb-01.example.com.pid)

Start MariaDB service in normal mode.

[root@mariadb-01 ~]# systemctl start mariadb

Login to MariaDB database as root user using new password.

[root@mariadb-01 ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.3.12-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. MariaDB [(none)]>

Our new root password is working fine.

We have successfully reset password of MariaDB root user on CentOS 7.

Reset MariaDB root password on CentOS 7


YOU MIGHT ALSO LIKE:

No comments:

Post a Comment