You will have MySQL operating someplace in your knowledge middle. If that’s the case, there could be a time when that you must set or change the foundation consumer password. This may occur if you’ve forgotten the password, or if you’re wanting up your safety sport after remembering you set the unique MySQL password to one thing far too easy.

The method is dealt with fully by way of the command line and works with both MySQL or MariaDB installations. The Linux distribution used doesn’t matter so long as you will have admin entry by the use of su or sudo.

SEE: A quick and livid information to MySQL database engines

Easy methods to set MySQL password for the primary time

Please notice, that I’ll discuss with MySQL with the concept every thing will work for each MySQL and MariaDB.

Usually, through the set up of MySQL and MariaDB, you might be requested to set an preliminary password. If that didn’t occur, you will want to set a password for the primary time. To try this, open up a terminal window and difficulty the next command:

mysqladmin -u root password NEWPASSWORD

On this case, NEWPASSWORD is the placeholder password. Subsequent, if you log into MySQL with the command mysql -u root -p, you may be prompted to enter the newly configured password.

Another methodology for setting the foundation password for the primary time — that provides a little bit of safety to your MySQL database — is to make use of the mysql_secure_connection command. This command will set the foundation consumer password and assist you to take away nameless customers, disallow distant root login, and take away the take a look at database. To make use of this command, merely kind:

mysql_secure_connection

Reply the introduced questions, and your password shall be set, making your database a bit safer.

SEE: Password Administration Coverage

Easy methods to change MySQL root consumer password

To reset the password for MySQL you first should create a brand new file with the next contents:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD';

PASSWORD is the brand new password for use. Save that file as ~/mysql-pwd.

Subsequent, cease the MySQL daemon with the command:

sudo systemctl cease mysql

With the daemon stopped, difficulty the command:

sudo mysqld -init-file=~/mysql-pwd

As soon as your command immediate is returned, restart the MySQL daemon with the command:

sudo systemctl begin mysql

It is best to now be capable to log into the MySQL command immediate with the brand new admin password like so:

mysql -u root -p

When prompted, kind the admin password, and also you’re prepared.

Easy methods to change MySQL consumer password

To alter the password of a non-root consumer, as soon as logged in, run the next question:

ALTER USER ‘username’@‘host’ IDENTIFIED BY ‘PASSWORD’;

The place username is the MySQL username, host is the host from which the consumer can join, and PASSWORD is the brand new password of selection.

Then apply the modifications by operating the command:

FLUSH PRIVILEGES;

Easy methods to get well your MySQL password

What when you’ve forgotten your MySQL root consumer password? To get well the password, you merely need to observe these steps:

1. Cease the MySQL server course of with the command sudo service mysql cease
2. Begin the MySQL server with the command sudo mysqld_safe –skip-grant-tables –skip-networking &
3. Hook up with the MySQL server as the foundation consumer with the command mysql -u root

At this level, that you must difficulty the next MySQL instructions to reset the foundation password:

mysql> use mysql;
‹mysql> replace consumer set authentication_string=password('NEWPASSWORD') the place consumer="root";
‹mysql> flush privileges;
‹mysql> give up

The place NEWPASSWORD is the brand new password for use.

Restart the MySQL daemon with the command sudo service mysql restart. It is best to now be capable to log into MySQL with the brand new password.

And that’s it. Now you can set, reset, and get well your MySQL password.

SEE: Easy methods to Question A number of Tables in SQL

Easy methods to present MySQL customers and passwords

Often, you could need to generate a listing of MySQL customers and passwords to, say, audit or backup credentials. You are able to do this by querying the mysql.consumer desk, however notice that passwords are saved in a hashed format, in order that they can’t be retrieved instantly in plaintext.

Run the next question to retrieve the consumer and password columns:

SELECT Person, Host, authentication_string FROM mysql.consumer;

The place Person is the MySQL username, Host is the host from which the consumer can join, corresponding to localhost, and authentication_string is the hashed password.

Set a tough password on your MySQL root consumer

I need to remind you the way essential it’s to set a tough password for the MySQL root consumer. Given the present state of assaults throughout the panorama of IT, I extremely advocate you utilize robust passwords on your databases. As a substitute of utilizing an simply memorized password, use a random password generator and retailer that in a password supervisor. Be safer than secure.

Fiona Jackson up to date this text in January 2025.