MySQL数据库的备份与恢复

1. 备份

a. 使用 mysqldump 工具备份

mysqldump 是 MySQL 提供的一个命令行工具，用于创建数据库的逻辑备份（即将数据导出到 SQL 文件中）。

备份整个数据库：

mysqldump -u username -p password --all-databases > backup.sql

备份特定数据库：

mysqldump -u username -p password database_name > backup.sql

备份特定表：

mysqldump -u username -p password database_name table_name > backup.sql

备份数据库（包含数据和结构）并压缩：

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

备份数据库（包括视图和存储过程）以及创建数据库：

mysqldump -u username -p password --all-databases --routines --triggers --events > backup.sql

恢复

a. 从 SQL 文件恢复数据

恢复整个数据库：

mysql -u username -p password database_name < backup.sql

恢复特定表：

mysql -u username -p password database_name < backup.sql

恢复数据（从压缩备份文件）：

gunzip < backup.sql.gz | mysql -u username -p password database_name

 

如何使用shell脚本实现自动备份

#!/bin/bash

# 数据库连接信息
DB_USER="lcsdb"
DB_PASS="123456"
DB_NAME="fgcj_db"
BACKUP_DIR="/opt/mysql"
DATE=$(date +%F)  # 备份日期，格式为 YYYY-MM-DD
BACKUP_FILE="${BACKUP_DIR}/fgcj_db_backup_${DATE}.sql"

# 创建备份
mysqldump -u ${DB_USER} -p${DB_PASS} ${DB_NAME} > ${BACKUP_FILE}

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "备份成功: ${BACKUP_FILE}"
else
    echo "备份失败!"
fi

 

如果是使用lcsdb用户进行备份，需要先赋权，负责表空间不能转储

-- 数据库级别权限
GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON fgcj_db.* TO 'lcsdb'@'localhost';

-- 全局权限
GRANT PROCESS ON *.* TO 'lcsdb'@'localhost';

-- 刷新权限表
FLUSH PRIVILEGES;

 以上使用mysqldump的备份都是逻辑备份，需要物理备份，可以用mysqlbackup

 

表空间的转储

转储表空间意味着在备份过程中包含所有与表空间相关的数据文件和元数据。这通常涉及到以下内容：

• 表和索引数据：表空间包含表数据和索引数据，转储会将这些数据导出到备份文件中。
• 表空间结构：如果涉及 InnoDB 表空间的备份，可能会包括表空间文件的结构信息。

如果转储表空间：

• 完整性：备份将包含表空间的所有数据和元数据，确保备份的数据与生产数据库中的数据一致。
• 恢复：可以从备份中恢复表空间的内容，包括所有表和索引数据，恢复过程通常更加完整。
• 安全：包括表空间的数据和结构可以提供更全面的备份，但也可能需要更多的权限和更多的备份时间。

如果不转储表空间：

• 限制：备份可能无法完全恢复数据库的状态。特别是在使用 InnoDB 表时，不转储表空间可能会丢失某些重要的表和索引数据。
• 恢复问题：在恢复时，可能会遇到缺少数据或数据不一致的情况，因为备份未包含完整的表空间信息。
• 备份速度：转储过程中不包含表空间可能会使备份过程更快，但恢复数据的完整性和一致性可能会受到影响。

以下权限赋予后，即可转储表空间