xtrabackup备份

#!/bin/bash

#xtrabackup流式压缩热备份
#预配置SSH免密码登陆并确认通过

#远程IP及账户等基础信息
REMOTE_HOST="192.168.31.101"
REMOTE_HOST_SSH_PORT="22"
REMOTE_DB_USERNAME="root"
REMOTE_DB_PASSWORD="szyg@123456"
REMOTE_DB_PORT="3306"

#本地IP及账户等基础信息
LOCAL_HOST="192.168.31.100"
LOCAL_HOST_SSH_PORT="22"
LOCAL_DB_USERNAME="root"
LOCAL_DB_PASSWORD="szyg@123456"
LOCAL_DB_PORT="3306"

#本地数据库存放路径
MYSQL_PATH="/data/mysql/data"

#本地xtrabackup流式备份数据库暂存路径
BACKUP_PATH="/data/mysql/backup"

#异地xtrabackup流式备份数据库存放路径
REMOTE_BACKUP_PATH="/data/mysql/backup"

###在远程服务器流式备份表空间，传送本地服务器

#使用tar格式备份
#innobackupex --user=${LOCAL_DB_USERNAME} --password="${LOCAL_DB_PASSWORD}" --stream=tar --extra-lsndir=${BACKUP_PATH}/chkpoint /data/mysql/backup/ | gzip | ssh -p${REMOTE_HOST_SSH_PORT} ${REMOTE_HOST} "cat - > ${REMOTE_BACKUP_PATH}/$(date +%F)_full.tar.gz"

#使用xbstream格式备份
innobackupex --user=${LOCAL_DB_USERNAME} --password="${LOCAL_DB_PASSWORD}" --stream=xbstream --extra-lsndir=${BACKUP_PATH}/chkpoint /data/mysql/backup/ | gzip | ssh -p${REMOTE_HOST_SSH_PORT} ${REMOTE_HOST} "cat - > ${REMOTE_BACKUP_PATH}/$(date +%F)_full.xbstream.gz"

增量：

innobackupex --user=${LOCAL_DB_USERNAME} --password="${LOCAL_DB_PASSWORD}"  --incremental --stream=xbstream --extra-lsndir=${BACKUP_PATH}/chkpoint --incremental-basedir=${BACKUP_PATH}/chkpoint /data/mysql/backup/ | gzip | ssh -p${REMOTE_HOST_SSH_PORT} ${REMOTE_HOST} " cat - >  ${REMOTE_BACKUP_PATH}/$(date +%F)_incre.xbstream.gz"