{"id":23890,"date":"2021-09-26T17:40:52","date_gmt":"2021-09-26T17:40:52","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=23890"},"modified":"2022-11-28T16:05:26","modified_gmt":"2022-11-28T16:05:26","slug":"install-and-configure-prometheus-mysql-exporter","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/install-and-configure-prometheus-mysql-exporter\/","title":{"rendered":"Install and Configure Prometheus MySQL Exporter"},"content":{"rendered":"\n

Step 1 – Add Prometheus system user and group:<\/h2>\n\n\n
$ sudo groupadd --system prometheus\n$ sudo useradd -s \/sbin\/nologin --system -g prometheus prometheus\n\n# This user will manage the exporter service.<\/span>\n\n<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2 – Download and install Prometheus MySQL Exporter<\/h2>\n\n\n\n
This should be done on MySQL \/ MariaDB servers, both slaves and master servers. You may need to check Prometheus MySQL exporter releases page for the latest release, then export the latest version.<\/p>\n\n\n\n
    \n
  • https:\/\/github.com\/prometheus\/mysqld_exporter\/releases<\/li>\n<\/ul>\n\n\n
    $ curl -s https:\/\/api.github.com\/repos\/prometheus\/mysqld_exporter\/releases\/latest   | grep browser_download_url   | grep linux-amd64 | cut -d '\"' -f 4   | wget -qi -<\/span>\n$ tar xvf mysqld_exporter*.tar.gz\n$ sudo mv  mysqld_exporter-*.linux-amd64\/mysqld_exporter \/usr\/local\/bin\/\n$ sudo chmod +x \/usr\/local\/bin\/mysqld_exporter\n\n# Confirm installation by checking version of mysqld_exporter<\/span>\n$ mysqld_exporter  --version<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 3 – Create Prometheus exporter database user<\/h2>\n\n\n
    $ mysql -u root -p\n\nThe user should have PROCESS, SELECT, REPLICATION CLIENT grants:\n\nmysql> CREATE USER 'mysqld_exporter'<\/span>@'localhost'<\/span> IDENTIFIED BY 'StrongPassword'<\/span>;\nmysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'<\/span>@'localhost'<\/span>;\nmysql> FLUSH PRIVILEGES;\nmysql> EXIT<\/span>\n\n# If you have a Master-Slave database architecture, create user on the master servers only.<\/span>\n# WITH MAX_USER_CONNECTIONS 2 is used to set a max connection limit for the user to avoid overloading the server with monitoring scrapes under heavy load.<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 4 – Configure database credentials<\/h2>\n\n\n
    # Create database credentials file:<\/span>\n$ sudo vim \/etc\/.mysqld_exporter.cnf\n\n# Add correct username and password for user create<\/span>\n\n[client]\nuser=mysqld_exporter\npassword=StrongPassword\n\n# Set ownership permissions:<\/span>\n$ sudo chown root:prometheus \/etc\/.mysqld_exporter.cnf<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 5 – Create systemd unit file ( For Systemd systems )<\/h2>\n\n\n\n
    This is for systemd servers, for SysV init system, use Prometheus MySQL exporter init script for SysV init system<\/p>\n\n\n\n
    Create a new service file:<\/p>\n\n\n
    sudo vi \/etc\/systemd\/system\/mysql_exporter.service\n\nAdd the following content\n\n[Unit]\nDescription=Prometheus MySQL Exporter\nAfter=network.target\nUser=prometheus\nGroup=prometheus\n\n[Service]\nType=simple\nRestart=always\nExecStart=\/usr\/<\/span>local\/bin\/mysqld_exporter \\\n--config.my-cnf \/etc\/.mysqld_exporter.cnf \\\n--collect.global_status \\\n--collect.info_schema.innodb_metrics \\\n--collect.auto_increment.columns \\\n--collect.info_schema.processlist \\\n--collect.binlog_size \\\n--collect.info_schema.tablestats \\\n--collect.global_variables \\\n--collect.info_schema.query_response_time \\\n--collect.info_schema.userstats \\\n--collect.info_schema.tables \\\n--collect.perf_schema.tablelocks \\\n--collect.perf_schema.file_events \\\n--collect.perf_schema.eventswaits \\\n--collect.perf_schema.indexiowaits \\\n--collect.perf_schema.tableiowaits \\\n--collect.slave_status \\\n--web.listen-address=0.0<\/span>.0<\/span>.0<\/span>:9104<\/span>\n\n[Install]\nWantedBy=multi-user.target\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    If your server has a public and private network, you may need to replace 0.0.0.0:9104 with private IP, e.g. 192.168.4.5:9104<\/p>\n\n\n\n
    Step 6 – When done, reload systemd and start mysql_exporter service<\/h2>\n\n\n
    $ sudo systemctl daemon-reload\n$ sudo systemctl enable mysql_exporter\n$ sudo systemctl start mysql_exporter<\/code><\/span><\/pre>\n\n\n
    Step 7 – Configure MySQL endpoint to be scraped by Prometheus Server<\/h2>\n\n\n
    Login<\/span> to<\/span> your<\/span> Prometheus<\/span> server<\/span> and<\/span> Configure<\/span> endpoint<\/span> to<\/span> scrape<\/span>. Below<\/span> is<\/span> an<\/span> example<\/span> for<\/span> two<\/span> MySQL<\/span> database<\/span> servers<\/span>.\n\nscrape_configs<\/span>:\n  -<\/span> job_name<\/span>: server1_db<\/span>\n    static_configs<\/span>:\n      -<\/span> targets<\/span>: ['10.10.1.10:9104'<\/span>]<\/span>\n        labels<\/span>:\n          alias<\/span>: db1<\/span>\n\n  -<\/span> job_name<\/span>: server2_db<\/span>\n    static_configs<\/span>:\n      -<\/span> targets<\/span>: ['10.10.1.11:9104'<\/span>]<\/span>\n        labels<\/span>:\n          alias<\/span>: db2<\/span>\n\nThe<\/span> first<\/span> server<\/span> has<\/span> the<\/span> IP<\/span> address<\/span> 10.10<\/span>.1<\/span>.10<\/span> and<\/span> the<\/span> second<\/span> one<\/span> is<\/span> 10.10<\/span>.1<\/span>.11<\/span>. Add<\/span> other<\/span> targets<\/span> using<\/span> the<\/span> similar<\/span> format<\/span>. Job<\/span> names<\/span> should<\/span> be<\/span> unique<\/span> for<\/span> each<\/span> target<\/span>.\n<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
    Create \/ Import Grafana Dashboard for MySQL Prometheus exporter<\/h2>\n\n\n
    Let\u2019s download MySQL_Overview dashboard which has a good overview of database performance.\n\n$ mkdir ~\/grafana-dashboards\n$ cd ~\/grafana-dashboards\n$ wget https:\/\/raw.githubusercontent.com\/percona\/grafana-dashboards\/master\/dashboards\/MySQL_Overview.json<\/span>\n\nUpload Prometheus MySQL dashboard(s) to grafana\nGo to Dashboards > Import > Upload .json file \nLocate the directory with dashboard file and<\/span> import\nMetrics collected should start showing.\n\nYou need to restart Grafana server to import these dashboards.\n\nsudo systemctl restart grafana-server\nsudo service grafana-server restart\nYou can then start using the dashboards on Grafana. I\u2019ll do<\/span> a guide for<\/span> how to Monitor Linux server with Prometheus, for<\/span> OS metrics, before then, check similar guides below:\n\nHow to monitor Linux systems with Grafana, telegraf, and<\/span> InfluxDB.\nMonitor Linux Server Performance with Prometheus and<\/span> Grafana in 5<\/span> minutes\nMonitor Apache Web Server with Prometheus and<\/span> Grafana in 5<\/span> minutes\nYour support is our everlasting motiv<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Ubuntu 18.04 & CentOS 7<\/h2>\n\n\n\n