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 18.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
Add the MariaDB repository so the latest stable version can be used. The below instructions were generated based on the current version when creating this article. Check the MariaDB Foundation repository page for the latest version.
root@servername:~# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8 root@servername:~# add-apt-repository 'deb [arch=amd64] http://mirrors.accretive-networks.net/mariadb/repo/10.3/ubuntu bionic main' root@servername:~# aptitude update && aptitude upgrade root@servername:~# aptitude install mariadb-server php7.4-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
Configure my.cnf and unix sockets support.
my.cnf
Increase the the packet size to reduce log noise and other errors:
root@username:~# nano /etc/mysql/my.cnf
Change:
max_allowed_packet = 256M
Add unix socket support
Post Unix socket configuration to PHP:
root@username:~# nano /etc/php/7.4/fpm/php.ini
Change:
mysql.default_socket = /var/run/mysqld/mysqld.sock
pdo_mysql.default_socket = /var/run/mysqld/mysqld.sock
mysqli.default_socket = /var/run/mysqld/mysqld.sock
Restart MariaDB and PHP.
root@username:~# service mysql restart root@username:~# service php7.4-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 1812 Server version: 10.1.20-MariaDB-1~xenial mariadb.org binary distribution Copyright (c) 2000, 2016, Oracle, MariaDB Corporation 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
Create a database backup
While phpMyAdmin can be used for most minor tasks, creating backups may be more reliable through command line:
username@servername:~$ sudo mysqldump -u root -p databasename > filename.sql
The server will prompt for user's password (in this case, root) before completing the task.
Restore a database backup
Restoration should be done to an already created empty database, which can be created through command line as described above or through phpMyAdmin.
username@servername:~$ sudo mysql -u root -p databasename < filename.sql
The server will prompt for user's password (in this case, root) before completing the task.
Run an sql script on a database
Running an sql script on a database is fairly straightforward:
$ sudo mysql -u root -p databasename < scriptname.sql
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 ~ (ajax|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; }
location ~ /tmp/ { 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/5.1.1/phpMyAdmin-5.1.1-english.tar.gz user@servername:~$ tar -xvf phpMyAdmin-5.1.1-english.tar.gz user@servername:~$ rm phpMyAdmin-5.1.1-english.tar.gz user@servername:~$ mkdir phpMyAdmin-5.1.1-english/tmp user@servername:~$ find phpMyAdmin-5.1.1-english/ -type d | xargs -d '\n' chmod 775 user@servername:~$ find phpMyAdmin-5.1.1-english/ -type f | xargs -d '\n' chmod 664 user@servername:~$ sudo chown root:www-data phpMyAdmin-5.1.1-english/tmp user@servername:~$ mv /var/www/example/public/phpMyAdmin-5.1.1-english/ /var/www/example.com/public/phpmyadminsubdirectory
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/phpmyadminsubdirectory/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!
It is probably a good idea to set up a transactional mail server.
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.