服务器 Gind.cn
  1. 服务器 Gind.cn
  3. 如何在Linux上备份大MySQL数据库
Intereting Posts
什么是pipe理Windows Serverconfiguration的一些工具？ 可以想象的更安全的iptables脚本。 有一个存在吗？ php-frm能让PHP运行得更快吗？ 分发组中的外部收件人不接收电子邮件 我应该如何pipe理每个用户的带宽？ vCenter 5.1 – 添加v5.0主机不会引入模板 如何授予WMSvc IIS的文件夹权限 一个网卡用于AMT，另一个用于操作系统用户？ OpenLDAP：slapd.conf不在了，所以如何编辑权限？ 在LVM中格式化和分区device-mapper 采用快照VMWare的最佳做法是什么？ 鱿鱼和清漆只允许一次请求相同 从零开始Linux是正确的地方开始？ 在多个实例中安装一个S3存储桶 WHM：如何将curl扩展添加到PHP？

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

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

使用mysqldump – 我感觉备份了200GB的数据库，其中连续写入正在进行。 这是个好主意吗？ 我无法停止数据库的插入。 所以我想采取一种热备份。 我知道关于mysqlhotcopy..which只备份myisam表。 我在Innodb有我的桌子。

请给我一些关于如何去做的想法/评论？

MySQL文档中描述了许多备用方法。 在你的情况下，我会清楚地推荐在“使用文件系统快照进行备份”部分中描述的方法，在这里你发出FLUSH TABLES WITH READ LOCK，创build一个文件系统快照（LVM或其他）和UNLOCK TABLES。 之后，您可以将数据库文件复制到备份媒体。

其他的select显然是低劣的。 Mysqldump将创build长时间的负载尖峰和表锁，这将阻止您的INSERT很长一段时间，尤其是对于非常大的表。

复制是一个更不优雅的解决scheme，因为MySQL没有任何机制来确保您的副本集是相同的 – 您只需要希望情况如此 – 实际上不是您想要的备份。

您最好的select是创build另一个MySQL服务器并以主/主模式进行configuration。 这不仅可以为您提供实时备份解决scheme，还可以实现故障切换。

深入了解mysqldump的背景知识

恕我直言，如果你知道如何处理备份，备份已经变得更加艺术化了

你有select

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

这是最简单的一个，毫不费力！ 

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

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

选项2：将mysqldump单独的数据库分成不同的数据文件

首先创build一个数据库列表（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）中为通用目的创build不与数据库相关的文件夹。 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将单独的表分成不同的数据文件

首先创build一个表的列表 

 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. 保存date文件夹中的mysqldumps并旋出旧的备份文件夹。
  4. 将整个实例mysqldumps加载到独立服务器中。

警告

只有选项1带来了一切。 缺点是以这种方式创build的mysqldumps只能重新加载到与生成mysqldump的mysql相同的majot发行版本中。 换句话说，MySQL 5.0数据库中的mysqldump无法在5.1或5.5中加载。 原因 ？ 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会将具有剩余脏页的清空表用于缓冲池之外。 这是我的build议：

执行备份之前大约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，这将加速脏页面到磁盘的刷新。 这可以防止或者至less减less对任何InnoDB表执行任何mysqldump之前清理InnoDB数据的任何不完整的两阶段提交的影响。

在mysqldump的最后一个字

大多数人回避mysqldump转而支持其他工具，这些工具确实不错。

这些工具包括

  1. MAATKIT（并行转储 / 恢复脚本，来自Percona [已弃用，但很棒]）
  2. XtraBackup （来自Percona的TopNotch Snapshot备份）
  3. CDP R1Soft （需要时间点快照的MySQL模块选项 ）
  4. MySQL企业备份 （以前InnoDB热备份[商业]）

如果你有一个真正的MySQL数据库pipe理员的精神，你可以拥抱mysqldump，并有完整的掌握，可以实现。 可能所有的备份都反映了你作为MySQL DBA的技能 。

如果你所有的表都是InnoDB，你应该使用Xtrabackup来提供在线备份。 您也可以执行LVM快照，但LVM快照的性能问题仍然表明Xtrabackup是推荐的解决scheme。

干杯