In this tutorial, we are going to discuss how to manage MySQL database, users and tables on MySQL server through command line. With the help of this article, users can be able to administrate over various MySQL basic queries.
MySQL is one of the widely used open-source database management system which allows users to create a number of users and databases. Also, it offer users to grant various privileges to created users in order to access and manage databases. So, let’s start to know some essential commands and its uses to manage MySQL with command line.
Prerequisites
- MySQL or MariaDB server installed on system
- Superuser (sudo) enabled user or any user with root privileges
We hope you might have met all prerequisites as mentioned above. Now let’s start with opening MySQL prompt.
Opening MySQL Prompt
To open MySQL/MariaDB on your system, you can run the following commands
sudo mysql (If MySQL is configured to open with auth_socket default plugin)
or
sudo mysql -u root -p (If MySQL is configured to open with password)
you will be asked to enter your root user password for MySQL. Just input the passcode and press enter. Here the output you can expect to see on screen.
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.19-0ubuntu5 (Ubuntu) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Creating a new MySQL database
Once the MySQL Prompt is open, we need to create a new database first. Just run the command below.
mysql> CREATE DATABASE database_name;
Replace database_name with the new name which you want to assign to your new database. Once the command runs, you can see the output as below.
Query OK, 1 row affected (0.00 sec)
However, in case if you are attempting to create a database with name that already exists, the following error message will appear on screen.
ERROR 1007 (HY000): Can't create database 'database_name'; database exists
MySQL allows to use another command to create a database, but checks first if the database name is already existing or not. Here’s the command.
mysql> CREATE DATABASE IF NOT EXISTS database_name;
Note: In case if you see 1 warning in output message, this means the query run successfully, but the database was not created due to the assigned name already existing. Just try out another name.
Listing MySQL databases
In order to see the list all databases which is existing on MySQL server, execute the following command.
mysql> SHOW DATABASES;
and the output will be shown on screen including all existing databases list like the one below.
+--------------------+ | Database | +--------------------+ | information_schema | | database_name | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
Deleting MySQL database
While dealing with MySQL/MariaDB, you may also need to delete a database. Do make sure to be cautious while running this command as once the database is deleted, it can’t be restored back. Here the command you should run to delete a database.
mysql> DROP DATABASE database_name;
The output is here if the database is successfully deleted.
Query OK, 0 rows affected (0.00 sec)
However, if you trying to delete a non-existing database, you will see and error message like this.
ERROR 1008 (HY000): Can't drop database 'database_name'; database doesn't exist
Alike creating database command discussed above, users can also check the specified database name existence before deleting a it. To do so, run the command below.
mysql> DROP DATABASE IF EXISTS database_name;
Creating new MySQL user account
A MySQL user account is essential to deal with any database or tables when required. So, to create a new MySQL user account, run the following command. Don’t forget to replace ‘database_user’ with the name of user account name you want to generate.
mysql> CREATE USER 'database_user'@'localhost' IDENTIFIED BY 'user_password';
Alike above commands, users can also try below command to avoid error message while creating an existing user account.
mysql> CREATE USER IF NOT EXISTS 'database_user'@'localhost' IDENTIFIED BY 'user_password';
Note: ‘localhost’ in the command indicates that the user will be able connect to MySQL only from localhost, where the DBMS is installed. Users can replace the localhost with a remote machine IP to connect from specific host. Also, a ‘%’ wildcard can be used that indicates the MySQL server can be connected from any hosts. Also, don’t forget to replace user_password in the command with the one you want to create for new user.
Changing MySQL user account password
When it requires to change password for user account in MySQL or MariaDB, the syntax varies depending on what version of DBMS is being used on a system.
You can check the version of MySQL or MariaDB with executing below command.
mysql –version
In case you have installed MySQL 5.7.5 (or later) or MariaDB 10.1.20 (or later), then run the following command to change user account password.
mysql> ALTER USER 'database_user'@'localhost' IDENTIFIED BY 'new_password';
otherwise the users need to execute this command for older ones.
mysql> SET PASSWORD FOR 'database_user'@'localhost' = PASSWORD('new_password');
Listing MySQL/MariaDB user accounts
In order to view the list of all MySQL accounts, you can run the command as mentioned.
mysql> SELECT user, host FROM mysql.user;
The output will include a table containing all the user accounts in DBMS installed on your system like this.
+------------------+-----------+ | user | host | +------------------+-----------+ | database_user | localhost | | debian-sys-maint | localhost | | mysql.session | localhost | +------------------+-----------+ 6 rows in set (0.00 sec)
Deleting MySQL user account
If you wish to delete a user account from DBMS, you can accomplish it running the following command.
mysql> DROP USER 'database_user@'localhost'; or mysql> DROP USER IF EXISTS 'database_user'@'localhost';
Note: The second command as mentioned will obviously check the user account name you entered before deleting the account. If it finds the match, the account will be deleted, else the user will receive a warning in the output.
Command to grant permissions to MySQL user account
We have used only a few example in this aspect because there can be multiple types of privileges which can be granted to user accounts. We will discuss the same in another post soon. Here, we are going to discuss:
Grant all privileges to a user account over a selected database.
mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
Grant all privileges to a user account over all databases
mysql> GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';
Grant all privileges to a user account over a selected table in a selected database
mysql> GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';
Revoking permissions from a MySQL user account
Since the syntax for granting permissions to a user account is different in various cases as discussed, revoking such permissions may also vary in the same way. You just need to use REVOKE instead of GRANT in the syntax which is used for granting permissions. One of the example to revoke all privileges to a user account over a selected table in a selected database is here.
mysql> REVOKE ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';
Know privileges granted to MySQL user accounts
Before you try to revoke permissions from a user account in MySQL, you might require to know actually what permissions you have granted while creating a user account. Unless you know it, you won’t be able to revoke the same. Therefore, to identify or find the privileges granted to a user account, run the following command.
mysql> SHOW GRANTS FOR 'database_user'@'localhost';
Note: The output will show you all granted permissions to selected user account in MySQL/MariaDB, no matters the account is associated with a single database or multiple database or conditional permissions.
So, in this article, we have learned about various commands to manage MySQL databases and users from command line. This is really easy and allows you to master in basic queries to deal with any MySQL/MariaDB database management system.
Nishant Verma is a senior web developer who love to share his knowledge about Linux, SysAdmin, and more other web handlers. Currently, he loves to write as content contributor for ServoNode.
