In this article I will consider the installation, basic configuration and performance optimization of the database server MariaDB on Linux CentOS 7. At the end of the article I’ll show some examples of MariaDB configuration files so that you will be able to choose the best parameters for your db server.
Install MariaDB on CentOS
Recently, MariaDB has been added to the standard CentOS 7 base repository, but its version in the repository is 5.5. This version is not up-to-date, has some performance problems and doesn’t provide full text search in InnoDB. To install the current version of MariaDB, you first need to add the mariadb.org developer repository.
Install the nano editor using yum:
yum install nano -y
Then open the repository file to edit it:
nano /etc/yum.repos.d/mariadb.repo
Add the following text there:
[mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.4/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
Save the mariadb.repo config file and install the MariaDB server and client:
yum --disablerepo=AppStream install MariaDB-server MariaDB-client -y
Installation is complete, now you need to add the mariadb service to startup:
systemctl start mariadb
systemctl enable mariadb
Check the service status:
systemctl status mariadb
The mariadb service is active and running, so you can go on with the configuration.
Securing MariaDB Server
After we have installed and run the MariaDB server, we can move on to security settings. Run the built-in script:
/usr/bin/mysql_secure_installation
First of all, it will prompt you to enter the root password (after the initial installation of mariadb no password has been set), so we press ENTER and set a complex password in the next step. Then you can just press ENTER up to the end of the scenario execution. Anonymous users, a remote root login and test tables will be deleted, and privileges will be updated.
To connect remotely to mariadb server, you must create some rules for Linux firewall using iptables:
iptables -I INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables-save > /etc/sysconfig/iptables
Optionally, you can allow outbound connections to remote MariaDB databases:
iptables -I OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT
MariaDB Connection Test
You need to make sure that MariaDB has been successfully installed.
Let’s connect to the DB server using the built-in mysqladmin tool:
mysqladmin version
The command returns the following:
mysqladmin Ver 9.1 Distrib 10.4.7-MariaDB, for Linux on x86_64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Server version 10.4.7-MariaDB Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 59 min 35 sec Threads: 10 Questions: 24 Slow queries: 0 Opens: 28 Flush tables: 1 Open tab
It means that MariaDB has been installed successfully, the database is up and running.
You can connect to the mariadb server console to run SQL commands interactively:
mysql -u root -p
MariaDB Config File – my.cnf
Usually, after MariaDB is installed, I add my standard configuration to the config file /etc/my.cnf that works on most servers and has not caused any problems so far. Clear my.cnf and add the following text to it:
[mysqld] local-infile=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 max_allowed_packet = 128M sql_mode = "" log-error = /var/log/mysql-error.log # Cache parameters query_cache_size = 16M table_open_cache = 4096 thread_cache_size = 16 key_buffer_size = 8M thread_stack = 256K join_buffer_size = 2M sort_buffer_size = 2M # Parameters for temporary tables tmpdir = /tmp max_heap_table_size = 32M tmp_table_size = 32M # InnoDB parameters innodb_file_per_table innodb_buffer_pool_size = 32M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_use_native_aio = 0 transaction-isolation = READ-COMMITTED character-set-server = utf8 collation-server = utf8_unicode_ci init-connect = "SET NAMES utf8 COLLATE utf8_unicode_ci" skip-name-resolve [mysqldump] quick quote-names max_allowed_packet = 128M default-character-set = utf8 [mysql] [isamchk] key_buffer = 16M [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid
Let’s dwell on the main my.cnf parameters in more detail:
- datadir is the directory the DB files are stored in;
- tmpdir is the directory to store temporary files;
- skip-name-resolve disables DNS name resolving;
- max_allowed_packet is the maximum package size allowed. If you DB is using blob fields, its value cannot be less than the size of the largest field;
- max_connections is the maximum number of opened connections; the parameter sets how many clients can work with the mariadb server simultaneously;
- # Cache parameters section includes everything related to the query cache. It is not recommended to set too high values since your DB server will consume more resources;
- # InnoDB parameters section includes everything related to the innodb tables;
- innodb_buffer_pool_size is the cache buffer for data and indexes. If 1-2 projects are located on your server, set the value to 70-80% of available RAM;
- innodb_flush_method set the value O_DIRECT for Linux, it will disable OS-level caching;
- innodb_flush_log_at_trx_commit this parameter affects the write speed of innoDB tables. Take this parameter seriously: if you set 0 here, you will have higher performance, but the risk of data loss increases. I prefer to set the value 2 here, since I have not seen any obvious DB server performance gain, while security is a priority.
Performance Optimization & Tunning for MariaDB
I would like to add that you do not need to copy my configuration file: every server and every project need their own parameters. I advise to use some scripts that check MariaDB configuration automatically and provide some recommendations on the server optimization.
To optimize your mariadb parameters you can use the Tuning-Primer.sh script. First, install additional tools:
yum install bc net-tools -y
Download the optimization script:
wget https://launchpadlibrarian.net/78745738/tuning-primer.sh
Assign the execute permissions to a .sh file:
chmod +x tuning-primer.sh
Run the script:
./tuning-primer.sh
After running, the script will show you the useful information you need to know. The most important things will be highlighted in red. These are the things that are recommended to be fixed.
For example, the script showed that the number of connections is much less than I have set.
You can change this parameter both in my.cnf file and in the console. I have set a lower value (10) and the script approved it:
Using these checks, you can make the performance of your mariadb server perfect.
I would like to note that the recommended DB server continuous operating time is at least 48 hours, then the information will be more accurate and you will be able to tune your server.
You can add a section to activate a slow query log. It will help to analyze the performance of your projects. Add the following to mysqld section:
slow_query_log = 1 # enable the log of slow queries
long_query_time = 5 # set the time in seconds
slow_query_log_file = /var/log/slow-query.log # the name of your slow query log file and the path to it
log_queries_not_using_indexes # whether to write queries that do not use indexes to the log file
Also you must create log files for MariaDB and a slow query log errors:
touch /var/log/mysql-error.log
touch /var/log/slow-query.log
Restart the db service:
systemctl restart mariadb
You can view your slow query log if you open /var/log/slow-query.log or check it in real time:
tail -f /var/log/slow-query.log
Thus, you will be able to analyze the queries your project sends to your DB and conduct an audit.
If you change any DB settings, check your configuration for errors. After making any changes to the my.cnf settings, you need to check the configuration for errors:
systemctl status mariadb -l
Basic MariaDB Shell Commands
The main cli commands an administrator has to use often in mariadb console are shown below.
To locally log on to your MariaDB console, run this command:
mysql -u root -p
To remotely connect to your MariaDB server:
mysql -u root -p -h 192.168.1.20
Useful MariaDB (MySQL) commands:
create database db1; — creates a DB with the name db1
show databases; — displays the list of DBs on server
use db1; — log on to a DB with the name db1
show tables; — lists all tables in the current database
create user 'project'@'localhost' identified by '$sup#erP@ss1'; — creates a user (project) and sets its password to $sup#erP@ss1
grant all privileges on database_name.* to project@'localhost'; — grants full privileges to user project
flush privileges; — updates all privileges
show processlist; — view active DB connections. You can also use this command:
show status where `variable_name` = 'Threads_connected';
In your mysql console, you can view or change some variables, like:
SHOW VARIABLES LIKE 'max_error_count';
To change:
SET max_error_count=256;
In conclusion I would like to say that when you configure a DB server you must consider the projects to be located on the server. What is applicable for minor projects will be definitely not suitable for a large one. Try, experiment and always view your logs. In the next article we’ll show how to provide high availability and increase app performance by using replication between MariaDB database servers.