How to install MariaDB Server on Debian 9 Stretch

Welcome to Debian Tutorial. Today I am going to build a MySQL Server on top of my Debian 9 system. I want to use this server for my spatial database. Well, many people said that MySQL Spatial extension is not stable yet but I think its worth to try. So first of all, I am going to install MariaDB Server on Debian 9. Let’s get started

Steps to install MariaDB Server on Debian 9 

Open Terminal and update your Debian 9 with the following command

su
apt update

Next, install MariaDB Server

apt install mysql-server

Output

root@debian:/home/dhani# apt install mysql-server
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  default-mysql-server galera-3 libaio1 libcgi-fast-perl libcgi-pm-perl
  libdbd-mysql-perl libdbi-perl libfcgi-perl libhtml-template-perl
  libjemalloc1 libmariadbclient18 libterm-readkey-perl mariadb-client-10.1
  mariadb-client-core-10.1 mariadb-common mariadb-server-10.1
  mariadb-server-core-10.1 mysql-common rsync socat
Suggested packages:
  libclone-perl libmldbm-perl libnet-daemon-perl libsql-statement-perl
  libipc-sharedcache-perl mailx mariadb-test netcat-openbsd tinyca
The following NEW packages will be installed:
  default-mysql-server galera-3 libaio1 libcgi-fast-perl libcgi-pm-perl
  libdbd-mysql-perl libdbi-perl libfcgi-perl libhtml-template-perl
  libjemalloc1 libmariadbclient18 libterm-readkey-perl mariadb-client-10.1
  mariadb-client-core-10.1 mariadb-common mariadb-server-10.1
  mariadb-server-core-10.1 mysql-common mysql-server rsync socat
0 upgraded, 21 newly installed, 0 to remove and 0 not upgraded.
Need to get 25.3 MB of archives.
After this operation, 189 MB of additional disk space will be used.
Do you want to continue? [Y/n] 

Once the installation completed we can continue to do some tweak to our MySQL/MariaDB installation. 

Secure MySQL Installation

Execute this command to start securing the MySQL/MariaDB installation:

mysql_secure_installation

Follow on screen wizard. You will need to create new password for MySQL root user.

root@debian:/home/dhani# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should 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 authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should 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] n
 ... skipping.

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 moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take 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.

Thanks for using MariaDB!

At this point, our MariaDB Server is ready for use. Now we can continue to create a new MariaDB user and new database. We will also configure our MariaDB so it can be accessed remotely. In other words, we will configure remote access to our MariaDB installation. 

Create New MySQL/MariaDB user

Follow these steps to log in and create a new user. From the Terminal, use the following command to log in to MySQL. 

#login to MySQL
mysql -u root -p

You will need to enter the MySQL root password to continue. Now let’s create a new MySQL user. 

CREATE NEW USER 'dhani'@'localhost' IDENTIFIED BY '12345';

The command will create a new MySQL user called dhani, with password 12345

Create New MySQL Database

Now, let’s create a new database. 

CREATE DATABASE manjaro;

It will create a new database called manjaro. Now let’s grant privileges for dhani to manjaro database.

GRANT ALL PRIVILEGES ON manjaro.* to 'dhani'@'localhost' IDENTIFIED BY '12345';
FLUSH PRIVILEGES;

Allow Remote Access to MySQL Database

If you are planning to access the MySQL Database from outside server, you will need to do this. First, grant MySQL user to access from any machine, not only localhost. To do so, use this command

GRANT PRIVILEGES ON manjaro.* to 'dhani'@'%' IDENTIFIED BY '12345';
EXIT;

Please note the ‘%’ of the command above. 

Now, we need to do one more thing to allow MySQL database to be accessed from the network. I wrote an article on How to enable remote access to MariaDB Server. But I will summarize it here. We will edit the file 50-server.cnf located in /etc/mysql/mariadb.conf.d directory. 

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Find the following lines

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

Change it to

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address            = 127.0.0.1

Close the file and restart MariaDB Service

systemctl restart mariadb

The following picture shows how I connect the MySQL Workbench to MariaDB Database on my Debian 9 Stretch. 

install mariadb server on debian 9

DONE. At this point, we have successfully installed MariaDB Server, create new user and database, enable remote access to our database. Thanks for coming and hope this article is useful for anyone who wants to install Mariadb server on Debian 9 Stretch. 

Be the first to comment

Leave a Reply

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.