MySQL 表分区和事件调度器

MySQL 表分区实践及操作

MySQL 分区表的备份与恢复策略

针对分区表的备份和恢复，有多种策略可供选择。以下是详细的操作示例方法：

1. 分区级备份（推荐）

备份单个分区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 创建分区备份表（结构相同但不分区）
CREATE TABLE orders_backup_p202312 LIKE orders;
ALTER TABLE orders_backup_p202312 REMOVE PARTITIONING;

-- 拷贝分区数据到备份表
ALTER TABLE orders EXCHANGE PARTITION p202312 
WITH TABLE orders_backup_p202312;

-- 此时数据在备份表中，原分区为空
-- 将数据插回原分区（可选）
INSERT INTO orders SELECT * FROM orders_backup_p202312;

批量备份多个分区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


#!/bin/bash
# 分区备份脚本

BACKUP_DIR="/backup/mysql_partitions"
DATE=$(date +%Y%m%d)

# 备份指定月份的分区
PARTITIONS="p202312 p202311 p202310"

for PARTITION in $PARTITIONS; do
    # 创建备份表
    mysql -e "CREATE TABLE orders_backup_${PARTITION} LIKE orders;"
    mysql -e "ALTER TABLE orders_backup_${PARTITION} REMOVE PARTITIONING;"
    
    # 交换分区数据到备份表
    mysql -e "ALTER TABLE orders EXCHANGE PARTITION ${PARTITION} WITH TABLE orders_backup_${PARTITION};"
    
    # 导出备份表数据
    mysqldump -u username -p database orders_backup_${PARTITION} > ${BACKUP_DIR}/orders_${PARTITION}_${DATE}.sql
    
    # 将数据插回原分区
    mysql -e "INSERT INTO orders SELECT * FROM orders_backup_${PARTITION};"
    
    # 清理备份表
    mysql -e "DROP TABLE orders_backup_${PARTITION};"
done

2. 物理文件备份

备份分区物理文件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


#!/bin/bash
# 物理文件备份

MYSQL_DATA_DIR="/var/lib/mysql"
BACKUP_DIR="/backup/mysql_physical"
DATABASE="your_database"
TABLE="orders"

# 停止MySQL或锁定表
mysql -e "FLUSH TABLES $DATABASE.$TABLE WITH READ LOCK;"

# 备份分区文件
for PARTITION_FILE in ${MYSQL_DATA_DIR}/${DATABASE}/orders#P#p*.ibd; do
    if [ -f "$PARTITION_FILE" ]; then
        FILENAME=$(basename "$PARTITION_FILE")
        cp "$PARTITION_FILE" "${BACKUP_DIR}/${FILENAME}_$(date +%Y%m%d)"
    fi
done

# 解锁表
mysql -e "UNLOCK TABLES;"

# 备份表结构
mysqldump -d $DATABASE $TABLE > ${BACKUP_DIR}/orders_structure.sql

3. 逻辑备份工具

使用 mysqldump 备份特定分区

1
2
3
4
5
6
7
8


# 备份单个分区
mysqldump -u username -p database orders --where="created_at >= '2023-12-01' AND created_at < '2024-01-01'" > orders_p202312.sql

# 备份表结构+所有分区数据
mysqldump -u username -p database orders > orders_full_backup.sql

# 仅备份表结构
mysqldump -d -u username -p database orders > orders_structure.sql

使用 mysqlpump（MySQL 5.7+）

1
2


# 并行备份，支持进度显示
mysqlpump -u username -p --parallel-schemas=4 database --include-tables=orders > orders_backup.sql

4. 分区数据恢复

从逻辑备份恢复分区

1
2
3
4
5
6
7
8


-- 方法1：直接插入备份数据
LOAD DATA INFILE '/backup/orders_p202312.csv' 
INTO TABLE orders 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

-- 方法2：使用SOURCE命令
SOURCE /backup/orders_p202312.sql;

恢复单个分区数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


-- 创建临时恢复表
CREATE TABLE orders_restore_p202312 LIKE orders;
ALTER TABLE orders_restore_p202312 REMOVE PARTITIONING;

-- 导入备份数据到临时表
SOURCE /backup/orders_p202312.sql;

-- 清空目标分区（如果需要）
ALTER TABLE orders TRUNCATE PARTITION p202312;

-- 交换数据回分区
ALTER TABLE orders EXCHANGE PARTITION p202312 
WITH TABLE orders_restore_p202312;

-- 清理临时表
DROP TABLE orders_restore_p202312;

5. 完整的分区备份恢复方案

备份脚本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


#!/bin/bash
# 完整分区备份脚本

BACKUP_BASE="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="${BACKUP_BASE}/${DATE}"
LOG_FILE="${BACKUP_BASE}/backup.log"

mkdir -p $BACKUP_DIR

# 备份表结构
echo "$(date): Backing up table structure..." >> $LOG_FILE
mysqldump -d -u backup_user -p'password' your_database orders > ${BACKUP_DIR}/orders_structure.sql

# 备份每个分区数据
echo "$(date): Backing up partition data..." >> $LOG_FILE
mysql -u backup_user -p'password' -N -e "SELECT PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='orders' AND TABLE_SCHEMA='your_database'" | while read partition; do
    if [ "$partition" != "pmax" ]; then
        echo "Backing up partition: $partition"
        mysqldump -u backup_user -p'password' your_database orders --where="created_at >= DATE_SUB(DATE_ADD(STR_TO_DATE(REPLACE('$partition', 'p', ''), '%Y%m'), INTERVAL 1 MONTH), INTERVAL 1 MONTH) AND created_at < DATE_ADD(STR_TO_DATE(REPLACE('$partition', 'p', ''), '%Y%m'), INTERVAL 1 MONTH)" > ${BACKUP_DIR}/orders_${partition}.sql
    fi
done

# 备份分区定义
echo "$(date): Backing up partition definition..." >> $LOG_FILE
mysql -u backup_user -p'password' -e "SHOW CREATE TABLE orders" your_database > ${BACKUP_DIR}/orders_partition_def.sql

echo "$(date): Backup completed to $BACKUP_DIR" >> $LOG_FILE

恢复脚本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


#!/bin/bash
# 分区恢复脚本

BACKUP_DIR="$1"
DATABASE="your_database"

if [ -z "$BACKUP_DIR" ]; then
    echo "Usage: $0 <backup_directory>"
    exit 1
fi

# 恢复表结构
echo "Restoring table structure..."
mysql -u root -p $DATABASE < ${BACKUP_DIR}/orders_structure.sql

# 重新创建分区（如果需要）
echo "Recreating partitions..."
mysql -u root -p $DATABASE < ${BACKUP_DIR}/orders_partition_def.sql

# 恢复分区数据
echo "Restoring partition data..."
for partition_file in ${BACKUP_DIR}/orders_p*.sql; do
    if [ -f "$partition_file" ]; then
        partition_name=$(basename $partition_file .sql | sed 's/orders_//')
        echo "Restoring partition: $partition_name"
        mysql -u root -p $DATABASE < $partition_file
    fi
done

echo "Restore completed!"

6. 基于时间点的备份策略

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


#!/bin/bash
# 自动化备份策略

# 每日增量备份（最近7天）
mysqldump -u backup_user -p'password' your_database orders --where="created_at >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)" > /backup/daily/orders_$(date +%Y%m%d).sql

# 每周全量备份
if [ $(date +%u) -eq 1 ]; then  # 每周一
    mysqldump -u backup_user -p'password' your_database orders > /backup/weekly/orders_$(date +%Y%m%d).sql
fi

# 每月分区归档
if [ $(date +%d) -eq 1 ]; then  # 每月1号
    LAST_MONTH=$(date -d "last month" +%Y%m)
    mysqldump -u backup_user -p'password' your_database orders --where="created_at >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH) AND created_at < DATE_FORMAT(NOW(), '%Y-%m-01')" > /backup/monthly/orders_${LAST_MONTH}.sql
fi

重要注意事项

备份期间锁定：大型分区备份时考虑业务影响，可在业务低峰期进行
存储空间：分区备份需要足够的磁盘空间
恢复测试：定期测试备份文件的恢复流程
监控告警：设置备份任务的监控和告警机制
保留策略：制定合理的备份文件保留周期

MySQL 表分区备份和恢复

Contents

MySQL 分区表的备份与恢复策略

1. 分区级备份（推荐）

备份单个分区

批量备份多个分区

2. 物理文件备份

备份分区物理文件

3. 逻辑备份工具

使用 mysqldump 备份特定分区

使用 mysqlpump（MySQL 5.7+）

4. 分区数据恢复

从逻辑备份恢复分区

恢复单个分区数据

5. 完整的分区备份恢复方案

备份脚本

恢复脚本

6. 基于时间点的备份策略

重要注意事项