{"id":1862,"date":"2021-05-05T01:21:16","date_gmt":"2021-05-04T23:21:16","guid":{"rendered":"https:\/\/itsimple.info\/?p=1862"},"modified":"2021-06-08T13:37:09","modified_gmt":"2021-06-08T11:37:09","slug":"centos-disk-space-finished-due-to-mysql-database-table-growth","status":"publish","type":"post","link":"https:\/\/itsimple.info\/?p=1862","title":{"rendered":"CentOS Disk Space Finished Due to MYSQL Database table growth"},"content":{"rendered":"\n<p>Some time and it can be due to an error in the database or the application using it cause the database to expand and take all the disk space until total stop of the database service. as a result the application stops. here is the steps to find the files\/database that cause the issue and free up some space.<\/p>\n\n\n\n<p>First lets free some space in order to give the system some air, clean old installation package :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>yum clean all<\/code><\/pre>\n\n\n\n<p>You can try and trim\/delete some old logs or very large one at \/var\/log\/<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>find \/var -name \"*.log\" \\( \\( -size +50M -mtime +7 \\) -o -mtime +30 \\) -exec truncate {} --size 0 \\;<\/code><\/pre>\n\n\n\n<p>Get the current disk space :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>df -h<\/code><\/pre>\n\n\n\n<p>Now that you have some space, install NCDU which is nice and easy file information tool<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>yum install epel-release\nyum install ncdu<\/code><\/pre>\n\n\n\n<p>Now you can get specific idea about you disk space, with the arrow keys you can browse the file system<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> cd \/      (to get to the root file system)\nncdu       (it will start enumerating the file size)<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-gallery columns-1 is-cropped wp-block-gallery-1 is-layout-flex wp-block-gallery-is-layout-flex\"><ul class=\"blocks-gallery-grid\"><li class=\"blocks-gallery-item\"><figure><img loading=\"lazy\" decoding=\"async\" width=\"1021\" height=\"612\" src=\"https:\/\/itsimple.info\/wp-content\/uploads\/2021\/05\/ncdu-disk_size-1.png\" alt=\"\" data-id=\"1867\" data-full-url=\"https:\/\/itsimple.info\/wp-content\/uploads\/2021\/05\/ncdu-disk_size-1.png\" data-link=\"https:\/\/itsimple.info\/?attachment_id=1867\" class=\"wp-image-1867\" srcset=\"https:\/\/itsimple.info\/wp-content\/uploads\/2021\/05\/ncdu-disk_size-1.png 1021w, https:\/\/itsimple.info\/wp-content\/uploads\/2021\/05\/ncdu-disk_size-1-300x180.png 300w, https:\/\/itsimple.info\/wp-content\/uploads\/2021\/05\/ncdu-disk_size-1-768x460.png 768w\" sizes=\"auto, (max-width: 1021px) 100vw, 1021px\" \/><\/figure><\/li><\/ul><\/figure>\n\n\n\n<p>Another way to get the largest 10 files in a directory (in this example \/var  folder):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>du -a \/var | sort -n -r | head -n 10<\/code><\/pre>\n\n\n\n<p>Lets assume you have found \/var\/lib\/mysql as the biggest directory and the file ibdata1 as the largest file around, <strong><span class=\"has-inline-color has-vivid-red-color\">you can not just delete this file as all your information will be gone !!!<\/span><\/strong><\/p>\n\n\n\n<p>Lets start getting information on the databases :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>du -h --max-depth=1 \/var\/lib\/mysql\/        (will show you the database folder size)<\/code><\/pre>\n\n\n\n<p>To get the database true size :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -u root -p\nSELECT table_schema AS \"Database\", SUM(data_length + index_length) \/ 1024 \/ 1024 \/ 1024 AS \"Size (GB)\" FROM information_schema.TABLES GROUP BY table_schema ;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-gallery columns-1 is-cropped wp-block-gallery-2 is-layout-flex wp-block-gallery-is-layout-flex\"><ul class=\"blocks-gallery-grid\"><li class=\"blocks-gallery-item\"><figure><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"315\" src=\"https:\/\/itsimple.info\/wp-content\/uploads\/2021\/05\/Mysql_DatabaseSize-1024x315.png\" alt=\"\" data-id=\"1869\" data-full-url=\"https:\/\/itsimple.info\/wp-content\/uploads\/2021\/05\/Mysql_DatabaseSize.png\" data-link=\"https:\/\/itsimple.info\/?attachment_id=1869\" class=\"wp-image-1869\" srcset=\"https:\/\/itsimple.info\/wp-content\/uploads\/2021\/05\/Mysql_DatabaseSize-1024x315.png 1024w, https:\/\/itsimple.info\/wp-content\/uploads\/2021\/05\/Mysql_DatabaseSize-300x92.png 300w, https:\/\/itsimple.info\/wp-content\/uploads\/2021\/05\/Mysql_DatabaseSize-768x237.png 768w, https:\/\/itsimple.info\/wp-content\/uploads\/2021\/05\/Mysql_DatabaseSize.png 1188w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure><\/li><\/ul><\/figure>\n\n\n\n<p>In this session it is zabbix_db that takes a lot of space, To get the all the table size in GB run :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)\/1024\/1024\/1024 AS TABLE_SIZE_in_MB FROM information_schema.TABLES;\n<\/code><\/pre>\n\n\n\n<p>To empty specific table <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>use zabbix_db;\nTRUNCATE TABLE table_name;<\/code><\/pre>\n\n\n\n<p>Some time the space reclamation do not happen so the best way is to replace the table with new and empty one, Lets assume we want to empty table call history_unit , here are the steps :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE new_history_uint LIKE history_uint;\n   INSERT INTO new_history_uint SELECT * FROM history_uint;  -- This could take a long time\n   RENAME TABLE history_uint TO old, new_history_uint TO history_uint;   -- Atomic swap\n   DROP TABLE old;<\/code><\/pre>\n\n\n\n<p>Now if that didn&#8217;t help reclaim the disk space you will need to drop the databse, delete the ibdata1 file and restore the database all together. (if you are running zabbix you might want to stop it )<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>systemctl stop zabbix-server<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>create Backup folder\ncd \/var\/lib\/mysql\nmkdir \/backup\n\ninstall pv progressbar viewer:\nyum install pv\n\nbackup all DB:\nmysqldump -u root -p --all-databases | pv -W &gt; \/var\/lib\/mysql\/backup\/all-database.sql\n\nbackup only zabbix:\nmysqldump \u2013q -l -u root -p zabbix_db | pv -W &gt; \/var\/lib\/mysql\/backup\/zabbix_db.sql<\/code><\/pre>\n\n\n\n<p>More comprehensive back way<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\n\nmysqldump -u USERNAME -p --all-databases --events --ignore-table=mysql.event --extended-insert --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers | gzip &gt; \"all_databases.gz\"\n\nTo restore from this sql dump :\ngunzip &lt; all_databases.gz | mysql -u USERNAME -p<\/code><\/pre>\n\n\n\n<p>Now you are ready to start .<span class=\"has-inline-color has-vivid-red-color\"><strong> Make sure your backup has succeed before continue (check SQL file size for example)!<\/strong><\/span> <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>drop all databases:\nmysql -u root -p\nmysql&gt; show databases;\n\nmysql&gt; drop database zabbix_db;\n\nstop mysql:\nsystemctl stop mariadb\n\ndelete the file:\ncd \/var\/lib\/mysql\/\nrm ibdata1\nrm ib_logfile0\nrm ib_logfile1\n\nstart the mysql:\nsystemctl start mariadb      (it will re-create ibdate1)<\/code><\/pre>\n\n\n\n<p>At this point make sure the free disk space is accordingly. Now restore the database from the dump you made before :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -u root -p --all-databases | pv -W &lt; \/var\/lib\/mysql\/backup\/all-database.sql\nin case of one DB you will need to recreate the database and the structure:\nCREATE DATABASE zabbix_db;\nmysql -u root -p zabbix_db | pv -W  &lt; \/var\/lib\/mysql\/backup\/zabbix_db.sql<\/code><\/pre>\n\n\n\n<p>In case of very large Database this can take some time .<\/p>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<p class=\"has-vivid-green-cyan-color has-text-color has-large-font-size\"><em><strong>Good Luck<\/strong><\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Some time and it can be due to an error in the database or the application using it cause the database to expand and take all the disk space until total stop of the database service. as a result the application stops. here is the steps to find the files\/database that cause the issue and [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,21,4,12],"tags":[],"class_list":["post-1862","post","type-post","status-publish","format-standard","hentry","category-linux","category-operating-systems","category-tech","category-tutorials"],"_links":{"self":[{"href":"https:\/\/itsimple.info\/index.php?rest_route=\/wp\/v2\/posts\/1862","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itsimple.info\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itsimple.info\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/itsimple.info\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/itsimple.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1862"}],"version-history":[{"count":0,"href":"https:\/\/itsimple.info\/index.php?rest_route=\/wp\/v2\/posts\/1862\/revisions"}],"wp:attachment":[{"href":"https:\/\/itsimple.info\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1862"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itsimple.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1862"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itsimple.info\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1862"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}