MySQL Common User Tasks

guide mysql questions

Simon Bennett 28th May 2020

How do I show/list MySQL users?

To show all the users in your MySQL database, log into your MySQL server as an admin user using the mysql command line client or a query GUI, and run this MySQL query:

mysql> select host, user, password from mysql.user;

(note if you want to see all the data you can run with *, or to see which tables desc mysql.user;)

How do I create MySQL database users?

To create a new MySQL user you need to open console with a existing user, root for example and run the following query, don't forget to replace the username and password with something more secure.

mysql> CREATE USER `newusername`@`localhost` IDENTIFIED BY 'password';

This command has created a newusername that can login to MySQL from localhost (the same machine as the database) however you have not granted permissions yet.

How DO I grant a MySQL user access to all Databases?

If you want to grant this user access to all of the MySQL databases run the following command:

mysql> GRANT ALL PRIVILEGES on *.* TO `newusername`@`localhost`;

mysql> FLUSH PRIVILIGES


How DO I grant a MySQL user access to a single Database?

A far better way is to limit the user to only the databases they need access to, the following command will grant all access to the database limited to localhost.

mysql> GRANT ALL PRIVILEGES on `database_name`.* TO `newusername`@`localhost`;

mysql> FLUSH PRIVILIGES



Get Started Now

Get started with SnapShooter for free!
Backup one droplet, volume or mysql database