84

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

4

9 Answers 9

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>'
13
  • 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. Apr 27, 2016 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, 2016 at 8: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. Apr 28, 2016 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, 2016 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, 2016 at 21:46
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/

1
  • and do not forget to change value my_password to your password for user root ;) Dec 7, 2019 at 18:14
9

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.

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

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;
1
  • It'll work until the next MySQL apt update. I.e, you'll need to apply this after every MySQL update on Ubuntu. Nov 6, 2018 at 13:59
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

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

4
  • 3
    You mean username should be "phpmyadmin"? Jun 15, 2018 at 21:05
  • 1
    Yes it worked for me
    – plutesci
    Jun 16, 2018 at 2:47
  • And for me that followed a tutorial, the password was "some_pass" haha. Thanks! Jul 16, 2019 at 11:15
  • @MarceloÁgil I've placed several warnings in my above answer asking people to replace some_pass by a real password! Oct 1, 2019 at 14:04
1

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.

1

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.

-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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .