Categories

How to Manage MySQL Databases And Users from the Command Line?

Posted by By Adam 9 months Ago
How to Manage MySQL Databases and Users from the Command Line

In this tutorial, we will learn how to Manage MySQL Databases and Users from the Command Line?. MySQL is one of the best and popular relational database management systems. MySQL allows us to create Databases, Users and grants permission to the users, so the users can access and modify the database.

Read Also: How to Install PHP 8.0 on Ubuntu 18.04 and 20.04

Login in MySQL Server

To login in MySQL server as root user and password:

mysql -u root -p

The root user can also access the MySQL server without a password, Enter the below command in the terminal:

mysql

Create a new MySQL database

In the following steps, we will learn how to create the Databases:

  • To create a new Database in MySQL, follow the below command and replace the Linuxpanda With the name which you want to use to create the database:
CREATE DATABASE Linuxpanda;

You will get the following output:

Query OK, 1 row affected (0.00 sec)
  • If we try to create a database which already exists in MySQL Server then we will see the following error:
ERROR 1007 (HY000): Can't create database 'Linuxpanda'; database exists

To avoid this type of error, check that the database exists or not while creating the database:

CREATE DATABASE IF NOT EXISTS Linuxpanda;
Query OK, 1 row affected, 1 warning (0.00 sec)

In the output Query OK indicates that the query was successful and
1 warning indicates that the database already exists in the MySQL Server.

List of all MySQL databases

To see the list of all databases that exist in the MySQL or MariaDB server, run the following command:

SHOW DATABASES;

The output will look something like this:

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

MySQL creates some databases at the installation time. These databases store information about other databases, users, permission system configuration, and other important data. All these databases are important for MySQL functionality.

Delete a MySQL database

Deleting a database is a very simple and easy task. Before deleting the database make sure that you are not removing the wrong database, it is a non-reversible action. If the database got deleted we can’t recover it.

To delete a database in MySQL or MariaDB, replace the Linuxpanda with your database name. Run the following command:

DROP DATABASE Linuxpanda;
Query OK, 0 rows affected (0.00 sec)

If we try to delete a database that did not exist in the MySQL or MariaDB Server:

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

To avoid this type of error uses the following command:

DROP DATABASE IF EXISTS Linuxpanda;

Create a new MySQL user account

While creating a MySQL user, we have to mention two details which are the user and the hostname. In the following step, we will learn how to create a User in MySQL.

To create a new User in MySQL run the following command and replace the Panda with your desired username which you want to create:

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

In the above command, we have set the hostname as localhost it means that the user can only access the MySQL Server with localhost. If we want to give access to another host then simply change the localhost with the remote IP Address or use ‘%’ wildcard. Now users can access MySQL by using any host.

To avoid errors the same as above we have done, we will use the following command while creating the new user account which already exists:

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

To change the user password, first, check the MySQL or MariaDB version in your system. Run the following command to check the version:

mysql --version

If your system has MySQL 5.7.6 or newer, MariaDB 10.1.20 or newer then run the following command:

ALTER USER 'Panda'@'localhost' IDENTIFIED BY 'New_Strong_Password';

If your system has MySQL 5.7.5 or older, MariaDB 10.1.20 or older then use the following command:

SET PASSWORD FOR 'Panda'@'localhost' = PASSWORD('New_Strong_Password');

In both cases, we will the following output :

Query OK, 0 rows affected (0.00 sec)

List of all MySQL user accounts

If we want to see the list of users available in the MySQL or MariaDB with their Host then we have to use the following command:

SELECT user, host FROM mysql.user;

You will get an output that is similar to it:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| database_Alex    | %         |
| database_HTML    | localhost |
| mysql.design     | localhost |
| mysql.system     | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

Delete MySQL user account

To delete a user account from MySQL or MariaDB then use the following command:

DROP USER 'Panda'@'localhost';

If we try to delete a user which did not exist in MySQL or MariaDB then We will see an error like this:

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

To avoid these types of errors then use the following command to delete the user account:

DROP USER IF EXISTS 'Panda'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Grant permissions to a Manage MySQL Databases

MySQL or MariaDB have multiple types of privileges (permissions) that we can grant to any user account. In this tutorial, we will also learn about privileges.

If we want to grant all the privileges to a user account over a database then run the following command. Replace Linuxpanda and Panda with your database and User:

GRANT ALL PRIVILEGES ON Linuxpanda.* TO 'Panda'@'localhost';

If we want to grant all privileges to a user account overall databases then run the following command. (*.*) means all the databases:

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

If we want to grant all the privileges to a user account over a specific table then run the following command:

GRANT ALL PRIVILEGES ON Linuxpanda.table_part1 TO 'Panda'@'localhost';

If we want to grant only some specific privileges to a user account over a database then run the following command:

GRANT SELECT, INSERT, DELETE ON Linuxpanda.* TO 'Panda'@'localhost';

Revoke permissions from a MySQL user account

If we want to revoke (remove) one or more or all privileges from a user account in MySQL, the syntax is mostly the same as the syntax of granting privileges. For example, we want to revoke all the privileges from a single database Linuxpanda then run the following command:

REVOKE ALL PRIVILEGES ON Linuxpanda.* FROM 'Panda'@'localhost';

If we want to revoke all the privileges of a User from all the databases:

REVOKE ALL PRIVILEGES ON *.* FROM 'Panda1'@'localhost';

Display MySQL user account privileges

I want to see privileges of a user account granted on databases in MySQL or MariaDB, It will show the list of databases that can be accessed by that particular user account, run the following command:

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

Conclusion

In this tutorial, we have learned how to manage the MySQL or MariaDB like create and delete the user, grant and revoke the privileges on databases for the user.

If you guys have any queries related to the Manage MySQL Databases tutorial, Let me know in the comments.