How To Install And Setup MariaDB On Ubuntu

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-commonsudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://sgp1.mirrors.digitalocean.com/mariadb/repo/10.2/ubuntu xenial main'sudo apt updatesudo 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

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/mysqlsudo 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.servicesudo systemctl restart mysql.servicesudo service mariadb restartsudo service mysql restart

Install Python Library for MariaDB/MySQL

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

Enable Remote Access

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

Open Firewall Port for MariaDB/MySQL

sudo ufw allow 3306/tcp