How to log in to MySQL shell when the user has no password?

Question

I got this error:

root@sys3026:/home/sys3026# mysql --user=root --password    
Enter password:     
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Any one please help me. Thanks in advance.

Answer 1

When a MySQL user is configured to use auth_socket (instead of mysql_native_password), as it is by default in Ubuntu 18.04, you can log-in as root (for example) in the following way:

sudo mysql -u'root'

or just:

sudo mysql

References:

• Digital Ocean: How To Install MySQL on Ubuntu 18.04 - read Step 3

Answer 2

If your database has no password, just leave out the --password parameter.

$ mysql --user=root

Reference:

• https://stackoverflow.com/questions/3843973/mysql-is-prompting-for-password-even-though-my-password-is-empty

Answer 3

If you have forgotten your password or you can not login you can always run mysql in a "safe_mode". Which allows you to access it without any password restriction - to change the root password or adjust something else if something went wrong.

systemctl stop mysql
sudo mysqld_safe --skip-grant-tables &

Now you can access the mysql server without a password.

mysql -uroot

Add a new password to the root user in the mysql shell

use mysql;
update user set password=PASSWORD("newpassword") where User='root';
flush privileges;

Now restart it in normal mode again and it will work with the new password.

systemctl stop mysql
systemctl start mysql

Answer 4

Try this.

This is the command to run mysql when no password is set, But If you just installed it and you did not set any password, I am pretty sure it still has a random password that can be found in logs located in /var/log/mysql.

mysql -u root

Answer 5

sudo mysql -e "CREATE USER $USER" to do further logins without sudo

You can also create a user for yourself without setting a password:

sudo mysql -e "CREATE USER $USER"

and from now on you can login directly with:

mysql

This works based on the same principle as why sudo mysql works for root as mentioned at https://askubuntu.com/a/1129495/52975: if the password is not set, it seems to automatically try and authenticate by Linux username.

Tested on Ubuntu 20.10.

Answer 6

For example, you create the user john with no password as shown below:

CREATE USER 'john'@'localhost';

Or:

CREATE USER 'john'@'localhost' IDENTIFIED BY '';

Then, you can log in with no password as shown below. *The doc says MYSQL_PWD is deprecated as of MySQL 8.0; expect it to be removed in a future version of MySQL. and my answer explains how to log in to MySQL without a password prompt:

mysql -u john

Or:

mysql -u john --password=''

Or:

MYSQL_PWD='' mysql -u john

Or, you can log in with no password for the password prompt as shown below:

mysql -u john -p
Enter password:

Or:

mysql -u john --password
Enter password:

Or:

MYSQL_PWD='' mysql -u john -p
Enter password:

Or:

MYSQL_PWD='' mysql -u john --password
Enter password:

Or on Windows, you can set the user john and no password under [client] in my.ini as shown below. *My answer explains [client] and my answer explains where my.ini is located on Windows and my answer explains how to log in with my.ini:

# "my.ini"

[client]
user="john"
password=""

Or:

# "my.ini"

[client]
user="john"
# password=""

Then, you can log in by setting my.ini's location to --defaults-file= or --defaults-extra-file= as shown below:

mysql --defaults-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini'

Or:

mysql --defaults-extra-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini'

*Not setting my.ini's location to --defaults-file= or --defaults-extra-file= gets error as shown below:

mysql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)