Cannot enter phpmyadmin 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>'

If phpymadmin can't connect it may be due to the auth mechanism set to auth_socket. You can change it to use a regular password like this:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'my_password';

I was struggling with this just now on a clean install of Ubuntu 18.04 and this is what made it work. There's a decent article here explaining more:

https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/


I was facing the same problem when using mariaDB with phpmyadmin (Ubuntu 16.04LTS).

Prerequisites:

1) Install MariaDB

sudo apt-get -y install mariadb-server mariadb-client
sudo mysql_secure_installation (answer to some interactive questions):
    Enter current password for root (enter for none): <enter>
    Set root password: n
    Remove anonymous users: n
    Disallow root login remotely: n
    Remove test database and access to it: n
    Reload privilege tables now: Y

If you want to uninstall mariaDB:

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5.5 mysql-client-core-5.5
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

2) Install phpmyadmin

sudo apt-get -y install phpmyadmin (and answer some interactive questions)
    Configuring phpmyadmin:
        Web server to reconfigure automatically: apache2
        Configure database for phpmyadmin with dbconfig-common: Yes
        MySQL application password for phpmyadmin: <blank>

3) In apache2, create a symbolic link to phpmyadmin

sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-available/phpmyadmin.conf
sudo a2enconf phpmyadmin.conf
sudo service apache2 restart

Ok, now, if you follow Rael's instructions, you'll be able to login into phpmyadmin, but, at least for me, I wasn't able to create new databases, as a red message came up: No privileges (or some similar message)

The fix was by reconfiguring phpmyadmin, and by answering some interactive questions.

sudo dpkg-reconfigure phpmyadmin
<Ok>
Reinstall database for phpmyadmin: <Yes>
Connection method for MySQL database server for phpmyadmin: TCP/IP
Host name of the MySQL database server for phpmyadmin: localhost
Port number for the MySQL service: 3306
MySQL database name for phpmyadmin: phpmyadmin
<Ok>
MySQL username for phpmyadmin: root
MySQL application password for phpmyadmin: pass  # OR ANY PASSWORD YOU WANT
Password confirmation: pass
Name of the database's administrative user: root
Web server to reconfigure automatically: apache2
An error occurred while installing the database: ignore

Now if you try connect into phpmyadmin (localhost/phpmyadmin) using

username: root
password: pass

you'll be able to create databases.

Tags:

Mysql

Phpmyadmin

Recent Posts