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 (7 years), please keep this in mind when using the information written here. Links, code and commands might be outdated or broken.

Random articles

  • appserver.io – A New Way of Magento Enterprise Infrastructure (26min video talk)appserver.io – A New Way of Magento Enterprise Infrastructure (26min video talk)
  • 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
  • Two excellent introductions into AngularJS by Todd MottoTwo excellent introductions into AngularJS by Todd Motto
  • Learn AngularJS in 20 (or 90) minutes with Dan WahlinLearn AngularJS in 20 (or 90) minutes with Dan Wahlin
  • PHP 5.7 gets refactored core, is 10%-30% faster than PHP 5.5! Wow!PHP 5.7 gets refactored core, is 10%-30% faster than PHP 5.5! Wow!
  • Ignore .idea folder from git in PHPStormIgnore .idea folder from git in PHPStorm
  • October CMS, built on top of Laravel, is beautiful, clever and on the way to be the new #1 CMSOctober CMS, built on top of Laravel, is beautiful, clever and on the way to be the new #1 CMS
  • DEF CON 18 – When your computer got stolen and you can still SSH into it: “Pwned by the 0wner” (22min conference talk)DEF CON 18 – When your computer got stolen and you can still SSH into it: “Pwned by the 0wner” (22min conference talk)
  • PHP Opcache Explained by Julien Pauli (video from PHP UK Conference 2014)PHP Opcache Explained by Julien Pauli (video from PHP UK Conference 2014)
Share this

Leave A Comment Cancel reply

The architecture of StackOverflow

One of the most interesting talks these weeks, and a rare insight into one of the most active pages on

How Instagram.com works

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

steam sale coupon voucher

-30% to -90% on Steam and Origin

Soft off-topic, but as most developers like to play from time to time, this might be your change to grab

Material Design – How Google designed Android L (7min video)

A very interesting insight into Android L’s (= Android 5.0) new design and the ideas behind. Android was always #3

bitdeli git github stats

php-login goes #2 PHP script worldwide in BitDeli stats

Holy! I just checked the stats of my little php-login hobby project on BitDeli (the tracking service for GitHub) and

phpstorm-8

When PHPStorm’s cache eats up your harddisk space

I recently stumbled over this: By default PHPStorm (obviously in all versions since 1.0) creates a local history of all

First look on Gitter, the chat for GitHub

GitHub has definitly become the #1 platform for git-based public repositories on the planet, no question. The site offers excellent

(Links) How to fix an extremely slow Symfony inside a Vagrant box

When you work with Vagrant (if not – try it!) on a Windows or Mac machine (which is an excellent

php

[Link] Excellent PHP best practices, 2014 style

Excellent post on This interests me: A collection of useful best practices in modern PHP development, like Redirecting the user

redaktionelle-hochlastseiten

Hochlastseiten mit PHP, MySQL und Apache am Beispiel stern.de (deutscher Artikel)

Eine grandiose Powerpoint-Präsentation die sich jeder, der mit PHP, MySQL und Apache auf Linux arbeitet, mal anschauen sollte: Nils Langner,

1/4

Categories

Search

php
A super-simple introduction into PHP namespaces (7min video)
php uk conference
Slides & talks from PHP UK Conference 2014
Symfony devs: Creator of Symfony framework is hiring (Cologne, Germany)!
Rare Steve Jobs AND Bill Gates video interview from 2007’s D5 conference (90min)
A collection of beautiful ajax loaders / spinners in pure .svg / CSS
How to fix the ugly font rendering in Google Chrome
Install MINI in 30 seconds inside Ubuntu 14.04 LTS
digitalocean coupon
Free $10 coupon for DigitalOcean SSD cloud VPS hosting
php
Awesome list of Design Patterns with PHP code examples
How to hack time (KUNG FURY promo campaign)
Dangerous Performance Myths in the Web (video talk by Thomas Lohner, PHPUG Latvia)
java vs php
Switching from Java to PHP. Seriously. A very interesting and pre-judice-free talk with Ph.D. Aris Zakinthinos
php
Must-read PHP blog: PHPweekly.com
php
PHP.net hacked, but most things are fine again
hack-php
Facebook releases HipHop (HHVM) 3.0, adds mysqli and support for Hack language

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 All”, you consent to the use of ALL the cookies. However, you may visit "Cookie Settings" to provide a controlled consent.
Cookie SettingsAccept All
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