关于大规模 MySQL 数据库的维护

[Livid]

ibdata1 文件尺寸超过 1T，每个月大概还会新增 50G。

对于这种情况，关于备份和拆表，想听听大家的分享。

谢谢。

[likexian]

innodb_file_per_table 一表一文件，怎么拆表就随便了

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

[haha1903]

@likexian 这个好。

[andybest]

InnoDB在意外断电的时候会导致数据库崩溃，而且无法恢复只能从热备服务器中拿备份出来。@Livid 有没遇到这个问题？如何避免的？

[leafonsword]

@andybest
大部分情况下配置 innodb_force_recovery = N
是能恢复的

[andybest]

@leafonsword 谢谢。

在一个读写非常频繁的 MySQL 环境中，（100+每秒读写），每次意外断电 100% 会导致数据库崩溃，再次启动后进入 force_recovery 流程，是大部分时间是可以恢复，但偶尔无法恢复的时候呢？（如果是1T的数据从热备机恢复过来，怎么都很折腾很难忘吧）
还是说各位从没有遇到过无法恢复的时候？

[leafonsword]

@andybest
每次恢复确实麻烦,但这个时代怎么可能经常断电?
所以重要生产环境肯定考虑高可用--Galera Cluster或MHA

[napoleonu]

1.这么大的数据单个 instance 居然可以支持，并且是在基本设置（innodb_file_per_table）都不合理的情况下。说明很多日志型的数据或者冷数据，建议归档而不是分表，推荐 pt-archiver 。

2.备份的话，在 1T 这种级别可以开一个从库，在从库上想怎么备份都可以。逻辑备份推荐 mydumper，物理备份就 xtrabackup 或者干脆 shutdown 了压缩。

如果你数据库有压力了，或者其他什么问题建议还是找个专业的 MySQL DBA 看看，比如我这样的 :)，简单的几句描述很难断定该怎么做才最好。

[virushuo]

我想问问比较大规模的mysql备份怎么恢复比较快。我这有个感觉不算多大的库，几十G吧，经常恢复20个小时还没恢复完，是我哪做错了吗？

[leafonsword]

@virushuo
几十G理论上最多1小时恢复;假如你恢复慢的话,可能:
1.你是单线程恢复,可以考虑并行恢复工具--mydumper或xtrabackup
2.机器硬件太差(主要CPU、IO)或这台机器还被用作其他用处
3.假如通过SQL文件恢复,可以恢复过程中禁用binlog、将innodb_flush_logs_at_trx_commit设为0

[virushuo]

@leafonsword 多谢指点，1，3我去试试看。

[Livid]

@virushuo 用 innobackupex 试试吧，这个工具的瓶颈就只是磁盘：

https://www.percona.com/doc/percona-xtrabackup/2.1/how-tos.html

[Yousri]

没用启用独享表空间么
按业务功能垂直拆分出大表，再不行就水平按数据拆分一表多库上。
备份方式个人觉得
1、使用 innobackupex 每周或半个月做一次全备，每天用xtrabackup做增备（每天增备量相对会少）
2、采用主从同步延迟备份，比如percona也有相关第三方工具可以设置延迟几个小时，然后还担心的话就在备库上做相应dump的逻辑备份

[napoleonu]

@virushuo

逻辑备份的恢复速度会比较慢，可以使用 @leafonsword 说的 mydumper，多线程的逻辑备份和备份还原，速度会有4，5倍。

物理备份是恢复速度最快的，文件拷贝。物理冷备就是停服务复制文件，物理热备就xtrabackup，innobackupex也一样，只是对xtrabackup的一个封装的工具。但物理备份相对更耗空间，也意味着更多的解压缩时间，解压缩时间也可用pigz这样的工具来优化，4-8线程下有1倍左右速度提升。


@leafonsword

逻辑导入期间禁用binlog是有不小的提升，但是你修改的参数是错得，或者你的说法会误导别人。

innodb_flush_logs_at_trx_commit设为0 是修改redo的flush方式。
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

binlog是这个
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_sql_log_bin

[leafonsword]

@napoleonu
哈，说的不准确，对于新手确实会误导以为全局禁用binlog
@virushuo
是会话零时禁用
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_sql_log_bin