{"id":743,"date":"2022-02-24T14:58:10","date_gmt":"2022-02-24T19:58:10","guid":{"rendered":"https:\/\/molecularsciences.org\/content\/?p=743"},"modified":"2023-11-29T12:56:01","modified_gmt":"2023-11-29T17:56:01","slug":"how-to-empty-a-mysql-database-2","status":"publish","type":"post","link":"https:\/\/molecularsciences.org\/content\/how-to-empty-a-mysql-database-2\/","title":{"rendered":"How to empty a MySQL database"},"content":{"rendered":"\n<p>Emptying a databases involves dropping (deleting) all its tables. The easiest way to do this is to drop and recreate a database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> drop database mole;\nmysql> create database mole;<\/code><\/pre>\n\n\n\n<p>Depending on the system you are working, the database administrator might not give the rights to drop or create databases. In such cases, you have no choice but to drop and recreate each table manually or with a script. I found a couple of really nifty shell scripts to do this at&nbsp;<a target=\"_blank\" href=\"http:\/\/www.thingy-ma-jig.co.uk\/blog\/10-10-2006\/mysql-drop-all-tables\" rel=\"noreferrer noopener\">this site<\/a>. The code is as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqldump -u&#91;USERNAME] -p&#91;PASSWORD] --add-drop-table --no-data &#91;DATABASE] | grep ^DROP | mysql -u&#91;USERNAME] -p&#91;PASSWORD] &#91;DATABASE]<\/code><\/pre>\n\n\n\n<p>or you can use gawk<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysql -u uname dbname -e \"show tables\" | grep -v Tables_in | grep -v \"+\" | gawk '{print \"drop table \" $1 \";\"}' | mysql -u uname dbname<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Emptying a databases involves dropping (deleting) all its tables. The easiest way to do this is to drop and recreate a database: Depending on the system you are working, the database administrator might not give the rights to drop or create databases. In such cases, you have no choice but to drop and recreate each [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1236,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,204],"tags":[14,27],"class_list":["post-743","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","category-mysql","tag-database","tag-mysql"],"_links":{"self":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/743","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/comments?post=743"}],"version-history":[{"count":1,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/743\/revisions"}],"predecessor-version":[{"id":744,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/743\/revisions\/744"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media\/1236"}],"wp:attachment":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media?parent=743"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/categories?post=743"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/tags?post=743"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}