MySQL backup and restore

backup and restoreoverview

datalibrarybackup and restore is datalibrarymanagement important 组成部分, 用于保障datasecurity, 防止dataloss, 并 in 发生灾难时able to fast 速restore业务.

for what需要backup？

• dataloss防护: 防止硬件failure, 人 for error, 病毒攻击etc.导致 dataloss
• 灾难restore: in 发生自然灾害, system崩溃etc.灾难时able to fast 速restore
• datamigration: 方便 in 不同environment之间migrationdata
• test and Development: for test and Developmentenvironmentproviding真实 data集
• compliance要求: 满足行业法规 and 企业政策 要求

backup策略 目标

• integrity: 确保backuppackage含所 has 必要 data
• consistency: 确保backupdata consistency, 避免data损 bad
• reliability: 确保backupable to成功restore
• efficiency: 最 small 化backup for systemperformance 影响
• 可restore性: 确保 in 需要时able to fast 速, 准确地restoredata

backupclass型

按backup方式classification

按backup范围classification

按backupstatusclassification

• 热backup: in datalibraryrun时forbackup, 不影响正常operation
• 温backup: in datalibrarylock定status under forbackup, 可能影响部分operation
• 冷backup: in datalibrary关闭status under forbackup, 完全不影响operation

backupmethod

1. usingmysqldumptool

mysqldump is MySQL自带 逻辑backuptool, 适用于in small 型datalibrary.

basic用法

-- backup整个datalibrary
mysqldump -u username -p database_name > backup.sql

-- backup many 个datalibrary
mysqldump -u username -p --databases db1 db2 db3 > backup.sql

-- backup所 has datalibrary
mysqldump -u username -p --all-databases > backup.sql

-- backupspecific表
mysqldump -u username -p database_name table1 table2 > backup.sql

-- 压缩backup
mysqldump -u username -p database_name | gzip > backup.sql.gz

-- 带时间戳 backup
mysqldump -u username -p database_name > backup_$(date +%Y%m%d_%H%M%S).sql

advanced选项

-- package含store过程 and function
mysqldump -u username -p --routines database_name > backup.sql

-- package含event
mysqldump -u username -p --events database_name > backup.sql

-- package含触发器
mysqldump -u username -p --triggers database_name > backup.sql

-- 禁用 out 键check (加 fast restore速度) 
mysqldump -u username -p --skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset database_name > backup.sql

-- usingtransaction保证consistency
mysqldump -u username -p --single-transaction database_name > backup.sql

2. usingMySQL Enterprise Backup

MySQL Enterprise Backup is MySQL企业版providing 物理backuptool, support热backup and 增量backup.

basic用法

-- 完全backup
mysqlbackup --user=username --password=password --backup-dir=/path/to/backup backup

-- 增量backup
mysqlbackup --user=username --password=password --backup-dir=/path/to/backup --incremental --incremental-base=dir:/path/to/full/backup backup

-- restorebackup
mysqlbackup --user=username --password=password --backup-dir=/path/to/backup --datadir=/path/to/mysql/data copy-back-and-apply-log

3. usingfilesystem级backup

for 于MyISAMstore引擎, 可以直接copydatafileforbackup.

-- 1. lock定表
FLUSH TABLES WITH READ LOCK;

-- 2. 记录二进制log位置
SHOW MASTER STATUS;

-- 3. copydatafile
cp -r /var/lib/mysql /path/to/backup

-- 4. 解lock表
UNLOCK TABLES;

4. using二进制logbackup

二进制log记录了所 has data变更, 可以用于增量backup and 点-in-timerestore.

-- 启用二进制log ( in my.cnfin) 
[mysqld]
log-bin=mysql-bin
server-id=1

-- 查看二进制logfile
SHOW BINARY LOGS;

-- 刷 new 二进制log
FLUSH LOGS;

-- backup二进制log
mysqlbinlog mysql-bin.000001 > binlog_backup.sql

restoremethod

1. usingmysqldumpbackuprestore

-- restore整个datalibrary
mysql -u username -p database_name < backup.sql

--  from 压缩backuprestore
gunzip < backup.sql.gz | mysql -u username -p database_name

-- restorespecific表
mysql -u username -p database_name < table_backup.sql

2. using二进制logrestore

二进制log可以用于点-in-timerestore, restore to specific 时间点 or transaction.

-- 查看二进制log in 容
mysqlbinlog mysql-bin.000001

-- restore to specific时间点
mysqlbinlog --stop-datetime="2024-01-01 12:00:00" mysql-bin.000001 mysql-bin.000002 | mysql -u username -p

-- restore to specific位置
mysqlbinlog --stop-position=12345 mysql-bin.000001 | mysql -u username -p

-- restorespecificdatalibrary
mysqlbinlog --database=database_name mysql-bin.000001 | mysql -u username -p

3. usingfilesystem级restore

-- 1. 停止MySQLservice
systemctl stop mysql

-- 2. backup当 before dataTable of Contents
mv /var/lib/mysql /var/lib/mysql_old

-- 3. restorebackup
cp -r /path/to/backup /var/lib/mysql

-- 4. 设置permission
chown -R mysql:mysql /var/lib/mysql

-- 5. 启动MySQLservice
systemctl start mysql

4. 点-in-timerestore

点-in-timerestore结合了完全backup and 二进制log, 可以restore to 任意时间点.

-- 1. restore完全backup
mysql -u username -p database_name < full_backup.sql

-- 2. application二进制log to specific时间点
mysqlbinlog --start-datetime="2024-01-01 00:00:00" --stop-datetime="2024-01-01 11:59:59" mysql-bin.000001 mysql-bin.000002 | mysql -u username -p database_name

backup策略

制定backup策略 考虑因素

• data important 性: 不同级别 data需要不同 backup策略
• data量: data量 big small 影响backup时间 and store空间
• 业务requirements: 考虑RTO (restore时间目标) and RPO (restore点目标)
• systemperformance: 避免backup for producesystemperformance造成严重影响
• store空间: 合理planningbackupstore空间
• backup频率: 根据data变更频率确定backup频率

commonbackup策略

策略1: 完全backup + 增量backup

• 周日: 执行完全backup
• 周一至周六: 执行增量backup
• 优点: 节省store空间, backup速度 fast
• 缺点: restore过程 complex , 需要先restore完全backup, 再逐个restore增量backup

策略2: 完全backup + diffbackup

• 周日: 执行完全backup
• 周一至周六: 执行diffbackup
• 优点: restore过程相 for simple , 只需要restore完全backup and 最 after 一次diffbackup
• 缺点: diffbackupfile会逐渐增 big

策略3: 定期完全backup

• 每天: 执行完全backup
• 优点: restore simple , 只需要一个backupfile
• 缺点: 占用store空间 big , backup时间 long

backupstore策略

• 本地store: fast 速访问, 但存 in 单点failurerisk
• 异地store: 防止本地灾难, improvingdatasecurity性
• 云store: flexiblescale, high availability性
• many replica: 保留 many 个backupreplica, improvingreliability
• backup轮换: 定期轮换backup, 避免store空间耗尽

backupverification and monitor

backupverification

定期verificationbackup has 效性 is backup策略 important 组成部分.

verificationmethod

• file big small check: 确保backupfile big small 合理
• fileintegritycheck: usingmd5sum or sha1sumverificationfileintegrity
• restoretest: 定期 in testenvironmentinrestorebackup
• dataconsistencycheck: verificationrestore after dataconsistency

verification脚本example

#!/bin/bash

# backupverification脚本
BACKUP_FILE="backup.sql.gz"
TEST_DB="test_restore"

# checkfile存 in 性
if [ ! -f "$BACKUP_FILE" ]; then
    echo "backupfile不存 in "
    exit 1
fi

# checkfile big  small 
if [ $(stat -c %s "$BACKUP_FILE") -lt 1000 ]; then
    echo "backupfile可能不完整"
    exit 1
fi

# creationtestdatalibrary
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS $TEST_DB"

# 尝试restorebackup
gunzip < "$BACKUP_FILE" | mysql -u root -p "$TEST_DB"

if [ $? -eq 0 ]; then
    echo "backuprestore成功"
else
    echo "backuprestore失败"
    exit 1
fi

# cleantestdatalibrary
mysql -u root -p -e "DROP DATABASE $TEST_DB"

echo "backupverificationcompletion"

backupmonitor

• backupstatusmonitor: monitorbackup is 否成功completion
• backup时间monitor: monitorbackup执行时间, 及时发现exception
• store空间monitor: monitorbackupstore空间usingcircumstances
• 告警mechanism: 设置backup失败 or exception 告警mechanism
• log记录: 详细记录backup过程 and 结果

灾难restore

灾难restore计划

制定详细 灾难restore计划 is 保障业务连续性 关键.

灾难restore计划 in 容

• 灾难class型定义: 明确可能发生 灾难class型
• restore流程: 详细 restore步骤 and 责任分工
• restore时间目标: 明确不同级别 灾难restore时间要求
• restore点目标: 明确dataloss 可接受范围
• test计划: 定期test灾难restore计划
• 联系人list: 灾难发生时 联系人及联系方式

灾难restore演练

定期for灾难restore演练 is 确保灾难restore计划 has 效性 important 手段.

演练步骤

1. 计划准备: 制定详细 演练计划, including演练目标, 范围, 步骤 and 时间安排
2. environment准备: 准备演练environment, 确保 and produceenvironment隔离
3. 演练执行: 按照灾难restore计划执行演练
4. 结果assessment: assessment演练结果, 记录发现 issues
5. 计划update: 根据演练结果update灾难restore计划

backupbest practices

一般best practices

• 定期backup: 根据业务requirements制定合理 backup计划
• how heavybackup: 保留 many 个backupreplica, store in 不同位置
• backupverification: 定期verificationbackup has 效性
• encryptionbackup: for 敏感data backupforencryption
• documentation化: 详细记录backup策略 and restore流程
• automation: using脚本automationbackup过程
• monitor: monitorbackup过程, 及时发现并解决issues
• testrestore: 定期testrestore过程, 确保able to成功restore

performanceoptimizationbest practices

• 选择合适 backup时间: in systemload较 low 时间段执行backup
• using压缩: 压缩backupfile, reducingstore空间 and 传输时间
• using增量backup: for 于 big 型datalibrary, using增量backupreducingbackup时间
• optimizationmysqldumpparameter: using合适 parameterimprovingbackup速度
• usingparallelbackup: for 于 big 型datalibrary, 考虑usingparallelbackuptool
• 调整innodb_buffer_pool_size: 适当调整缓冲池 big small , improvingbackup and restore速度

securitybest practices

• 限制backupfile访问: 确保backupfile只能被authorization人员访问
• using专用backupaccount: creation专用 backupaccount, 只授予必要 permission
• network传输encryption: in network传输backupfile时usingencryption
• 定期轮换backup: 避免backupfile long 期store导致 securityrisk
• backupdelete策略: 制定合理 backupdelete策略, 符合compliance要求

实践case

#!/bin/bash

# MySQL自动backup脚本

# configurationinformation
USER="root"
PASSWORD="your_password"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="$BACKUP_DIR/backup_$DATE.log"

# creationbackupTable of Contents
mkdir -p "$BACKUP_DIR"

# 开始backup
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 开始backupMySQLdatalibrary" >> "$LOG_FILE"

# backup所 has datalibrary
mysqldump -u "$USER" -p"$PASSWORD" --all-databases --routines --events --triggers | gzip > "$BACKUP_DIR/full_backup_$DATE.sql.gz"

if [ $? -eq 0 ]; then
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] backup成功" >> "$LOG_FILE"
else
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] backup失败" >> "$LOG_FILE"
    exit 1
fi

# verificationbackupfile
echo "[$(date +'%Y-%m-%d %H:%M:%S')] verificationbackupfile" >> "$LOG_FILE"

if [ -f "$BACKUP_DIR/full_backup_$DATE.sql.gz" ] && [ $(stat -c %s "$BACKUP_DIR/full_backup_$DATE.sql.gz") -gt 1000 ]; then
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] backupfileverification成功" >> "$LOG_FILE"
else
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] backupfileverification失败" >> "$LOG_FILE"
    exit 1
fi

# clean old backup (保留最近7天 backup) 
echo "[$(date +'%Y-%m-%d %H:%M:%S')] clean old backup" >> "$LOG_FILE"
find "$BACKUP_DIR" -name "full_backup_*.sql.gz" -mtime +7 -delete
find "$BACKUP_DIR" -name "backup_*.log" -mtime +7 -delete

echo "[$(date +'%Y-%m-%d %H:%M:%S')] backupcompletion" >> "$LOG_FILE"

# 发送backupnotification (可选) 
# echo "MySQLbackupcompletion" | mail -s "MySQLbackup报告" admin@example.com

 exit 0

-- 步骤1: restore完全backup
mysql -u root -p database_name < full_backup.sql

-- 步骤2: application二进制log to specific时间点
mysqlbinlog --start-datetime="2024-01-01 00:00:00" --stop-datetime="2024-01-01 10:00:00" mysql-bin.000001 mysql-bin.000002 | mysql -u root -p database_name

--  or 者using位置点restore
mysqlbinlog --start-position=107 --stop-position=954 mysql-bin.000001 | mysql -u root -p database_name

互动练习

#!/bin/bash

# configurationinformation
USER="root"
PASSWORD="your_password"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="$BACKUP_DIR/backup_$DATE.log"

# creationbackupTable of Contents
mkdir -p "$BACKUP_DIR"

# 开始backup
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 开始backupMySQLdatalibrary" >> "$LOG_FILE"

# backup所 has datalibrary
mysqldump -u "$USER" -p"$PASSWORD" --all-databases --routines --events --triggers | gzip > "$BACKUP_DIR/full_backup_$DATE.sql.gz"

if [ $? -eq 0 ]; then
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] backup成功" >> "$LOG_FILE"
else
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] backup失败" >> "$LOG_FILE"
    exit 1
fi

# clean old backup (保留最近30天 backup) 
echo "[$(date +'%Y-%m-%d %H:%M:%S')] clean old backup" >> "$LOG_FILE"
find "$BACKUP_DIR" -name "full_backup_*.sql.gz" -mtime +30 -delete
find "$BACKUP_DIR" -name "backup_*.log" -mtime +30 -delete

echo "[$(date +'%Y-%m-%d %H:%M:%S')] backupcompletion" >> "$LOG_FILE"

# 添加 to crontab
# 0 2 * * * /path/to/backup_script.sh

backup策略建议: 

1. 完全backup: 
   - 频率: 每周日凌晨2点执行一次完全backup
   - 方式: usingmysqldumpfor逻辑backup, 压缩store

2. 增量backup: 
   - 频率: 每天 (除周日) 凌晨2点执行一次增量backup
   - 方式: using二进制logfor增量backup

3. diffbackup: 
   - 频率: 每 small 时执行一次diffbackup
   - 方式: usingmysqldumpbackup自 on 次完全backup以来 变更

4. backupstore: 
   - 本地store: 保留最近7天 backup
   - 异地store: 保留最近30天 backup
   - 云store: 保留最近90天 backup

5. verification策略: 
   - 每天verification增量backup
   - 每周verification完全backup
   - 每月 in testenvironmentinfor完整restoretest

6. 灾难restore计划: 
   - 建立详细 灾难restoredocumentation
   - 每季度for一次灾难restore演练
   - 确保backupfile可 in 15分钟 in 访问

此策略平衡了backup频率, store空间 and restore速度, 满足RTO and RPO要求.