After installing phpMyAdmin you might run in to the problem of not being able to create edit or delete any tables or export database. This is because you probably need to change permission for the database user that you using.
How to add permission for phpMyAdmin database user.
Lets say that we have added the user called “phpmyadmin” (it could be something else but for this example we will assume that this is our database user name that we have created and this user is the user that we use to login to mysql database when login in phpmyadmin panel).
If you need to change access permissions for database user you will need to login to your VPS as root user. Then access mysql running this command (usually no pass or user name will be needed if you are logged in as root:
sudo -su mysql
mysql -u root -p
Once you logged in to mysql go to database named “mysql” and search for user table:
Now run the fallowing:
SELECT user, plugin, host FROM user;
Show privileges of all mysql users.
SELECT user, create_priv, select_priv FROM user;
Now we see that our database user “phpmyadmin” do not have create and select privileges granted. This is the reason why we could not manage our database with phpMyAdmin software. To fix this we need to add to our user all the necessary privileges.
Grant privileges to mysql user
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin@localhost'; FLUSH PRIVILEGES;
If your database is placed on different server then replace @localhost with @yourserver.address.com . You can also use @IP address.
Now you are almost done there are two things you still need to remember about. Don’t forget to restart your server (apache2 or other) and mysql server.
/etc/init.d/apache2 restart /etc/init.d/mysql restart
The privileges will get refresh upon re-login so don’t forget to logout and login again to your phpMyAdmin panel!
Congratulation! You should be now able to work with database through phpMysqlAdmin login in as phpmyadmin database user.
Where to edit my php configuration settings?
If you also would like to change configuration of your php and you running apache2 then just
cd /etc/php/7.4/apache2/ , now you can view with cat or edit with nano, vi, vim.
Configure php to your need. It is always wise to copy file before editing. Backup file using
cp /etc/php/7.4/apache2/php.init /etc/php/7.4/apache2/_php.init