V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
kisshere
V2EX  ›  程序员

MySQL 这么成熟的数据库,为什么用 mysqldump 老是要锁表,生产环境直接崩溃

  •  2
     
  •   kisshere · 2019-09-26 08:50:17 +08:00 · 16021 次点击
    这是一个创建于 1886 天前的主题,其中的信息可能已经有所发展或是发生改变。

    MySQL5.7 版本,一张 800 万行的 MyISAM 表,准备每天定时备份,导出 SQL 至备份盘。因为是生产环境,肯定不能锁表,那样业务全都被锁死了,于是尝试各种方法:

    mysqldump -u root -p --lock-tables=false MyDB MyTable>MyTable.sql //锁表卡死
    
    mysqldump -u root -p --lock-tables=false --skip-add-locks MyDB MyTable>MyTable.sql //锁表卡死
    
    mysqldump -u root -p --lock-tables=false --quick MyDB MyTable>MyTable.sql //锁表卡死
    
    mysqldump -u root -p --compress --quick --triggers --routines --lock-tables=false --single-transaction MyDB MyTable>MyTable.sql //锁表卡死
    

    用 show processlist 查看,一直卡在

    SELECT /*!40001 SQL_NO_CACHE */ * FROM `MyTable`
    

    目前根本不敢备份,用 mysqldump 就锁表,试了好多方法都不行,我快崩溃了,求 V 友数据大神帮忙

    61 条回复    2021-12-14 17:14:50 +08:00
    GTim
        1
    GTim  
       2019-09-26 08:55:52 +08:00 via iPhone   ❤️ 1
    典型的不知道锁表的好处就来攻击锁表

    对于 myisam 备份最快的方式是拷贝物理文件
    wweir
        2
    wweir  
       2019-09-26 08:56:33 +08:00 via Android
    人那么高级的物种,为什么吃饭的时候还要用餐具?吃饭的时候还要花时间洗餐具,直接崩溃
    kisshere
        3
    kisshere  
    OP
       2019-09-26 08:57:28 +08:00
    @GTim 拷贝 MySQL 那些数据文件,以后 MySQL 版本升级了咋办,兼容性敢保证么
    ixiaohei
        4
    ixiaohei  
       2019-09-26 08:57:30 +08:00 via iPhone   ❤️ 7
    物理拷贝;或者用 binlog 同步到备份数据库;然后对备份数据库 dump 出来,也会不影响生产
    MeteorCat
        5
    MeteorCat  
       2019-09-26 09:00:02 +08:00 via Android
    楼上说的对,直接物理拷贝之后导入同版本 mysql 之后再 mysqldump 出来,虽然费点功夫但是安全和兼容性最高
    lianxiaoyi
        6
    lianxiaoyi  
       2019-09-26 09:02:10 +08:00   ❤️ 2
    XtraBackup 了解一下,你这 800 万记录计算你 dump 出来了,当你要恢复的时候,没有半小时算我输。。。
    但是物理备份就不一样了,800 万记录也就几秒钟的事情吧,恢复也就几秒钟的事情。
    我写的简单使用方法,我个人自建的 mysql 就在使用。
    http://notephp.com/?id=58
    imNull
        7
    imNull  
       2019-09-26 09:07:33 +08:00 via Android
    用 binlog
    naix1573
        8
    naix1573  
       2019-09-26 09:09:49 +08:00   ❤️ 20
    mysql,你已经是个成熟的数据库了,应该学会自己备份。
    TiggeYL
        9
    TiggeYL  
       2019-09-26 09:12:51 +08:00
    给你一条建议
    你要是建立数据库之前开启了 binlog
    https://github.com/alibaba/canal
    可以尝试阿里云这个方法备份
    TiggeYL
        10
    TiggeYL  
       2019-09-26 09:13:22 +08:00
    @TiggeYL 说错了 是阿里巴巴
    GTim
        11
    GTim  
       2019-09-26 09:13:58 +08:00
    @kisshere 谁吃饱了撑着没事干敢在没停机时升级数据库。如果非要升级数据库,你要先把网站关了,然后使用 dump 之类的导出数据库,然后再备份物理文件,然后再升级。

    mysql 数据目录下的同名数据库里有三个表同名的文件,把那三个文件备份一下就好了
    wd
        12
    wd  
       2019-09-26 09:14:04 +08:00 via iPhone
    myisam 换 innodb ?
    nekolr
        13
    nekolr  
       2019-09-26 09:16:42 +08:00
    MyISAM 不使用共享表空间,直接物理复制数据库目录就可以了,然后放到相同版本的数据库中,再使用 dump 导出 sql。
    DRcoding
        14
    DRcoding  
       2019-09-26 09:20:54 +08:00
    mysqldump 是逻辑备份当然会锁表咯,所以还是花钱上云会方便很多,各种全量、增量备份,数据恢复都妥妥的。

    生产环境的备份 LZ 去了解下 MySQL 如何热备份就好了。
    love
        15
    love  
       2019-09-26 09:30:34 +08:00
    印象中 MyISAM 表就是只能锁表的,innodb 就不会
    siparadise
        16
    siparadise  
       2019-09-26 09:31:13 +08:00
    mysql 这么多存储引擎,偏要用 myisam 这东西 ... ...
    lscho
        17
    lscho  
       2019-09-26 09:35:13 +08:00   ❤️ 2
    MySQL 这么成熟的软件,竟然还有人用 mysqldump 热备份?业务量不大的话,推荐物理备份。中等的话,推荐 binlog。再往上我也不知道了。
    siparadise
        18
    siparadise  
       2019-09-26 09:35:37 +08:00
    换个支持 mvcc 的存储引擎,就没这种问题了
    fuye
        19
    fuye  
       2019-09-26 09:37:25 +08:00
    --lock-tables=false 可以取消锁表,但是会导致数据的不一致

    考虑 Innodb 引擎,就可以使用 --single-transaction 做全量一致性备份,配合--flush-logs 可以做增量备份

    --lock-tables, -l
    Lock all tables before dumping them. The tables are locked with READ
    LOCAL to allow concurrent inserts in the case of MyISAM tables. For
    transactional tables such as InnoDB and BDB, --single-transaction is
    a much better option, because it does not need to lock the tables at
    all.
    love
        20
    love  
       2019-09-26 09:37:36 +08:00
    @lscho 我的 vps 就是用 mysqldump 的,每天一次,数据量也不太小吧,备份时哪会锁死网站。
    Felldeadbird
        21
    Felldeadbird  
       2019-09-26 09:51:14 +08:00
    4 楼做法最好了。既不影响生产,也不影响备份。
    Marstin
        22
    Marstin  
       2019-09-26 09:54:57 +08:00
    支持#11 老哥
    zkaip
        23
    zkaip  
       2019-09-26 10:00:21 +08:00
    4 楼 +1
    fuyufjh
        24
    fuyufjh  
       2019-09-26 10:04:34 +08:00
    这年头还有人用 MyISAM
    xiaogui
        25
    xiaogui  
       2019-09-26 10:08:46 +08:00
    推荐分主从数据库,然后用从库进行备份、查询等工作。
    wps353
        26
    wps353  
       2019-09-26 10:20:59 +08:00
    嗦嘎,这年头我还有人在使用这个引擎。
    bomb77
        27
    bomb77  
       2019-09-26 10:21:53 +08:00
    lz 觉得物理备份有兼容性问题的这个思路不对,备份是用于故障意外导致原本的数据库损害无法访问等问题的恢复工作,不是拿来给你升级用的,你拿备份恢复数据的时候总不能用一个不同的 mysql 版本吧。。
    升级数据库版本就是另外一个问题了。
    d0m2o08
        28
    d0m2o08  
       2019-09-26 10:22:21 +08:00
    xtrabackup +1
    用了都说好
    awanabe
        29
    awanabe  
       2019-09-26 10:37:15 +08:00
    做一个主备
    no13bus
        30
    no13bus  
       2019-09-26 10:39:38 +08:00
    锁表是为了一致性
    ladypxy
        31
    ladypxy  
       2019-09-26 10:39:44 +08:00 via iPhone
    Myisam 最好的备份方法是加锁,物理备份,释放锁。
    如果是 zfs,直接快照,瞬间完成
    a54552239
        32
    a54552239  
       2019-09-26 10:49:58 +08:00
    为啥这年头不能用 MyISAM ?
    Raymon111111
        33
    Raymon111111  
       2019-09-26 10:53:57 +08:00
    MyISAM 不成熟
    x7395759
        34
    x7395759  
       2019-09-26 11:58:26 +08:00
    学艺不精,请多看书
    akira
        35
    akira  
       2019-09-26 12:01:42 +08:00
    不锁表的话,备出来的数据只能拿来参考,不能拿来恢复的。 而且原则上,全量备份的时候,建议还是先停业务,再来备份,会比较安全。
    Sherlocker
        36
    Sherlocker  
       2019-09-26 12:05:19 +08:00
    mydumper 了解一下
    Mac
        37
    Mac  
       2019-09-26 12:28:02 +08:00 via Android
    主从复制,然后备份从表啊
    littlewing
        38
    littlewing  
       2019-09-26 13:02:08 +08:00
    1. 用 innodb,mysqldump --single-transaction
    2. 用 xtrabackup
    zhaogaz
        39
    zhaogaz  
       2019-09-26 13:06:10 +08:00   ❤️ 1
    java,你已经是个成熟的语言了,应该学会自己写业务代码。
    haozxuan001
        40
    haozxuan001  
       2019-09-26 16:46:54 +08:00
    我只想说一句话,不锁表,老板问你这是什么时候的数据,你说两个小时前的?五分钟前的?哪怕完成前一秒有个更新操作,你不就崩了。这个道理你不会不懂吧
    julyclyde
        41
    julyclyde  
       2019-09-26 16:52:18 +08:00
    存储引擎都这么成熟了,你还不使用成熟的那个
    swulling
        42
    swulling  
       2019-09-26 17:13:39 +08:00
    开启主备,然后对备库进行 dump。不都这么做的,还用来问?
    wzq001
        43
    wzq001  
       2019-09-26 17:28:54 +08:00
    #4 #42 方法可以啊,觉得没毛病,求楼下大佬怼
    small32
        44
    small32  
       2019-09-26 17:31:41 +08:00
    @naix1573 #8 你也在看超神制卡师?
    dog82
        45
    dog82  
       2019-09-26 17:36:12 +08:00
    行锁、表锁适用的场合不同。
    不要用老旧的 myIsam 了
    数据库管理系统的复杂度跟操作系统是一个级别的,不明白内部运行机制,还是要怀有敬畏之心
    IamUNICODE
        46
    IamUNICODE  
       2019-09-26 17:46:32 +08:00
    看标题心里咯噔一下,然后发现我们引擎用的 innodb,还好还好
    reus
        47
    reus  
       2019-09-26 17:48:06 +08:00
    是你知识水平不够,不是 mysql 不够成熟,就是这样
    AngelCriss
        48
    AngelCriss  
       2019-09-26 17:49:16 +08:00 via Android
    换 pg
    Unclev21x
        49
    Unclev21x  
       2019-09-26 17:55:09 +08:00
    @lianxiaoyi 您这个博客用的哪个啊,看起来挺舒服的。
    cyspy
        50
    cyspy  
       2019-09-26 18:13:03 +08:00
    binlog 重放
    wujianxiong
        51
    wujianxiong  
       2019-09-26 19:24:02 +08:00
    mysql,你已经是个成熟的数据库了,应该学会自己备份。
    FullBridgeRect
        52
    FullBridgeRect  
       2019-09-26 21:22:25 +08:00 via Android
    我,mysql,备份
    eric
        53
    eric  
       2019-09-26 22:00:45 +08:00
    XtraBackup 了解一下
    realpg
        54
    realpg  
       2019-09-26 22:03:37 +08:00
    MYISAM 啥时候就能代表 MYSQL 了?
    darkmi
        55
    darkmi  
       2019-09-26 22:51:49 +08:00
    mydumper
    mysqldump
    mysqlpump
    xtrabackup
    areless
        56
    areless  
       2019-09-26 23:15:33 +08:00
    每天都从 raid1 拔出一块硬盘,标上日期。插入一块同型号的新硬盘。如此循环反复
    simonlu9
        57
    simonlu9  
       2019-09-26 23:18:47 +08:00
    mysqldump 有一个参数开启事务的,不会锁表的,如果表是 myisam 就没办法啦,肯定锁表
    Imr
        58
    Imr  
       2019-09-27 08:13:49 +08:00 via iPhone
    @lscho 再往上还是 mysqldump /滑稽 小数据量和超大大数据量备份最高效的选择
    lopetver
        59
    lopetver  
       2019-09-27 08:33:38 +08:00
    推荐 XtraBackup 目前项目的生产环境就是用它负责备份的。官网文档很齐全
    mineqiqi
        60
    mineqiqi  
       2019-09-27 14:13:03 +08:00
    4 楼说得对,不影响生产,不影响备份。
    statement
        61
    statement  
       2021-12-14 17:14:50 +08:00
    @lianxiaoyi 你博客变成色情网站了 公司打开社死了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2666 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 33ms · UTC 00:33 · PVG 08:33 · LAX 16:33 · JFK 19:33
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.