How to Manage MySQL Databases and Users from the Command Line

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 using the command line to create and manage MySQL or MariaDB databases and users.

Before you begin

Before starting with this tutorial, we assume 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, replace database_name with the name of the database that you want to create:

myszl> 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:

mysql> 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:

mysql> 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 store information about all other databases, system configuration, users, permission, and other vital data. These databases are necessary for the proper functionality of 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 the wrong database, as once you delete the database, it cannot be recovered.

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

mysql> 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:

mysql> 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:

mysql> 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 ‘%’ as a 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 that already exists, you can use:

mysql> 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 more recent, to change the password, use the following command:

mysql> 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:

mysql> 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:

mysql> SELECT user, host FROM mysql.user;

The output should look similar to the 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:

mysql> DROP USER 'database_user@'localhost';

An error will occur if you try to delete a user account that doesn’t exist.

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:

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

Query OK, 0 rows affected, 1 warning (0.00 sec)

Grant permissions to a MySQL user account

Multiple types of privileges can be granted to a user account. You can find a complete list of privileges supported by MySQL here. In this guide, we will go through several examples:

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

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

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

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

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

mysql> 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:

mysql> 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:

mysql> 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:

mysql> 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 start for anyone who wants to learn how to manage MySQL databases and users from the command line. You can also check the tutorial about resetting 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.