Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Copy MySQL Database One Server (Remote) To Another (Local) Server
#!/bin/bash
# copymysql.sh
# GENERATED WITH USING ARTUR BODERA'S SCRIPT
# Source script at: https://gist.github.com/2215200
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
REMOTESERVERIP=""
REMOTESERVERUSER=""
REMOTESERVERPASSWORD=""
REMOTECONNECTIONSTR="-h ${REMOTESERVERIP} -u ${REMOTESERVERUSER} --password=${REMOTESERVERPASSWORD} "
LOCALSERVERIP=""
LOCALSERVERUSER=""
LOCALSERVERPASSWORD=""
LOCALCONNECTION="-h ${LOCALSERVERIP} -u ${LOCALSERVERUSER} --password=${LOCALSERVERPASSWORD} "
IGNOREVIEWS=""
MYVIEWS=""
IGNOREDATABASES="select schema_name from information_schema.SCHEMATA where schema_name not like '% %' and schema_name not like '%-%' and schema_name != 'information_schema' and schema_name != 'mysql' and schema_name != 'performance_schema' ;"
# GET A LIST OF DATABASES
echo "GET THE DATABASE LIST"
databases=`$MYSQL $REMOTECONNECTIONSTR -e "${IGNOREDATABASES}" | tr -d "| " | grep -v schema_name`
# CREATE NON-EXISTING DATABASES
for db in $databases; do
echo "create database if not exists $db; "
#$MYSQL $LOCALCONNECTION --batch -N -e "drop database $db; "
$MYSQL $LOCALCONNECTION --batch -N -e "create database if not exists $db; "
done
# COPY ALL TABLES
echo "TABLES "$db
for db in $databases; do
# GET LIST OF TABLES
tables=`$MYSQL $REMOTECONNECTIONSTR --batch -N -e "select table_name from information_schema.tables where table_name not like '% %' and table_name not like '%-%' and table_type='BASE TABLE' and table_schema='$db';"`
for table in $tables; do
echo $db"."$table
$MYSQLDUMP $REMOTECONNECTIONSTR $IGNOREVIEWS --compress --quick --create-options --extended-insert --lock-tables=false --skip-add-locks --skip-comments --skip-disable-keys --default-character-set=latin1 --skip-triggers --single-transaction $db $table | mysql $LOCALCONNECTION $db
done
done
# COPY ALL PROCEDURES
for db in $databases; do
echo "PROCEDURES "$db
#PROCEDURES
$MYSQLDUMP $REMOTECONNECTIONSTR --compress --quick --routines --no-create-info --no-data --no-create-db --skip-opt --skip-triggers $db | \
sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | mysql $LOCALCONNECTION $db
done
# COPY ALL TRIGGERS
for db in $databases; do
echo "TRIGGERS "$db
#TRIGGERS
$MYSQLDUMP $REMOTECONNECTIONSTR --compress --quick --no-create-info --no-data --no-create-db --skip-opt --triggers $db | \
sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | mysql $LOCALCONNECTION $db
done
# COPY ALL VIEWS
for db in $databases; do
# GET LIST OF ITEMS
views=`$MYSQL $REMOTECONNECTIONSTR --batch -N -e "select table_name from information_schema.tables where table_name not like '% %' and table_name not like '%-%' and table_type='VIEW' and table_schema='$db';"`
MYVIEWS=""
for view in $views; do
MYVIEWS=${MYVIEWS}" "$view" "
done
echo "VIEWS "$db
if [ -n "$MYVIEWS" ]; then
#VIEWS
$MYSQLDUMP $REMOTECONNECTIONSTR --compress --quick -Q -f --no-data --skip-comments --skip-triggers --skip-opt --no-create-db --complete-insert --add-drop-table $db $MYVIEWS | \
sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | mysql $LOCALCONNECTION $db
fi
done
echo "OK!"
@benzntech

This comment has been minimized.

Copy link

@benzntech benzntech commented Jan 19, 2016

Have any one tested this code. And find successful..

@ecdundar

This comment has been minimized.

Copy link
Owner Author

@ecdundar ecdundar commented Feb 4, 2016

I have already tested again :)

@mudassarhanifchohan

This comment has been minimized.

Copy link

@mudassarhanifchohan mudassarhanifchohan commented May 26, 2017

what is your openion for this code??

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.