How to Monitor MySQL/MariaDB Databases using Netdata on CentOS 7

[vyshnavk]

Netdata is a free open source, simple and scalable, real-time system performance and health monitoring application for Unix-like systems such as Linux, FreeBSD and MacOS. It gathers various metrics and visualizes them, allowing you to watch operations on your system. It supports various plugins for monitoring current system status, running applications, and services such as MySQL/MariaDB database server, plus lots more.

In this article, we will explain how to monitor MySQL/MariaDB  database server performance using Netdata on CentOS 7 or RHEL 7  distribution.

At the end of this article, you will be able to watch visualizations of bandwidth, queries, handlers, locks, issues, temporaries, connections, binlog, threads metrics of your MySQL/MariaDB database server from a netdata monitoring web interface.

Requirements:

• A CentOS 7 Server or RHEL 7 Server with Minimal Install.
  
• MySQL or MariaDB database server installation.
  

Step 1: Install MariaDB Database Server on CentOS 7

1. First start by adding MariaDB YUM software repository to your system.

Code: [Select]

# vim /etc/yum.repos.d/MariaDB.repo

Now add the following lines in this file.

Code: [Select]

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

2. Next, install the MariaDB package, as follows.

Code: [Select]

# yum install MariaDB-server MariaDB-client -y

3. Once you have installed MariaDB database, start the database server daemon for the time being, and enable it to start automatically at system boot, and confirm that it is up and running using following commands.

Code: [Select]

# systemctl start mariadb
# systemctl enable mariadb
# systemctl status mariadb

4. By default, the MySQL installation is unsecure and you need to secure it by running the security script which comes with the binary package. You will be asked to set a root password, set it and proceed.

Code: [Select]

# mysql_secure_installation

Once you have set the root password, enter yes/y to the rest of the questions to remove anonymous users, disallow root login remotely, remove test database and access to it, as well as reload privilege tables now.

5. To collect performance statistics from your MySQL/MariaDB  database server, netdata needs to connect to the database server. So create a database user called “netdata” to give it the the ability to connect to the database server on localhost, without a password.

Code: [Select]

# mysql -u root -p
MariaDB [(none)]> CREATE USER 'netdata'@'localhost';
MariaDB [(none)]> GRANT USAGE on *.* to 'netdata'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit

Step 2: Install Netdata to Monitor MySQL Performance

6. Luckily, we already have a one-liner script provided by the developers of netdata, for painlessly installing it from the source tree on github repository.

The kickstarter script downloads another script for detecting your Linux distro; installs the required system packages for building netdata; then downloads the latest netdata source tree; builds and installs it on your system.

This command will help you launch the kickstarter script, the all option allows for installing required packages for all netdata plugins including the ones for MySQL/MariaDB.

Code: [Select]

# bash <(curl -Ss https://my-netdata.io/kickstart.sh) all

If your not managing your system as root, you will be prompted to enter your user password for sudo command, and you will also be asked to confirm a number of functions by simply pressing [Enter].

Install Netdata on CentOS 7

7. Once the script has completed building and installing netdata, it will automatically start the netdata service, and enables it to start at system boot.

Netdata Installation Summary

8. Netdata listens on port 19999 by default, you will use this port to access the web UI. So, open the port on your system firewall.

Code: [Select]

# firewall-cmd --permanent --add-port=19999/tcp
# firewall-cmd --reload

Step 2: Configure Netdata to Monitor MySQL/MariaDB

9. The netdata configuration for MySQL/MariaDB plugin is /etc/netdata/python.d/mysql.conf, which is written in YaML format.

Code: [Select]

# vim /etc/netdata/python.d/mysql.conf

The default configuration is just enough to get you started with monitoring your MySQL/MariaDB database server. In case you have read the documentation, and made any changes to the above file, you need to restart the netdata service to effect the changes.

Code: [Select]

# systemctl restart netdata

10. Next, open a web browser and use the any of the following URL to access the netdata web UI.

Code: [Select]

http://domain_name:19999
OR
http://SERVER_IP:19999
From the netdata dashboard, search for “MySQL local” on the right hand side list of plugins, and click on it to start monitoring your MySQL/MariaDB server. You will be able to watch visualizations of bandwidth, queries, handlers, locks, as well as galera, as shown in the following screenshot.

Monitor MySQL/MariaDB Database Using Netdata

That’s all!