{"id":813,"date":"2015-05-18T17:34:09","date_gmt":"2015-05-19T02:34:09","guid":{"rendered":"http:\/\/blog.box.kr\/?p=813"},"modified":"2015-05-18T17:34:09","modified_gmt":"2015-05-19T02:34:09","slug":"scraphow-to-monitor-mysql-replication","status":"publish","type":"post","link":"https:\/\/blog.box.kr\/?p=813","title":{"rendered":"[scrap]How to Monitor MySQL Replication?"},"content":{"rendered":"

\"how<\/p>\n

Just setting up MySQL replication is not enough, you would need to periodically monitor your slaves to ensure they continue to work seamlessly. Here is a basic overview of the Slave variables to monitor and the tools that will help you monitor those with ease.<\/p>\n

Top variables to monitor on your slaves<\/h3>\n

Replication is best monitored by checking the following variables-<\/p>\n

    \n
  1. SLAVE_RUNNING:<\/strong> This is a global status variable and its value can be checked using SHOW GLOBAL STATUS like 'slave_running'<\/tt>. It can either be \u2018ON\u2019 or \u2018OFF\u2019.If slave_running<\/tt> is \u2018ON\u2019, then the slave is up and working fine, which means both the SQL thread and the IO thread are running. If either the SQL thread or the IO thread is not running then this variable would be \u2018OFF\u2019. Use SHOW SLAVE STATUS<\/tt> and try to determine whether there was an error or the slave was stopped manually. Check the last 100 lines of the slave\u2019s error log to see if that gives a clue. Look at Last_Error_Number<\/tt> and Last_Error_Message<\/tt> for specific error information and fix your slave.The following variables are a part of SHOW SLAVE STATUS<\/tt><\/li>\n
  2. SLAVE_IO_RUNNING: <\/strong>It tells us if the Slave\u2019s IO thread is able to connect to its master and is running fine. The possible values for this variable is \u2018Yes\u2019 or \u2018No\u2019 or \u2018connecting\u2019.If this variable reads \u2018NO\u2019 then you will have to check the Last_Error_Number<\/tt> and Last_Error_Message<\/tt> and fix your slave. As of MySQL 5.1.20, these columns are aliases for Last_SQL_Errno<\/tt> and Last_SQL_Error<\/tt>. Before 5.1.20, they indicate the error number and error message returned by the most recently executed statement. An error number of 0 and message of the empty string mean \u201cno error.\u201d<\/li>\n
  3. SLAVE_SQL_RUNNING: <\/strong>It tells if the Slave\u2019s SQL thread has started and is working fine. The possible values of this variable are \u2018Yes\u2019 or \u2018No\u2019.If this variable reads \u2018No\u2019, then the IO thread was caused to stop. You will have to check theLast_SQL_Errno<\/tt> and Last_SQL_Err<\/tt> for more information on the cause. An error number of 0 and message of the empty string mean \u201cno error.\u201d The Last_SQL_Error<\/tt> appears in the slave\u2019s error log.<\/li>\n
  4. SECONDS_BEHIND_MASTER: <\/strong>As the name suggests, this field tells how late your slave is. In other words, it tells the time in seconds that the Slave\u2019s SQL thread lags while processing Master\u2019s binary log. A continuous increase in this value is not a very good sign as it means that the slave is not able to catch up with its master. There is no threshold value for this variable to compare against, to determine if the value is high or low. It completely depends on your application, network speed etc.NOTE:<\/strong> Although seconds_behind_master<\/tt> is the best option available for determining slave lag available in all MySQL versions, it has been criticised for not always being accurate.MySQL 5.5 version has a status variable MASTER_HEARTBEAT_PERIOD<\/tt> which when set will send beat packages to the Slave. After the loss of a beat the Slave IO thread will disconnect and try connecting again. Various solutions for adding a \u2018heartbeat\u2019 mechanism have been proposed and patches and plugins are available for MySQL < 5.5. If you have added such \u2018heartbeat\u2019 mechanism you should monitor that as well.<\/li>\n<\/ol>\n

    What tools to use to monitor MySQL replicas?<\/h3>\n

    There are numerous tools available in the market to monitor MySQL replication. Some are command line based tools, the others GUI based. I have taken up 2 of each type and explained the benefits of each.<\/p>\n

      \n
        \n
      1. Manually:<\/strong> Execute SHOW GLOBAL STATUS like 'slave_running'<\/tt> and SHOW SLAVE STATUS<\/tt> periodically and store it in a file. Check the contents of the file periodically to see if any of your replicas have stopped replicating.
        \nOn Linux, you can have a cron job setup which executes these queries periodically and even configure to send email alert if slave_running<\/tt> value is \u2018NO\u2019.This is not a tool as such, but one of the boring ways of doing things. But if you are comfortable doing everything by yourself manually, why not?<\/li>\n
      2. Percona Toolkit<\/a>:<\/strong> Percona Toolkit comes with a bunch of very useful tools that help in monitoring as well as managing MySQL replicas –pt-heartbeat<\/a>:<\/strong> Convenient tool to monitor slave lag in real time.
        \n        pt-slave-restart<\/a>:<\/strong> Watches and restarts Slave on error.
        \n        pt-slave-find<\/a>:<\/strong> Finds replication hierarchy of the slaves.
        \n        pt-table-checksum<\/a>:<\/strong> Checks if databases on the slaves are in sync with their master.<\/li>\n<\/ol>\n<\/ol>\n

        \"Percona<\/p>\n

          \n
        1. MySQL Enterprise Monitor<\/a>:<\/strong> A \u201cVirtual DBA Assistant\u201d by Oracle is an agent based monitoring tool which has a neat web based GUI.\"MySQLSpecific to monitoring replicas, there is a \u2018Replication\u2019 tab which gives a topological view of all the Masters and their Slaves along with the output of SHOW SLAVE STATUS<\/tt> and SHOW MASTER STATUS<\/tt>.\n

          Notification alerts can be configured to reach your inbox on the event of replication failure.<\/p><\/li>\n

        2. MONyog \u2013 MySQL Monior and Advisor<\/a>:<\/strong> A \u201cMySQL DBA in a box\u201d by Webyog is an agentless GUI based tool that helps MySQL DBAs manage MySQL servers. Replication monitoring and managing include-\u2018Replication\u2019 tab that give a topological view of all the Masters and their Slaves along with the SHOW SLAVE STATUS<\/tt> and SHOW MASTER STATUS<\/tt>.\"MONyog\"MONyog\n

          Auto registering of slaves, given the Master details.<\/p>\n

          Notification alerts through emails\/SNMP traps that are sent out on the event of replication failure.<\/p><\/li>\n<\/ol>\n

          Depending on your needs, you can choose the tool of your choice. If you are interested in MONyog \u2013 MySQL Monitor and Advisor<\/a>, you can try an unrestricted copy for a month. You can write to us atsupport@webyog.com<\/a> to give us your feedback and suggestions. Cheers!<\/p>\n","protected":false},"excerpt":{"rendered":"

          Just setting up MySQL replication is not enough, you would need to periodically monitor your slaves to ensure they continue to work seamlessly. Here is a basic overview of the Slave variables to monitor and the tools that will help you monitor those with ease. Top variables to monitor on your slaves Replication is best monitored by checking the following variables- SLAVE_RUNNING: This is a global status variable and its value can be checked using SHOW GLOBAL STATUS like ‘slave_running’. It can either be \u2018ON\u2019 or \u2018OFF\u2019.If slave_running is \u2018ON\u2019, then the slave is up and working fine, which means both the SQL thread and the IO thread are running. If either the SQL thread or the IO thread is not running then this variable would be \u2018OFF\u2019. Use SHOW SLAVE STATUS and try to determine whether there was an error or the slave was stopped manually. Check the last 100 lines of the slave\u2019s error log to see if that gives a clue. Look at Last_Error_Number and Last_Error_Message for specific error information and fix your slave.The following variables are a part of SHOW SLAVE STATUS SLAVE_IO_RUNNING: It tells us if the Slave\u2019s IO thread is able to connect to […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"ngg_post_thumbnail":0,"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[12,5],"tags":[],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5q9Zn-d7","jetpack-related-posts":[{"id":801,"url":"https:\/\/blog.box.kr\/?p=801","url_meta":{"origin":813,"position":0},"title":"[scrap] MySQL Replication \uc124\uc815\uacfc \uba87 \uac00\uc9c0 \ud14c\uc2a4\ud2b8","date":"2015-05-18","format":false,"excerpt":"http:\/\/blog.hibrainapps.net\/130 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 MySQL\uc744 DB\ub85c \uc0ac\uc6a9\ud558\uba74\uc11c \uc11c\ubc84\uc758 \ubd80\ud558 \ubd84\uc0b0\uc744 \uc704\ud55c \ubc29\ubc95 \uc911 \ud558\ub098\ub85c Replication \uc744 \uc0ac\uc6a9\ud55c\ub2e4. Replication \uc740 Master \ud558\ub098\uc5d0 n\uac1c\uc758 Slave\ub85c \uc9c0\uc815\uc774 \uac00\ub2a5\ud558\ub2e4. Slave\ub294 \ub2e4\uc2dc Master \uc5ed\ud560\uc744 \ud560\uc218 \uc788\uc73c\uba70 \uc5ed\uc2dc \ub610 \ub2e4\ub978 n\uac1c\uc758 Slave\ub97c \uc9c0\uc815\ud560 \uc218 \uc788\ub2e4. \ubd80\ud558 \ubd84\uc0b0\uc758 \ud6a8\uacfc\ub294 inser,update \ub4f1 \ubcc0\uacbd\uacfc \uad00\ub828\ub41c \ubaa8\ub4e0 \uc791\uc5c5\uc740\u2026","rel":"","context":"In "DB\uad00\ub828"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":807,"url":"https:\/\/blog.box.kr\/?p=807","url_meta":{"origin":813,"position":1},"title":"[scrap] mysql – dual master replication","date":"2015-05-18","format":false,"excerpt":"http:\/\/simonshin.egloos.com\/2247518 \/etc\/my.cnf \ud30c\uc77c \ub0b4\uc6a9 Below is what needs to be added to the configuration for Server A: server-id = 1 replicate-same-server-id = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/\uc2ac\ub808\uc774\ube0c \uc11c\ubc84\uc5d0\uc11c \uc0ac\uc6a9\ub428. \uc77c\ubc18\uc801\uc73c\ub85c\ub294 \ub514\ud3f4\ud2b8 \uc124\uc815 \uac12\uc778 0\uc744 \uc0ac\uc6a9\ud558\ub294\ub370, \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/\uc774 \uac12\uc740 \uc21c\ud658 \ub9ac\ud50c\ub9ac\ucf00\uc774\uc158(circular replication)\uc5d0 \uc758\ud55c \ubb34\ud55c \ub8e8\ud504\ub97c \ubc29\uc9c0\ud569\ub2c8\ub2e4. \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/\uc774 \uac12\uc744 1\ub85c \uc124\uc815\ud55c\ub2e4\uba74, \uc2ac\ub808\uc774\ube0c\ub294 \uc790\uc2e0\uc758 \uc11c\ubc84\u2026","rel":"","context":"In "DB\uad00\ub828"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":720,"url":"https:\/\/blog.box.kr\/?p=720","url_meta":{"origin":813,"position":2},"title":"[\ud38c]Java \uc5d0\uc11c DataBase Replication Master\/Slave (write\/read) \ubd84\uae30 \ucc98\ub9ac\ud558\uae30","date":"2015-04-15","format":false,"excerpt":"http:\/\/kwon37xi.egloos.com\/ \ub300\uaddc\ubaa8 \uc11c\ube44\uc2a4 \uac1c\ubc1c\uc2dc\uc5d0 \uac00\uc7a5 \uae30\ubcf8\uc801\uc73c\ub85c \ud558\ub294 \ud29c\ub2dd\uc740 \ubc14\ub85c \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0\uc11c Write\uc640 Read DB\ub97c Replication(\ub9ac\ud50c\ub9ac\ucf00\uc774\uc158)\ud558\uace0 \uc4f0\uae30 \uc791\uc5c5\uc740 Master(Write)\ub85c \ubcf4\ub0b4\uace0 \uc77d\uae30 \uc791\uc5c5\uc740 Slave(Read)\ub85c \ubcf4\ub0b4\uc5b4 \ubd80\ud558\ub97c \ubd84\uc0b0 \uc2dc\ud0a4\ub294 \uac83\uc774\ub2e4. \ud2b9\ud788 \ub300\ubd80\ubd84\uc758 \uc11c\ube44\uc2a4\ub294 \uc77d\uae30\uac00 \uc555\ub3c4\uc801\uc73c\ub85c \ub9ce\uae30 \ub54c\ubb38\uc5d0\u00a0 Slave\ub294 \uc5ec\ub7ec \ub300\ub97c \ub450\uc5b4 \uc77d\uae30 \ubd80\ud558\ub97c \ubd84\uc0b0 \uc2dc\ud0a8\ub2e4. \uadf8\ub7f0\ub370 \ub610 \ud558\ub098 \uae30\uc5b5\ud574\uc57c \ud560 \uac83\uc774 Replication\uc740 \ube44\ub85d \uc9e7\ub354\ub77c\ub3c4\u2026","rel":"","context":"In "JAVA"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":590,"url":"https:\/\/blog.box.kr\/?p=590","url_meta":{"origin":813,"position":3},"title":"[\ud38c]Java \uc5d0\uc11c DataBase Replication Master\/Slave (write\/read) \ubd84\uae30 \ucc98\ub9ac\ud558\uae30 \ud504\ub85c\uadf8\ub798\ubc0d","date":"2015-01-30","format":false,"excerpt":"Cloud\ud658\uacbd\uc5d0\uc11c DB\ub97c \uc0ac\uc6a9\ud558\ub2e4\ubcf4\ub2c8 Disk I\/O\ub098 \ub108\ubb34 \ub290\ub824\uc11c \ub9ce\uc740 \uace0\ubbfc\uc744 \ud558\uac8c \ub410\ub2e4. \uc77c\ub2e8 \uc0dd\uac01\ub098\ub294\uac83\uc774 \ub9ce\uc774 \ub4e4 \ud558\ub294 Replication\uc744 \uc774\uc6a9\ud55c \ubd80\ud558\ubd84\uc0b0 ( Master\ub294 Insert\/Update\ub9cc, Slave\ub294 SELECT\ub9cc ) \uadf8\ub7f0\ub370 \ub9c9\uc0c1 \ud560\ub824\uba74 \uc774\uac83 \uc800\uac83 \uc190\uc774 \ub9ce\uc774 \uac04\ub2e4.. \uadf8\ub798\uc11c.. \uc0dd\uac01\ub9cc \ud558\uace0 \uc801\uc6a9\uc744 \uc548\ud588\uc5c8\ub294\ub370.. \u00a0 \uc6f9\uc11c\ud551\uc744 \ud558\ub2e4\uac00 \uc774\ub7f0 \uae00\uc744 \ubc1c\uacac.. \uc544!! \uc774\ub807\uac8c \uc88b\uc744 \uc218\uac00.. \u00a0 \uc5ed\uc2dc\u2026","rel":"","context":"In "JAVA"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":687,"url":"https:\/\/blog.box.kr\/?p=687","url_meta":{"origin":813,"position":4},"title":"[\ud38c]MariaDB \uc124\uc815 \ud30c\uc77c my.cnf","date":"2015-04-13","format":false,"excerpt":"http:\/\/www.programkr.com\/blog\/MUDM5ADMwYT2.html \u00a0 \u00a0 # MariaDB database server configuration file. # # You can copy this file to one of: # - \"\/etc\/mysql\/my.cnf\" to set global options, # - \"~\/.my.cnf\" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help\u2026","rel":"","context":"In "\uae30\uc220\uc790\ub8cc"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":653,"url":"https:\/\/blog.box.kr\/?p=653","url_meta":{"origin":813,"position":5},"title":"[\ud38c]Too many connection \ubb38\uc81c \ud574\uacb0","date":"2015-04-05","format":false,"excerpt":"http:\/\/netholic.tistory.com\/116 \u00a0 \u00a0 \uc774\ubc88\ud574 \ucd08\ubd80\ud130 \uc2dc\uc791\ub41c too many connection \ubb38\uc81c\uac00 \ud558\ub8e8\uc5d0 \ud55c\ubc88\uc529 mysql \uc744 \ubed7\uac8c \ub9cc\ub4e4\uc5c8\ub2e4. \ub418\ub294\ub300\ub85c... \uc544\ubb34\uac83\ub3c4 \ubaa8\ub978\ucc44\ub85c \ub9cc\ub4e0 \uc6b4\uc601\ud234\uc774 \uadf8 \uc6d0\uc778... \ucffc\ub9ac\ub3c4 10\ucd08 \uc774\uc0c1 \uac78\ub9ac\ub294 \uac83\ub3c4 \uc874\uc7ac\ud588\uace0..\uc778\ub371\uc2a4\ub3c4 \uc81c\ub300\ub85c \uc548\uac78\ub824\uc788\uace0.. \uadf8\ub798\uc11c \uc2ac\ub85c\uc6b0 \ucffc\ub9ac\ub97c \uc7a1\uace0 \uc778\ub371\uc2a4\ub3c4 \uc81c\ub300\ub85c \uac78\uc5b4\uc11c...\uadf8\ub7f0 \ucffc\ub9ac\ubb38\uc81c\ub97c \uc5c6\uc570\uc73c\ub85c\uc11c \ucee4\ub125\uc158 \ubb38\uc81c\ub294 \uc5c6\uc5b4\uc9c8 \uc904 \uc54c\uc558\ub2e4. \uadf8\ub7f0\ub370 \uc5ec\uc804\ud788 \ud558\ub8e8\uc5d0 \ud55c\ubc88\uc529...\uc11c\ubc84\ub97c \ub0b4\ub838\ub2e4\u2026","rel":"","context":"In "\uae30\uc220\uc790\ub8cc"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/813"}],"collection":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=813"}],"version-history":[{"count":0,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/813\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=813"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=813"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=813"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}