mysql定期备以及优化过程

在备份 MySQL 数据库时，可以使用 mysqldump 提供的参数来防止长时间的表锁定，尤其是在生产环境中有持续的写操作时。以下是一些推荐的参数来减少对数据库的锁定影响：

--single-transaction:
- 这个选项会在 mysqldump 开始时启动一个事务，并在事务隔离级别为 REPEATABLE READ 的情况下备份数据，从而避免全局锁定（适用于 InnoDB 存储引擎）。这个选项非常适合用于不需要全库一致性的逻辑备份。
- 注意: 对于 MyISAM 表，这个参数不起作用，因为 MyISAM 不支持事务。
--quick:
- 这个选项会使 mysqldump 一次一行地从数据库中读取数据，而不是将整个结果集读入内存，减少内存消耗，特别适合大表的备份。
--lock-tables=false:
- 在默认情况下，mysqldump 会对所有表加全表锁以确保数据一致性。使用 --lock-tables=false 可以避免加锁。对于支持事务的引擎（如 InnoDB），配合 --single-transaction 可以达到类似效果。

修改后的脚本：

我们将这些参数加入到脚本中，避免锁定库表。

#!/bin/bash

# 配置参数
DB_USER="your_db_user"          # MySQL 用户
DB_PASSWORD="your_db_password"  # MySQL 密码
DB_HOST="your_db_host_ip"       # MySQL 服务器IP地址
DB_PORT=3306                    # MySQL 端口号（默认3306）
DB_NAMES=("db1" "db2" "db3")    # 需要备份的数据库列表
BACKUP_DIR="/path/to/backup"    # 备份文件存储目录
DATE=$(date +"%Y%m%d")          # 当前日期，用于备份文件命名
MAX_RATE=1M                     # 限制备份速率, 1M为每秒1MB
RETENTION_DAYS=30               # 保留天数

# 创建备份目录（如果不存在）
mkdir -p "${BACKUP_DIR}"

# 遍历每个数据库并进行备份
for DB_NAME in "${DB_NAMES[@]}"; do
    BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_backup_${DATE}.sql.gz"
    echo "开始备份数据库：${DB_NAME} （服务器：${DB_HOST}，端口：${DB_PORT}）"
    
    # 进行数据库备份，避免锁库，并限制压缩速率
    mysqldump -h"${DB_HOST}" -P"${DB_PORT}" -u"${DB_USER}" -p"${DB_PASSWORD}" --single-transaction --quick --lock-tables=false "${DB_NAME}" | pv -qL ${MAX_RATE} | gzip > "${BACKUP_FILE}"
    
    # 检查备份是否成功
    if [[ $? -eq 0 ]]; then
        echo "数据库 ${DB_NAME} 备份成功，保存为 ${BACKUP_FILE}"
    else
        echo "数据库 ${DB_NAME} 备份失败！"
    fi
done

# 删除超过保留天数的备份
find "${BACKUP_DIR}" -type f -name "*_backup_*.sql.gz" -mtime +${RETENTION_DAYS} -exec rm {} \;

# 提示完成
echo "所有数据库备份完成。旧备份（超过 ${RETENTION_DAYS} 天）已被删除。"

参数解释：

--single-transaction:
- 确保在备份时启动一个一致性事务，防止全库锁定，适用于 InnoDB 存储引擎。
--quick:
- 每次从数据库中读取一行数据，减少内存占用，适合大表的备份。
--lock-tables=false:
- 避免锁表，确保备份过程中不会对表进行锁定。