If you require details on how to login to OCI Mysql using mysql-shell, check tutorial "Access MySQL Instance"
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:
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
