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

#1 楼

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


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

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

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

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

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

#2 楼

了解使用mysqldump进行备份

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

您有选择

选项1：mysqldump一个完整的mysql实例

这是最简单的一个，不用费脑子!!!

mysqldump -h... -u... -p... --hex-blob --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... --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：mysqld将单独的表转储到单独的数据文件中

首先创建表列表

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 }'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print }'`
    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：使用您的想象力

尝试上述选项的变体以及用于清洁快照的技术

示例


按每个表的大小升序或降序对表列表进行排序。
使用单独的进程，在启动mysqldumps之前运行“带读锁的刷新表； SELECT SLEEP（86400）”。 mysqldumps完成后，请终止此过程。如果数据库同时包含InnoDB和MyISAM，这将很有帮助。
将mysqldumps保存在带日期的文件夹中，并旋转出旧的备份文件夹。
将整个实例mysqldumps装入独立服务器。 br />
只有选项1才能带来一切。缺点是通过这种方式创建的mysqldumps只能重新加载到生成mysqldump的相同的majot发行版mysql中。换句话说，无法在5.1或5.5中加载来自MySQL 5.0数据库的mysqldump。原因 ？ mysql模式在主​​要版本之间完全不同。

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

这是为用户转储SQL Grants的通用方法它既易读又更便于移植

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而使用其他工具，而这些工具确实不错。

这类工具包括


MAATKIT（并行转储/还原脚本，来自Percona [不推荐使用，但功能强大]）

XtraBackup（Percona的TopNotch快照备份）

CDP R1Soft（带时间点快照的MySQL模块选件）

MySQL企业备份（以前是InnoDB热备份[商业]）

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

#3 楼

方案A：
另请参阅Percona的Xtrabackup。这样就可以在线备份InnoDB，而没有任何明显的锁定。

计划B：
可以停止从站，并且可以通过以下几种方法中的任何一种进行一致的备份（复制文件，mysqldump） ，xtrabackup等）

计划C：
LVM快照。进行一些神秘的设置后，无论数据库的大小如何，备份的停机时间都将少于一分钟。您停止mysqld，创建快照，重新启动mysqld，然后复制快照。最后一步可能要花很长时间，但是MySQL并没有关闭。

#4 楼

看看MySQL复制主服务器到从服务器。它允许您将master数据库克隆到具有相同数据库的另一台数据库服务器。其中包括主身份和从身份。从站将自己制作主数据库服务器和/或其数据库的精确副本。主机和从机之间可能存在一对一，多对多的关系。从属服务器不断读取主服务器上的二进制日志（bin日志存储在主数据库服务器上写入的查询），并输入到其从属数据库服务器。 （这意味着您的主数据库完全不会受到影响）

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

在同一台机器上的MySQL复制

#5 楼

首先要注意几个管理要点：您是否正在连接以执行ftp或ssh's in并且快要死了？如果是ssh，请确保使用屏幕，以便在comcast崩溃后可以继续。如果是ftp，请确保在发送前先压缩它/ tar。

也可以尝试--opt参数或--quick

--opt
此选项打开一组附加选项，以进行转储和重新加载操作更高效。具体来说，这等效于一起使用--add-drop-table，-add-locks，-all，-quick，-extended-insert，-lock-tables和--disable-keys选项。请注意，此选项使输出的可移植性降低，其他数据库系统也不太可能理解该输出。服务器，这可能对大型表有用。默认情况下，mysqldump在写入输出之前将表中的所有行读入内存。对于大表，这需要大量内存，可能导致转储失败。

#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

#7 楼

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

方法之一是通过在模式中创建表组来实现的，为每个组创建一个单独的数据库用户，然后最终使用MySQL权限不允许只使用一个数据库用户即可插入表。

这是一种行之有效，快速，几乎并行的技术，但并非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）快速并行还原”