MySQL Integration

Xitoring is a comprehensive monitoring solution that allows you to monitor your MySQL database and view statistics and graphs in real time. With Xitoring, you can track key performance indicators. The platform also provides detailed graphs and charts to help you visualize your database performance and identify any issues before they become critical. Additionally, Xitoring offers customizable alerts and notifications, making it easier to stay on top of your MySQL database's health and performance.

Getting Access to MySQL Data

For gathering information and statistics from MySQL, you need to give Xitogent access to your database. As we do not recommend you give Xitogent your database root access, we are going to walk you through creating a user with the necessary privileges for Xitogent.

Enable MySQL Performance Schema

Xitogent will use performance_schema table to collect some of the metrics and statistics, so you need to enable performance_schema in the MySQL configuration, open the MySQL configuration file with a text editor and place the following line under the [mysqld] block to make it look like below:

[mysqld]
performance_schema=ON

After that, you need to restart the MySQL service to make the changes. on Linux you can execute the following command:

sudo systemctl restart mysqld

After the service has restarted, you can verify that performance_schema is enabled by connecting to the MySQL server and running the following command:

mysql> SHOW VARIABLES LIKE 'performance_schema';

If performance_schema is enabled, you should see output similar to the following:

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

Create a user for Xitogent

First, execute the following command to create a user on your MySQL and set a password for it:

CREATE USER 'xitoring'@'%' IDENTIFIED BY '{password}';

The command above will create a user named xitoring on your MySQL.

For MySQL versions 8 or higher you can use MySQL native password hashing method:

CREATE USER 'xitoring'@'%' IDENTIFIED WITH mysql_native_password by 'password';

Set the correct privileges

To enable the collection of metrics, Xitogent requires specifically limited privileges. For MySQL versions 5.6 and 5.7, it is recommended to grant the Xitoring user replication client and set max_user_connections using the following command:

GRANT REPLICATION CLIENT ON *.* TO 'xitoring'@'%' WITH MAX_USER_CONNECTIONS 5;

For MySQL versions 8 or higher, it is recommended to grant the Xitoring user replication client and set max_user_connections using the following command:

GRANT REPLICATION CLIENT ON *.* TO 'xitoring'@'%';
ALTER USER 'xitoring'@'%' WITH MAX_USER_CONNECTIONS 5;

Set Xitoring user the process and performance schema privilege:

GRANT PROCESS ON *.* TO 'xitoring'@'%';
GRANT SELECT ON performance_schema.* TO 'xitoring'@'%';

Please ensure that the replication user is properly verified by replacing the "{password}" placeholder with the password you created earlier. This step is essential to ensure that Xitoring's Agent can collect metrics from the MySQL database.

mysql -u xitoring --password={password} -e "show status" | \
grep Uptime && echo -e "\033[0;32mMySQL user - OK\033[0m" || \
echo -e "\033[0;31mCannot connect to MySQL\033[0m"

Enable MySQL integration on Xitogent

Now that the challenging part is behind us, we can easily configure Xitogent to collect MySQL statistics and send them for analysis. This is a straightforward process thanks to Xitogent's CLI integration setup. To begin, execute the following command:

xitogent integrate

You will see something like below:

1) Mysql
2) Nginx
3) Apache
4) Supervisor
5) Php-fpm
6) Redis
7) Keydb

You will select MySQL which is number 1 from the list of available integrations and Xitogent CLI will prompt you for MySQL host, As most users will run MySQL on the localhost you probably need to enter 127.0.0.1 as your MySQL host.

After that, you will be asked for a MySQL port, the default port for MySQL is 3306 but if you are running MySQL on other ports you need to enter the correct port number.

As the final step, you need to enter the username and password for the user you just create.

If you have done everything right you will see a message like below:

connection established
integration setup was successful and the config file updated

Now the configuration file is created in the /etc/xitogent/integrations/mysql_integration.conf and the content of the config file is something like the below:

host=127.0.0.1
port=3306
user=xitoring
password=aUniquePassword

Setup Triggers for MySQL integration

You can create triggers for the following parameters so you get incidents if any of the parameters exceed or go less than a specific value:

CPU usage
Memory Usage