Install MariaDB

MariaDB is a MySQL compliant database that is a fork of the MySQL project. With MariaDB installed on a server, the server can use software that requires MySQL, as most of the popular web applications do. The installation of MariaDB in this article assumes a server running Ubuntu 16.04, nginx, and PHP with SSL/TLS installed as per the previous articles.

Most everything in this article will require root privileges.

username@servername:~$ sudo -i

Install and configure MariaDB

MariaDB 10.1 will be supported until October, 2020, making it a good candidate for admins desiring to perform minimal version upgrades but will be migrating from Ubuntu 16.04 to Ubuntu 20.04. MariaDB 10.1 is not in the official Ubuntu 16.04 repositories, so the MariaDB repository must be added.

root@servername:~# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
root@servername:~# add-apt-repository 'deb [arch=amd64] http://ftp.osuosl.org/pub/mariadb/repo/10.1/ubuntu xenial main'
root@servername:~# aptitude update && aptitude upgrade
root@servername:~# aptitude install mariadb-server php5.6-mysql

The installation of MariaDB will require creation of a root database user password. The root database user will have full access to all databases stored on the server, while each application's database will have its own user created as part of the installation of the application. It is important to have a complex password for this user, though it will be needed to create new users and databases and perform other administrative tasks.

As a final part of installation, it is important on all MariaDB servers to run the following command which will remove several unsecure default configuration settings:

root@servername:~# mysql_secure_installation

It is not necessary to reset the root password, so it is okay to answer no to that question, but the rest of the questions should be answered yes.

Configuration

Post Unix socket configuration to PHP:

root@username:~# nano /etc/php/5.6/fpm/php.ini

Change:

mysql.default_socket = /var/run/mysqld/mysqld.sock

Restart MariaDB and PHP.

root@username:~# service mysql restart
root@username:~# service php5.6-fpm restart

Verification

To confirm MariaDB has been installed correctly, load the phpinfo.php page into a browser. PHP should now include two new blocks, mysql and mysqli.

Managing databases

Probably the most common method for database management is with phpMyAdmin. However, there is a command line tool for MariaDB that is usually faster than logging in to phpMyAdmin.

Common database usage

There are a number of practices that are commonly done, though not required, including using separate databases for each program installed that requires a database. Using one database and having different prefixes in each entry is simply not necessary and actually adds complexity to database management, as well as creating a single point of failure for several programs. This can occur if the table gets corrupted, which happens, or if one of the programs using the database is compromised.

Creating a common database prefix based on domains can also be useful. When performing maintenance on databases or backing up databases, phpMyAdmin can be a useful tool, and the phpMyAdmin Graphical User Interface (GUI) will conveniently group together databases that have the same prefix. If several domains run the same software, such as Piwik and are named 'piwik_domain', then they will all be grouped under 'piwik'. However, it may be more convenient to group databases by domain, and abbreviating the domain may be easiest. The domain example.com could be abbrevated 'ex', such that each database uses the suffix 'ex_', so 'ex_piwik', 'ex_wordpress', 'ex_smf', etc., would all be grouped together. It is also a good idea to use the name of the software. It may be that a domain will have multiple programs of one type of software, especially for testing purposes. Thus, instead of using 'ex_forum', using 'ex_vanilla' would be more practical.

Each database will have its own user with privileges only to edit the database the user is assigned to. It is common practice for the name of the database and its assigned user to share the same name.

Command line

The command line tool for MariaDB is identical to the one for MySQL, and is very powerful. This guide only explains the very, very basics of its use, as creating a database from command line is usually faster than logging into phpMyAdmin.

Logging in

The command line tool is accessed with the following command and then entering the MySQL root user's password:

root@servername:~# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 524
Server version: 5.5.32-MariaDB-1~precise-log mariadb.org binary distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Create a new database

A database with UTF8, the type most commonly used, can be created with the following command:

MariaDB [(none)]> create database databasename default character set utf8 default collate utf8_general_ci;

A user, user permissions, and user password can be assigned to databasename with the following command:

MariaDB [(none)]> grant all on databasename.* to 'databasenameusername'@'localhost' identified by 'databasenameusernamepassword';

To exit the tool:

MariaDB [(none)]> exit

phpMyAdmin

A popular GUI for MySQL compatible databases is phpMyAdmin, which allows for managing databases through a web interface. PhpMyAdmin can be installed through a PPA or as a regular PHP application. The PPA is no currently recommended because it depends on a number of other packages, including some Apache packages.

Using phpMyAdmin does not require a custom configuration, as it can be used as simply a PHP interface with MySQL. However, there are considerable features available for phpMyAdmin, which some administrators may desire. See the phpMyAdmin home page for information on custom configurations.

Configure sites-available file

PhpMyAdmin should only be opened in secure sessions, so a location block should be created to forward browsers to a secure session.

Additionally, phpMyAdmin should not be run from a subdirectory called phpmyadmin. Like all PHP packages, phpMyAdmin is subject to occasional vulnerabilities. Since bots are regularly scanning to find such vulnerabilities, it is wise to use a random directory name.

Create the package-configs files:

root@servername:~# nano /etc/nginx/package-configs/phpmyadmin.conf

Add to the new file:

location ^~ /phpmyadminsubdirectory/ {
    return 301 https://$server_name$request_uri?;
}

root@servername:~# nano /etc/nginx/package-configs/phpmyadmin_https.conf

Add to the new file:

location /phpmyadminsubdirectory/ {
    location ~ (changelog|chk_rel|config|db_create|db_datadict|db_export|db_operations|db_printview|db_qbe|db_que|db_routines|db_search|db_sql|db_structure|db_tracking|db_triggers|export|get_image.js|get_scripts.js|import|index|logout|messages|navigation|phpinfo|phpmyadmin.css|pmd_general|prefs_manage|querywindow|schema_edit|server|server_binary|server_collations|server_databases|server_engines|server_export|server_priveleges|server_privileges|server_replication|server_status|server_variables|sql|tbl_addfield|tbl_change|tbl_create|tbl_indexes|tbl_printpreview|tbl_relation|tbl_select|tbl_structure|tbl_tracking|url|version_check|view_create|whitelist).*\.php$ {
        include global-configs/php_https.conf;
    }
    location ~ \.php$ { deny all; }
}

PhpMyAdmin presents a nearly endless wellspring of php files that require to be passed. Fortunately, it is generally good at informing users when an error has been encountered, so be sure to check the error logs when some part of the installation appears to be failing for no other reason, and add to the above (alphabetical) list, as necessary.

It is important to note that due to the immense popularity of phpMyAdmin, there are a substantial number of bots scanning for vulnerable installations. It is a good idea to completely rename the directory containing phpMyAdmin, which, itself is not concerned with its directory name, other than the nginx permissions be properly configured. Simply replacing the /phpmyadmin/ install directory location in the above and following instructions with any random name is enough to, at the very least, make a small dent in reducing server load, but note that it is not necessary to change the package-configs file name.

root@servername:~# nano /etc/nginx/sites-available/example.com

Add to the HTTP server block:

    include package-configs/phpmyadmin.conf;

Add to the HTTPS server block:

    include package-configs/phpmyadmin_https.conf;

Test and reload nginx.

root@servername:~# nginx -t
root@servername:~# service nginx reload

Install phpMyAdmin from file

The current version can be verified by checking the phpMyAdmin Downloads page. Download, decompress, and install phpMyAdmin:

user@servername:~$ wget https://files.phpmyadmin.net/phpMyAdmin/4.6.5.2/phpMyAdmin-4.6.5.2-english.tar.gz
user@servername:~$ tar -xvf phpMyAdmin-4.6.5.2-english.tar.gz -C /var/www/example.com/public/
user@servername:~$ rm phpMyAdmin-4.6.5.2-english.tar.gz
user@servername:~$ mv /var/www/example/public/phpMyAdmin-4.6.5.2-english/ /var/www/example.com/public/phpmyadmin
user@servername:~$ find /var/www/webdevs/core/phpmyadmin/ -type d | xargs chmod 775
user@servername:~$ find /var/www/webdevs/core/phpmyadmin/ -type f | xargs chmod 664

The config.inc.php file can optionally be created to provide a minimal feature set for phpMyAdmin. A sample file can be created with the built-in tool located at https://www.example.com/phpmyadminsubdirectory/setup/.

user@servername:~$ nano /var/www/example.com/public/phpmyadmin/config.inc.php

Here is a sample file with some basic settings. The ShowPhpInfo option is the same as phpinfo.php covered above, but can only be viewed by users who are logged into phpMyAdmin.

<?php
/*
 * Generated configuration file
 * Generated by: phpMyAdmin 4.5.1 setup script
 * Date: Tue, 27 Oct 2015 23:27:41 +0000
 */

$cfg['DefaultLang'] = 'en';
$cfg['ServerDefault'] = 1;
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';
$cfg['blowfish_secret'] = '';
$cfg['ForceSSL'] = true;
?>

Adding a 32-character string of random characters into the blowfish_secret option (add them between the '' marks) will make the session cookies more secure.

To log into phpMyAdmin, navigate to https://www.example.com/phpmyadminsubdirectory/ and use any user configured in MariaDB. Note that since phpMyAdmin is only a GUI for MariaDB, the user will only be able to make the changes as its permissions permit.

UNPM server complete!

The UNPM server is now set up and ready to serve. A good first project is to install Piwik. Piwik is a powerful web-analytics software that can be used to track and generate reports on site visitors. Nearly all popular web applications have plugins for Piwik.

External links

MariaDB An enhanced, drop-in replacement for MySQL.

What is in the Different MariaDB Releases?

Get your database on | Ars Technica