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 MariaDB or 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.
SHOW DATABASES;
The databases like information_schema, MySQL, performance_schema are already created when you installed MariaDB or MySQL. Those are storing information about all other databases, system configuration, users, 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 books_database.
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 newer or 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 older or 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 student_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 amit at linuxgurus.
SHOW GRANTS FOR 'amit'@'linuxgurus';
Conclusion
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,
