MySQL 导出与导入

mysqldump -uroot -p --databases isvee > isvee.sql
mysql -uroot -p --database isvee < isvee.sql

mysqldump -uroot -p --databases isvee | gzip > isvee.sql.gz
解压:
gzip -d isvee.sql.gz

scp name@52.52.xxx:/home/user/isvee.sql ~
--opt --single-transaction --master-data=2

https://blog.csdn.net/weiliu1463/article/details/25305371
https://blog.csdn.net/cug_jiang126com/article/details/49824471



yum install sshpass

Add target IP to ssh known hosts:
ssh sshuser@123.123.123.123

mkdir /home/mysqlbak
chmod -R 777 /home/mysqlbak

vi /home/mysqlbackup.sh
#!/bin/bash

backupdir=/home/mysqlbak
time=`date +%Y%m%d-%H%M%S`

/usr/bin/mysqldump -h127.1.2.3 -uroot -p'passwdord' --opt --single-transaction --set-gtid-purged=OFF --databases dbname | gzip > $backupdir/dbname_$time.sql.gz

sshpass -p sshpasswdord scp -P 22 $backupdir/dbname_$time.sql.gz sshuser@123.123.123.123:/home/sshuser/


chmod 711 /home/mysqlbackup.sh
chown root /home/mysqlbackup.sh

Add to crontab:
vi /etc/crontab
0 3 * * * root /home/mysqlbackup.sh

service crond reload
自动恢复:
vi /home/mysqlimport.sh

#!/bin/bash
cd /home/mysqlbackups/
lastfile=`ls -lt . | grep 'db_pro_*' | head -n 1 | awk '{print $9}'`
gzip -d $lastfile
ungzipedfile=${lastfile: 0: 35}
mysql -uroot -p'xxxxxx' --database db_pro < /home/mysqlbackups/$ungzipedfile


chmod +x /home/mysqlimport.sh

Add to crontab:
vi /etc/crontab
0 4 * * * root /home/mysqlimport.sh

service crond reload