24

I have a root user in mysql-server on the Ubuntu server. I am unable to login to phpMyAdmin with the root user and password. I was trying to find the configuration file but could not locate it. Help would be appreciated.

sotirov
  • 4,455
user14010
  • 341

9 Answers9

17

I encountered a similar problem in Ubuntu 14.04 using MariaDB. Instead of trying to change everything I just created a new user.

mysql -u root -p

Entered the root password Created a new user using the following command:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'some_very_complex_password';

Granted all permissions to newuser:

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

You can then log on using newuser in phpMyadmin. I would strongly encourage you to only grant specific privileges to newuser instead of Carte Blanche privileges but it's your own funeral.

lollilol
  • 3
Garikai Dzoma
  • 336
  • I had the same problem with my Raspberry Pi. I was about to complain that it was giving me the same access denied error when I remembered sudo. Thank you for this shorter workaround – dmeehan Mar 06 '20 at 22:39
16

You have to reconfigure phpMyAdmin, reset MySQL password:

  1. Reconfigure phpMyAdmin
  2. Ctrl+Alt+T to launch terminal
  3. sudo dpkg-reconfigure phpmyadmin
  4. Connection method for MySQL database for phpmyadmin: unix socket
  5. Name of the database's administrative user: root
  6. Password of the database's administrative user: mysqlsamplepassword (change this to your desired MySQL password)
  7. MySQL username for phpmyadmin: root
  8. MySQL database name for phpmyadmin: phpmyadmin
  9. Web server to reconfigure automatically: apache2
  10. ERROR 1045
  11. Ignore
  12. sudo dpkg-reconfigure mysql-server-5.5
  13. New password for the MySQL "root" user: mysqlsamplepassword (use the same password as in step 6)
  14. Repeat password for the MySQL "root" user: mysqlsamplepassword (use the same password as in steps 6 and 13)
sotirov
  • 4,455
Amigo Chan
  • 707
8

To log in as root in phpmyadmin:

echo "UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'unix_socket';FLUSH PRIVILEGES;" | mysql -u root -p

Found at the end of this tutorial

Worked for me :)

Zanna
  • 72,471
Ric
  • 81
  • Thank you for actually posting the answer to this question, unlike the other replies. – cantsay Dec 28 '19 at 21:30
  • sudo bash before the above stuff for some ubuntu based distros ;) – Eric Feb 22 '20 at 03:12
  • this is the ONLY solution that worked for me. i tried everything, this is the only. THANKS! – Chris Sep 03 '21 at 08:05
  • View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them – Nilpo Jun 03 '25 at 07:44
4

Might for some reason the AllowRoot option be set to False? See the documentation.

B. Shea
  • 1,251
loevborg
  • 7,434
3

By "rootuser" you mean the MySQL root user, not the system root user, right?

During the installation of mysql-server, the MySQL root account is created and its password is stored in /etc/mysql/debian.cnf.

The configuration files of phpMyAdmin are stored in /etc/phpmyadmin.

Lekensteyn
  • 178,864
  • Yes, I mean MySQL root user and not the system root user.I found the config file. Why is phpmyadmin not allowing to log me in with the mysql user login – user14010 Apr 10 '11 at 19:30
  • @user14010: Does the command-line mysql version work? E.g. mysql -hlocalhost -uroot -p ? – Lekensteyn Apr 10 '11 at 20:57
  • Yes it works. I am able to login to mysql through command line version but not with phpmyadmin. The reason why I am trying to use phpmyadmin is I have a sql script when I am trying to import it using the commandline mysql it is creating problems with the foreignkeys resulting in error 121. When I import the same script uing phpmyadmin it succesfully creates the tables – user14010 Apr 10 '11 at 21:11
  • @user14010: What error message did you get? I've updated my answer with the location of the PMA configuration files. – Lekensteyn Apr 11 '11 at 12:50
  • I had to use debian-sys-maint user to login in phpmyadmin, for some reason mysql root user cannot login in phpmyadmin. debian-sys-maint has the rights to create databases, create users, grant privileges, etc., so it can be used for administrative tasks. debian-sys-maint credentials are generated automatically when installing mysql-server and are saved in /etc/mysql/debian.cnf. – Augusto Destrero Mar 30 '19 at 10:13
3

I installed MySQL using synaptic manager. Didn't have to enter a root password. The command:

mysqladmin -u root password NEWPASSWORD

worked. I was able to login into PhpMyAdmin immediately.

Dimitri Podborski
  • 2,715
  • 18
  • 22
omarioja
  • 31
0

well , hello first download the phpmyadmin from here : https://www.phpmyadmin.net/downloads/

then extract the downloaded (rar,zip) to : {INSTILLATION_PATH }\laragon\etc\apps rename the folder to phpmyadmin .

now go to http://localhost/phpmyadmin

and your there :) .

if you want to login to the phpmyadmin you need to search in the phpmyadmin folder for a file called config.sample.inc and duplicate it and rename it to config.inc open the file and search for : $cfg['Servers'][$i]['AllowNoPassword'] = false; and set it to true : $cfg['Servers'][$i]['AllowNoPassword'] = true; save the file and DONE you can login using username of : root

divesh
  • 1
0

It seemed logical to me to keep things at the simplest level possible:

mysql -u root -p
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Thus phpmyadmin user which was created during the installation manages everything, including create databases.

Antonio J. de Oliveira
  • 101
  • 1
  • 6
0

I recently came across a very similar issue with Ubuntu 12.04. I just couldn't seem to login with root & no password. I set the AllowNoPassword setting to TRUE in the config. Later I found out that I was editing the wrong config.inc.php file to add the AllowNoPassword setting.

Edit:
/etc/phpmyadmin/config.inc.php
Not:
/usr/share/phpmyadmin/config.inc.php

I believe the first is the debian local config file, which will override the usr version.

jjwdesign
  • 320