Manage MySQL Databases and Users from the Command Line

Manage MySQL from the Command Line

To open the MySQL prompt, run the following command:

mysql -u root -p

Create a Database

CREATE DATABASE database_name;

Result shoud look like:

Query OK, 1 row affected (0.00 sec)

If you want to create a database that already exists, an error will occur:

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

To avoid creating a database that already exists, modify the command as follows:

CREATE DATABASE IF NOT EXISTS database_name;

If the database already exists, the result will be:

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

1 warning is an indicator, that the database already exists and no new database has been created.

List All Databases

SHOW DATABASES;

The result should look like this:

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

These databases are default mysql databases and should not be touched:

  • information_schema
  • mysql
  • performance_schema
  • sys

Delete a Database

DROP DATABASE database_name;

Result should be:

Query OK, 0 rows affected (0.00 sec)

If you try do delete a database that does not exist, you will get a message like this:

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

You can avoid by adding an IF EXISTS to the command:

DROP DATABASE IF EXISTS database_name;

Create a New MySQL User

CREATE USER 'database_user'@'localhost' IDENTIFIED BY 'user_password';

This command sets the hostname to localhost which means, this user can only connect to the MySQL server from localhost. If you want to specify an IP address, enter the desired IP address instead of localhost. You can also use % as wildcard for the host, so that the user can connect from any host (caution – unsecure!).

To avoid creating an user, that already exists, add IF NOT EXISTS:

CREATE USER IF NOT EXISTS 'database_user'@'localhost' IDENTIFIED BY 'user_password';

Result should be:

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

The 1 warning indicates an already existing user.

Check MySQL Version

mysql --version

Change User Password

If you have MySQL 5.7.6 and newer or MariaDB 10.1.20 and newer, you can change a user password by using this 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, you have to use this command:

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

The output should look like this:

Query OK, 0 rows affected (0.00 sec)

List All MySQL Users

SELECT user, host FROM mysql.user;

The result should look like this:

+------------------+-----------+
| 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

To delete a MySQL user, use this command:

DROP USER 'database_user@'localhost';

Deleting a user that does not exist shows this error:

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

As before, you can modify the command by adding IF EXISTS:

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

Result should look like this:

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

The 1 warning indicates a not existing user.

Grant Permissions to a User

To grant all privileges for one specific database to a user, use this command:

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

To grant all privileges for a user to all databases, use this command:

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

To grant all privileges to a user for a specific table in a specific database, use this command:

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

To grant specific privileges to a user for a specific database, use:

GRANT SELECT, INSERT, DELETE ON database_name.* TO database_user@'localhost';

Revoke Permissions

To revoke permissions for a specific database, use:

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

Show Privileges

To check privileges of a specific user, you can use this command:

SHOW GRANTS FOR 'database_user'@'localhost';

Result should look like this:

+---------------------------------------------------------------------------+
| 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)

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top