Cannot enter phpmyadmin as root (MySQL 5.7)

Question

I'm using Ubuntu desktop 16.04 (upgraded from 15.10).

I had installed phpmyadmin from by apt-get install phpmyadmin. It works if I go to localhost/phpmyadmin but I cannot log into it as root.

I have searched a lot for it. I have found many sources in which they suggest to alter /etc/phpmyadmin/config.inc.php and replace the user and password with 'root' and '' (empty for password). But my config.inc.php is different from theirs. For example in my file, there is no line for user and password and it seems it gets it automatically from another file which is /etc/phpmyadmin/config-db.php. Despite this, I have changed the user and password in that file, but now I get this error:

#1698 - Access denied for user 'root'@'localhost'

What I should do?

Phpmyadmin Version: 4.5.4.1deb2ubuntu1
mysql Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using EditLine wrapper

*THE SIMPLE WAY* search login and pathword in /etc/mysql/debian.cnf -- Ubuntu 16.04 — cl-r, Jun 07 '17 at 22:25
Try using this: Delete root user > Add root user > Grant all privileges to root user. — solf, Sep 08 '18 at 13:14

Rael Gugelmin Cunha · Accepted Answer · 2020-08-10T00:21:33.907

165

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>'

edited Aug 10 '20 at 00:21

answered Apr 26 '16 at 19:35

Rael Gugelmin Cunha

6,834

Thanks. But my mysql config file (if I am not wrong, it should be /etc/mysql/my.cnf or mysql.cnf) is very short and doesn't have anything related to logging. – Mostafa Ahangarha Apr 26 '16 at 19:48
File in Ubuntu is /etc/mysql/mysql.conf.d/mysqld.cnf. – Rael Gugelmin Cunha Apr 26 '16 at 21:54
While logging is working, but there is no match for grep command – Mostafa Ahangarha Apr 27 '16 at 07:24
2

I'll investigate. As a workaround for in the meanwhile, you can add a new user and grant all permissions and then add this to phpmyadmin config file. – Rael Gugelmin Cunha Apr 27 '16 at 16:28
13

Got to love that MySQL keeps breaking things after upgrades. Was happily using root in my workbench and now it seems the only solution is to "sudo mysql" on the command line, create a new user, grant all the same privileges and use that in Workbench. Stupid. – Andris Apr 28 '16 at 08:49
4

@MostafaAhangarha After investigate, I found that blank password is allowed, but root user always required the sudo usage on 5.7. So, I've updated the answer to guide create a new user with all privileges. – Rael Gugelmin Cunha Apr 28 '16 at 16:16
3

@Andris This will force people to use MySQL in a more secure way. It is never a good idea to allow the root user on your database to login without a password anyway. It may be fine on a local development machine, but on a server it is very dangerous. – Dan Apr 28 '16 at 18:05
3

@Dan - that's my biggest gripe - it broke my local development environment after upgrading to Ubuntu 16.04 without any warnings. I don't mind stuff like this being the default for new installations but could at least ask before installing an update. – Andris Apr 28 '16 at 21:46
1

with these instructions, you can login into phpmyadmin but not create any other users, databases etc. – phil294 Mar 03 '17 at 04:25
@Blauhirn If you create the privileges like described above, ran the flush privileges and logged in at your localhost phpmyadmin with the new user, yes, you should be able to create. I just double checked. – Rael Gugelmin Cunha Mar 03 '17 at 11:57
@Andris you can create a different user with the same priviledges like this – machineaddict Apr 12 '19 at 11:56
Working like a charm. – Sarfaraj Sipai Nov 06 '19 at 07:22
And now I know the origin of all my headaches... Million thanks! – Alfonso Nishikawa Oct 28 '20 at 17:44

Eaten by a Grue · Answer 2 · 2019-09-12T08:26:02.280

24

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/

edited Sep 12 '19 at 08:26

answered Apr 29 '18 at 05:34

Eaten by a Grue

2,542

and do not forget to change value my_password to your password for user root ;) – Vladimir Ch Dec 07 '19 at 18:14

score 9 · Answer 3 · answered Mar 19 '17 at 18:36

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.

Thank you for your answer. But wasn't it better to just share the last part instead of the whole? I think the rest is not useful here? — Mostafa Ahangarha, Mar 20 '17 at 17:02
@MostafaAhangarha Maybe the post is a little too long, but I always like to know to context in which an answer is given. — Dan Costinel, Mar 20 '17 at 21:55
Does running sudo dpkg-reconfigure phpmyadmin have any connection to installing mariadb and unistalling mysql? If yes, then your information might be useful (might be because you are suggesting the removal of mysql which is the DB I am using and I will use). If not, then there is no point of giving it as the context. If I am wrong, make me correct. — Mostafa Ahangarha, Mar 21 '17 at 08:47
@kurdtpage as long as you install and use phpmyadmin inside your local PC, then it's ok. Because nobody can access your PC, unless you give them with access. — Dan Costinel, May 17 '19 at 21:34

score 8 · Answer 4 · edited Jul 25 '18 at 11:28

In Ubuntu 16.04 for Mysql 5.7 check below table information and perform necessary settings:

mysql>  SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| root             | localhost | auth_socket           |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_password |
| phpmyadmin       | localhost | mysql_native_password |
| root             | %         | mysql_native_password |
+------------------+-----------+-----------------------+

Check if root is having auth_socket plugin then run below command:

mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

It'll work until the next MySQL apt update. I.e, you'll need to apply this after every MySQL update on Ubuntu. — Rael Gugelmin Cunha, Nov 06 '18 at 13:59

score 3 · Answer 5 · edited Jun 28 '18 at 17:38

3

For completeness, I've found a solution to my trouble in using MariadB version 10.1.23. The syntax to use in order to setup a new user is similar to one reported into the post above from @Rael Gugelmin Cunha, I put here my solution for reference to others facing same trouble:

root@raspberrypi:# sudo mysql --user=root mysql
use mysql;
CREATE USER 'phpmyadmin'@'%' IDENTIFIED BY 'some_password';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Regards

edited Jun 28 '18 at 17:38

Thomas Ward

74,764

answered Jun 27 '18 at 12:02

FIRE FOX

31

1

Welcome to Ask Ubuntu! Ask Ubuntu is for Ubuntu only, please do not post answers about Raspbian (based on Debian). – fosslinux Jun 28 '18 at 04:59
2

@ubashu This solution is not Raspbian specific, it's MariaDB specific. – Thomas Ward Jun 28 '18 at 17:38
Thanks! Still no option to create new database though. – Melroy van den Berg Aug 18 '18 at 22:08

score 2 · Answer 6 · edited Dec 28 '18 at 10:33

2

I had the same problem, I've followed most the advice and none of it worked for me! My issue was the same when I opened http://localhost/phpmyadmin in my browser it asked for the log in details.

I was using root as user, and the password I knew was correct. And I was getting

#1698 - Access denied for user 'root'@'localhost'

All it took was to use phpmyadmin as user, not root, with the known password and it let me in.

edited Dec 28 '18 at 10:33

Daniele Santi

3,164

answered Jun 15 '18 at 21:02

plutesci

341

3

You mean username should be "phpmyadmin"? – Mostafa Ahangarha Jun 15 '18 at 21:05
1

Yes it worked for me – plutesci Jun 16 '18 at 02:47
And for me that followed a tutorial, the password was "some_pass" haha. Thanks! – Marcelo Ágil Jul 16 '19 at 11:15
@MarceloÁgil I've placed several warnings in my above answer asking people to replace some_pass by a real password! – Rael Gugelmin Cunha Oct 01 '19 at 14:04

score 1 · Answer 7 · edited Mar 16 '18 at 11:50

Referring to Rael Cunha:

Yes his solution works, and I have tried others. However, referring to a user having an exposed password on a configuration file like /etc/dbconfig-common/phpmyadmin.conf looks like a security flaw for a system-wide action.

So I would rather suggest in what MariaDB 10.1.x is concerned (some_user and some_pass will be anything you may come up with):

# mysql -u root
use mysql;
CREATE USER 'some_user'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON \*.\* TO 'some_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

and leaving phpadmin setup (4) as is.

Log in to phpmyadmin with your own credentials afterwards

On my own setups I replace the above '%' by 'localhost' for security purposes, but if you have port 3306 closed on your server that would not necessarily present a security risk to you.

score 1 · Answer 8 · answered Oct 22 '18 at 10:56

Only below code works for ubuntu 18.04, php 7 above and mysql 5.7

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

Do not replace 'phpmyadmin'. Creating any other user does not have full privilege like create new user, assign database privileges to that user like that. Only phpmyadmin user will gain full access.

score -3 · Answer 9 · answered Mar 16 '18 at 11:26

-3

if you install MySQL separate stop it by this command below sudo /opt/lampp/lampp stop service mysql stop sudo /opt/lampp/lampp start

answered Mar 16 '18 at 11:26

zouhair elhadraoui

1

Cannot enter phpmyadmin as root (MySQL 5.7)

9 Answers9

1. Connect to mysql

2. Create a real password

3. Create a user for phpMyAdmin

4. Optional and unsafe: allow remote connections

5. Update phpMyAdmin

dbc_dbpass: database user password

the password to use with the above username when connecting

to a database, if one is required

Linked

Related