How to Manage MySQL Databases and Users from the Command Line

When you maintain a MySQL server, you regularly need to create databases, manage user accounts, assign privileges, and remove resources that are no longer needed. These are core day-to-day administration tasks for both development and production environments.

This guide covers the essential MySQL commands for managing databases and users from the command line. All commands work with both MySQL and MariaDB.

Before You Begin #

We assume you already have MySQL or MariaDB installed on your system. This guide applies to MySQL 8.0 and later and MariaDB 10.5 and later. If you are unsure which version you are running, see our guide on how to check the MySQL version .

All commands in this guide are executed from the MySQL shell as the root user. To open the MySQL shell, run the following command and enter your password when prompted:

mysql -u root -p

Managing Databases #

Create a Database #

To create a new database , run the CREATE DATABASE command followed by the database name:

CREATE DATABASE my_database;

Query OK, 1 row affected (0.00 sec)

If a database with the same name already exists, MySQL returns an error. To avoid this, use IF NOT EXISTS:

CREATE DATABASE IF NOT EXISTS my_database;

Query OK, 1 row affected, 1 warning (0.00 sec)

The 1 warning indicates that the database already exists and no new database was created.

List All Databases #

To list all databases on the server, use the SHOW DATABASES command:

SHOW DATABASES;

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

The information_schema, mysql, performance_schema, and sys databases are system databases created during installation. They store metadata about the server, user accounts, and performance data.

Delete a Database #

To delete a database , use the DROP DATABASE command:

DROP DATABASE my_database;

Query OK, 1 row affected (0.00 sec)

To avoid an error when the database does not exist, use IF EXISTS:

DROP DATABASE IF EXISTS my_database;

Managing Users #

Create a User #

A MySQL user account has two parts: a username and a hostname. The hostname specifies where the user is allowed to connect from.

To create a new user , use the CREATE USER command:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';

In this example, app_user can only connect from the local machine. To allow connections from any host, use the % wildcard:

CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';

To avoid an error when the user already exists, use IF NOT EXISTS:

CREATE USER IF NOT EXISTS 'app_user'@'localhost' IDENTIFIED BY 'strong_password';

List All Users #

To list all user accounts , query the mysql.user table:

SELECT user, host FROM mysql.user;

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

Change a User Password #

To change a user password, use the ALTER USER command:

ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_strong_password';

Query OK, 0 rows affected (0.00 sec)

For more details, see our guide on how to reset a MySQL root password .

Delete a User #

To delete a user account , use the DROP USER command:

DROP USER 'app_user'@'localhost';

To avoid an error when the user does not exist, use IF EXISTS:

DROP USER IF EXISTS 'app_user'@'localhost';

Managing Privileges #

Grant Privileges #

The GRANT command assigns privileges to a user account. You can grant access at different levels: global, database, table, or column.

To grant all privileges on a specific database:

GRANT ALL PRIVILEGES ON my_database.* TO 'app_user'@'localhost';

To grant all privileges on all databases:

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

To grant all privileges on a specific table:

GRANT ALL PRIVILEGES ON my_database.my_table TO 'app_user'@'localhost';

To grant only specific privileges on a database:

GRANT SELECT, INSERT, UPDATE, DELETE ON my_database.* TO 'app_user'@'localhost';

After granting privileges, flush the privilege tables to apply the changes:

FLUSH PRIVILEGES;

Revoke Privileges #

The REVOKE command removes privileges from a user account. The syntax mirrors GRANT but uses FROM instead of TO.

To revoke all privileges on a specific database:

REVOKE ALL PRIVILEGES ON my_database.* FROM 'app_user'@'localhost';

To revoke specific privileges:

REVOKE INSERT, UPDATE ON my_database.* FROM 'app_user'@'localhost';

Show User Privileges #

To display the privileges granted to a specific user, use the SHOW GRANTS command:

SHOW GRANTS FOR 'app_user'@'localhost';

+--------------------------------------------------------------------------+
| Grants for app_user@localhost                                            |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app_user'@'localhost'                             |
| GRANT ALL PRIVILEGES ON `my_database`.* TO 'app_user'@'localhost'        |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

GRANT USAGE means the user can connect to the server but has no global privileges. The second line shows the database-level privileges.

Quick Reference #

Troubleshooting #

ERROR 1045: Access denied for user ‘root’@’localhost’
You entered the wrong password or the root account uses a different authentication method. Try connecting without -p or see our guide on resetting the MySQL root password .

ERROR 1007: Can not create database; database exists
The database already exists. Use CREATE DATABASE IF NOT EXISTS db_name; to suppress the error, or choose a different name.

ERROR 1396: Operation DROP USER failed
The user account you are trying to drop does not exist, or the username and hostname combination does not match. Verify with SELECT user, host FROM mysql.user; and make sure both parts match exactly.

Changes to privileges are not taking effect
Run FLUSH PRIVILEGES; after making changes. While GRANT and REVOKE normally reload the privilege tables automatically, running FLUSH PRIVILEGES ensures the changes are applied.

FAQ #

What is the difference between localhost and % in a user account?
The localhost hostname means the user can only connect from the same machine where MySQL is running. The % wildcard allows connections from any host.

Can I grant privileges on a database that does not exist yet?
Yes. MySQL allows you to grant privileges on a database name before that database is created. The privileges will take effect once the database is created.

How do I see which databases a user has access to?
Run SHOW GRANTS FOR 'user'@'host'; to see all privileges for that account. Database-level privileges will show which databases the user can access.

Do I need to run FLUSH PRIVILEGES after GRANT or REVOKE?
Not strictly. The GRANT and REVOKE commands reload the privilege tables automatically. However, if you modify the mysql.user table directly with INSERT or UPDATE, you must run FLUSH PRIVILEGES for the changes to take effect.

Conclusion #

This guide covered the essential commands for managing MySQL databases and user accounts from the command line. For more advanced topics, see our guide on MySQL JOINs or learn how to list tables in a database.

If you have any questions, feel free to leave a comment below.

About the authors

Dejan Panovski

Dejan Panovski is the founder of Linuxize, an RHCSA-certified Linux system administrator and DevOps engineer based in Skopje, Macedonia. Author of 800+ Linux tutorials with 20+ years of experience turning complex Linux tasks into clear, reliable guides.

View author page