How to Manage MySQL Databases and Users from the Command Line

Updated on

6 min read

Manage MySQL or MariaDB databases and users

MySQL is the most popular open-source relational database management system. MySQL server allows us to create numerous users and databases and grant appropriate privileges so that the users can access and manage databases.

This tutorial explains how to use the command line to create and manage MySQL or MariaDB databases and users.

Before you begin

Before you start with this tutorial, we are assuming that you already have MySQL or MariaDB server installed on your system. All commands will be executed as a root user.

To open the MySQL prompt, type the following command and enter the MySQL root user password when prompted:

mysql -u root -p

Create a new MySQL database

To create a new MySQL database run the following command, just replace database_name with the name of the database that you want to create:

CREATE DATABASE database_name;
Query OK, 1 row affected (0.00 sec)

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 errors if the database with the same name as you are trying to create exists you can use the following command:

CREATE DATABASE IF NOT EXISTS database_name;
Query OK, 1 row affected, 1 warning (0.00 sec)

In the output above, Query OK means that the query was successful, and 1 warning tells us that the database already exists and no new database was created.

List all MySQL databases

You can list all databases that exist on our MySQL or MariaDB server with the following command:

SHOW DATABASES;

The output will look something like this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| database_name      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

The information_schema, mysql, performance_schema, and sys databases are created at installation time 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.

Delete a MySQL database

Deleting a MySQL database is as simple as running a single command. This is a non-reversible action and should be executed with caution. Make sure that you are not removing a wrong database, as once you delete the database it cannot be recovered.

To delete a MySQL or MariaDB, database run the following command:

DROP DATABASE database_name;
Query OK, 0 rows affected (0.00 sec)

If you try to delete a database that doesn’t exist you will see the following error message:

ERROR 1008 (HY000): Can't drop database 'database_name'; database doesn't exist

To avoid this error you can use the following command:

DROP DATABASE IF EXISTS database_name;

Create a new MySQL user account

A user account in MySQL consists of a user name and host name parts.

To create a new MySQL user account run the following command, just replace ‘database_user’ with the name of the user that you want to create:

CREATE USER 'database_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.

Same as when working with the databases to avoid an error when trying to create a user account which already exists you can use:

CREATE USER IF NOT EXISTS 'database_user'@'localhost' IDENTIFIED BY 'user_password';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Change a MySQL user account password

The syntax for changing a MySQL or MariaDB user account password depends on the server version you are running on your system.

You can find your server version by issuing 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:

ALTER USER 'database_user'@'localhost' IDENTIFIED BY 'new_password';

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

SET PASSWORD FOR 'database_user'@'localhost' = PASSWORD('new_password');

In both cases, the output should look like this:

Query OK, 0 rows affected (0.00 sec)

List all MySQL user accounts

You can list all MySQL or MariaDB user accounts by querying the mysql.users table:

SELECT user, host FROM mysql.user;

The output should look 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

To delete a user account , use the following command:

DROP USER 'database_user@'localhost';

If you try to delete a user account which doesn’t exist an error will occur.

ERROR 1396 (HY000): Operation DROP USER failed for 'database_user'@'localhost'

Same as when working with the databases to avoid the error you can use:

DROP USER IF EXISTS 'database_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Grant permissions to a MySQL user account

There are multiple types of privileges that can be granted to a user account. You can find a full list of privileges supported by MySQL here . In this guide we will go through several examples:

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

GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

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

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

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

GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';

If you want to grant only specific privileges to a user account over a specific database type:

GRANT SELECT, INSERT, DELETE ON database_name.* TO database_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 syntax 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:

REVOKE ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

Display MySQL user account privileges

To find the privilege(s) granted to a specific MySQL user account type:

SHOW GRANTS FOR 'database_user'@'localhost';
+---------------------------------------------------------------------------+
| Grants for database_user@localhost                                        |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'database_user'@'localhost'                         |
| GRANT ALL PRIVILEGES ON `database_name`.* TO 'database_user'@'localhost'  |
+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Conclusion

This tutorial covers only the basics, but it should be a good starting for anyone who wants to learn how to manage MySQL databases and users from the command line. You can also check the tutorial about how to reset a MySQL root password in case you have forgotten it.

That’s all! If you have any questions or feedback, feel free to leave a comment.