MySQL Backup--Xtrabackup备份设置锁等待问题

约定不等于承诺〃 2021-10-20 01:26 402阅读 0赞

问题描述

innobackupex备份过程需要保证备份数据一致性，通过刷新表缓存和加全局读锁(FLUSH TABLES WITH READ LOCK)获取备份位点，而为防止锁等待超时，会先设置：

SET SESSION lock_wait_timeout=31536000

在MySQL中，导致FLUSH TABLES WITH READ LOCK操作获取锁超时的场景有：

1、超长事务或超大DML操作
    2、超大表DDL操作

如果要解决锁超时问题，可以从加锁和KILL超长查询两方面入手，innobackupex命令有如下参数：

--no-lock
    Use this option to disable table lock with "FLUSH TABLES
    WITH READ LOCK". Use it only if ALL your tables are
    InnoDB and you DO NOT CARE about the binary log position
    of the backup. This option shouldn't be used if there are
    any DDL statements being executed or if any updates are
    happening on non-InnoDB tables (this includes the system
    MyISAM tables in the mysql database), otherwise it could
    lead to an inconsistent backup. If you are considering to
    use --no-lock because your backups are failing to acquire
    the lock, this could be because of incoming replication
    events preventing the lock from succeeding. Please try
    using --safe-slave-backup to momentarily stop the
    replication slave thread, this may help the backup to
    succeed and you then don't need to resort to using this
    option.
    
    --no-backup-locks
    This option controls if backup locks should be used
    instead of FLUSH TABLES WITH READ LOCK on the backup
    stage. The option has no effect when backup locks are not
    supported by the server. This option is enabled by
    default, disable with --no-backup-locks.
    
    --kill-long-query-type=name
    This option specifies which types of queries should be
    killed to unblock the global lock. Default is "all".
    
    --kill-long-queries-timeout=#
    This option specifies the number of seconds innobackupex
    waits between starting FLUSH TABLES WITH READ LOCK and
    killing those queries that block it. Default is 0
    seconds, which means innobackupex will not attempt to
    kill any queries.

如果在从库上执行备份，可以考虑暂停复制进程来防止阻塞：

--safe-slave-backup
    Stop slave SQL thread and wait to start backup until
    Slave_open_temp_tables in "SHOW STATUS" is zero. If there
    are no open temporary tables, the backup will take place,
    otherwise the SQL thread will be started and stopped
    until there are no open temporary tables. The backup will
    fail if Slave_open_temp_tables does not become zero after
    --safe-slave-backup-timeout seconds. The slave SQL thread
    will be restarted when the backup finishes.
    
    --safe-slave-backup-timeout=#
    How many seconds --safe-slave-backup should wait for
    Slave_open_temp_tables to become zero. (default 300)

转载于:https://www.cnblogs.com/gaogao67/p/10956208.html