Script to transfer a database table quickly without temporary files

transfer

#!/bin/sh

# Modify the source and target variables, and run:
# $./transfer table_name

# Note that this will only run on the same machine as the target MySQL server machine.

SOURCE_MYSQL_HOST=
SOURCE_MYSQL_PORT=
SOURCE_MYSQL_USERNAME=
SOURCE_MYSQL_PASSWORD=
SOURCE_MYSQL_DATABASE=source

TARGET_MYSQL_HOST=localhost
TARGET_MYSQL_PORT=3306
TARGET_MYSQL_USERNAME=root
TARGET_MYSQL_PASSWORD=
TARGET_MYSQL_DATABASE=target

TABLE=$1
MYSQL_CLI=`which mysql`
SOURCE_CLI="$MYSQL_CLI --host=$SOURCE_MYSQL_HOST --port=$SOURCE_MYSQL_PORT --user=$SOURCE_MYSQL_USERNAME --password=$SOURCE_MYSQL_PASSWORD --database=$SOURCE_MYSQL_DATABASE -BCN"
TARGET_CLI="$MYSQL_CLI --host=$TARGET_MYSQL_HOST --port=$TARGET_MYSQL_PORT --user=$TARGET_MYSQL_USERNAME --password=$TARGET_MYSQL_PASSWORD --database=$TARGET_MYSQL_DATABASE -BCN"


echo "Recreating table structure for" $TABLE

$TARGET_CLI -e "DROP TABLE IF EXISTS $TABLE"
CREATE_SQL=`$SOURCE_CLI -qe "SHOW CREATE TABLE $TABLE" | sed '1s/.*CREATE TABLE/CREATE TABLE/'`
$TARGET_CLI -e "$CREATE_SQL" 1> /dev/null

echo "Transferring table" $TABLE
$SOURCE_CLI -qe "SELECT 'Number of records to import:', COUNT(1) FROM $TABLE"
PIPE_FILENAME=$PWD/mysql_pipe.csv
mkfifo $PIPE_FILENAME
$SOURCE_CLI -qe "SELECT * FROM $TABLE" | perl -pe 's/\r/\\r/g;s/(^|\t)NULL(?=\t|$)/\1\\N/g' > $PIPE_FILENAME &
time $TARGET_CLI -qe "LOAD DATA INFILE '$PIPE_FILENAME' INTO TABLE $TABLE FIELDS TERMINATED BY '\t'"
rm $PIPE_FILENAME

$TARGET_CLI --batch -e "SELECT 'Number of imported records:', COUNT(1) FROM $TABLE"