MariaDB server is one of the most popular and open-source relational database servers in the world. It is a backward compatible, and replacement of MySQL. In this guide, you will learn how to install the MariaDB server on Ubuntu 18.10.
First of all, you need to log in as sudo user. Then, use the following command to install the MariaDB server:
> sudo apt install mariadb-server
Next, you can use the following command to check if MariaDB service is enabled at system startup.
> sudo systemctl status mariadb
If it is not enabled at system startup, then you can enable it using the command:
> sudo systemctl enable mariadb
Also, you can start the service with this command:
> sudo systemctl start mariadb
Securing MariaDB Server
After installation, to secure it, you can run the following command:
> sudo mysql_secure_installation
In order to log into MariaDB to secure it, you’ll need to enter the current password for the root user. If you’ve just installed the MariaDB server, and you haven’t set the root password yet, the password will be blank, so you can just press “Enter” here.
Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorization. You already have a root password set, so you can safely answer 'n' or you can press 'y' to change it. Change the root password? [Y/n] n ... skipping.
By default, the installation has an anonymous user which allows anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing. You can remove them before moving into a production environment.
Remove anonymous users? [Y/n] y ... Success!
Normally, root should only be allowed to connect from “localhost”. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y ... Success!
By default, MariaDB comes with a database named “test” that anyone can access. This is also intended only for testing and should be removed before going into a production environment.
Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success!
Now, reload the privilege tables so, the changes can take place their effect immediately.
Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure.
Creating Database & Assign Users
In order to log into the MariaDB server to run SQL commands, you can run the following command:
> sudo mysql
If the above command doesn’t work, then you can run this command instead:
> sudo mysql -u root -p
Here, you will need to enter the MySQL root password. Once, you are logged into mariadb., you can run the following command to create a database and with a character set (optionally).
> mariadb> CREATE DATABASE exampledb DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Here, the database name is “exampledb”. Now, to show all the databases, you can run this command:
> mariadb> show databases;
To create a new database user and grant all permissions of this database (“exampledb”) to that user, you can use the following command:
> mariadb> GRANT ALL PRIVILEGES ON exampledb.* TO "exampledbuser"@"localhost" IDENTIFIED BY "your_password";
Here, “exampledbuser” is the new user, and the password for that user is “your_password”. Also, you can assign existing user “testdbuser” to this new database “exampledb” using the command:
> mariadb> GRANT ALL PRIVILEGES ON exampledb.* TO "testdbuser"@"localhost";
Now, you will need to flush the privileges for changes to take an effect.
> mariadb> FLUSH PRIVILEGES;
Finally, you can exit from mariadb mode using the “exit” command:
> mariadb> exit;