How to manage MySQL databases and MySQL users from the command line

Learn how to efficiently manage MySQL databases and users directly from the command line. This guide is perfect for developers, database administrators and anyone looking to streamline their workflow with MySQL.

What is MySQL?

MySQL is popular an open source database management software that helps users store, organize and retrieve data using Structured Query Language (SQL). SQL is the most popular language for adding, accessing and managing content in a database. MySQL can be used to retrieve information from databases underpinning CMS systems, forums, galleries, blogs, shopping carts and many others.

This short blog post describes how to carry out common MySQL administration tasks from the command line.

Login to MySQL shell

Once you have MySQL installed, type the following your terminal:

  mysql -u root -p;

You will be prompted to enter the MySQL root user password into the prompt, which will allow you to access the MySQL shell.

It is very important to end all MySQL commands with a semicolon ( ; ) otherwise the command will not execute.

MySQL commands are normally written in uppercase but databases, tables, usernames and other text is by convention written in lowercase. However, MySQL commands are not case-sensitive and you may choose to ignore these conventions if you wish.

List databases

Because we have logged into MySQL as root user, we have access to all databases. To list all MySQL databases on the system, run the following command.

  SHOW DATABASES;

The output should look something like this

  mysql> SHOW DATABASES;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | test               |
  +--------------------+
  4 rows in set (0.00 sec)

Create MySQL database

Creating MySQL database is very easy, MySQL command below creates new database called rails_project.

  CREATE DATABASE rails_project;

Running the SHOW DATABASES; command again results in

  mysql> SHOW DATABASES;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | rails_project      |
  | test               |
  +--------------------+
  5 rows in set (0.00 sec)

Create a MySQL database user

Once we have rails_project database created, it is a good idea to create a user with appropriate permissions to access the database. This avoids having to use MySQL root user credentials.

The MySQL command below will create a user called deploy with password helloWorld

  CREATE USER 'deploy'@'localhost' IDENTIFIED BY 'helloWorld';

Now we have user deploy created but without the necessary permissions. This means if we logout from MySQL and then attempt to login again with mysql -u deploy -p ,permission will be refused

Grant MySQL user database privileges

MySQL command below will grant all privileges (full access) to deploy user on rails_project database.

  GRANT ALL PRIVILEGES ON rails_project . * TO 'deploy'@'localhost';

NOTE: Replacing rails_project with * would grant all privileges to all databases on your system.

Reload privileges

The last step to finalize deploy user permissions to login and interact with rails_project database is to reload all privileges.

  FLUSH PRIVILEGES;

User deploy is now able to login to the MySQL shell and access rails_project database.

Select MySQL database

If deploy user wishes to interact (create tables, update records …) with rails_project database using MySQL shell, deploy user must select rails_project database.

  USE rails_project;

Running command above will select rails_project database and deploy user can start performing actions on this database.

List all MySQL users

If you still logged in as MySQL root user, you could be wondering how to list all MySQL users created on system. Command bellow is only privileged to MySQL root user as ordinary MySQL user will get no output.

  SELECT User FROM mysql.user;

Your output might be similar to one bellow, please note user deploy user in the first row.

  mysql> SELECT User FROM mysql.user;
  +------------------+
  | user             |
  +------------------+
  | deploy           |
  | josef            |
  | mysql.sys        |
  | root             |
  | thomas           |
  +------------------+
  5 rows in set (0.00 sec)

Show MySQL user privileges

While still logged in as MySQL root user, you listed all MySQL users. Now you wish to check privileges for user deploy.

  SHOW GRANTS FOR 'deploy'@'localhost';

Your output might be similar to one bellow, please note deploy user in the first row.

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

MySQL commands mentioned above will help you to get up and running but what if you wish remove MySQL user and MySQL database?

Delete MySQL user

Please be aware that deleting a MySQL user can’t be undone! MySQL command bellow deletes MySQL user deploy.

  DROP USER 'deploy'@'localhost';

If you now run SELECT User FROM mysql.user; you should see that the deploy user is no longer listed.

  mysql> SELECT User FROM mysql.user;
  +------------------+
  | user             |
  +------------------+
  | josef            |
  | mysql.sys        |
  | root             |
  | thomas           |
  +------------------+
  4 rows in set (0.00 sec)

Delete MySQL database

The MySQL command given below deletes the database rails_project, but again be aware that deleting MySQL database can’t be undone!

  DROP DATABASE rails_project;

Executing SHOW DATABASES; command now shows that rails_project database is no longer present.

  mysql> SHOW DATABASES;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | test               |
  +--------------------+
  4 rows in set (0.00 sec)

Conclusion

Managing MySQL databases and users via the command line is a fundamental skill for developers and database administrators. This approach offers direct control over database operations, enabling efficient and streamlined workflows.

By mastering commands to create, modify, and delete databases and users, as well as manage user privileges, one can ensure robust database security and integrity. Regular practice of these command-line operations not only enhances proficiency but also deepens understanding of MySQL’s functionalities, contributing to more effective database management.