How To Install And Setup MariaDB On Ubuntu

December 11, 2017

Install MariaDB

Visit MariaDB Repositories for instruction on how to install MariaDB for various Linux Distro/Release and MariaDB version.

The following command will install MariaDB 10.2 for Ubuntu 16.04 LTS from Singapore’s Digital Ocean mirror.

Note: do provide a password for root during installation.

sudo apt-get install software-properties-common
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://sgp1.mirrors.digitalocean.com/mariadb/repo/10.2/ubuntu xenial main'

sudo apt update
sudo apt install mariadb-server

Confirm MariaDB is installed.

mysql -V

Secure MariaDB setup

mysql_secure_installation

The following questions shall be promoted:

  • Change the root password? N, since we just created our root password.
  • Remove anonymous users? Y
  • Disallow root login remotely? Y
  • Remove test database and access to it? Y
  • Reload privilege tables now? Y

Setup Database

Login to MariaDB.

mysql -u root -p

Create database.

CREATE DATABASE my_app;

Import structure & data from existing sql file.

use my_app;
SET autocommit=0; source sql_file.sql; COMMIT; 

Grant permission to table for user web on localhost only.

GRANT SELECT,INSERT,UPDATE,DELETE ON my_app.* TO 'web'@'localhost' IDENTIFIED BY 'PASSWORD';

Optimize MaridDB

Make a copy of the original configuration.

cd /etc/mysql
sudo cp my.cnf my.cnf.bak

Edit the configuration with the following settings.

sudo nano my.cnf

If you are using MyISAM, explore the following configuration.

# 30% of Memory assuming shared server with web server
key_buffer_size=512M

If you are using InnoDB.

# 30% of Memory assuming shared server with web server
innodb_buffer_pool_size=512M

Restart mysql. Any of the following command works:

sudo systemctl restart mariadb.service
sudo systemctl restart mysql.service
sudo service mariadb restart
sudo service mysql restart

Install Python Library for MariaDB/MySQL

sudo apt-get install python-pip python-dev libmariadbclient-dev libssl-dev
pip install mysqlclient

Enable Remote Access

mysql -u root -p
GRANT ALL PRIVILEGES ON [TABLE].* TO '[USER]'@'[IP]' IDENTIFIED BY 'password';

Open Firewall Port for MariaDB/MySQL

sudo ufw allow 3306/tcp
This work is licensed under a
Creative Commons Attribution-NonCommercial 4.0 International License.