MySQL备份与恢复

发表于 2024-06-18 更新于 2024-11-20 分类于 Database 本文字数： 981 阅读时长 ≈ 4 分钟

一、逻辑备份/恢复

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

优点	缺点
可以跨平台和MySQL版本	备份速度慢, 占用较大的cpu和I/O

1.1 逻辑备份

#!/bin/bash

MYSQLDUMP=/data/mysql/bin/mysqldump
MYSQL=/data/mysql/bin/mysql
DBNAME=(db1 db2 db3 db4)              ## 输入要备份的数据库，空格隔开。
DATE=$(date -d "now" +%Y-%m-%d)
BACKUP_ROOT=/data/mysql_logical_bak
BACKUP_DIR=${BACKUP_ROOT}/${DATE}
LOGFILE=${BACKUP_DIR}/backup.log
HOST=localhost
PORT=3306
USER=root
PASSWD=$password
EXPIRE_DAYS=30

function fun_mkdir(){
    mkdir -p ${BACKUP_DIR}
}

function fun_lock_tables(){
    echo "Locking all tables..."
    $MYSQL -h${HOST} -P${PORT} -u${USER} -p${PASSWD} -e "FLUSH TABLES WITH READ LOCK;"
}

function fun_unlock_tables(){
    echo "Unlocking all tables..."
    $MYSQL -h${HOST} -P${PORT} -u${USER} -p${PASSWD} -e "UNLOCK TABLES;"
}   

function fun_backup(){
    for DB in "${DBNAME[@]}"
    do
        echo "Backing up $DB..."
        $MYSQLDUMP -h${HOST} -P${PORT} -u${USER} -p${PASSWD} \
            --compress --databases --routines --quick --set-gtid-purged=OFF \
            ${DB} | gzip > ${BACKUP_DIR}/${DB}.sql.gz
        if [[ $? == 0 ]]; then
            echo "$(date -d "now" "+%Y-%m-%d %H:%M:%S") - Database: ${DB} backup successful." >> $LOGFILE
        else
            echo "$(date -d "now" "+%Y-%m-%d %H:%M:%S") - Database: ${DB} backup failed!" >> $LOGFILE
        fi
    done
}

function fun_cleanup(){
    echo "Cleaning up old backups..."
    find ${BACKUP_ROOT} -type d -mtime +${EXPIRE_DAYS} -exec rm -rf {} +
}

source ~/.bash_profile && source /etc/profile

if [ -z $BACKUP_ROOT ]; then
    echo "BACKUP_ROOT is not set" && exit 1
fi

fun_mkdir
fun_lock_tables
fun_backup
fun_unlock_tables
fun_cleanup

echo "Backup process completed."

# Uncomment the following line to enable remote backup syncing
###rsync -avrz --delete $BACKUP_ROOT/ root@$REMOTEHOST:$REMOTELOCATION

1.2 逻辑恢复

gunzip < ${BACKUP_DIR}/${DB_NAME}.sql.gz | mysql -h${HOST} -P${PORT} -u${USER} -p${PASSWD}

二、物理备份/恢复

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

DATE=$(date -d "now" +%Y-%m-%d)
CONFIG_FILE="/etc/my.cnf"
BACKUP_ROOT="/data/mysql_physical_bak"
BACKUP_DIR="${BACKUP_ROOT}/${DATE}"
LOGFILE="${BACKUP_DIR}/backup.log"
SOCKET="/usr/local/mysql/mysql.sock"
USER="root"
PASSWD="$password"
PORT=3306
EXPIRE_DAYS=30

function fun_mkdir() {
    mkdir -p "${BACKUP_DIR}"
}

function fun_backup() {
    innobackupex --defaults-file="${CONFIG_FILE}" \
                 --user="${USER}" \
                 --password="${PASSWD}" \
                 --port="${PORT}" \
                 --socket="${SOCKET}" \
                 --compress \
                 --parallel=4 \
                 "${BACKUP_DIR}" > "${LOGFILE}" 2>&1
}

function fun_cleanup_old_backups() {
    find "${BACKUP_ROOT}" -mindepth 1 -maxdepth 1 -type d -mtime "+${EXPIRE_DAYS}" -exec rm -rf {} \;
}

source ~/.bash_profile && source /etc/profile

if [ -z "${BACKUP_ROOT}" ]; then
    echo "BACKUP_ROOT is not set" && exit 1
fi

fun_mkdir
fun_backup
fun_cleanup_old_backups

echo "Backup process completed. Check ${LOGFILE} for details."

# Uncomment the following line to enable remote backup syncing
# rsync -avrz --delete "${BACKUP_ROOT}/" "root@${REMOTE_HOST}:${REMOTE_LOCATION}"

2.2 物理恢复

假设场景：从主库的物理备份中创建一个从库，并开启主从复制。
假设物理备份的位置： /data/mysql/backup-physical
主从数据库的数据目录： /data/mysql/data

解压备份
这个步骤会在每个压缩文件旁边创建一个解压后的版本
innobackupex --decompress /data/mysql/backup-physical/ > decompress.log 2>&1
恢复到MySQL数据目录
默认会根据 /etc/my.cnf 恢复, 也可以使用 --datadir="your_data_path"
--copy-back :复制数据到目标目录下，当前备份数据不删除
--move-back :移动数据到目标目录下，当前数据会删除（适合存储空间不大的情况）
xtrabackup --copy-back --target-dir=/data/mysql/backup-physical/

从库启动之前，如果主库发生变化，需要将主库备份时间节点之后的bin-log文件复制到从库的数据目录
修改MySQL数据目录的权限
chown -R mysql:mysql /data/mysql/data
启动MySQL
systemctl start mysqld

配置从库

mysql> 
mysql> change master to \
    -> master_host='16.167.13.137',
    -> master_user='repl',
    -> master_password='$yourpassword',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
                                                                                                                                                                      
mysql> 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

2.3 常见问题

主从同步的时候提示： Could not execute Update rows event on table xxxxxx: can't find record in xxxxxx
原因：从库启动之前主库数据发生变动
解决：发生数据变动期间的 bin-log 文件复制到从库的数据目录

三、增量备份

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

注意这里的 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)

一、 逻辑备份/恢复