Ubuntu – MySQL how to log into “root” from a user account after upgade to 5.7

14.04MySQLserver

Running Ubuntu Server 14.04, installed MySQL 5.7 using the MySQL APT Repository. I cannot access MySQL using the "root" user from a user account ($) but I can with sudo or sudo su - (#). The MySQL "root" user is working, of course – I can log in as root (#) with my password.

All MySQL configuration other than the custom "root" password is defaults. The "root" password is updated as # echo "ALTER USER 'root'@'localhost' IDENTIFIED BY '$MYSQLPASS'" | mysql.

Both the above command and installing MySQL are done automatically as provisioning steps, if that influences the defaults at all. I was able to log in to MySQL from $ using "root" prior to the upgrade, but I'm not seeing any MySQL specific changes related to this.

How can I log into MySQL's as "root" from a user account again?

Best Answer

stop mysql server (service mysql stop or ps aux | grep mysql to determine the PID and then kill -9 PID)
mysqld_safe --skip-grant-tables
UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User = 'root' to fix the root PW not working
update mysql.user set authentication_string=password('YOURNEWPASSWORD') where user='root';
killall -9 mysqld_safe
service mysql start

Related Solutions

Ubuntu – MySQL Access denied for user ‘root’@’localhost’

If you're running n localhost, just type the command below in terminal:

mysql -u root -p

If you're using an external server, enter the host IP (xxx.xxx.xxx.xxx) as well:

mysql -hxxx.xxx.xxx.xxx -uroot -p

You will be prompted for your password, enter it and you will be able to access your MySQL prompt.

You can also look at this answer on how to reset your MySQL password.

Ubuntu – Cannot enter phptheadmin as root (MySQL 5.7)

MySQL 5.7 changed the secure model: now MySQL root login requires a sudo.

I.e., phpMyAdmin will be not able to use root credentials.

The simplest, safest and permanent solution will be create a new user and grant required privileges.

1. Connect to mysql

sudo mysql --user=root mysql

2. Create a real password

In the below steps I'll use <please_replace_this> as a sample password. PLEASE, REPLACE IT BY YOUR PASSWORD! DON'T USE <please_replace_this> AS PASSWORD!

3. Create a user for phpMyAdmin

Run the following commands (replacing <please_replace_this> by the desired password):

CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY '<please_replace_this>';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

If your phpMyAdmin is connecting to localhost, this should be enough.

4. Optional and unsafe: allow remote connections

Remember: allow a remote user to have all privileges is a security concern and this is not required in most of cases.

With this in mind, if you want this user to have the same privileges during remote connections, additionally run (replacing <please_replace_this> by the password used in Step #2):

CREATE USER 'phpmyadmin'@'%' IDENTIFIED BY '<please_replace_this>';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

5. Update phpMyAdmin

Using sudo, edit /etc/dbconfig-common/phpmyadmin.conf file updating user/password values in the following sections (replacing <please_replace_this> by the password used in Step #2):

# dbc_dbuser: database user
#       the name of the user who we will use to connect to the database.
dbc_dbuser='phpmyadmin'

# dbc_dbpass: database user password
#       the password to use with the above username when connecting
#       to a database, if one is required
dbc_dbpass='<please_replace_this>'