We’ve been learning how to install and upgrade MariaDB on ubuntu server and now we will learn the basic operation to manage MariaDB database on linux via command line. This tutorial will teach you how to create and delete database and user and how to grant or remove previleges to MariaDB user. MariaDB has the same command as MySQL, so this tutorial will also work with MySQL database server.
Entering MariaDB’s SQL command line
Connect to your server via SSH and make sure you are logged in as root then type this command to enter MariaDB’s SQL command
mysql -u root -p
You will be asked for MariaDB root password. Enter your password then you will be entering MariaDB’s SQL command line.
Create MariaDB database
Creating database in MariaDB through linux command line is very easy and straight forward. Type the following command to create MariaDB database.
mariadb> create database database_name;
Change database_name to whatever you want as the name of database you want to create.
Create MariaDB user
Just like creating database, creating mariaDB user is also easy. You just have to change database to user.
mariadb> create user database_user;
Change database_user to database user name you want to make. Your database will be created. But this user doesn’t have password yet. We can give this user a password so it will be asked for password to be able to connect to our database server.
We can create MariaDB user with password using this following command
mariadb> create user 'database_user'@'localhost' IDENTIFIED BY 'user_password';
user_password is your database user’s password. This way whenever this user want to login or connect to our database, it will be asked for password.
Grant Privileges For MariaDB User
We have created MariaDB user with password but this user hasn’t yet have any permission to access any database on our server. We will have to give it some privileges to access certain database on our server.
Grant MariaDB User all privileges over a database
mariadb> grant all privileges on database_name.* to 'database_user'@'localhost';
The command above will give database_user all permission over all table on database_name. To give database_user all privileges only to certain table on database_name, change the wildcard character (*) to the name of the table to which you want to grant access.
mariadb> grant all privileges on database_name.table_name to 'database_user'@'localhost';
If you only need to grant a database user only some privileges, not all, you can change the “all privileges” on the commands above to the list of privileges bellow:
- CREATE- allows user to create tables or databases
- DROP- allows user to delete tables or databases
- DELETE- allows user to delete rows from specific tables
- INSERT- allows user to insert rows into specific tables
- SELECT- allows user to read the database
- UPDATE- allow them to update table rows
Grant MariaDB User All Plrivileges on All Database and Table
If you want to grant MariaDB user all privileges over all database and table on our database server. simply change the database name and table name to wildcard symbol (*).
mariadb> grant all privileges on *.* to 'database_user'@'localhost';
The syntax of granting permission is as below:
grant [type of permission] on [database_name].[table_name] to ‘[database_user']’@'localhost’;
Once we’re done granting permission to MariaDB user, we need to reload all privileges for the changes to take effect
Remove MariaDB User and Database
Removing MariaDB database and user is as easy as creating them.
Delete MariaDB database using this command.
mariadb>drop database database_name;
To delete MariaDB user, use the following command.
mariadb>drop user 'database_user'@'localhost';
Remove Permission for MariaDB user
The syntax of removing permission is as the same as granting permission. You just need to change “grant” to “revoke”.
revoke [type of permission] on [database_name].[table_name] to '[database_user]'@'localhost';
Remember to reload all privileges for the changes to take effect
To show permission of a MariaDB user, use the following syntax.
show grants for '[database_user]'@'localhost';
log out from MariaDB command line