{"id":2183,"date":"2015-04-28T10:28:58","date_gmt":"2015-04-28T10:28:58","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2183"},"modified":"2024-01-27T17:37:44","modified_gmt":"2024-01-27T17:37:44","slug":"how-to-recipes-for-mysqldumpsplitter","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/how-to-recipes-for-mysqldumpsplitter","title":{"rendered":"How-to extract from mysqldump | mysql-dump-splitter recipes"},"content":{"rendered":"\n

The latest mysqldumpsplitter (v 5.0)<\/a> is out. I’d like to make it easier for everyone to get started with it.  So in this post you’ll get a quick & handy command-line “recipe” for extracting table, database  or more from mysqldump.<\/p>\n\n\n\n

The Mysqldumpsplitter can extract database, table, all databases, all tables or tables matching on regular expression. It can also extract multiple tables from one database or describe the contents from a mysqldump.<\/p>\n\n\n\n

The tool can extract a MySQL database from a compressed or uncompressed mysqldump. It by default produces the compressed output of sqls in default out directory but you can specify target directory or uncompressed output as well.<\/p>\n\n\n\n

Check out more  on recipe commands to use the mysqldumpsplitter.<\/p>\n\n\n\n\n\n\n\n


At the time or writing, the version is 5.0, which you can download from mysql-dump-splitter GitHub repository.<\/a><\/strong><\/span><\/p>\n\n\n\n

MySQL Dump Splitter How-Tos:<\/h1>\n\n\n\n

1) Extract single database from mysqldump:<\/h2>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source filename --extract DB --match_str database-name\n<\/pre>\n<\/blockquote>\n\n\n\n
Above command will create sql for specified database from specified “filename” sql file and store it in compressed format to database-name.sql.gz.<\/p>\n\n\n\n
2) Extract single table from mysqldump:<\/h2>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source filename --extract TABLE --match_str table-name\n<\/pre>\n<\/blockquote>\n\n\n\n
Above command will create sql for specified table from specified “filename” mysqldump file and store it in compressed format to database-name.sql.gz.<\/p>\n\n\n\n
3) Extract tables matching regular expression from mysqldump:<\/h2>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source filename --extract REGEXP --match_str regular-expression\n<\/pre>\n<\/blockquote>\n\n\n\n
Above command will create sqls for tables matching specified regular expression from specified “filename” mysqldump file and store it in compressed format to individual table-name.sql.gz.<\/p>\n\n\n\n
4) Extract all databases from mysqldump:<\/h2>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source filename --extract ALLDBS\n<\/pre>\n<\/blockquote>\n\n\n\n
Above command will extract all databases from specified “filename” mysqldump file and store it in compressed format to individual database-name.sql.gz.<\/p>\n\n\n\n
5) Extract all table from mysqldump:<\/h2>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source filename --extract ALLTABLES\n<\/pre>\n<\/blockquote>\n\n\n\n
Above command will extract all tables from specified “filename” mysqldump file and store it in compressed format to individual table-name.sql.gz.<\/p>\n\n\n\n
6) Extract tables in the list from mysqldump:<\/h2>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source filename --extract REGEXP --match_str '(table1|table2|table3)'\n<\/pre>\n<\/blockquote>\n\n\n\n
Above command will extract tables from the specified “filename” mysqldump file and store them in compressed format to individual table-name.sql.gz.<\/p>\n\n\n\n
7) Extract a database from compressed mysqldump:<\/h2>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source filename.sql.gz --extract DB --match_str 'dbname' --decompression gzip\n<\/pre>\n<\/blockquote>\n\n\n\n
Above command will decompress filename.sql.gz using gzip, extract database named “dbname” from “filename.sql.gz” & store it as out\/dbname.sql.gz<\/p>\n\n\n\n
8) Extract a database from compressed mysqldump in an uncompressed format:<\/h2>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source filename.sql.gz --extract DB --match_str 'dbname' --decompression gzip --compression none\n<\/pre>\n<\/blockquote>\n\n\n\n
Above command will decompress filename.sql.gz using gzip and extract database named “dbname” from “filename.sql.gz” & store it as plain sql out\/dbname.sql<\/p>\n\n\n\n
9) Extract alltables from mysqldump in different folder:<\/h2>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source filename --extract ALLTABLES --output_dir \/path\/to\/extracts\/\n<\/pre>\n<\/blockquote>\n\n\n\n
Above command will extract all tables from specified “filename” mysqldump file and extracts tables in compressed format to individual files, table-name.sql.gz stored under \/path\/to\/extracts\/. The script will create the folder \/path\/to\/extracts\/ if not exists.<\/p>\n\n\n\n
10) Extract one or more tables from one database in a full-dump:<\/h2>\n\n\n\n
Consider you have a full dump with multiple databases and you want to extract few tables from one database. You can do that in two parts:<\/p>\n\n\n\n
       A. Extract single database:<\/h3>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source filename --extract DB --match_str DBNAME --compression none\n<\/pre>\n<\/blockquote>\n\n\n\n
       B. Extract all tables<\/h3>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source out\/DBNAME.sql --extract REGEXP --match_str \"(tbl1|tbl2)\"\n<\/pre>\n<\/blockquote>\n\n\n\n
though we can use another option to do this in single command as follows (note the DBTABLE extract option):<\/p>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source filename --extract DBTABLE --match_str \"DBNAME.(tbl1|tbl2)\" --compression none\n<\/pre>\n<\/blockquote>\n\n\n\n
Above command will extract both tbl1 and tbl2 from DBNAME database in sql format under folder “out” in current directory.<\/p>\n\n\n\n
You can extract single table as follows:<\/h3>\n\n\n\n
\n
sh mysqldumpsplitter.sh --source filename --extract DBTABLE --match_str \"DBNAME.(tbl1)\" --compression none\n<\/pre>\n<\/blockquote>\n\n\n\n
11) Extract all tables from specific database:<\/h2>\n\n\n\n
\n
mysqldumpsplitter.sh --source filename --extract DBTABLE --match_str \"DBNAME.*\" --compression none\n<\/pre>\n<\/blockquote>\n\n\n\n
Above command will extract all tables from DBNAME database in sql format and store it under “out” directory.<\/p>\n\n\n\n
12) List content of the mysqldump file<\/h2>\n\n\n\n
\n
mysqldumpsplitter.sh --source filename --desc\n<\/pre>\n<\/blockquote>\n\n\n\n
Above command will list databases and tables from the dump file.<\/p>\n\n\n\n
Let me know if I can improve this. Hope this serves well and if it does, don’t hesitate to like and share with fellow mysqlers.<\/p>\n\n\n\n
I’ll try to add more functionalities to the mysqldumpsplitter tool. If you have suggestions or bug reports please comment below.<\/p>\n\n\n\n
Cheers.<\/p>\n","protected":false},"excerpt":{"rendered":"This post will answer how to extract database, tables etc from mysqldumpsplitter utility.\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":[8,870,377],"tags":[677,678,676,245,675],"class_list":{"0":"post-2183","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-tools","8":"category-mysql-articles","9":"tag-extract-database-from-mysqldump","10":"tag-extract-from-mysqldump","11":"tag-extract-table-from-mysqldump","12":"tag-mysqldumpsplitter","13":"tag-split-mysqldump"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2183","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=2183"}],"version-history":[{"count":9,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2183\/revisions"}],"predecessor-version":[{"id":2844,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2183\/revisions\/2844"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2183"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2183"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2183"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}