☰

如何在 Linux 上备份大型 MySQL 数据库

tag-icon • tag-icon mysql backup
如何在 Linux 上备份大型 MySQL 数据库

我想备份大约 200GB 的 MySQL 数据库。我应该怎么做？

使用 mysqldump - 我觉得备份 200GB 的数据库时，会不断有写入操作。这是个好主意吗？我无法停止数据库的插入。所以我想进行某种热备份。我知道 mysqlhotcopy..它只备份 myisam 表。我的表在 Innodb 中。

请给我一些关于我应该如何处理的想法/意见？

答案1

有许多可能的备份方法描述在MySQL 文档。对于您的情况，我建议您使用“使用文件系统快照进行备份”部分中描述的方法，即发出 FLUSH TABLES WITH READ LOCK，创建文件系统快照（LVM 或其他）并解锁 TABLES。之后，您只需将数据库文件复制到备份媒体即可。

其他选项显然较差。Mysqldump 将产生长时间的负载峰值和表锁定，这将长时间阻止您的 INSERT，尤其是对于非常大的表。

复制是一个更不优雅的解决方案，因为 MySQL 没有机制来确保您的副本集是相同的 - 您只能希望情况如此 - 这真的不是您想要的备份。

答案2

最好的选择是创建另一个 MySQL 服务器并将其配置为主/主模式。这不仅可以为您提供实时备份解决方案，还可以提供故障转移。

答案3

了解如何使用 mysqldump 进行备份

我认为，如果你知道如何做备份，那么备份就变成了一门艺术。

您有选择

选项 1：mysqldump 整个 mysql 实例

这是最简单的一个，无需动脑筋！！！

mysqldump -h... -u... -p... --routines --triggers --all-databases | gzip > MySQLData.sql.gz

所有内容都写在一个文件中：表结构、索引、触发器、存储过程、用户、加密密码。其他 mysqldump 选项还可以导出不同风格的 INSERT 命令、二进制日志中的日志文件和位置坐标、数据库创建选项、部分数据（--where 选项）等等。

选项 2：mysqldump 将单独的数据库转储到单独的数据文件中

首先创建数据库列表（有两种方法可以做到这一点）

技术 1

mysql -h... -u... -p... -A --skip-column-names -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > ListOfDatabases.txt

技术 2

mysql -h... -u... -p... -A --skip-column-names -e"SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfDatabases.txt

技术 1 是最快的方法。技术 2 是最可靠和最安全的。技术 2 更好，因为有时用户会在 /var/lib/mysql (datadir) 中创建与数据库无关的一般用途文件夹。information_schema 会在 information_schema.schemata 表中将该文件夹注册为数据库。技术 2 会绕过不包含 mysql 数据的文件夹。

一旦您编译了数据库列表，您就可以继续循环遍历列表并对其进行 mysqldump，如果需要，甚至可以并行进行。

for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
done
wait

如果一次无法启动的数据库太多，可以一次并行转储 10 个数据库：

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

选项 3：mysqldump 将单独的表转储到单独的数据文件中

首先创建表格列表

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfTables.txt

然后以 10 个为一组转储所有表

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat ListOfTables.txt`
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    mysqldump -h... -u... -p... --triggers ${DB} ${TB} | gzip > ${DB}_${TB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

选项 4：发挥你的想象力

尝试上述选项的变体以及获得干净快照的技术

例子

  1. 按每个表的大小升序或降序对表列表进行排序。
  2. 使用单独的进程，在启动 mysqldumps 之前运行“FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)”。mysqldumps 完成后终止此进程。
  3. 将 mysqldumps 保存在日期文件夹中并轮换旧的备份文件夹。
  4. 将整个实例 mysqldump 加载到独立服务器中。

警告

只有选项 1 可以解决所有问题。缺点是，以这种方式创建的 mysqldump 只能重新加载到生成 mysqldump 的 mysql 主版本中。换句话说，来自 MySQL 5.0 数据库的 mysqldump 无法加载到 5.1 或 5.5 中。原因是什么？mysql 模式在主要版本之间完全不同。

选项 2 和 3 不包括保存用户名和密码。

这是转储用户 SQL 授权的通用方法，这种方法更易读且更便携

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql

选项 3 不保存存储过程，因此您可以执行以下操作

mysqldump -h... -u... -p... --no-data --no-create-info --routines > MySQLStoredProcedures.sql &

另一点需要注意的是 InnoDB。如果您有一个大型 InnoDB 缓冲池，在执行任何备份之前尽可能地刷新它是有意义的。否则，MySQL 会花时间从缓冲池中刷新带有剩余脏页的表。以下是我的建议：

执行备份前约 1 小时运行此 SQL 命令

SET GLOBAL innodb_max_dirty_pages_pct = 0;

在 MySQL 5.5 中，默认 innodb_max_dirty_pages_pct 为 75。在 MySQL 5.1 及更早版本中，默认 innodb_max_dirty_pages_pct 为 90。通过将 innodb_max_dirty_pages_pct 设置为 0，可以加快将脏页刷新到磁盘的速度。这将防止或至少减轻在对任何 InnoDB 表执行任何 mysqldump 之前清理 InnoDB 数据的任何未完成两阶段提交的影响。

关于 mysqldump 的最后说明

大多数人避开 mysqldump 而选择其他工具，而这些工具确实很好。

这些工具包括

  1. MAATKIT（平行倾倒/恢复脚本，来自 Percona [已弃用，但很棒]）
  2. 超级备份（Percona 的 TopNotch 快照备份）
  3. CDP R1软件（MySQL 模块选项拍摄时间点快照
  4. MySQL 企业备份（以前称为 InnoDB 热备份 [商业]）

如果您具有真正的 MySQL DBA 精神，您可以拥抱 mysqldump 并完全掌握它。希望您的所有备份都能反映出您作为 MySQL DBA 的技能。

答案4

我没有备份那个巨大的数据库，但是 20GB 的数据库运行良好：

mysqldump -u xxxxx --password=xxxxx --quick --max_allowed_packet=512M --skip-lock-tables --verbose   -h xxx.xxx.xxx.xxx > db.sql

这--skip-lock-tables对于实时转储来说特别有用，但可能会导致不一致。

相关内容