Skip to content

Instantly share code, notes, and snippets.

@wvanbergen

wvanbergen/transfer

Created Jul 28, 2011
Embed
What would you like to do?
Script to transfer a database table quickly without temporary files
#!/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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment