Logo
  • PHP
    • HipHop / HHVM
    • Modern PHP
    • PHPStorm
    • LAMP
    • Laravel
    • Composer
    • PDO
  • JavaScript
    • node.js
    • AngularJS
  • CSS
    • SASS
    • “CSS4” (CSS level 4)
  • HTML
  • Git
  • LAMP
  • Vagrant
  • UI / UX
  • Architecture of …
  • Off-Topic
With ♥ from Berlin
December 20, 2014
Chris
Local Development, MySQL
0

How to get a single table out of a massive MySQL .sql database backup file (mysql dump splitter)

PreviousNext

Imagine the following situation: Somebody backs up an entire MySQL database – a very large one – with common tools. This will usually result in a tar’ed and gzip’ed file, containing ALL the create/fill statements for ALL tables of the database. All tables in one file. This is also very common when third party clients deliver database backups.

This situation gets annoying when your backup is in the 100+ GB area and you just need ONE table (or at least not all). Re-deploying backups in these dimensions from scratch (for testing for example) is a huge time-killer (running these statements can take days!), and for sure using just that 2 GB table is by far the better option than running the whole script when you really just need that one table.

This was a common task at my former company, and unfortunately all professional tools were not able to read such a file or totally crashed while doing so. The final solution to solve this problem was a tiny self-written bash script I found in on Change is inevitable, a very nice dev blog. This awesome script is extremely easy to use and simply extracts one specific table out of the giant file (and puts it into another file) or grabs a set of tables, you can even use regex here. Fantastic!

Kedar of Change is inevitable has even made a little tutorial on Youtube, check the blog post for the video. Big thanks, man!

As the original blog post shows the script in a unformatted way, here’s the full code again:

#!/bin/sh
# http://kedar.nitty-witty.com
#SPLIT DUMP FILE INTO INDIVIDUAL TABLE DUMPS
# Text color variables
txtund=$(tput sgr 0 1)    # Underline
txtbld=$(tput bold)       # Bold
txtred=$(tput setaf 1)    # Red
txtgrn=$(tput setaf 2)    # Green
txtylw=$(tput setaf 3)    # Yellow
txtblu=$(tput setaf 4)    # Blue
txtpur=$(tput setaf 5)    # Purple
txtcyn=$(tput setaf 6)    # Cyan
txtwht=$(tput setaf 7)    # White
txtrst=$(tput sgr0)       # Text reset

TARGET_DIR="."
DUMP_FILE=$1
TABLE_COUNT=0

if [ $# = 0 ]; then
        echo "${txtbld}${txtred}Usage: sh MyDumpSplitter.sh DUMP-FILE-NAME${txtrst} -- Extract all tables as a separate file from dump."
        echo "${txtbld}${txtred}       sh MyDumpSplitter.sh DUMP-FILE-NAME TABLE-NAME ${txtrst} -- Extract single table from dump."
        echo "${txtbld}${txtred}       sh MyDumpSplitter.sh DUMP-FILE-NAME -S TABLE-NAME-REGEXP ${txtrst} -- Extract tables from dump for specified regular expression."
        exit;
elif [ $# = 1 ]; then
        #Loop for each tablename found in provided dumpfile
        for tablename in $(grep "Table structure for table " $1 | awk -F"\`" {'print $2'})
        do
                #Extract table specific dump to tablename.sql
                sed -n "/^-- Table structure for table \`$tablename\`/,/^-- Table structure for table/p" $1 > $TARGET_DIR/$tablename.sql
                TABLE_COUNT=$((TABLE_COUNT+1))
        done;
elif [ $# = 2  ]; then
        for tablename in $(grep -E "Table structure for table \`$2\`" $1| awk -F"\`" {'print $2'})
        do
                echo "Extracting $tablename..."
                #Extract table specific dump to tablename.sql
                sed -n "/^-- Table structure for table \`$tablename\`/,/^-- Table structure for table/p" $1 > $TARGET_DIR/$tablename.sql
                TABLE_COUNT=$((TABLE_COUNT+1))
        done;
elif [ $# = 3  ]; then

        if [ $2 = "-S" ]; then
                for tablename in $(grep -E "Table structure for table \`$3" $1| awk -F"\`" {'print $2'})
                do
                        echo "Extracting $tablename..."
                        #Extract table specific dump to tablename.sql
                        sed -n "/^-- Table structure for table \`$tablename\`/,/^-- Table structure for table/p" $1 > $TARGET_DIR/$tablename.sql
                        TABLE_COUNT=$((TABLE_COUNT+1))
                done;
        else
                echo "${txtbld}${txtred} Please provide proper parameters. ${txtrst}";
        fi
fi

#Summary
echo "${txtbld}$TABLE_COUNT Table extracted from $DUMP_FILE at $TARGET_DIR${txtrst}"

 

Usage

You can use the mysqldumpsplitter like this (assuming you have put the script inside a file called mysqldumpsplitter.sh):

Extract all tables of file XXX to lots of single sql files:

sh mysqldumpsplitter.sh XXX

Extract table ZZZ of file XXX to single file:

sh mysqldumpsplitter.sh XXX ZZZ

Extract table(s) via regex of file XXX:

sh mysqldumpsplitter.sh XXX -S REGEX
This article was written quite a while ago (8 years), please keep this in mind when using the information written here. Links, code and commands might be outdated or broken.

Random articles

  • How blind people use websites (video with Sina Bahram, blind accessibility researcher)How blind people use websites (video with Sina Bahram, blind accessibility researcher)
  • Laracon 2013 – Kapil Verma: Engineering Complex Applications with Laravel 4 (40min video)Laracon 2013 – Kapil Verma: Engineering Complex Applications with Laravel 4 (40min video)
  • JavaScript ECMAScript6 – A short video introduction (5min)JavaScript ECMAScript6 – A short video introduction (5min)
  • Vote for “Hack” for HipHop/HHMV support (future style PHP) in PHPStorm 8Vote for “Hack” for HipHop/HHMV support (future style PHP) in PHPStorm 8
  • (Links) How to fix an extremely slow Symfony inside a Vagrant box(Links) How to fix an extremely slow Symfony inside a Vagrant box
  • New project: Building a naked PHP skeleton / boilerplate application from scratchNew project: Building a naked PHP skeleton / boilerplate application from scratch
  • A preinstalled Vagrant box with PHP HipHop / HHVM and Ubuntu 13.10 (Saucy Salamander)A preinstalled Vagrant box with PHP HipHop / HHVM and Ubuntu 13.10 (Saucy Salamander)
  • Meet the developers behind Ableton (14min video)Meet the developers behind Ableton (14min video)
  • Nice gifts for devs: Nerdy playing-cards decksNice gifts for devs: Nerdy playing-cards decks
Share this

Leave A Comment Cancel reply

php

New GitHub repo: simple php-long-polling for creating real-time apps

Mornin’ ! I’ve just pushed a sweet little new project into GitHub: A very simple and totally reduced PHP long-polling

hiphop php

Vote for “Hack” for HipHop/HHMV support (future style PHP) in PHPStorm 8

“Hack” will come. And “Hack” will change PHP, definitly. In case you never heard of it, have a look into

organizing css

Jonathan Snook – CSS is a Mess – How to organize CSS in big projects (54min video talk)

This article was written quite a while ago (9 years), please keep this in mind when using the information written

How to fix the ugly font rendering in Google Chrome

Update, August 2014: Google has rolled out Chrome 37, which finally fixes this issue nativly. Yeah! For historical reasons the

sass

[german video] Modernizing and minimalizing frontend markup code at AutoScout24

As this blog has a lot of german speaking readers I’ll post german stuff from time to time. Sorry for

php-7

PHP 6.0 will be PHP 7

Germany’s excellent PHP Magazin just posted interesting news: The next major version of PHP will be PHP 7, the development

phpstorm 7.0 php

How to setup and use XDEBUG with PHPStorm 6/7 (locally in Windows 7/8 and Mac OS X)

Real debugging is probably one of the most coolest things that are possible in software development: Remember the times where

css

How to center a div vertically and horizontally (modern methods, without fixed size!)

“How to center a div” (inside another div or inside the body itself) is one of the most discussed questions

Useful basic linux stuff: Show kernel version, distribution name and distribution version on Ubuntu systems

Always nice to know: Show kernel version: uname -r Show kernel version and CPU: uname -a Show distribution version: lsb_release

php

How to use the PHP 5.5 password hashing functions

PHP 5.5 introduced some very interesting password hashing functions that will make your life much much easier, the web much

1/4

Categories

Search

Frontend Ops Conf 2014 – Paul Irish: Delivering The Goods In Under 1000ms (40min video)
php
How to install PHP 7.0 on Ubuntu 14.04 LTS
How Instagram.com works
set up a local virtual machine for development with vagrant and puphpet / puppet (and ubuntu, linux, php, apache, mysql)
A super-simple pre-configured Vagrant box with HipHop, Hack and Hack code examples
vagrant
A preinstalled Vagrant box with PHP HipHop / HHVM and Ubuntu 12.04 (Precise Pangolin)
Hacked french TV channel exposed passwords in TV interview (video, screenshots, links)
[Link] Redesigning SoundCloud by Evan Simoni
logo-internet-explorer
How to professionally test on old Internet Explorer versions
sass laravel
SASSmeister is a real-time JSfiddle for SASS / CSS. Awesome!
Test GZIP compression of your server easily
phpstorm-github-code-color-syntax-theme
Get Github’s syntax highlighting colors in PHPStorm
forbes 30 under 30
Need motivation ? Check out these 2 awesome “FORBES 30 under 30” lists (web, UI, games)
hack-php
Facebook releases HipHop (HHVM) 3.0, adds mysqli and support for Hack language
Rare Steve Jobs AND Bill Gates video interview from 2007’s D5 conference (90min)
You made a mess with Git ? Here’s a flowchart guideline on how to fix

Tags

apache bash centos composer conference coupon CSS debian fonts framework git GitHub hack HHVM HipHop HTML HTML5 IDE JavaScript JS LAMP laravel linux mod_rewrite MVC MySQL Nginx optimization PHP PHP 5.5 PHP 5.6 phpmyadmin PHPStorm security server SSD Ubuntu UI UX vagrant video virtual machine voucher VPS wordpress
Side-Project: Wordle-Solver:
www.wordle-helper.info

Pages

  • Privacy Policy
 
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
Do not sell my personal information.
Cookie SettingsAccept
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT