CODEWITHSUNDEEP
mysql
FluxEngine
FluxEngine

Reputation: 13290

How to create a root user with no password in mysql

I am able to login into mysql using a username & password that I have created.

I ran SELECT user FROM mysql.user;

and it shows my created account and 3 instances of root.

1.) How do I see the passwords for any of the root users?

2.) If that cannot be down, how do remove those root users, and create a new root user with no password?

Upvotes: 4

Views: 9724

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

You cannot see the plain text passwords.

DELETE FROM mysql.user WHERE user='root' AND host <> 'localhost';
UPDATE mysql.user SET password = '' WHERE user='root';
FLUSH PRIVILEGES;

Before doing this, you should run mysql_secure_installation.

If you cannot get back in with root@localhost and no password, do the following:

SQLSTMT="GRANT ALL PRIVILEGES ON *.* to root@localhost"
SQLSTMT="${SQLSTMT} IDENTIFIED BY 'resetpwd' WITH GRANT OPTION;"
echo ${SQLSTMT} > /var/lib/mysql/init.sql
service mysql restart --init-file=/var/lib/mysql/init.sql
rm -f /var/lib/mysql/init.sql
SQLSTMT="UPDATE mysql.user SET password = '' WHERE user='root'"
SQLSTMT="${SQLSTMT} AND host='localhost'; FLUSH PRIVILEGES;"
mysql -uroot -presetpwd -e"${SQLSTMT}"
mysql -uroot

Upvotes: 7

Danny Beckett
Danny Beckett

Reputation: 20788

The 3 instances of root will likely be for the host variations. In other words, there might be one for root@localhost and one for root@%.

If you're using phpMyAdmin you could delete the users you don't want, or reset the passwords by clicking the Users tab.

You can check the users you don't want, and click Go on Remove selected users.

Or to reset the password for a specific user, you can click Edit privileges, scroll down to the Change password section, select No password, and hit Go.

You can download phpMyAdmin here, and you'll of course need to be logged in as root to do this.

Users Reset pass

Upvotes: 0

Siki
Siki

Reputation: 111

It is not recommended to have a root mysql user with blank password!!

However, here is how to do it

SET PASSWORD FOR [email protected]=PASSWORD('');

please not that you must be logged in to mysql as root!

Upvotes: 2

Related Questions