V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
Livid
V2EX  ›  MySQL

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

  •  1
     
  •   Livid · 2014-05-22 15:37:42 +08:00 · 5663 次点击
    这是一个创建于 3825 天前的主题,其中的信息可能已经有所发展或是发生改变。
    ibdata1 文件尺寸超过 1T,每个月大概还会新增 50G。

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

    谢谢。
    14 条回复    2014-06-15 10:42:23 +08:00
    likexian
        1
    likexian  
       2014-05-22 15:46:21 +08:00   ❤️ 3
    innodb_file_per_table 一表一文件,怎么拆表就随便了

    http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
    haha1903
        2
    haha1903  
       2014-05-22 16:30:01 +08:00
    @likexian 这个好。
    andybest
        3
    andybest  
       2014-05-22 16:44:53 +08:00 via Android
    InnoDB在意外断电的时候会导致数据库崩溃,而且无法恢复只能从热备服务器中拿备份出来。@Livid 有没遇到这个问题?如何避免的?
    leafonsword
        4
    leafonsword  
       2014-05-22 16:56:25 +08:00
    @andybest
    大部分情况下配置 innodb_force_recovery = N
    是能恢复的
    andybest
        5
    andybest  
       2014-05-22 17:05:07 +08:00
    @leafonsword 谢谢。

    在一个读写非常频繁的 MySQL 环境中,(100+每秒读写),每次意外断电 100% 会导致数据库崩溃,再次启动后进入 force_recovery 流程,是大部分时间是可以恢复,但偶尔无法恢复的时候呢?(如果是1T的数据从热备机恢复过来,怎么都很折腾很难忘吧)
    还是说各位从没有遇到过无法恢复的时候?
    leafonsword
        6
    leafonsword  
       2014-05-22 19:30:41 +08:00
    @andybest
    每次恢复确实麻烦,但这个时代怎么可能经常断电?
    所以重要生产环境肯定考虑高可用--Galera Cluster或MHA
    napoleonu
        7
    napoleonu  
       2014-05-22 20:01:19 +08:00   ❤️ 2
    1.这么大的数据单个 instance 居然可以支持,并且是在基本设置(innodb_file_per_table)都不合理的情况下。说明很多日志型的数据或者冷数据,建议归档而不是分表,推荐 pt-archiver 。

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

    如果你数据库有压力了,或者其他什么问题建议还是找个专业的 MySQL DBA 看看,比如我这样的 :),简单的几句描述很难断定该怎么做才最好。
    virushuo
        8
    virushuo  
       2014-05-23 04:42:45 +08:00
    我想问问比较大规模的mysql备份怎么恢复比较快。我这有个感觉不算多大的库,几十G吧,经常恢复20个小时还没恢复完,是我哪做错了吗?
    leafonsword
        9
    leafonsword  
       2014-05-23 11:24:33 +08:00   ❤️ 1
    @virushuo
    几十G理论上最多1小时恢复;假如你恢复慢的话,可能:
    1.你是单线程恢复,可以考虑并行恢复工具--mydumper或xtrabackup
    2.机器硬件太差(主要CPU、IO)或这台机器还被用作其他用处
    3.假如通过SQL文件恢复,可以恢复过程中禁用binlog、将innodb_flush_logs_at_trx_commit设为0
    virushuo
        10
    virushuo  
       2014-05-23 14:26:32 +08:00
    @leafonsword 多谢指点,1,3我去试试看。
    Livid
        11
    Livid  
    MOD
    OP
       2014-05-24 15:55:36 +08:00   ❤️ 1
    @virushuo 用 innobackupex 试试吧,这个工具的瓶颈就只是磁盘:

    https://www.percona.com/doc/percona-xtrabackup/2.1/how-tos.html
    Yousri
        12
    Yousri  
       2014-05-27 10:01:57 +08:00
    没用启用独享表空间么
    按业务功能垂直拆分出大表,再不行就水平按数据拆分一表多库上。
    备份方式个人觉得
    1、使用 innobackupex 每周或半个月做一次全备,每天用xtrabackup做增备(每天增备量相对会少)
    2、采用主从同步延迟备份,比如percona也有相关第三方工具可以设置延迟几个小时,然后还担心的话就在备库上做相应dump的逻辑备份
    napoleonu
        13
    napoleonu  
       2014-05-30 23:08:00 +08:00   ❤️ 1
    @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
        14
    leafonsword  
       2014-06-15 10:42:23 +08:00   ❤️ 1
    @napoleonu
    哈,说的不准确,对于新手确实会误导以为全局禁用binlog
    @virushuo
    是会话零时禁用
    http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_sql_log_bin
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2913 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 13:06 · PVG 21:06 · LAX 05:06 · JFK 08:06
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.