How to Split a Large MySQL Dump into Smaller Files?

Splitting Large mysqldump Output into Smaller Files

It becomes necessary to transfer tables from one database to another, but the dump output from mysqldump is often too large for certain upload limits. This article explores a clever solution to split bulky mysqldump output into manageable, smaller files.

One potential approach suggested by shell scripting is to split the dump into separate files for each table. This can be achieved using the csplit command and specifying table structure markers as splitting points.

Consider this bash script that employs this technique:

#!/bin/bash

####
# Split MySQL dump SQL file into one file per table
# based on https://gist.github.com/jasny/1608062
####

#adjust this to your case:
START="/-- Table structure for table/"
# or 
#START="/DROP TABLE IF EXISTS/"


if [ $# -lt 1 ] || [[  == "--help" ]] || [[  == "-h" ]] ; then
        echo "USAGE: extract all tables:"
        echo "  DUMP_FILE"
        echo "extract one table:"
        echo "  DUMP_FILE [TABLE]"
        exit
fi

if [ $# -ge 2 ] ; then
        #extract one table 
        csplit -s -ftable  "/-- Table structure for table/" "%-- Table structure for table \`\`%" "/-- Table structure for table/" "%40103 SET TIME_ZONE=@OLD_TIME_ZONE%1"
else
        #extract all tables
        csplit -s -ftable  "$START" {*}
fi

[ $? -eq 0 ] || exit

mv table00 head

FILE=`ls -1 table* | tail -n 1`
if [ $# -ge 2 ] ; then
        # cut off all other tables
        mv $FILE foot
else
        # cut off the end of each file
        csplit -b '%d' -s -f$FILE $FILE "/40103 SET TIME_ZONE=@OLD_TIME_ZONE/" {*}
        mv ${FILE}1 foot
fi

for FILE in `ls -1 table*`; do
        NAME=`head -n1 $FILE | cut -d$'x60' -f2`
        cat head $FILE foot > "$NAME.sql"
done

rm head foot table*

This script uses csplit to split the dump file into individual table files, with each file named after the corresponding table. This allows for easy reassembly into a single file later on. To use this script, simply provide the path to the dump file as the first argument, and optionally specify a specific table to extract as the second argument.

The above is the detailed content of How to Split a Large MySQL Dump into Smaller Files?. For more information, please follow other related articles on the PHP Chinese website!