DB Import & Export: Difference between revisions
Jump to navigation
Jump to search
| Line 7: | Line 7: | ||
<quickgv name="syncdb" theme="sakura"> | <quickgv name="syncdb" theme="sakura"> | ||
rankdir=TB; | rankdir=TB; | ||
newrank=true; | |||
subgraph group1 { | subgraph group1 { | ||
A [label="Remote MariaDB"]; | rank=same; | ||
D [label="Local MariaDB"]; | A [label="Remote MariaDB"]; | ||
D [label="Local MariaDB"]; | |||
} | } | ||
subgraph group2 { | subgraph group2 { | ||
B [label="Tunnel Port"]; | rank=same; | ||
C [label="SQL File"]; | B [label="Tunnel Port"]; | ||
C [label="SQL File"]; | |||
} | } | ||
Revision as of 04:15, 5 March 2019
MariaDB
mysqldump -h 127.0.0.1 -P 13306 -u root -p**** db_name > db_exp.sql
Script to sync db
DATESN=`date +'%m%d'`
DBNAME=mydb
DBFILE=~/Downloads/${DBNAME}-${DATESN}.sql
SSHTUN=13306
SSHCTRL=tun.ctrl
SSHCONF=myconf
RPASS=********
LPASS=********
if [ -e $SSHCTRL ]; then
echo "* Close existed tunnel."
ssh -S $SSHCTRL -O exit $SSHCONF 2> /dev/null
fi
echo "* Load data from remote and save as $DBFILE"
ssh -M -S $SSHCTRL -NCfL $SSHTUN:127.0.0.1:3306 $SSHCONF
mysqldump -h 127.0.0.1 -P $SSHTUN -u root -p$RPASS $DBNAME > $DBFILE
ssh -S $SSHCTRL -O exit $SSHCONF 2> /dev/null
echo '* Import data into local'
echo "DROP DATABASE IF EXISTS $DBNAME" | mysql -u root -p$LPASS test
echo "CREATE DATABASE IF NOT EXISTS $DBNAME" | mysql -u root -p$LPASS test
echo "source $DBFILE" | mysql -u root -p$LPASS $DBNAME
echo 'Done.'