Manage Databases and Users in MySQL

Categories:

You create users and databases with the CREATE command and delete them with the DROP command.

Delete databases and users:

DROP DATABASE mydb;
DROP USER 'myuser'@'localhost';

Create databases and users:

CREATE DATABASE mydb;
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'my-secret-password';

Change the user’s password:

SET PASSWORD FOR 'myuser'@'localhost' = 'my-new-password';

Add permisions to the user for the database:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

Keep in mind that you should always only grant the minimum privileges required by the user. The above command should be revised for each purpose (e.g. for a user that will only backup simple databases you might only want to grant SELECT and LOCK TABLES, etc.)

Activate the changes:

FLUSH PRIVILEGES;

In this example localhost was used for the user’s IP. You can use any IP instead of localhost or % to allow access from any IP.

Review the user’s privileges:

SHOW GRANTS FOR 'myuser'@'localhost';

Leave a Reply

Your email address will not be published. Required fields are marked *