mysql主从数据同步和远程备份恢复实操

主库备份
xtrabackup --backup --target-dir=/data/full/ --user=root --password=123456 --socket=/tmp/mysql.sock --no-server-version-check
关注点
MySQL binlog position: filename 'mysql-bin.000034', position '156'
210726 23:10:28 [00] Writing /data/full/backup-my.cnf
备库恢复
xtrabackup  --target-dir=/data/full/ -uroot -p123456 --database=full --copy-back --datadir=/data/mysql

stop slave;
 CHANGE MASTER TO
MASTER_HOST='192.168.1.88',
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000034',
MASTER_LOG_POS=156;
start slave;
//这时候就应可以了

远程备份mysql
mysqldump -h 192.168.1.88 -u root -P 3306 -p --opt --default-character-set=utf8 --triggers --hex-blob --all-databases=TRUE > /tmp/db_name.sql
mysqldump -h [$Host] -u [$User] -P [$Port] -p --opt --default-character-set=utf8 --triggers --hex-blob [$Database_name] > /tmp/db_name.sql

恢复到本地
mysql -h [$Host] -u [$User] -P [$Port] -p [$Database_name] < /tmp/db_name.sql