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 ?

user1066991
  • 481

4 Answers4

65

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.

user9517
  • 117,122
42

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/

Irfy
  • 153
chaintng
  • 529
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.

Brian Deterling
  • 243
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;
...
jris198944
  • 87