{"id":2605,"date":"2011-07-29T04:33:00","date_gmt":"2011-07-29T04:33:00","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2605"},"modified":"2024-02-01T07:21:08","modified_gmt":"2024-02-01T07:21:08","slug":"mysql-binary-logs-generated-per-hour-growth-estimate","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/mysql-binary-logs-generated-per-hour-growth-estimate","title":{"rendered":"MySQL Binary logs Generated Per Hour | Growth Estimate"},"content":{"rendered":"\n<p>The MySQL binary log is a set of log files that contain information about data modifications made to a MySQL server instance. At times we need to understand the disk usage by binary logs to know how much data is being written and probably growth estimation.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How many binary logs do you generate per hour?<\/h2>\n\n\n\n<p>Following command (shell script) will provide you with the hourly binlogs generated in MB. Note that the binary log name needs to be updated, I have used mysql-bin.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Binary log growth per hour<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>find . -name \"mysql-bin.*\" -exec ls -lt --time-style=+%F {} \\; | awk 'BEGIN{FS=\" \"; print \"Date\\t\\t Files\\t Total \\t\\t PerHour\"} NR!=1 {a&#91;$6]++;sum&#91;$6]=sum&#91;$6]+$5}END{for (i in a) printf(\"%s %10.0f %10.2f MB %10.2f MB\\n\", i, a&#91;i], sum&#91;i]\/1048576, sum&#91;i]\/24\/1048576)} '<\/code><\/pre>\n\n\n\n<p><\/p>\n\n\n\n<p>Assuming you have to collect the binary log growth from multiple servers we can use following script to fetch the data from all of them together. That said, note that we have assumed passwordless authentication for shipping the script to all the servers.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Binary log growth estimate automated script<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>\nfor srvr in server1 server2; do\n# get binlog dir\nBINLOGDIR=$(dirname $(db_connect $srvr --silent -N --raw -e \"select @@log_bin_basename\"));\n# get binlog name\nBINLOGNAME=$(basename $(db_connect $srvr --silent -N --raw -e \"select @@log_bin_basename\"));\necho $srvr; echo \"-------------------\";\necho \"Binlog Dir: $BINLOGDIR - Binlog Basename: $BINLOGNAME\";\n echo \"-------------------\";\n# ship the script to respective server\nscp -q binlog_size.sh $srvr:\/tmp\/;\n# execute the script\necho \"$srvr: sh \/tmp\/binlog_size.sh $BINLOGDIR\/ $BINLOGNAME\";\nssh -q -o \"StrictHostKeyChecking no\" $srvr \"sh \/tmp\/binlog_size.sh $BINLOGDIR\/ $BINLOGNAME\";\necho \"-------------------\"; echo;\ndone\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"The MySQL binary log is a set of log files that contain information about data modifications made to a MySQL server instance. At times we need to understand the disk&hellip;\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[869,8,378],"tags":[898,900,896,895,899],"class_list":{"0":"post-2605","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mariadb","7":"category-mysql","8":"category-mysql-scripts-mysql","9":"tag-binary-log-growth-estimate","10":"tag-binary-logs-per-hour","11":"tag-hourly-binlog","12":"tag-mysql-binary-logs-generated-per-hour","13":"tag-mysql-growth-estimate"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2605","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2605"}],"version-history":[{"count":4,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2605\/revisions"}],"predecessor-version":[{"id":2891,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2605\/revisions\/2891"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2605"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2605"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2605"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}