MySQL备份与恢复

说明

一、 逻辑备份/恢复

基于MySQL自带的mysqldump进行逻辑备份和恢复

优点：
缺点：

1.1 逻辑备份

#!/bin/bash

############## environment ##############
MYSQLDUMP=/data/mysql/bin/mysqldump
DBNAME=(db_web_app gisqbpm log logdb mysql)  ## 输入要备份的数据库， 空格隔开。
BACKUP=/data/backup/full_by_databases
LOGFILE=${BACKUP}/log/mysql_backup.log
MYSQLIP=localhost
MYSQLPORT=3306
BKUSER=root
PASSWD=$password
DATE=`date -d "now" +%Y%m%d`

############## make directory ##############
function fun_mkdir(){
if [ ! -d ${BACKUP}/data/${DATE} ]; then
    mkdir -p ${BACKUP}/data/${DATE}
fi
if [ ! -d ${BACKUP}/log ]; then
    mkdir -p ${BACKUP}/log
fi
}

############## backup mysql database ##############
function fun_backup(){
for DB in ${DBNAME[*]}
do
    $MYSQLDUMP -h${MYSQLIP} -P${MYSQLPORT} -u${BKUSER} -p${PASSWD} -C -B -R -q --set-gtid-purged=OFF ${DB} | gzip > $BACKUP/data/${DATE}/${DB}.sql.gz

    if [[ $? == 0 ]]; then
        echo `date -d "now" "+%Y-%m-%d %H:%M:%S"` >> $LOGFILE
        echo "DataBase ${DB} Backup Success!" >> $LOGFILE
        echo -e "\n========================" >> $LOGFILE
    else
        echo `date -d "now" "+%Y-%m-%d %H:%M:%S"` >> $LOGFILE
        echo "DataBase ${DB} Backup Fail!" >> $LOGFILE
        echo -e "\n========================" >> $LOGFILE
    fi
done
}

############## execute the function ##############
source ~/.bash_profile  && source /etc/profile
if [ -z $BACKUP ]; then
    echo "BACKUP is NULL" && exit 1
fi

fun_mkdir
fun_backup

############## delete 7 days ago backup ##############
if [ -d ${BACKUP}/data ]; then
    find ${BACKUP}/data -mtime +30 -type d -exec rm -rf {} \;
fi

###rsync -avrz --delete $BACKUP/data/ root@$REMOTEHOST:$REMOTELOCATION

1.2 逻辑恢复

二、 物理备份

XtraBackup 是由 Percona 提供的开源备份工具，主要用于 MySQL、MariaDB 和 Percona Server 数据库的备份。它可以为 InnoDB 和 XtraDB 存储引擎提供热备份功能，这意味着可以在数据库继续运行、读写数据的情况下进行备份，而不会影响应用程序的正常使用。

2.0 安装 XtraBackup

yum install epel-release -y && yum install percona-xtrabackup -y

2.1 物理备份

#!/bin/bash

############## environment ##############
BACKUP=/nfs/mysql_backup_physical
LOGFILE=${BACKUP}/log/mysql_backup.log
BKUSER=root
PASSWD=$yourpassword
SOCK=/usr/local/mysql/mysql.sock
PORT=3306

############## make directory ############
function fun_mkdir(){
if [ ! -d ${BACKUP}/data/ ]; then
    mkdir -p ${BACKUP}/data/
fi
if [ ! -d ${BACKUP}/log ]; then
    mkdir -p ${BACKUP}/log
fi
}

############## backup mysql database ########
function fun_backup(){
innobackupex --defaults-file=/etc/my.cnf --user=${BKUSER} --password=${PASSWD} --port=${PORT} --socket=${SOCK} --compress --parallel=4 ${BACKUP}/data/ 1>${LOGFILE} 2>&1
}

############## execute the function ##########
source ~/.bash_profile  && source /etc/profile
if [ -z $BACKUP ]; then
    echo "BACKUP is NULL" && exit 1
fi

fun_mkdir
fun_backup

############## delete 7 days ago backup #########
if [ -d ${BACKUP}/data ]; then
    find ${BACKUP}/data -mtime +2 -type d -exec rm -rf {} \;
fi

###rsync -avrz --delete $BACKUP/data/ root@$REMOTEHOST:$REMOTELOCATION

2.2 物理恢复

将物理备份的文件复制到MySQL的数据目录

修改权限：

chown -R mysql:mysql /data/mysql/data

解压缩

innobackupex –decompress /backup/mysql_restore/data/

完整备份
增量备份

在线备份
离线备份

本地备份
远程备份

查看某个用户拥有某个数据库的权限

注意这里的 127.0.0.1和localhost不一样

mysql> show grants for 'root'@'127.0.0.1';
+-----------------------------------------------------------------------------+
| Grants for root@127.0.0.1                                                   |
+-----------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)