{"id":28965,"date":"2023-08-09T16:47:53","date_gmt":"2023-08-09T16:47:53","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=28965"},"modified":"2023-08-09T16:50:07","modified_gmt":"2023-08-09T16:50:07","slug":"how-to-monitor-mysql-mariadb-using-zabbix","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/how-to-monitor-mysql-mariadb-using-zabbix\/","title":{"rendered":"How to monitor MySQL &#038; mariadb using zabbix?"},"content":{"rendered":"\n<hr class=\"wp-block-separator has-css-opacity\"\/>\n\n\n\n<p>There are 2 default template has been given by Zabbix which can be used to MySQL metrices monitoring<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MySQL by Zabbix agent<\/li>\n\n\n\n<li>MySQL by Zabbix agent 2<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Zabbix Template &#8211; MySQL by Zabbix agent<\/h3>\n\n\n\n<p>For Zabbix version: 6.0 and higher. The template is developed for monitoring DBMS MySQL and its forks.<\/p>\n\n\n\n<p>This template was tested on:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MySQL, version 5.7, 8.0<\/li>\n\n\n\n<li>Percona, version 8.0<\/li>\n\n\n\n<li>MariaDB, version 10.4<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1 &#8211; Install MySQL Server or MariaDB <\/h3>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-devopsschool-com wp-block-embed-devopsschool-com\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"9GpbXSxmap\"><a href=\"https:\/\/www.devopsschool.com\/blog\/how-to-install-mysql-and-maridadb-in-centos-rhel\/\">How to install MySql and MaridaDb in Centos\/RHEL?<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;How to install MySql and MaridaDb in Centos\/RHEL?&#8221; &#8212; DevOpsSchool.com\" src=\"https:\/\/www.devopsschool.com\/blog\/how-to-install-mysql-and-maridadb-in-centos-rhel\/embed\/#?secret=fbKelkaGhJ#?secret=9GpbXSxmap\" data-secret=\"9GpbXSxmap\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-devopsschool-com wp-block-embed-devopsschool-com\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"L2RWgS6iRz\"><a href=\"https:\/\/www.devopsschool.com\/blog\/how-to-install-mysql-server-and-client-on-ubuntu\/\">How To Install MySQL server and client on Ubuntu?<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;How To Install MySQL server and client on Ubuntu?&#8221; &#8212; DevOpsSchool.com\" src=\"https:\/\/www.devopsschool.com\/blog\/how-to-install-mysql-server-and-client-on-ubuntu\/embed\/#?secret=SRGINECHT5#?secret=L2RWgS6iRz\" data-secret=\"L2RWgS6iRz\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2 &#8211; Install Zabbix agent<\/h3>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-devopsschool-com wp-block-embed-devopsschool-com\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"hpD5jKSC8P\"><a href=\"https:\/\/www.devopsschool.com\/blog\/how-to-install-configure-zabbix-agent\/\">How to install &#038; Configure Zabbix Agent?<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;How to install &#038; Configure Zabbix Agent?&#8221; &#8212; DevOpsSchool.com\" src=\"https:\/\/www.devopsschool.com\/blog\/how-to-install-configure-zabbix-agent\/embed\/#?secret=zFGalVwEVU#?secret=hpD5jKSC8P\" data-secret=\"hpD5jKSC8P\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Step 4 &#8211; Work on template_db_mysql.conf<\/h3>\n\n\n\n<hr class=\"wp-block-separator has-css-opacity\"\/>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">Copy template_db_mysql.conf into the folder with Zabbix agent configuration (\/etc\/zabbix\/zabbix_agentd.d\/ by <span class=\"hljs-keyword\">default<\/span>). Don<span class=\"hljs-string\">'t forget to restart Zabbix agent.\n- https:\/\/gist.github.com\/devops-school\/155ad219966fdca8684e394a6df81681\n- https:\/\/git.zabbix.com\/projects\/ZBX\/repos\/zabbix\/browse\/templates\/db\/mysql_agent\n\nOR\n\n# UBUNTU\n$ sudo apt install mlocate\n$ locate userparam\n$ cp \/usr\/share\/doc\/zabbix-agent\/userparameter_mysql.conf \/etc\/zabbix\/zabbix_agentd.d\/template_db_mysql.conf\n\n#Centos\/RHEL\n$ yum install mlocate\n$ updatedb\n$ locate userparam\n$ cp \/usr\/share\/doc\/zabbix-agent-6.0.0\/userparameter_mysql.conf \/etc\/zabbix\/zabbix_agentd.d\/template_db_mysql.conf\n\n\n<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"286\" src=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/zabbix-mysql-2-1024x286.jpg\" alt=\"\" class=\"wp-image-29001\" srcset=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/zabbix-mysql-2-1024x286.jpg 1024w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/zabbix-mysql-2-300x84.jpg 300w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/zabbix-mysql-2-768x215.jpg 768w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/zabbix-mysql-2.jpg 1242w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-css-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Step 5 &#8211; Create a MySQL user for monitoring ( at your discretion):<\/h3>\n\n\n\n<hr class=\"wp-block-separator has-css-opacity\"\/>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">locate my.cnf\nvi \/etc\/my.cnf\n<\/code><\/span><\/pre>\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"644\" height=\"363\" src=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-84.png\" alt=\"\" class=\"wp-image-29004\" srcset=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-84.png 644w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-84-300x169.png 300w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-84-355x199.png 355w\" sizes=\"auto, (max-width: 644px) 100vw, 644px\" \/><\/figure>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">\n$ systemctl stop mysql\n$ systemctl start mysql\nor\n$ service stop mysql\n$ service start mysql<\/code><\/span><\/pre>\n\n\n<hr class=\"wp-block-separator has-css-opacity\"\/>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">$ mysql -u root -p\nCREATE USER <span class=\"hljs-string\">'zbx_monitor'<\/span>@<span class=\"hljs-string\">'%'<\/span> IDENTIFIED BY <span class=\"hljs-string\">'DevOpsSchool$123'<\/span>;\nGRANT ALL PRIVILEGES ON *.* TO <span class=\"hljs-string\">'zbx_monitor'<\/span>@<span class=\"hljs-string\">'%'<\/span> IDENTIFIED BY <span class=\"hljs-string\">'DevOpsSchool$123'<\/span> WITH GRANT OPTION;\nFLUSH PRIVILEGES;\nquit;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-css-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Step 6 &#8211; Create &#8220;.my.cnf&#8221; in the home directory of Zabbix agent<\/h3>\n\n\n\n<hr class=\"wp-block-separator has-css-opacity\"\/>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">$ mkdir \/<span class=\"hljs-keyword\">var<\/span>\/lib\/zabbix\n$ vi \/<span class=\"hljs-keyword\">var<\/span>\/lib\/zabbix\/.my.cnf\n\nCreate <span class=\"hljs-string\">\".my.cnf\"<\/span> <span class=\"hljs-keyword\">in<\/span> the home directory <span class=\"hljs-keyword\">of<\/span> Zabbix agent <span class=\"hljs-keyword\">for<\/span> Linux (<span class=\"hljs-regexp\">\/var\/<\/span>lib\/zabbix by <span class=\"hljs-keyword\">default<\/span> ) or my.cnf <span class=\"hljs-keyword\">in<\/span> c:\\ <span class=\"hljs-keyword\">for<\/span> Windows. The file must have three strings:\n\n&#91;client]\nuser=<span class=\"hljs-string\">'zbx_monitor'<\/span>\npassword=<span class=\"hljs-string\">'DevOpsSchool$123'<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator has-css-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Step 7 &#8211; Restart Zabbix Agent<\/h3>\n\n\n\n<hr class=\"wp-block-separator has-css-opacity\"\/>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">$ service zabbix-agent start\n$ systemctl enable zabbix-agent\n$ service zabbix-agent restart<\/code><\/span><\/pre>\n\n\n<hr class=\"wp-block-separator has-css-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Step &#8211; 8 Add Macros to mysql host<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>In the&nbsp;<em>Macros<\/em>&nbsp;tab, switch to&nbsp;<em>Inherited and host macros<\/em>, look for the following macros and click on&nbsp;<em>Change<\/em>&nbsp;next to the macro value to update it:\n<ul class=\"wp-block-list\">\n<li>{$MYSQL.DSN} &#8211; set the data source of the MySQL server (the&nbsp;<a href=\"https:\/\/www.zabbix.com\/documentation\/current\/en\/manual\/appendix\/config\/zabbix_agent2_plugins\/mysql_plugin#parameters\" target=\"_blank\" rel=\"noopener\">connection string of a named session<\/a>&nbsp;from the MySQL Zabbix agent 2 plugin configuration file). This guide uses the default data source &#8220;tcp:\/\/localhost:3306&#8221; for monitoring a MySQL server that is installed on the same machine as Zabbix server and Zabbix agent 2.<\/li>\n\n\n\n<li>{$MYSQL.PASSWORD} &#8211; set the password of the previously&nbsp;<a href=\"https:\/\/www.zabbix.com\/documentation\/current\/en\/manual\/guides\/monitor_mysql#creating-a-mysql-user\" target=\"_blank\" rel=\"noopener\">created MySQL user<\/a>&nbsp;&#8220;zbx_monitor&#8221;.<\/li>\n\n\n\n<li>{$MYSQL.USER} &#8211; set the name of the previously&nbsp;<a href=\"https:\/\/www.zabbix.com\/documentation\/current\/en\/manual\/guides\/monitor_mysql#creating-a-mysql-user\" target=\"_blank\" rel=\"noopener\">created MySQL user<\/a>&nbsp;&#8220;zbx_monitor&#8221;.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"520\" src=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-284-1024x520.png\" alt=\"\" class=\"wp-image-38065\" srcset=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-284-1024x520.png 1024w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-284-300x152.png 300w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-284-768x390.png 768w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-284-1536x780.png 1536w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-284.png 1626w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Add MySQL by Zabbix agent 2 to MySql HOST<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"654\" src=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-285-1024x654.png\" alt=\"\" class=\"wp-image-38066\" srcset=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-285-1024x654.png 1024w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-285-300x191.png 300w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-285-768x490.png 768w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-285.png 1161w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"457\" src=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/zabbix-mysql-3-1024x457.jpg\" alt=\"\" class=\"wp-image-29002\" srcset=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/zabbix-mysql-3-1024x457.jpg 1024w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/zabbix-mysql-3-300x134.jpg 300w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/zabbix-mysql-3-768x343.jpg 768w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/zabbix-mysql-3-1536x685.jpg 1536w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/zabbix-mysql-3.jpg 1879w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Step 8 &#8211; Validate MySql Data in Zabbix<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"660\" src=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-286-1024x660.png\" alt=\"\" class=\"wp-image-38067\" srcset=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-286-1024x660.png 1024w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-286-300x193.png 300w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-286-768x495.png 768w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2023\/08\/image-286.png 1300w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Reference<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>https:\/\/www.zabbix.com\/documentation\/current\/en\/manual\/guides\/monitor_mysql<\/li>\n\n\n\n<li>https:\/\/git.zabbix.com\/projects\/ZBX\/repos\/zabbix\/browse\/templates\/db\/mysql_agent?at=release\/6.4<\/li>\n\n\n\n<li>https:\/\/git.zabbix.com\/projects\/ZBX\/repos\/zabbix\/browse\/templates\/db\/mysql_agent2?at=release%2F6.4<\/li>\n\n\n\n<li>https:\/\/git.zabbix.com\/projects\/ZBX\/repos\/zabbix\/browse\/templates\/db\/mysql_agent2?at=release\/6.4<\/li>\n\n\n\n<li>https:\/\/www.zabbix.com\/integrations\/mysql#mysql_agent2<\/li>\n\n\n\n<li>https:\/\/www.zabbix.com\/integrations\/mysql#mysql_agent<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>There are 2 default template has been given by Zabbix which can be used to MySQL metrices monitoring Zabbix Template &#8211; MySQL by Zabbix agent For Zabbix version: 6.0 and&#8230; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[2],"tags":[],"class_list":["post-28965","post","type-post","status-publish","format-standard","hentry","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/28965","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=28965"}],"version-history":[{"count":12,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/28965\/revisions"}],"predecessor-version":[{"id":38070,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/28965\/revisions\/38070"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=28965"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=28965"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=28965"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}