How to create a root user with no password in mysql

Question

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?

Answer 1

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

Answer 2

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!