In this article, you will learn about how to manage MySQL and MariaDB databases and users?
MariaDB is the replacement of
MySQL in CentOS 7 and 8. The command-line is the same as in MySQL. In this tutorial, we’ll learn to create and manage databases. And, the same command will work with MySQL.
I am assuming that you have already installed the
MySQL server. If you have not installed MariaDB then you can follow the below link to install the latest version of MariaDB.
Login into MySQL as a root user
I am going to login to MariaDB server using the
root user account. You can also use the user account, which you have created.
mysql -u root -p
It will prompt for the password, enter the password you created for it. Now let’s learn to manage MariaDB and MySQL.
Creating a Database
Step 1: I am going to create a database with the name
books_database. But, you can name it anything else as per your choice or requirement.
CREATE DATABASE books_database;
Step 2: If you want to create a database, but you want to check that if the name of the
database already exists or not, then you can use below command
CREATE DATABASE IF NOT EXISTS books_database;
If you are getting output like in the above picture, it means no database created, and
1 warning means this already exists.
List all the MariaDB or MySQL databases
You can check all the databases created in MariaDB or MySQL using the below command. This command will list every single databases.
The databases like
performance_schema are already created when you installed MariaDB or MySQL. Those are storing information about all other
permissions, and other essential data. These databases are required to work correctly with the MariaDB or MySQL.
Deleting a MariaDB or MySQL database
Now we are going to delete a database, but make sure you are removing that which you want to delete; otherwise, you can not recover the deleted database. And here we are deleting the
DROP DATABASE books_database;
If you delete any database that does not exist then you get errors, to stop showing the error you can use the below command.
DROP DATABASE IF EXISTS books_database;
Creating a new MariaDB or MySQL user account
If you want to create a
new user account, then you can type the below command.
CREATE USER 'database_user'@'localhost' IDENTIFIED BY 'user_password';
For example, I want to create a user whose name is
amit, and hostname is
linuxgurus, and password is
2, then I have to enter a command like below. If you want to grant access from another host(s), just change the localhost with the remote machine IP or use
% wildcard for the host part, which means that the user account will be able to connect from any host.
CREATE USER 'amit'@'linuxgurus' IDENTIFIED BY '2';
Changing a user account password
If you want to Change the password of a user account, then it depends on the version of MariaDB or MySQL. So, you can check your database server version by typing below command.
# mysql --version
If you have
MariaDB 10.1.20 and
MySQL 5.7.6 and
newer, then to change the password use the below command:
ALTER USER 'database_user'@'localhost' IDENTIFIED BY 'new_password';
For example, if you have a user with the name
amit and hostname of the machine is
linuxgurus and password
123 then you can type the command like below.
ALTER USER 'amit'@'linuxgurus' IDENTIFIED BY '123';
If you have
MariaDB 10.1.20 and
MySQL 5.7.5 and
older, then type below command.
SET PASSWORD FOR 'database_user'@'localhost' = PASSWORD('new_password');
For example, I’m going to set the password for user
amit, and the hostname is
linuxgurus and a new password is
password so the command will look like below.
set password for 'amit'@'linuxgurus' = password('password');
List all the user’s account
You can type below command to get the user account list,
SELECT user, host FROM mysql.user;
Deleting a MariaDB or MySQL user account
If you want to delete the user account then follow the below command.
DROP USER 'database_user@'localhost';
I’m going to delete a user whose name is
amit and hostname is
linuxgurus. So, the command will be as below.
DROP USER 'amit'@'linuxgurus';
Granting permission to the user
There are many types of permission that we can grant a user. But, this is a big list. So, you are going to learn the most important of them to use as below.
1: To grant permission for a user account for a specific database. So, you can use the below command to help with this task.
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
For example, I’m going to give them permission to the user
amit for database
GRANT ALL PRIVILEGES ON student_database.* TO 'amit'@'linuxgurus';
2: So, if you want to grant
all privileges to a
user account over
all the databases,
GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';
3: To grant all privileges to a user account over a
specific table from a database,
GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';
4: If you want to grant only specific privileges to a user account over a particular type of database. So, use the below command.
GRANT SELECT, INSERT, DELETE ON database_name.* TO database_user@'localhost';
Revoking the permission of a user account from MariaDB or MySQL
If you need to revoke one or more privileges or all privileges from a user account, the syntax is almost identical to granting it. For example, if you want to withdraw all rights from a user account over a specific database,
REVOKE ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
Displaying user account privileges in MariaDB or MySQL
To find the privilege(s) granted to a specific MySQL user account,
SHOW GRANTS FOR 'database_user'@'localhost';
For example, I’m going to display the privilege for user
SHOW GRANTS FOR 'amit'@'linuxgurus';
In this tutorial, you learned about manage MariaDB or MySQL management commands. So, I hope you understand, but, if you have any questions, you can ask in the comment section.
Also, for further read,