No MySQL Privileges For Any Usr

I have searched previous posts for days now... and no solution seems to work, I am risking a "duplicate post"...

PROBLEM: PHPMYADMIN installs flawlessly, but none of my users have even basic permissions (ie drop, create,grant,etc). So I can't create my the dbs for wordpress and drupal on my localhost dev server.

basic info

Fresh install Ubuntu 18.04

Linux rider1 5.3.0-53-generic #47~18.04.1-Ubuntu SMP Thu May 7 13:10:50 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

Apache2

Server version: Apache/2.4.29 (Ubuntu)

MySQL 5.7

mysql Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using EditLine wrapper

PHP 7

PHP 7.2.24-0ubuntu0.18.04.6 (cli) (built: May 26 2020 13:09:11) ( NTS )

PROBLEM DETAILS: I have two mysql users root and phpmyadmin, both should have "superuser" privileges, but each time I try to grant privileges and/or create a DB using either user via mysql, I get the following errors, respectively:

GRANT PRIVILEGES: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
CREATE DATABASE: ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'wp_db'

MYSTERY

When I log into PHPMYADMIN via the browser the default dbs are missing are different for each user. This is what I see for my root user:

NOTE: Clicking Check privileges only results in a message confirming no privileges.

When I log into PHPMYADMIN as phpmyadmin user, this is what I see:

NOTE: Again, clicking Check privileges only results in a message stating that I have no privileges on the dbs.

Also inside of phpmyadmin, I do NOT have any Users or Privileges tabs to click on to add users and/or privileges.

Also, when I log into MySQL from the command line use the commands SHOW GRANTS and SHOW DATABASES for root and phpmyadmin users I get confirmation of the information shown in the screenshots above.

SOLUTIONS that I have tried:

1.) I installed LAMP via tasksel and confirmed it installed flawlessly with and sudo apache2ctl configtest

2.) I have completely uninstalled, purged, and reinstalled phpmyadmin

3.) I confirmed that apache2 was properly configured to use phpmyadmin via ls /etc/apache2/conf-available/ and ls /etc/apache2/conf-enabled/

4.) I tried every suggested solution listed in reply to similar question found here. Including running:

sudo systemctl stop mysql
sudo dpkg-reconfigure mysql-server-5.7
sudo systemctl start mysql

Which produced the following results:

myuser@mymachine:~$ sudo dpkg-reconfigure mysql-server-5.7
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.2).
Checking databases.
phpmyadmin.pma__bookmark OK
phpmyadmin.pma__central_columns OK
phpmyadmin.pma__column_info OK
phpmyadmin.pma__designer_settings OK
phpmyadmin.pma__export_templates OK
phpmyadmin.pma__favorite OK
phpmyadmin.pma__history OK
phpmyadmin.pma__navigationhiding OK
phpmyadmin.pma__pdf_pages OK
phpmyadmin.pma__recent OK
phpmyadmin.pma__relation OK
phpmyadmin.pma__savedsearches OK
phpmyadmin.pma__table_coords OK
phpmyadmin.pma__table_info OK
phpmyadmin.pma__table_uiprefs OK
phpmyadmin.pma__tracking OK
phpmyadmin.pma__userconfig OK
phpmyadmin.pma__usergroups OK
phpmyadmin.pma__users OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.

I have been reading other questions / solutions for 2 days and none of them match my LAMP / OS config with mysql 5.7 and ubuntu 18.04 and the overwhelming assume that the privileges of users work.

Anyone have any ideas how to solve this problem?

thx

2 Answers

CAUSE OF PROBLEM:If you are trying to install myphpadmin, run into whatever problems, then decide to completely uninstall phpmyadmin.... DO NOT select the "YES" when myphpmyadmin asks if you want to delete the related db's, user privileges, & configurations. INSTEAD, select "NO", then un-installation is complete run sudo apt-get autoremove to get rid of the additiona software packages that were installed.

IF YOU SELECT "YES" (cause of problem continued):What happens is that the un-installation process deletes an utterly vital directory necessary for mysql to work properly. Namely, /var/run/mysqld

To RE-ESTABLISH the vital directory, use the following command:

sudo mkdir -v /var/run/mysqld && sudo chown mysql /var/run/mysqld

After this command you should be able to re-establish your root user privileges using the following commands:

sudo mysqld --skip-grant-tables & , thensudo mysql -u root mysql , and GRANT ALL ON *.* TO 'user'@'localhost' with GRANT OPTION; from the mysql> prompt..

AT THIS POINT: I decided to completely re-install phpmyadmin because even though I had superuser privileges restored, some of the default installation databases were still missing. However, this time I selected "NO",when asked if I want to delete the default db's, user privileges, & configurations. Then I used sudo apt-get autoremove phpmyadmin && sudo apt-get purge phpmyadmin to get rid of all of the additional packages that were installed.

Once I ran sudo apt install phpmyadmin php-mbstring php-gettext, sudo phpenmod mbstring , and sudo phpenmod mbstring phpmyadmin worked perfectly.

If you miss privileges even on command line login then it's not PhpMyAdmin issue. Did you try the solution below found on

Stop mysqld and restart it with the --skip-grant-tables option. Connect to the mysqld server with just: mysql (i.e. no -p option, and username may not be required).

Issue the following commands in the mysql client:

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;

After that, you should be able to run GRANT ALL ON *.* TO 'root'@'localhost'; and have it work.