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