> For the complete documentation index, see [llms.txt](https://isaac-exe.gitbook.io/various-tutorials/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://isaac-exe.gitbook.io/various-tutorials/tutorials/3.-mysql/3.5-monitoring-db-instances/monitoring-tools/3.-mysql-prometheus-exporter.md). # 3. MySQL Prometheus Exporter Setup `Prometheus must have implemented a MySQL Exporter in order to fetch the metrics from the MySQL systems.` ### 3.1 Install MySQL Prometheus Exporter **`3.1.1`**` ``Download the latest version of MySQL Exporter from their official repository:` {% embed url="" %} ``` root@deploymentmachine:/home# curl -s https://github.com/prometheus/mysqld_exporter/releases | grep latest
Latest release Latest release root@deploymentmachine:/home# ``` *`... better use github endoipoints...`* ``` root@deploymentmachine:/home# curl -s https://api.github.com/repos/prometheus/mysqld_exporter/releases/latest |\ grep browser_download_url |\ grep linux-amd64.tar.gz |\ awk '{gsub(/"/, "", $2); print $2}' | wget -qi - ``` ``` root@deploymentmachine:/home# ls -ltr my* -rw-r--r-- 1 root root 7121565 Jul 29 2019 mysqld_exporter-0.12.1.linux-amd64.tar.gz ``` **`3.2`**` ``... and untar for extracting files:` ``` root@deploymentmachine:/home# tar xvf mysqld_exporter-0.12.1.linux-amd64.tar.gz mysqld_exporter-0.12.1.linux-amd64/ mysqld_exporter-0.12.1.linux-amd64/NOTICE mysqld_exporter-0.12.1.linux-amd64/mysqld_exporter mysqld_exporter-0.12.1.linux-amd64/LICENSE root@deploymentmachine:/home# root@deploymentmachine:/home# root@deploymentmachine:/home# ls my* mysqld_exporter-0.12.1.linux-amd64.tar.gz mysqld_exporter-0.12.1.linux-amd64: LICENSE NOTICE mysqld_exporter ``` **`3.3`**` ``Move mysqld_exporter under /usr/local/bin, and make it executable:` ``` root@deploymentmachine:/home# mv my*.linux-amd64/mysqld_exporter /usr/local/bin/ root@deploymentmachine:/home# chmod +x /usr/local/bin/mysqld_exporter ``` ### **3.2 Configure MySQL Prometheus Exporter** #### **`3.2.1`**` ``Create Exporter user to access databases` > *`If you require details on how to login to OCI Mysql using mysql-shell, check tutorial`* [*`"Access MySQL Instance"`*](/various-tutorials/tutorials/3.-mysql/3.3-access-mysql-instance.md) `We have deployed 3 MySQL systems with following private IPs: 10.0.0.28, 10.0.0.30, 10.0.0.32` `We need to create an`` `**`Exporter user`**` ``on every MySQL data, and grant it permissions:` `a) On MySQL system 10.0.0.28 (log into the system using mysqlsh)` ``` root@deploymentmachine:~# mysqlsh usertest@10.0.0.28:3306 Please provide the password for 'usertest@10.0.0.28:3306': *********** MySQL 10.0.0.28:3306 ssl JS > MySQL 10.0.0.28:3306 ssl JS > \sql Switching to SQL mode... Commands end with ; MySQL 10.0.0.28:3306 ssl SQL > MySQL 10.0.0.28:3306 ssl SQL > CREATE USER 'user_exporter'@'%' IDENTIFIED BY 'Passw0rdStr0nk' WITH MAX_USER_CONNECTIONS 2; Query OK, 0 rows affected (0.0201 sec) MySQL 10.0.0.28:3306 ssl SQL > GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'user_exporter'@'%'; Query OK, 0 rows affected (0.0034 sec) MySQL 10.0.0.28:3306 ssl SQL > MySQL 10.0.0.28:3306 ssl SQL > \exit Bye! ``` `b) On MySQL system 10.0.0.30 (log into the system using mysqlsh)` ``` root@deploymentmachine:~# mysqlsh usertest@10.0.0.30:3306 Please provide the password for 'usertest@10.0.0.30:3306': *********** MySQL 10.0.0.30:3306 ssl JS > MySQL 10.0.0.30:3306 ssl JS > \sql Switching to SQL mode... Commands end with ; MySQL 10.0.0.30:3306 ssl SQL > MySQL 10.0.0.30:3306 ssl SQL > CREATE USER 'user_exporter'@'%' IDENTIFIED BY 'Passw0rdStr0nk' WITH MAX_USER_CONNECTIONS 2; Query OK, 0 rows affected (0.0201 sec) MySQL 10.0.0.30:3306 ssl SQL > MySQL 10.0.0.30:3306 ssl SQL > GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'user_exporter'@'%'; Query OK, 0 rows affected (0.0034 sec) MySQL 10.0.0.30:3306 ssl SQL > MySQL 10.0.0.30:3306 ssl SQL > \exit Bye! ``` `c) On MySQL system 10.0.0.32 (log into the system using mysqlsh)` ``` root@deploymentmachine:~# mysqlsh usertest@10.0.0.32:3306 Please provide the password for 'usertest@10.0.0.32:3306': *********** MySQL 10.0.0.32:3306 ssl JS > MySQL 10.0.0.32:3306 ssl JS > \sql Switching to SQL mode... Commands end with ; MySQL 10.0.0.32:3306 ssl SQL > MySQL 10.0.0.32:3306 ssl SQL > CREATE USER 'user_exporter'@'%' IDENTIFIED BY 'Passw0rdStr0nk' WITH MAX_USER_CONNECTIONS 2; Query OK, 0 rows affected (0.0201 sec) MySQL 10.0.0.32:3306 ssl SQL > MySQL 10.0.0.32:3306 ssl SQL > GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'user_exporter'@'%'; Query OK, 0 rows affected (0.0034 sec) MySQL 10.0.0.32:3306 ssl SQL > MySQL 10.0.0.32:3306 ssl SQL > \exit Bye! ``` #### **`3.2.2`**` ``Configure Exporter files on the host where Prometheus is installed` `Since we have 3 hosts, each host needs its own configuration file for an Exporter service. (this seems to be a normal behavior for MySQL Exporter )` `For the configuration files (/etc/.mysqld_exporter.cnf, for instance), you need to provide same MySQL user (along with its password) that you have provided at step 3.2.1. Also, the host is the private IP of the database:` ``` root@deploymentmachine:/home# more /etc/.mysqld_exporter.cnf [client] user=user_exporter password=Passw0rdStr0nk host=10.0.0.28 root@deploymentmachine:/home# more /etc/.mysqld_exporter_1.cnf [client] user=user_exporter password=Passw0rdStr0nk host=10.0.0.30 root@deploymentmachine:/home# more /etc/.mysqld_exporter_2.cnf [client] user=user_exporter password=Passw0rdStr0nk host=10.0.0.32 root@deploymentmachine:/home# ``` `Once you have created all three configuration files, change the ownership:` ``` root@deploymentmachine:/home# chown root:prometheus /etc/.mysqld_exporter.cnf root@deploymentmachine:/home# chown root:prometheus /etc/.mysqld_exporter_1.cnf root@deploymentmachine:/home# chown root:prometheus /etc/.mysqld_exporter_2.cnf ``` #### **`3.2.3. Configure Systemd for Exporter`** `At this step, as you have guessed it, we need three different configuration files for Systemd. Pay attention to ports. The port for Exporter is 9104. Since we need 3 different Exporter services, we will provide three different ports in every mysql_exporter.service file: 9104, 9105, 9106` * `Systemd file that will use`` `**`/etc/.mysqld_exporter.cnf && port 9104`** ``` root@deploymentmachine:/home# more /etc/systemd/system/mysql_exporter.service [Unit] Description=Prometheus MySQL Exporter After=network.target User=prometheus Group=prometheus [Service] Type=simple Restart=always ExecStart=/usr/local/bin/mysqld_exporter \ --config.my-cnf /etc/.mysqld_exporter.cnf \ --collect.global_status \ --collect.info_schema.innodb_metrics \ --collect.auto_increment.columns \ --collect.info_schema.processlist \ --collect.binlog_size \ --collect.info_schema.tablestats \ --collect.global_variables \ --collect.info_schema.query_response_time \ --collect.info_schema.userstats \ --collect.info_schema.tables \ --collect.perf_schema.tablelocks \ --collect.perf_schema.file_events \ --collect.perf_schema.eventswaits \ --collect.perf_schema.indexiowaits \ --collect.perf_schema.tableiowaits \ --collect.slave_status \ --web.listen-address=0.0.0.0:9104 [Install] WantedBy=multi-user.target ``` * `Systemd file that will use`` `**`/etc/.mysqld_exporter_1.cnf && port 9105`** ``` root@deploymentmachine:/home# more /etc/systemd/system/mysql_exporter_1.service [Unit] Description=Prometheus MySQL Exporter After=network.target User=prometheus Group=prometheus [Service] Type=simple Restart=always ExecStart=/usr/local/bin/mysqld_exporter \ --config.my-cnf /etc/.mysqld_exporter_1.cnf \ --collect.global_status \ --collect.info_schema.innodb_metrics \ --collect.auto_increment.columns \ --collect.info_schema.processlist \ --collect.binlog_size \ --collect.info_schema.tablestats \ --collect.global_variables \ --collect.info_schema.query_response_time \ --collect.info_schema.userstats \ --collect.info_schema.tables \ --collect.perf_schema.tablelocks \ --collect.perf_schema.file_events \ --collect.perf_schema.eventswaits \ --collect.perf_schema.indexiowaits \ --collect.perf_schema.tableiowaits \ --collect.slave_status \ --web.listen-address=0.0.0.0:9105 [Install] WantedBy=multi-user.target ``` * `Systemd file that will use`` `**`/etc/.mysqld_exporter_2.cnf && port 9106`** ``` root@deploymentmachine:/home# more /etc/systemd/system/mysql_exporter_2.service [Unit] Description=Prometheus MySQL Exporter After=network.target User=prometheus Group=prometheus [Service] Type=simple Restart=always ExecStart=/usr/local/bin/mysqld_exporter \ --config.my-cnf /etc/.mysqld_exporter_2.cnf \ --collect.global_status \ --collect.info_schema.innodb_metrics \ --collect.auto_increment.columns \ --collect.info_schema.processlist \ --collect.binlog_size \ --collect.info_schema.tablestats \ --collect.global_variables \ --collect.info_schema.query_response_time \ --collect.info_schema.userstats \ --collect.info_schema.tables \ --collect.perf_schema.tablelocks \ --collect.perf_schema.file_events \ --collect.perf_schema.eventswaits \ --collect.perf_schema.indexiowaits \ --collect.perf_schema.tableiowaits \ --collect.slave_status \ --web.listen-address=0.0.0.0:9106 [Install] WantedBy=multi-user.target ``` ### 3.3 Before loading and enabling Systemd Exporter **`This has two parts - networking changes and Prometheus changes`** #### **`3.3.1 Networking changes`** `Since we are using new ports, we need to make a few changes:` **`3.3.1.1 In the Cloud`** `Add following ingress rules for ports`` `**`9104, 9105, respectively 9106:`** ![](/files/-MSZ7cI7iv66tB8S7yCM) ![](/files/-MSZ7efWzt6YQz-jEILM) ![](/files/-MSZ81V1wJ4jqG3npMV8) ![](/files/-MSZ8HEe2wsh4NJ24UO7) ![](/files/-MSZ8R0aLQoIPbJIvvYv) `... and the final result` ![](/files/-MSZ8UQyOS4kGM6qw_IN) **`3.3.1.2 On the host`** `Make sure you open the ports for listening:` ``` root@deploymentmachine:/home# firewall-cmd --add-port=9104/tcp --permanent success root@deploymentmachine:/home# firewall-cmd --add-port=9105/tcp --permanent success root@deploymentmachine:/home# firewall-cmd --add-port=9106/tcp --permanent success root@deploymentmachine:/home# firewall-cmd --reload success root@deploymentmachine:/home# ``` #### **`3.3.1 Prometheus changes`** `Add the following changes for`` `**`scrape_configs field`**` ``in the`` `**`/etc/prometheus/prometheus.yml`**` ``file:` ``` scrape_configs: # The job name is added as a label `job=` to any timeseries scraped from this config. - job_name: 'mysql_server1' # metrics_path defaults to '/metrics' # scheme defaults to 'http'. static_configs: - targets: ['localhost:9104'] - job_name: 'mysql_server2' static_configs: - targets: ['localhost:9105'] - job_name: 'mysql_server3' static_configs: - targets: ['localhost:9106'] - job_name: 'prometheus' static_configs: - targets: ['localhost:9090'] ``` `Load changes and restart prometheus service:` ``` root@deploymentmachine:/home# systemctl daemon-reload root@deploymentmachine:/home# systemctl restart prometheus ``` `Now we are ready to start the three Exporter services` ### 3.3 Start Exporter services and perform checking: ``` root@deploymentmachine:/home# systemctl daemon-reload root@deploymentmachine:/home# systemctl enable mysql_exporter.service root@deploymentmachine:/home# systemctl enable mysql_exporter_1.service root@deploymentmachine:/home# systemctl enable mysql_exporter_2.service root@deploymentmachine:/home# systemctl start mysql_exporter.service root@deploymentmachine:/home# systemctl start mysql_exporter_1.service root@deploymentmachine:/home# systemctl start mysql_exporter_2.service ``` `Check if MySQL Exporter is listening on the three ports:` ``` root@deploymentmachine:/home# lsof -i :9104 | grep -i listen COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld_ex 20292 root 3u IPv6 234242 0t0 TCP *:9104 (LISTEN) root@deploymentmachine:/home# lsof -i :9105 | grep -i listen COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld_ex 20308 root 3u IPv6 234288 0t0 TCP *:9105 (LISTEN) root@deploymentmachine:/home# lsof -i :9106 | grep -i listen COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld_ex 20752 root 3u IPv6 239749 0t0 TCP *:9106 (LISTEN) ``` `Check in browser if mysql metric names are showing up in Prometheus:` ![](/files/-MSZDGOypqNOFwk4UPAt)