{"id":1717,"date":"2020-12-30T15:58:39","date_gmt":"2020-12-30T13:58:39","guid":{"rendered":"https:\/\/itsimple.info\/?p=1717"},"modified":"2020-12-30T16:03:09","modified_gmt":"2020-12-30T14:03:09","slug":"how-to-truncate-mysql-database-table","status":"publish","type":"post","link":"https:\/\/itsimple.info\/?p=1717","title":{"rendered":"How to truncate mysql database table"},"content":{"rendered":"<p>\u00a0<\/p>\n<p>Sometime swollen table in a databasecan cause disk space problem and if the data inside if expandable then you want\/need to empty the table .<\/p>\n<p>First connect to the server with putty, To get the databases on server and there size, execute :<\/p>\n<pre>du -h --max-depth=1 \/var\/lib\/mysql\/<\/pre>\n<p>Enter the mysql prompt :<\/p>\n<pre># mysql -u root -p\nEnter password: \nWelcome to the MariaDB monitor.  Commands end with ; or \\g.\nYour MariaDB connection id is 43\nServer version: 10.3.13-MariaDB Source distribution\n\nCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nMariaDB [(none)]&gt; <\/pre>\n<p>Then to get the database size in GB, execute :<\/p>\n<pre>SELECT table_schema \"database\", sum(data_length + index_length)\/1024\/1024\/1024 \"size in GB\" FROM information_schema.TABLES GROUP BY table_schema;<br \/><br \/><\/pre>\n<p>Then to get the table size in MB, execute :<\/p>\n<pre class=\"rich-text block-editor-rich-text__editable wp-block-preformatted\" role=\"textbox\" contenteditable=\"true\" aria-multiline=\"true\" aria-label=\"Write preformatted text\u2026\">SELECT table_name AS \"Table\", ROUND(((data_length + index_length) \/ 1024 \/ 1024), 2) AS \"Size (MB)\" FROM information_schema.TABLES WHERE table_schema = \"testdb\" ORDER BY (data_length + index_length) DESC;<\/pre>\n<p>To get the table size in GB, execute :<\/p>\n<pre>SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)\/1024\/1024\/1024 AS TABLE_SIZE_in_MB FROM information_schema.TABLES;<br \/><br \/><\/pre>\n<p>By now you should know your database and tables size ! in order to truncate table, when still connected to the mysql prompt, execute :<\/p>\n<pre class=\"wp-block-code\"><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">USE<\/span> database_name;<\/code><\/pre>\n<p>then empty the table (this could take some time if the table is big) :<\/p>\n<pre class=\"wp-block-code\"><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">TRUNCATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> table_name;<br \/><\/code><\/pre>\n<p>Check the table size again to see the changes .<\/p>\n<hr \/>\n<h3>Good Luck<\/h3>\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u00a0 Sometime swollen table in a databasecan cause disk space problem and if the data inside if expandable then you want\/need to empty the table . First connect to the server with putty, To get the databases on server and there size, execute : du -h &#8211;max-depth=1 \/var\/lib\/mysql\/ Enter the mysql prompt : # mysql [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,13,21,12],"tags":[],"class_list":["post-1717","post","type-post","status-publish","format-standard","hentry","category-centos","category-linux","category-operating-systems","category-tutorials"],"_links":{"self":[{"href":"https:\/\/itsimple.info\/index.php?rest_route=\/wp\/v2\/posts\/1717","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=1717"}],"version-history":[{"count":0,"href":"https:\/\/itsimple.info\/index.php?rest_route=\/wp\/v2\/posts\/1717\/revisions"}],"wp:attachment":[{"href":"https:\/\/itsimple.info\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1717"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itsimple.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1717"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itsimple.info\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1717"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}