主页 / dba / 问题 / 20
Accepted
Patrick
Asked: 2011-01-04 13:13:48 +0800 CST

如何优化大型数据库的 mysqldump？

  • 772

我有一个 symfony 应用程序，其 InnoDB 数据库约为 2GB，有 57 个表。数据库的大部分大小位于单个表中（~1.2GB）。我目前正在使用 mysqldump 每晚备份数据库。

由于我的 comcast 连接，通常如果我手动运行转储，我与服务器的连接将在转储完成之前超时，导致我不得不重新运行转储。[我目前运行一个每晚执行转储的 cron，这仅适用于我手动运行的转储。]

有没有办法加快连接超时问题的转储，但也可以限制服务器被此进程占用的时间？

顺便说一句，我目前正在努力减少整个数据库的大小以解决这个问题。

mysql performance

7 个回答

  1. Best Answer

    像这样的转储中的主要瓶颈是驱动器 I/O。您正在读取大量数据并再次写入。您可以通过多种方式加快速度：

    • 确保您的输出将发送到与存储数据库文件的驱动器不同的驱动器 - 这将对旋转磁盘产生巨大影响，因为驱动器磁头不会在读取的位置之间不断滑动以及被写入的位置。
    • mysqldump 的输出将是非常可压缩的，因此如果您不能如上所述将输出与输入分开，请通过管道gzip或类似管道输出。这将减少正在完成的写入量（因此减少整体 IO 负载和磁头移动量），但会花费一些 CPU 时间（无论如何，这些时间你可能有很多空闲时间）。
    • 此外，（以及或代替压缩）通过管道实用程序（如pv ）传递输出，该实用程序支持大型写入缓冲区，以将写入驱动器的块更多地组合在一起，再次减少磁头移动延迟的影响 - 这将使如果使用该--quick选项来减少备份大表对 RAM 的影响，则会有很大的不同）。
    • 仅当 IO 负载较低时才运行备份过程。

    不过，您可能正在解决错误的问题：解决连接断开问题可能更容易（尽管减少备份施加的 I/O 负载将有助于减少您对其他用户的影响，因此无论如何都值得尝试）。您可以通过screen（或类似的工具，如tmux）运行手动备份吗？这样，如果您与服务器的连接断开，您只需重新连接并重新连接到screen会话，而不会中断任何进程。

    如果您直接通过连接发送数据（即您在本地计算机上针对远程数据库运行 mysqldump，因此转储出现在本地），您最好先在服务器上运行转储，根据需要进行压缩，然后传输使用支持部分传输的工具（例如rsync）通过网络传输数据，因此如果连接中断中断传输，您可以恢复传输（而不是重新启动）。

    作为您“减少整个数据库的大小以解决此问题”的一部分，我猜您的大部分数据不会改变。您可能可以将 1.2Gb 的一大块从该主表移到另一个表中，并将其从mysqldump调用复制的那些中删除。如果这些数据从不更改，则无需每次都备份此数据。以这种方式在表和数据库之间拆分数据通常称为数据分区，还可以让您将数据和 I/O 负载分散到多个驱动器上。高端数据库内置了对自动分区的支持，但在 mysql 中，您可能必须手动执行此操作并更改数据访问层以解决此问题。

    偏离本网站的主题（因此您可能应该向 ServerFault 或 SuperUser 询问您是否需要更多详细信息）：如果您似乎由于不活动而失去连接，请检查您的 SSH 服务器和 SSH 客户端中的选项以进行确保启用并经常发送保持活动数据包。如果即使连接处于活动状态也看到丢包，您也可以尝试使用 OpenVPN 或类似方法来包装连接 - 如果您的整个连接断开几秒钟，它应该可以处理短暂的丢包，甚至是完全丢包，例如 SSH 客户端和服务器没有注意到。

    • 163

  2. 深入了解使用 mysqldump 进行备份

    恕我直言，如果您知道如何进行备份，则备份已成为一种艺术形式

    你有选择

    选项 1：mysqldump 整个 mysql 实例

    这是最简单的，不费吹灰之力！！！

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

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

    选项 2：mysqldump 将单独的数据库转换为单独的数据文件

    首先创建一个数据库列表（执行此操作的 2 种技术）

    技术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.schema 表中的数据库。技术 2 将绕过不包含 mysql 数据的文件夹。

    一旦你编译了数据库列表，你可以继续循环遍历列表并 mysqldump 它们，如果需要的话，即使是并行的。

    for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --hex-blob --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... --hex-blob --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... --hex-blob --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 完成后终止这个进程。如果数据库同时包含 InnoDB 和 MyISAM，这将很有帮助
    3. 将 mysqldumps 保存在过时的文件夹中并轮换旧的备份文件夹。
    4. 将整个实例 mysqldump 加载到独立服务器中。

    警告

    只有选项 1 带来了一切。缺点是这种方式创建的 mysqldump 只能重新加载到生成 mysqldump 的 mysql 的同一 majot 发行版本中。换句话说，来自 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. XtraBackup（来自 Percona 的 TopNotch 快照备份）
    3. CDP R1Soft（获取时间点快照的MySQL 模块选项）
    4. MySQL 企业备份（以前的 InnoDB 热备份 [商业]）

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

    • 140

  3. 看看 MySQL 复制主从。它允许您将 master 的数据库克隆到具有相同数据库的另一个数据库服务器。这包括主从身份。从站使自己成为主数据库服务器和/或它的数据库的精确副本。主从之间可能存在一对一、一对多、多对一的关系。

    从服务器不断读取主服务器的二进制日志（bin 日志存储在主数据库服务器上写入的查询）并获取其从数据库服务器的输入。（这意味着您的主数据库根本不会受到影响）

    好消息是它不会对您的 MySQL 服务器造成太大影响，因为您不会注意到任何停机时间或查询响应缓慢。我们将它用于 10Gb 数据库，它的工作原理就像一个魅力，没有任何停机时间。

    同一台机器上的 MySQL 复制

    • 22

  4. 计划 A：另请参阅 Percona 的 Xtrabackup。这允许 InnoDB 的在线备份，而无需任何重要的锁定。

    方案 B：可以停止从站，您可以通过多种方式（复制文件、mysqldump、xtrabackup 等）进行一致的备份

    计划 C：LVM 快照。经过一些神秘的设置后，备份的停机时间不到一分钟，无论数据库的大小如何。你停止 mysqld，做快照，重新启动 mysqld，然后复制快照。最后一步可能需要很长时间，但 MySQL 并没有宕机。

    计划 D：从属设备的快照——零停机时间。

    • 21

  5. 首先是一些管理要点：您是连接到 ftp 还是 ssh'ed 并且它快死了？如果是 ssh，那么一定要使用screen以便在 comcast 崩溃后恢复。如果是 ftp，请确保在发送之前压缩它/tar。

    也试试 --opt 参数或 --quick

    --opt 此选项打开一组附加选项以使转储和重新加载操作更有效。具体来说，它相当于同时使用 --add-drop-table、--add-locks、--all、--quick、--extended-insert、--lock-tables 和 --disable-keys 选项。请注意，此选项会降低输出的可移植性，并且不太可能被其他数据库系统理解。

    --quick 此选项告诉 mysqldump 在从服务器读取每一行时写入转储输出，这对于大型表可能很有用。默认情况下，mysqldump 在写入输出之前将表中的所有行读取到内存中；对于大型表，这需要大量内存，可能导致转储失败。

    • 17

  6. 我曾经在大型数据库转储期间也遇到过超时问题。我终于解决了是否通过为数据库中的每个表发送单独的命令并将所有内容附加到一个文件中，如下所示：

    TABLES=`mysql -u $USER -p$PWD -Bse 'show tables' $DB`
for TABLE in $TABLES
do
    mysqldump -u $USER -p$PWD $DB $TABLE >> dump.sql
done
    • 4

  7. 我认为问题在于如何从 mysqldump 创建的转储文件中更快地恢复，而不是不同的备份解决方案。

    其中一种方法是，您可以在架构中创建表组，并为每个组创建一个单独的 DB 用户，然后最后使用 MySQL 权限不允许将表插入到使用除一个 DB 用户之外的所有用户中。

    这是一种经过验证的、快速的、几乎并行的技术，但不能 100% 确定从 500G 左右的大型转储中恢复需要多长时间。但在我看来，你需要一些平行的东西。查看下面的链接以获取示例。

    [从 MySQL 的 SQL 转储 (mysqldump) 快速、并行恢复][1]

    http://geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/fast_parallel_restore_from_sql_dumps_mysqldump_for_mysql.php

    “从 MySQL 的 SQL 转储 (mysqldump) 快速、并行恢复”

    • 3

相关问题