How do I split a large MySQL dump file into smaller files to overcome upload size limits?

Splitting Mysqldump Output into Smaller Files for Upload to Remote Server with Limited Capacity

The need to transfer bulky data from one MySQL database to another often arises, but limitations like restricted file upload sizes can pose a challenge. This is especially pertinent when using phpMyAdmin, which allows only compressed .sql files smaller than 2MB. Fortunately, a solution exists to overcome this obstacle.

To resolve this issue, the output of mysqldump can be meticulously fragmented into manageable chunks, enabling them to be uploaded in their smaller forms. Unfortunately, the split(1) utility cannot be employed due to the inability to concatenate the files back together on the remote server.

An Alternative Solution: Fragmenting MySQL Dump Output

A practical alternative to split(1) is showcased in the following bash script. It deftly splits a database dump file into individual .sql files, each corresponding to a distinct table. The script leverages csplit, a versatile tool for splitting files, and strategically employs START to identify table boundaries.

#!/bin/bash

# Adjust this to your specific scenario:
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 a specific 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 solution meticulously splits the dump file into individual table-specific .sql files, ensuring successful upload to the remote server even with restricted file capacity.

The above is the detailed content of How do I split a large MySQL dump file into smaller files to overcome upload size limits?. For more information, please follow other related articles on the PHP Chinese website!