42

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 ?

Improve this question

4 Answers 4

Reset to default
64

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.

Improve this answer
5
  • 1
    Yes this works and this is how cPanel is doing it
    – user1066991
    Dec 27, 2013 at 9:23
  • 10
    I 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.
    – Koen van der Rijt
    Dec 27, 2013 at 9:59
  • 1
    What if there is no ~/.my.cnf file? If I create it and restart mysqld, am I then supposed to be able to do this?
    – Alkanshel
    Sep 10, 2018 at 23:44
  • not work in Fedora + Mysql8
    – gdm
    Dec 14, 2021 at 11:12
  • I thought this was a nice solution, until some scripts using MYSQL_PWD to connect to other dbs from the same machine failed (MYSQL_PWD is ignored if there is a password under [client])
    – Ecuador
    Apr 11 at 14:02
41

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/

Improve this answer
3
  • 4
    I had to add an empty string at the end to make it work: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
    – kramer65
    Apr 3, 2018 at 19:55
  • Intersting @kramer, only works if I set it as you ... BY "";. Thanks!
    – Juan Antonio
    Jan 14, 2019 at 16:04
  • It are changing passwiord to 'ANY_PASSWORD' and not clearing the password. To clear and login without password, set it to a empty string.
    – Fernando Kosh
    May 20, 2019 at 19:31
9

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.

Improve this answer
4
  • Can 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
    – user2299958
    Oct 6, 2017 at 20:33
  • Yes, 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 '.
    – Brian Deterling
    Oct 8, 2017 at 0:11
  • 1
    Be 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.
    – mivk
    Apr 8, 2020 at 17:07
  • MariaDB 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
    – Emilio
    Feb 9, 2022 at 14:59
0

Make a file with only contains the mysql root password and only root can read.

# ls -l /root/.mysqlpw 
-rw------- 1 root root 7 2013-08-19 13:24 /root/.mysqlpw

You can import a database with

# mysql -u root -p`cat /root/.mysqlpw ` yourdatabase < databasedump.sql

or connect to you database and issue mysql commands

# mysql -u root -p`cat /root/.mysqlpw ` yourdatabase
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20460
Server version: 5.1.63-0ubuntu0.10.04.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| yourdatabase       |
+--------------------+
3 rows in set (0.00 sec)

mysql> show tables;
...
Improve this answer
3
  • 6
    This is much more work than the other answer. And your password is too short. :)
    – Michael Hampton
    Jan 2, 2014 at 17:56
  • 9
    Do not do this. It puts the password on the command line which any one can read with ps(1)
    – camh
    May 17, 2017 at 10:07
  • 1
    That's really a not good idea
    – Fernando Kosh
    May 20, 2019 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.