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 12.04, nginx, and PHP with SSL/TLS installed.

Most everything in this article will require root privileges.

username@servername:~$ sudo -i

Install and configure MariaDB

MariaDB is not in the official Ubuntu 12.04 repositories, so the MariaDB repository must be added.

root@servername:~# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
root@servername:~# add-apt-repository 'deb http://mirror.jmu.edu/pub/mariadb/repo/10.0/ubuntu precise main'
root@servername:~# aptitude update
root@servername:~# aptitude install mariadb-server php5-mysqlnd

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.

Configuration

Configure MariaDB to use Unix sockets.

root@servername:~# nano /etc/mysql/my.cnf

Add at the bottom:

skip-networking

Post Unix socket configuration to PHP:

root@username:~# nano /etc/php5/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-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 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.

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. It is not advised to use the version of phpMyAdmin in the Ubuntu Precise repositories as it does not appear to be maintained. 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.

Create the package-configs files:

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

Add to the new file:

location ^~ /phpmyadmin/ {
    return 301 <nowiki>https://$server_name$request_uri?</nowiki>;
}

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

Add to the new file:

location /phpmyadmin/ {
    location ~ (changelog|chk_rel|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|messages|navigation|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; }
}

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
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful
root@servername:~# service nginx reload
 * Reloading nginx configuration nginx                                   [ OK ]

Note that due to the popularity of phpMyAdmin, it may be advisable to change the directory the package is installed to. A quick review of most access logs will reveal substantial hits to /phpMyAdmin and /phpmyadmin. Though security through obscurity is not best practice, the resources required to serve a 404 are less than to serve the login page of phpMyAdmin.

Install phpMyAdmin through PPA

The phpMyAdmin developers regularly update the software, and occasionally it is for security fixes. Although phpMyAdmin now informs users through the interface whether or not the installed version is the most recent version, an extended period without logging into phpMyAdmin would preclude attendant updates. Installing from PPA is a convenient way to keep the package up to date. Install the PPA and phpMyAdmin.

Note that this PPA, though recommended on the phpMyAdmin home page, has not been updated since June 17, 2014.

root@servername:~# add-apt-repository ppa:nijel/phpmyadmin
root@servername:~# aptitude update && aptitude install phpmyadmin

During the setup process, do not check Apache or Lighttpd since the server is running nginx and allow dbconfig-common to configure the database. The MySQL root user password will be required for set up. Tracking the phpMyAdmin user's randomly assigned password is only necessary when using the optional configuration file.

The PPA installs phpMyAdmin to the /usr/share/phpmyadmin/ directory. Since updates to phpMyAdmin will be installed to this directory, it is easier to create a symlink to the directory in a site's public folder.

root@servername:~# ln -s /usr/share/phpmyadmin/ /var/www/example.com/public/phpmyadmin

On first use of phpMyAdmin, verify that http://www.example.com/phpmyadmin redirects to https://www.example.com/phpmyadmin. Now the server's databases can be managed by logging in to https://www.example.com/phpmyadmin with the MySQL root database user.

Upgrading from Ubuntu Precise repository

If the server has phpMyAdmin installed from the Ubuntu Precise repository, the only difference from the above procedure may be to run aptitude install phpmyadmin a second time.

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.

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

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.

Get your database on | Ars Technica