top of page

How to Connect MySQL Without Root Password.

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


Congratulation for reading the article and your time. If it help you share with others.


39 views0 comments

Recent Posts

See All

Step by Step Oracle 21c Installation on Linux

Oracle Database 21c is the first Oracle database release with CDB-only architecture. Oracle 20c was announced only CDB architecture but it was not release for on premises. Only for Cloud. That's why i

How to set cursor movement in oracle SQL

While you are working on SQL*Plus and RMAN on Linux environment and you did some mistake and you want to edit your query it won’t work by default. There is way to configure it. The rlwrap (readline wr

Comments


bottom of page