Percona XtraBackup 2.0.7 备份 MySQL 数据

本文将介绍如何使用 Percona XtraBackup 2.0.7 对 MySQL 数据进行备份，并提供还原数据的完整步骤。

一、备份 MySQL 数据

1.1备份方法

Percona XtraBackup 是一款专门用于 MySQL 数据备份的开源工具。它支持全量备份和增量备份，具体操作如下：

innobackupex --user=root --port=3306 --host=127.0.0.1 --defaults-file=/etc/my.cnf --stream=tar /usr/local/backup |gzip > 127.0.0.1_3306.tar.gz

1.2完整语法说明

--user：指定数据库用户，默认为 root。

--port：指定数据库监听地址，默认为 3306。

--host：指定数据库主机地址，默认为 127.0.0.1。

--defaults-file：指定默认配置文件路径，默认为 /etc/my.cnf。

--stream：将备份文件直接输出到标准输出流。

|gzip：对备份文件进行压缩，提升传输效率。

> 127.0.0.1_3306.tar.gz：将压缩后的文件保存为指定文件名。

1.3多实例备份

在多实例环境中，备份命令如下：

innobackupex --user=root --port=3306 --host=127.0.0.1 --defaults-file=/etc/my.cnf --stream=tar /usr/local/hero_all_backup/

1.4常用参数

--socket：指定 socket 文件路径。

--default-file：指定默认配置文件路径。

--apply-log：在备份目录中启动 MySQL 服务。

--copy-back：从备份目录中拷贝数据、索引和日志。

二、还原 MySQL 数据

2.1停止 MySQL 服务

/etc/init.d/mysqld stop

2.2备份数据目录

mv /var/lib/mysql /var/lib/mysql_bak

2.3创建新数据目录

mkdir -p /var/lib/mysql

2.4使用 `--apply-log` 进行还原

innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log /home/tank/backup/2014-09-18_16-35-12

2.5使用 `--copy-back` 进行数据恢复

innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /home/tank/backup/2014-09-18_16-35-12

2.6设置文件权限

chown -R mysql.mysql /var/lib/mysql

2.7启动 MySQL 服务

/etc/init.d/mysqld start

三、脚本自动化

提供一个自动化脚本，支持全量备份和增量备份：

#!/bin/bash# 脚本版本 V1.1# 作者: jchen#备份天数：3天#等待时间：60秒source /etc/profileDBIP="127.0.0.1"DBUSER="dbaroot"DBPWD="root"DATE=`date +%F_%H-%M`Hour=$(date +%H)PORT=$(netstat -tunlp | grep mysql | awk '{print $4}' | awk -F: '{print $2}')MYCNF="/etc/my.cnf"LOG_FILE="/home/msbakscript/logs/xtrabackup.log"BACKUP_PATH="/usr/local/hero_all_backup"MYSQL_LOG="/home/msbakscript/logs/mysql.log"INNOBACKUPEX="/usr/bin/innobackupex"bak_log="/home/msbakscript/logs/mysqlbak.log"MYSQL_PATH="/usr/local/mysql/var"HOST=$(awk -F= '/IPADDR=/ {print $2}' /etc/sysconfig/network-scripts/ifcfg-eth1)CHECKPOINT=$(awk '/to_lsn/ {print $3}' /usr/local/hero_all_backup/xtrabackup_checkpoints 2>/dev/null)all_bak="{$HOST}_{$DATE}"increase_bak="{$HOST}_{$DATE}-increase"if [ $# -eq 0 || $# -eq 1 || $# -eq 2 ]; then    echo "OK" > /dev/nullelse    echo "sh $0 allbakfile 或者 sh $0 allbakfile incbakfile"    exit 1fi[ ! -d ${BACKUP_PATH} ] && mkdir -p ${BACKUP_PATH}[ ! -d /home/msbakscript/logs ] && mkdir -p /home/msbakscript/logsif [ -z "`/sbin/pidof -s mysqld`" ]; then    echo "mysqld is not running"    exit 1fiif [ `rpm -qa | grep -c xtrabackup-0.9-2.rhel4` -eq 1 ]; then    rpm -e xtrabackup-0.9-2.rhel4fiif [ ! -f /usr/bin/innobackupex ]; then    echo "xtrabackup is not installed"    rpm -i --nodeps http://122.228.194.133:8080/percona-xtrabackup-2.0.7-552.rhel5.x86_64.rpmelif [ -f /usr/bin/innobackupex ] && [ `ps -ef | grep -v grep | grep -c /usr/bin/innobackupex` -ge 1 ]; then    echo "xtrabackup process already exist."    exit 1ficomplete_bak() {    cd ${BACKUP_PATH}    ${INNOBACKUPEX} --user=${DBUSER} --password=${DBPWD} --port=${PORT} --host=${DBIP} --defaults-file=${MYCNF} --stream=tar ${BACKUP_PATH}/ 2>>${LOG_FILE} |gzip >${BACKUP_PATH}/${all_bak}_${PORT}.tar.gz    if [ $? -eq 0 ] && [ `tail -10 "${LOG_FILE}" | grep -ic "completed OK"` -eq 1 ]; then        tar zxvfi ${BACKUP_PATH}/${all_bak}_${PORT}.tar.gz xtrabackup_checkpoints        echo "${all_bak}_${PORT}.tar.gz backup successed" >>${bak_log}        echo -en "ok\n${all_bak}_${PORT}.tar.gz" >>${MYSQL_LOG}        sh /home/msbakscript/redis_backup.sh        chown nobody.nobody *     else        echo "${all_bak}_${PORT}.tar.gz backup failed" >>${bak_log}        echo -e "failure\n${all_bak}_${PORT}.tar.gz" >>${MYSQL_LOG}        exit 1    fi}complete_bakincrease_bak() {    cd ${BACKUP_PATH}    if [ ! -f ${BACKUP_PATH}/xtrabackup_checkpoints -o -z ${BACKUP_PATH}/xtrabackup_checkpoints ]; then        echo "xtrabackup_checkpoints does not exist" >>${LOG_FILE}        complete_bak        exit 0    fi    ${INNOBACKUPEX} --user=${DBUSER} --password=${DBPWD} --port=${PORT} --host=${DBIP} --defaults-file=${MYCNF} --no-timestamp --incremental --throttle=30 ${BACKUP_PATH}/${increase_bak}_${PORT} --incremental-lsn=${CHECKPOINT} >>${LOG_FILE} 2>&1    if [ $? -eq 0 ] && [ `tail -10 "${LOG_FILE}" | grep -ic "completed OK"` -eq 1 ]; then        tar zcfi ${increase_bak}_${PORT}.tar.gz ${increase_bak}/        rm -rf ${increase_bak}_${PORT}        echo "${increase_bak}_${PORT}.tar.gz backup successed" >>${bak_log}        echo -e "ok\n${increase_bak}_${PORT}.tar.gz" >>${MYSQL_LOG}    else        echo "${increase_bak}_${PORT} backup failed" >>${bak_log}        echo -e "failure\n${increase_bak}_${PORT}.tar.gz" >>${MYSQL_LOG}        exit 1    fi}increase_bakdel_bakfile() {    for dbfile in `find "${BACKUP_PATH}/" -name "[0-9]*.tar.gz" -type f -mtime +${DAY}`; do        rm -f ${dbfile}    done}del_bakfilebakmysql() {    for i in ${exclude_time[@]}; do        [ "${Hour}" -eq "$i" ] && exit 0    done    if [ "${Hour}" -eq "${default_time}" ]; then        complete_bak    elif [ "${Hour}" -ne "${default_time}" -a ${open_increase_bak} -eq 0 ]; then        increase_bak    else        exit 0    fi}bakmysql() {    case $# in    0)        bakmysql        del_bakfile        ;;    1)        if [ "$1" = "all" ]; then            complete_bak            del_bakfile        elif [ "$1" = "inc" ]; then            increase_bak            del_bakfile        else            echo "sh $0 all 或者 sh $0 inc" && exit 0        fi        ;;    esac}# 执行备份bakmysql

转载地址：http://kebfk.baihongyu.com/

你可能感兴趣的文章

mysqldump 参数--lock-tables浅析

查看>>

mysqldump 导出中文乱码

查看>>

mysqldump 导出数据库中每张表的前n条

查看>>

mysqldump: Got error: 1044: Access denied for user ‘xx’@’xx’ to database ‘xx’ when using LOCK TABLES

查看>>

mysqldump备份时忽略某些表

查看>>