Allow linux root user mysql root access without password
Solution 1
The easiest way to do this is to use a client section of the ~/.my.cnf file, and add the credentials there.
[client]
user=root
password=somepassword
...
it's a good idea to make that file readable only by root too.
Solution 2
For mysql 5.7+, if you set empty password for the initial setup, mysql will automatically use auth_socket
as a strategy. An attempt to update password without change the strategy will have no result. You can always able to login without using password if your user is root
.
Solution Run following command to change the authentication strategy and set the password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY ''
reference: https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/
Solution 3
As of MySQL 5.6.6, you can use the mysql_config_editor to create an encrypted file that will log you in automatically:
mysql_config_editor set --login-path=client --host=localhost --user=root --password
Then enter the password when prompted.
Note: if your password has '#' in it, and possibly other characters, use single quotes when entering the password.
Related videos on Youtube
user1066991
Updated on September 18, 2022Comments
-
user1066991 over 1 year
On cPanel when I am logged in as root and type "mysql" without hostname and password it gives me direct access to mysql root user.
I would like to do this for one of my non-cpanel server where the linux root user gets password less logon to mysql root user in the same way as it does on cPanel.
Is this possible ?
-
user1066991 over 10 yearsYes this works and this is how cPanel is doing it
-
Koen van der Rijt over 10 yearsI need to point towards a potential security problem.. You need to be a bit careful that you don't edit this in the
/etc/my.cnf
which the mysql server uses to boot up. If you put the mysql root user in the/etc/my.cnf
ANYONE can use the mysql server with ALL privileges on EVERY database without a password. Iain points towards the shell user's own.my.cnf
in the root's home which is correct of course. -
Michael Hampton over 10 yearsThis is much more work than the other answer. And your password is too short. :)
-
camh almost 7 yearsDo not do this. It puts the password on the command line which any one can read with
ps
(1) -
user2299958 over 6 yearsCan you elaborate on the single-quote comment? It is prompting you for the password on the terminal, are you wrapping the password in single quotes
-
Brian Deterling over 6 yearsYes, I found that entering it without quotes did not work if it had special characters. So when prompted, just enter ' (single quote), then the password, then '.
-
kramer65 about 6 yearsI had to add an empty string at the end to make it work:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
-
Amalgovinus over 5 yearsWhat if there is no ~/.my.cnf file? If I create it and restart mysqld, am I then supposed to be able to do this?
-
Juan Antonio over 5 yearsIntersting @kramer, only works if I set it as you
... BY "";
. Thanks! -
Fernando Kosh almost 5 yearsThat's really a not good idea
-
Fernando Kosh almost 5 yearsIt are changing passwiord to 'ANY_PASSWORD' and not clearing the password. To clear and login without password, set it to a empty string.
-
mivk about 4 yearsBe aware that the file is not "encrypted" at all! It is only encoded, and can easily be decoded to reveal the password, with simple scripts like decode-mylogin or the one described here.
-
Ap Tsi over 2 yearsnot work in Fedora + Mysql8
-
Emilio about 2 yearsMariaDB does not allow this, since it gives a false sense of security. Since the file can be easily decoded, it's as good as storing it in plain text and allowing only the current user permissions to read it. jira.mariadb.org/browse/MDEV-20665