MariaDB¶
Installing Mariadb¶
Here we will install a basic LAMP stack with the addition of Nginx as a reverse proxy.
Firstly, in order to get the latest stable version of our database Mariadb, we add the official mariadb repository:
sudo apt update && sudo apt install software-properties-common
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64] http://mariadb.mirror.globo.tech/repo/10.5/ubuntu focal main'
And then we update the apt cache, and install mariadb:
sudo apt update && sudo apt install mariadb-server mariadb-client
Securing Mariadb¶
Now we secure the installation using the mysql_secure_installation comand. This enables you to improve the security of your MySQL/Mariadb installation by setting a password for root accounts, removing root accounts that are accessible from outside the local host, removing anonymous-user accounts, and removing the test database (which by default can be accessed by all users, even anonymous users).
Make the root password a strong one!
sudo mysql_secure_installation
Answer 'Y' to the remaining questions.
Enable the Mariadb service:
sudo systemctl enable mariadb --now
You can check the current status of Mariadb with:
sudo systemctl status mariadb
and check the version with:
mysql -V
You can log in to Mariadb as root with:
sudo mysql -u root -p
MariaDB Configuration¶
Server Defaults¶
Set the following defaults in the Mariadb config file:
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
default_storage_engine = InnoDB
Restart the service after configuration changes:
sudo service mariadb restart
Tuning¶
It's virtually impossible to just give an optimal configuration for Mariadb. It depends on a variety of factors... how much RAM you have?, how many cores?, how many databases will it be serving? How many tables? What other services will your server be running?
I strongly recommend that you work through the below links, have a good read, and do further research where necessary. Always make a backup of your configuration before you start making changes, never make lot's of changes at the same time, and always monitor performance afterwards to check the impact of your changes.
https://mariadb.com/kb/en/library/configuring-mariadb-for-optimal-performance/ https://mariadb.com/kb/en/library/optimization-and-tuning/ https://www.linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/ https://opensourceforu.com/2017/05/get-best-mariadb-performance-tuning/ https://haydenjames.io/mysql-performance-tuning-tips-scripts-tools/ https://haydenjames.io/my-cnf-tuning-avoid-this-common-pitfall/ https://haydenjames.io/mysql-query-cache-size-performance/ https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/ https://www.c-rieger.de/nextcloud-installation-guide-ubuntu/#c03 https://www.fromdual.com/mysql-configuration-file-sample https://support.plesk.com/hc/en-us/articles/115001738733-How-to-change-the-innodb-log-file-size-value-in-MySQL-MariaDB https://wiki.archlinux.org/index.php/MariaDB
Possibly the most important setting is the buffer pool size. A good startign point is to have the same number of buffer pool instances as cores, and each instance should be at least 1024M (1G) in size.
The settings you amend or add would generally go into the config file at
I have 8 cores so I set the following: /etc/mysql/my.cnf
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
Don't forget to restart the service after making any changes!
MySQLTuner¶
The MySQLTuner script assesses your MySQL installation, and then outputs suggestions for increasing your server’s performance and stability. If it does suggest changes then I advise making 1 change at a time and monitoring.
Download the MySQLTuner script:
sudo wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl -O /usr/local/sbin/mysqltuner.pl
and make executable:
sudo chmod 700 /usr/local/sbin/mysqltuner.pl
and run the script:
sudo /usr/local/sbin/mysqltuner.pl
It's worth noting that recommendations are more accurate if you leave MySQL running for a few days first.
Useful Commands¶
To get the version:
sudo mysql –version
Logging in to the command line client:
sudo mysql –u root –p
Once logged in....
Listing the databases:
SHOW DATABASES;
Moving into a database (eg nextcloud):
USE NEXTCLOUD;
Showing tables in nextcloud:
SHOW TABLES;
List table details (eg users):
DESCRIBE USERS;
Create database (eg nextcloud):
CREATE DATABASE NEXTCLOUD;
Delete database (eg nextcloud):
DROP DATABASE NEXTCLOUD;
To create a new user called ncuser and giving all priveleges on database nextcloud:
CREATE USER 'ncuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON nextcloud.* to 'ncuser'@'localhost';
FLUSH PRIVILEGES;
To backup a the nextcloud database:
sudo mysqldump –u root –p nextcloud>nextcloud_backup.sql
and to restore:
sudo mysql –u root –p nextcloud<nextcloud_backup.sql
Find much more at https://mariadb.com/kb/en/library/beginner-mariadb-articles/