How can I split a large mysqldump output into smaller files for easier transfer and import?

Method to Split Large mysqldump Output into Smaller Files

When transferring large MySQL tables between databases, the compressed mysqldump output may exceed the maximum file size allowed for import into the destination. To overcome this challenge, users can employ the following methods:

Split Dump File Using csplit

A bash script can be used to split a mysqldump file into separate files for each table. This script leverages the csplit utility to create files based on specific patterns:

START="/-- Table structure for table/"

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*

Using --extended-insert=FALSE with mysqldump

This option generates a SQL file that can be split into importable files. Split can be used with the --lines option to control the number of lines per file. Trial and error or compression tools such as bzip2 can be used to determine the appropriate number of lines for each file size.

The above is the detailed content of How can I split a large mysqldump output into smaller files for easier transfer and import?. For more information, please follow other related articles on the PHP Chinese website!