How do I split the output from mysqldump into smaller files?

Question

I need to move entire tables from one MySQL database to another. I don't have full access to the second one, only phpMyAdmin access. I can only upload (compressed) sql files smaller than 2MB. But the compressed output from a mysqldump of the first database's tables is larger than 10MB.

Is there a way to split the output from mysqldump into smaller files? I cannot use split(1) since I cannot cat(1) the files back on the remote server.

Or is there another solution I have missed?

Edit

The --extended-insert=FALSE option to mysqldump suggested by the first poster yields a .sql file that can then be split into importable files, provided that split(1) is called with a suitable --lines option. By trial and error I found that bzip2 compresses the .sql files by a factor of 20, so I needed to figure out how many lines of sql code correspond roughly to 40MB.

Answer 1

This bash script splits a dumpfile of one database into separate files for each table and names with csplit and names them accordingly:

#!/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 ] || [[ $1 == "--help" ]] || [[ $1 == "-h" ]] ; then         echo "USAGE: extract all tables:"         echo " $0 DUMP_FILE"         echo "extract one table:"         echo " $0 DUMP_FILE [TABLE]"         exit fi  if [ $# -ge 2 ] ; then         #extract one table $2         csplit -s -ftable $1 "/-- Table structure for table/" "%-- Table structure for table \`$2\`%" "/-- Table structure for table/" "%40103 SET TIME_ZONE=@OLD_TIME_ZONE%1" else         #extract all tables         csplit -s -ftable $1 "$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* 

based on https://gist.github.com/jasny/1608062
and https://stackoverflow.com/a/16840625/1069083