Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/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"
Add Comment
Please, Sign In to add comment