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

up vote 91 down vote accepted

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 user for phpMyAdmin

Run the following commands (replacing some_pass by the desired password):

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

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

3. Optional: allow remote connections

Remember: allow a remote user to have all privileges is a security concern.

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

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

4. Update phpMyAdmin

Using sudo, edit /etc/dbconfig-common/phpmyadmin.conf file updating user/password values in the following sections (replacing some_pass 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='some_pass'
  • 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
  • 7
    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 8:49
  • 3
    @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
  • 2
    @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
  • 2
    @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

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.

  • 1
    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 8:47
  • 2
    I only followed the reconfiguration and now it works. – cwhisperer Jun 20 at 8:46
  • Is it a good idea to have no password for the root account? – kurdtpage Sep 20 at 3:23

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

  • 1
    Welcome to Ask Ubuntu! Ask Ubuntu is for Ubuntu only, please do not post answers about Raspbian (based on Debian). – ubashu Jun 28 at 4:59
  • 2
    @ubashu This solution is not Raspbian specific, it's MariaDB specific. – Thomas Ward Jun 28 at 17:38
  • Thanks! Still no option to create new database though. – danger89 Aug 18 at 22:08

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 6 at 13:59

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.

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 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 localhost/phpmyadmin in my browser it asked for the log in details.

I was adding root as user. and the password I knew was correct. and i was getting #1698 - Access denied for user 'root'@'localhost'

all it is use phpmyadmin as user not root, and the known password and opens.

hope this helps anyone, kinda funny how much I overlooked it. but work for me with that error #1698 - Access denied for user 'root'@'localhost'

  • 1
    You mean username should be "phpmyadmin"? – Mostafa Ahangarha Jun 15 at 21:05
  • Yes it worked for me – plutesci Jun 16 at 2:47

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.

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

protected by Community Oct 24 at 16:07

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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