Many times, I got the question from my colleague or training participants, how to login MySQL if I forgot the password. I have explained the process in many times to them but thought to write this scenario with demo which may help DBAS or Jr. DBA. Please try to understand the concept which help you how password work in MySQL.
During the installation MySQL/MariaDB database server on Linux, it’s recommended to set a MySQL root user password to secure it and keep the password on safe location, password is required to access the database server with root user privileges. Sometimes user may forget the password or root use account lock with wrong password try in this case how to reset it. Let me show you the demo for it. We will show you how to connect and run MySQL commands without entering a password (mysql password less root login) on the Linux terminal.
How to Set New MySQL Root Password
To run MySQL commands without entering password on the terminal, you can store your user and password in the ~/.my.cnf user specific configuration file in user’s home directory as described below.
Using a text editor, create the file ~/.my.cnf with the following contents:
[root@dbatrainings ~]# vi ~/.my.cnf
[mysql]
prompt='\U[\d]> '
protocol=tcp
user=root
password=Rajeev@123
start the mysql client with no command-line arguments.
[root@dbatrainings ~]# mysql
root@localhost[(none)]> use mysql;
But it’s not a secure way as OS admin can or use who have access the file can see your password. I prefer to use alternate option.
[root@dbatrainings ~]# vi ~/.my.cnf
[mysql]
prompt='\U[\d]> '
protocol=tcp
[root@dbatrainings ~]# mysql_config_editor set --login-path=mysql --user=root --password
-- The client successfully connects using the encrypted credentials in the login path.
[root@dbatrainings ~]# mysql
Reset MySQL root password
Use the below mention steps to reset MySQL root password by using the command line interface.
Step 1: Prerequisites
You must have (Linux) root or (Windows) Administrator access to the Cloud Server to reset the MySQL root password.
Step 2: Stop MySQL Service
[root@dbatrainings ~]# service mysqld status
[root@dbatrainings ~]# service mysqld stop
OR
[root@dbatrainings ~]# service mariadb stop
Step 3: Start MySQL in Safe Mode
Run the following command. Do not forget to put ampersand (&) at the end of the command.
[root@dbatrainings ~]# systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
[root@dbatrainings ~]# systemctl start mysqld
[root@dbatrainings ~]# systemctl status mysqld
Step 4: Reset the new MySQL password.
mysql> use mysql;
mysql> flush privileges;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Rajeev@123';
mysql> \q
[root@dbatrainings ~]# mysql -u root -pRajeev@123
Step 5: Restart the MySQL as normal process.
[root@dbatrainings ~]# systemctl stop mysqld
[root@dbatrainings ~]# systemctl status mysqld
[root@dbatrainings ~]# systemctl start mysqld
[root@dbatrainings ~]# mysql -u root -pRajeev@123