Backup tables of mysql server and import to the other server

這邊的使用情況是要把 gcp sql 的 import 到 aliyun 去

先在 gcp server 上用mysqldump 備份，然後利用 sshpass (要另外安裝) 指定 password 做 scp 到 aliyun server 上

$ sudo more /var/opt/www/sqldump/dump.sh 
#!/bin/sh
mysqldump --set-gtid-purged=OFF -h sql_server_1_address -u USERNAME -pPASSWORD  dbname tablename1 > /var/opt/sqldump/tablename1.sql
mysqldump --set-gtid-purged=OFF -h sql_server_1_address -u USERNAME -pPASSWORD  dbname tablename2 > /var/opt/sqldump/tablename2.sql

sshpass -p "PASSWORD" scp /var/opt/sqldump/*.sql root@server_2_address:/var/opt/sqlimport/
$ sudo crontab -l
*/10 * * * * sh /var/opt/www/sqldump/dump.sh

如果mysqldump要加上條件篩選的話可以用類似這樣

mysqldump --set-gtid-purged=OFF -h sql_server_1_address -u USERNAME -pPASSWORD  dbname tablename3 -w "test_id tablename3.sql

然後於 aliyun server 上 import

$ more /var/opt/sqlimport/import.sh 
#!/bin/bash
mysql -h sql_server_1_address -u USERNAME -pPASSWORD dbname 

Original link: Phanix's Blog