How to grant full privileges to a user in MySQL

By Dillon Smart · · · 0 Comments

After setting up a lamp stack development environement with phpmyadmin locally on Linux you may notice that your phpmyadmin user (or other user name) doesn’t have the correct privileges to create new databases.

To use the phpmyadmin user to create new databases and users for those databases you need to grant full privileges to the phpmyadmin user, making the user a super user.

First access MySQL via the command line:

sudo mysql -u root

Once your in, run the following command:

GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost';

You should now get a response that looks similar to this:

Query OK, 0 rows affected (0.000 sec)

What you have done is grant the phpmyadmin user full privileges, giving the user the same access as the root user (super user). Typically you should only grant users privilleses on a single database, however as your using a local enviroment and protentially managing multiple databases for multiple projects then it can be more efficient to have a single user (phpmyadmin) to access every database on the fly.

The final step is to flush privileges.

FLUSH PRIVILEGES;

Now you can login to phpmyadmin and your user will have access to all databases and full privileges.

MySQL

0 Comment

Was this helpful? Leave a comment!

This site uses Akismet to reduce spam. Learn how your comment data is processed.

SQL Union – What is a Union and how to combine two or more SQL Select queries

Updated 1st December 2023

The SQL UNION Operator is used to combine two or more SELECT statements together into one set of results. Things to know about SQL Union Operator To execute a SQL UNION statement successfully, you need to be away of the requirements of a UNION statement. Example using SQL UNION In our example we have 2

MySQL 8: Create user and grant all privileges on database

Updated 16th August 2022

Learn how to create a new user in MySQL 8 and grant all privileges on a database. MySQL 8 introduced performance and security updates. Released in April 2018, MySQL 8 was the first major release since MySQL 5.7 in 2015. With MySQL 5.7 end of support looming in October 2023, it’s a good idea to

The ULTIMATE guide to setup Windows Subsystem for Linux (WSL) with Ubuntu, Apache2, MySQL and PHP

Updated 16th August 2022

Windows is now good for development! Yes! All thanks to Windows Subsystem for Linux (WSL). No more WAMP or Xampp, WSL can do everything you need. In this ultimate guide to setup Windows Subsystem for Linux with Ubuntu, Apache2, MySQL, and PHP we will cover: What is Windows Subsystem for Linux? Setting up Windows Subsystem