Read in 6 minutes

How to manage MySQL databases and users from the command line

This tutorial explains how to use the command line to create and 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 users can access and manage databases.

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 inside a MySQL prompt 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 or MariaDB 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 exist 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, you can see Query OK which means that the query was successful, and 1 warning which tell us that the database already exist and no new database was created.

List all MySQL databases

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

SHOW DATABASES;
+--------------------+
| 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. This databases are necessary for proper functionality of the MySQL installation.

Delete a MySQL database

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 exists 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;
Query OK, 0 rows affected, 1 warning (0.00 sec)

In the output above, you can see Query OK which means that the query was successful, and 1 warning which tell us that the database doesn’t exist.

Advertisement

Create a new MySQL user account

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

To create a new MySQL or MariaDB 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 host name 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 meant 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 exist 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

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

SELECT user, host FROM mysql.user;

The output should looks similar to below. This output lists the the default users for a MySQL 5.7 server running on an Ubuntu machine and two additional user accounts that we previously added, 'database_user'@'%' and 'database_user'@'localhost' .

+------------------+-----------+
| 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 '[email protected]'localhost';
DROP USER '[email protected]'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 trough 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 [email protected]                                       |
+---------------------------------------------------------------------------+
| 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.


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