# mysql -u root -p

Create a new MySQL database

Run the following command and replace db_name with the name of database you want to create:

mysql > CREATE DATABASE db_name;

If you try to create a database that already exists, you will see the following error message:

ERROR 1007 (HY000): Can't create database 'database_name'; database exists

To avoid creating duplicate database, we should use the following command:

mysql > CREATE DATABASE IF NOT EXISTS db_name;

Delete a MySQL database

Deleting a MySQL database is simple, but it is a non-reversible action, double check before executing the command. To delete a MySQL database, run the following command:

mysql > DROP DATABASE db_name;

If you try to delete a database that doesn't exists, you will see an error message, in order to avoid it, run the following command:

mysql > DROP DATABASE IF EXISTS db_name;

List all MySQL databases

You can list all the databases on your MySQL server with the following command:

mysql > SHOW DATABASES;

From the output, you may find that there are four databases not created by yourself. Don't worry, the information_schema, mysql, performance_schema, and sys databases are created by system and they are storing information about all other databases, system configuration, users, permission and other important data. These databases are necessary for the proper functionality of the MySQL installation.

Create a new MySQL user account

Run the following command, replace db_user with the user you want to create and user_password with the password you want to use:

mysql > CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'user_password';

In the command above we have set the hostname part to localhost which means that this user will be able to connect to the MySQL server only from the localhost ( i.e from the system where MySQL Server runs). If you want to grant access from another host(s) just change the localhost with the remote machine IP or use '%' wildcard for the host part, which means that the user account will be able to connect from any host.

You also can add IF NOT EXISTS to the command to avoid creating duplicate user account:

mysql > CREATE USER IF NOT EXISTS 'db_user'@'localhost' IDENTIFIED BY 'user_password';

List all MySQL user account

It is pretty easy to list all MySQL user accounts by querying the mysql.users table:

mysql > SELECT user, host FROM mysql.user;

The output should looks similar to below:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| database_user    | %         |
| database_user    | localhost |
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

Delete MySQL user account

Run the following command to delete a user account, you can find the host part from the above command of listing all the user account:

mysql > DROP USER 'db_user@'localhost';

You also can add IF NOT EXISTS to the command:

mysql > DROP USER IF EXISTS 'db_user'@'localhost';

Display MySQL user account privileges

Run the following command to find the privileges granted to a specific user account:

mysql > SHOW GRANTS FOR 'db_user'@'localhost';

Change a MySQL user account password

According to different MySQL versions, there are different commands. You can find your MySQL server version by running the following command:

# mysql --version

If you have MySQL 5.7.6 and newer or MariaDB 10.1.20 and newer, to change the password use the following command:

mysql > ALTER USER 'db_user'@'localhost' IDENTIFIED BY 'new_password';

If you have MySQL 5.7.5 and older or MariaDB 10.1.20 and older, then use:

mysql > SET PASSWORD FOR 'db_user'@'localhost' = PASSWORD('new_password');

Grant permissions to a MySQL user account

To grand all privileges to a user account over a specific database, use the following command:

mysql > GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'localhost';

To grand all privileges to a user account over all databases, use the following command:

mysql > GRANT ALL PRIVILEGES ON *.* TO 'db_user'@'localhost';

To grand all privileges to a user account over a specific table from a database, use the following command:

mysql > GRANT ALL PRIVILEGES ON db_name.table_name TO 'db_user'@'localhost';

To grant only specific privileges to a user account over a specific database, use the following command:

GRANT SELECT, INSERT, DELETE ON db_name.* TO db_user@'localhost';

Revoke permissions from a MySQL user account

If you need to revoke one or more privileges or all privileges from a user account, the command is almost identical to granting it. For example, if you want to revoke all privileges from a user account over a specific database, use the following command:

mysql > REVOKE ALL PRIVILEGES ON db_name.* TO 'db_user'@'localhost';