11. mysql 学习笔记 - 数据备份与恢复

千度麒麟
2 阅读6分钟

数据库备份是保证数据安全、避免数据丢失的关键措施。MySQL 提供了多种备份策略和工具，可以根据实际需求选择合适的方式进行备份。备份分为 全量备份、增量备份 和 差异备份，而恢复则是将备份数据恢复到数据库中的过程。

1. 备份策略

1.1 全量备份

全量备份是指对数据库的所有数据进行完整备份。备份过程中，所有的数据库文件（如表、索引、视图等）都会被备份。

  • 优点：

    • 备份简单，恢复方便。

    • 完整性好，能保证恢复时的可靠性。

  • 缺点：

    • 数据量大时，备份和恢复速度慢。

    • 占用较多存储空间。

mysqldump -u root -p --all-databases > full_backup.sql

上面的命令是备份mysql 中所有的数据库，包括表，字段，数据等

下面的命令是指针对 kylin_admin 数据库进行备份：

mysqldump -u root -p kylin_admin > kylin_admin_backup.sql

如果过想备份多个指定的数据库，则：

mysqldump -u root -p kylin_admin other_database > multi_db_backup.sql

为了节约空间，如果想让备份的 mysql 进行压缩，则：

mysqldump -u root -p kylin_admin | gzip > kylin_admin_backup.sql.gz

1.2 增量备份

增量备份只备份自上次备份以来发生变化的数据。它是基于上次备份的基础上进行的，不会重复备份未改变的数据。

  • 优点：

    • 备份速度快，占用存储空间少。

    • 适合频繁备份的数据。

  • 缺点：

    • 恢复时需要先恢复全量备份，然后依次恢复增量备份，操作较为复杂。

    • 增量备份依赖于先前的备份，可能导致恢复时的问题。

1.3 使用 mysqlbinlog 命令备份增量数据

如果你想使用 MySQL binlog 进行增量备份，首先需要确保启用了 binlog。增量备份的关键是 MySQL 的二进制日志（binlog） ，它记录了所有数据库的变更操作。

启用 binlog

确保在 MySQL 配置文件 my.cnf 中启用了 binlog：

[mysqld]
log-bin=mysql-bin
server-id=1

重启 MySQL 服务以生效。

查看 binlog 文件

使用以下命令查看 binlog 文件：

mysql -u root -p -e "SHOW BINARY LOGS;"

备份 binlog 文件

mysqlbinlog /path/to/mysql-bin.000001 > kylin_admin_incremental_backup.sql

恢复 binlog 文件

mysql -u root -p < kylin_admin_incremental_backup.sql

1.4 差异备份

差异备份是指从上次的全量备份以来，备份所有发生变化的数据。与增量备份不同，差异备份不依赖于上次备份的时间，而是基于全量备份的时间点。

  • 优点：

    • 比全量备份节省空间，但恢复比增量备份更简单。

    • 恢复时，只需要全量备份和最新的差异备份。

  • 缺点：

    • 随着备份时间的推移，差异备份的大小会逐渐增大。

    • 仍然需要定期进行全量备份来避免差异备份过大。

# 使用 `mysqlbinlog` 命令备份自上次全量备份以来的所有更改
mysqlbinlog --start-datetime="2024-01-01 00:00:00" /path/to/binlog > diff_backup.sql

2. MySQL 备份工具

MySQL 提供了多种备份工具和方法，可以根据需求选择不同的工具进行备份操作。常用的工具包括 mysqldump、mysqlhotcopy、Percona XtraBackup 和 MySQL Enterprise Backup。

2.1 mysqldump

mysqldump 是 MySQL 自带的命令行工具，广泛用于数据库的全量备份和恢复。它会导出数据库的 SQL 脚本，可以用来重建数据库和恢复数据。

备份命令：

# 备份单个数据库
mysqldump -u root -p database_name > backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases_backup.sql

# 备份某个表
mysqldump -u root -p database_name table_name > table_backup.sql

# 备份时排除某些表
mysqldump -u root -p --ignore-table=database_name.table1 --ignore-table=database_name.table2 database_name > backup.sql

恢复命令：

# 恢复数据库
mysql -u root -p database_name < backup.sql

优点：

  • 简单易用，支持指定数据库、表等的备份。

  • 可以导出为 SQL 文件，方便查看和编辑。

缺点：

  • 备份速度较慢，尤其是在大数据量情况下。

  • 只能进行全量备份，不支持增量备份。

2.2 mysqlhotcopy

mysqlhotcopy 是 MySQL 提供的一个备份工具，主要用于 MyISAM 存储引擎的表备份。它通过直接复制表的文件来实现备份，适合用于不频繁变更的数据库。

备份命令：

mysqlhotcopy -u root -p database_name /path/to/backup_dir

使用 mysqlhotcopy 备份 kylin_admin 数据库

mysqlhotcopy -u root -p kylin_admin /path/to/backup_dir

/path/to/backup_dir：指定备份文件的存储目录。

优点：

  • 备份速度快。

  • 适用于 MyISAM 表的备份。

缺点：

  • 仅支持 MyISAM 存储引擎，无法用于 InnoDB 表。

  • 无法备份表的外键约束等元数据。

2.3 Percona XtraBackup

Percona XtraBackup 是一个开源的备份工具，适用于 InnoDB 存储引擎，提供了 热备份（不需要停机）功能。

安装 Percona XtraBackup

如果尚未安装 Percona XtraBackup，可以根据系统类型进行安装：

# 对于 Ubuntu/Debian 系统
sudo apt-get install percona-xtrabackup

# 对于 CentOS/RHEL 系统
sudo yum install percona-xtrabackup

备份命令：

# 创建备份
xtrabackup --backup --target-dir=/path/to/backup_dir --user=root --password=root

# 准备备份（解锁并应用事务日志）
xtrabackup --prepare --target-dir=/path/to/backup_dir

  • /path/to/backup_dir：指定备份文件的存储目录。

  • --user=root：使用 root 用户进行备份。

  • --password=root_password：提供 root 用户的密码。

恢复命令：

# 恢复备份
xtrabackup --copy-back --target-dir=/path/to/backup_dir

# 恢复时需要修复权限
chown -R mysql:mysql /path/to/restore_dir

优点：

  • 支持 InnoDB 引擎，提供热备份功能。

  • 性能优越，适合大规模数据库备份。

缺点：

  • 配置和使用比 mysqldump 更复杂。

  • 仅支持 InnoDB 存储引擎，不支持 MyISAM。

2.4 MySQL Enterprise Backup

MySQL Enterprise Backup 是 MySQL 官方提供的企业级备份工具，功能强大，支持全量备份、增量备份、差异备份等多种备份方式。

备份命令：

# 全量备份
mysqlbackup --backup-dir=/path/to/backup_dir --user=root --password=root backup

# 增量备份
mysqlbackup --backup-dir=/path/to/backup_dir --incremental --incremental-basedir=/path/to/last_full_backup backup

恢复命令：

# 恢复备份
mysqlbackup --backup-dir=/path/to/backup_dir --user=root --password=root copy-back

优点：

  • 支持增量备份和差异备份。

  • 提供企业级备份解决方案，性能高，功能全面。

缺点：

  • 需要 MySQL Enterprise 版，不适用于社区版。

  • 使用和配置较为复杂。

3. 备份和恢复案例

案例 1：使用 mysqldump 进行全量备份和恢复

假设你要备份名为 mydb 的数据库：

全量备份：

mysqldump -u root -p mydb > mydb_backup.sql

假设我的数据库叫 kylin_admin，那么我想将这个数据库进行备份：

# 使用 mysqldump 备份 kylin_admin 数据库
mysqldump -u root -p kylin_admin > kylin_admin_backup.sql

  • -u root：使用 root 用户进行备份（根据实际情况使用合适的用户名）。

  • -p：提示输入密码。

  • kylin_admin：你要备份的数据库名。

  • > kylin_admin_backup.sql：将备份结果导出到 kylin_admin_backup.sql 文件中。

恢复数据库：

mysql -u root -p mydb < mydb_backup.sql

假设我的数据库叫 kylin_admin，那么我想将这个数据库进行备份：

# 使用 mysql 恢复 kylin_admin 数据库
mysql -u root -p kylin_admin < kylin_admin_backup.sql

  • -u root：恢复时使用 root 用户。

  • -p：提示输入密码。

  • kylin_admin：你要恢复的数据库名。

  • < kylin_admin_backup.sql：从 kylin_admin_backup.sql 文件中恢复数据。

案例 2：使用 Percona XtraBackup 进行热备份

创建备份：

xtrabackup --backup --target-dir=/path/to/backup_dir --user=root --password=root

准备备份：

xtrabackup --prepare --target-dir=/path/to/backup_dir

恢复备份：

xtrabackup --copy-back --target-dir=/path/to/backup_dir
chown -R mysql:mysql /path/to/restore_dir

案例 3：使用 mysqlhotcopy 进行 MyISAM 表备份

备份命令：

mysqlhotcopy -u root -p mydb /path/to/backup_dir
avatar
文章
阅读
粉丝