{"id":464,"date":"2015-03-21T12:26:15","date_gmt":"2015-03-21T11:26:15","guid":{"rendered":"http:\/\/www.dereckson.be\/blog\/?p=464"},"modified":"2017-02-09T02:48:21","modified_gmt":"2017-02-09T01:48:21","slug":"split-a-large-sql-dump-by-database","status":"publish","type":"post","link":"https:\/\/www.dereckson.be\/blog\/2015\/03\/21\/split-a-large-sql-dump-by-database\/","title":{"rendered":"Split a large SQL dump by database"},"content":{"rendered":"<p>You created a MySQL backup of a large server installation with dozens of databases and wish to get the schema and data for one of them. You now have to deal with a file of hundreds of MB in a text editor. How convenient.<\/p>\n<h2>Split a dump into several files<\/h2>\n<p>You can quickly split this dump in several files (one per database) with awk or csplit. With GNU awk (gawk on FreeBSD), this is a oneliner:<\/p>\n<pre>awk '\/Current Database\\: .*\/{g++} { print $0 &gt; g\".sql\" }' yourdump.sql<\/pre>\n<h2>Get database.sql files<\/h2>\n<p>To rename these files with actual database names, the following bash script could be useful. It assumes you don&#8217;t have the main dump in the same directory.<\/p>\n<pre>#!\/usr\/bin\/env bash\r\n\r\nregex=\"Current Database: \\`(.*)\\`\"\r\n\r\nfor f in *.sql\r\ndo\r\n    DATABASE_LINE=`head -n1 $f`\r\n    [[ $DATABASE_LINE =~ $regex ]]\r\n    mv $f ${BASH_REMATCH[1]}.sql\r\ndone<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>You created a MySQL backup of a large server installation with dozens of databases and wish to get the schema and data for one of them. You now have to deal with a file of hundreds of MB in a text editor. How convenient. Split a dump into several files You can quickly split this [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49,50],"tags":[304],"class_list":["post-464","post","type-post","status-publish","format-standard","hentry","category-dev","category-sysadmin","tag-mysql"],"_links":{"self":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts\/464","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/comments?post=464"}],"version-history":[{"count":1,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts\/464\/revisions"}],"predecessor-version":[{"id":465,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts\/464\/revisions\/465"}],"wp:attachment":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/media?parent=464"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/categories?post=464"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/tags?post=464"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}