ssh-dump-mysql-to-local.sh

ssh-dump-mysql-to-local.sh

# 使用 ssh 下载远程服务器上的 mysql 数据库

SSH_HOST=remote_ssh_host
SSH_USER=root
SSH_BIN="ssh $SSH_USER@$SSH_HOST"

REMOTE_DATABASE=remote_db_name
REMOTE_HOST=remote_host_ip
REMOTE_PORT=3306
REMOTE_USER=root
REMOTE_PASSWD=root
REMOTE_MYSQL_BIN="/usr/bin/mysql -u$REMOTE_USER -p$REMOTE_PASSWD -h$REMOTE_HOST -P$REMOTE_PORT"
REMOTE_MYSQLDUMP_BIN="/usr/bin/mysqldump -u$REMOTE_USER -p'$REMOTE_PASSWD' -h$REMOTE_HOST -P$REMOTE_PORT"
REMOTE_MYSQLDUMP_OPTS="--add-drop-table --extended-insert --quick --disable-keys --single-transaction --skip-comments"

LOCAL_DATABASE=local_db_name
LOCAL_HOST=127.0.0.1
LOCAL_PORT=3306
LOCAL_USER=root
LOCAL_PASSWD=root
LOCAL_MYSQL_BIN="$(which mysql) -u$LOCAL_USER -p$LOCAL_PASSWD -h$LOCAL_HOST -P$LOCAL_PORT"

tables=$($SSH_BIN "$REMOTE_MYSQL_BIN -e 'use $REMOTE_DATABASE; show tables;' | tail -n +2")

for table in ${tables[@]}
do
    echo "dumping $REMOTE_DATABASE.$table ..."
    $SSH_BIN "$REMOTE_MYSQLDUMP_BIN $REMOTE_MYSQLDUMP_OPTS $REMOTE_DATABASE $table" | $LOCAL_MYSQL_BIN $LOCAL_DATABASE
done