在MySQL 8.0中,引入了一个轻量级的备份锁,这个锁可以保证备份一致性,而且阻塞的操作相对比较少,是一个非常重要的新特性,接下来我们就来了解一下。
我们知道,在之前版本中,在物理备份的最后阶段,需要执行flush table with read lock,简称FTWRL,以获取一致性状态;FTWRL会关闭所有打开的表,并加上一把全局读锁,因此这个命令杀伤性非常大,容易造成各种阻塞。下面这个场景,相信大家都非常熟悉,FTWRL被慢查询 或 大事务 或 大表DDL阻塞,后续select和DML又被FTWRL阻塞,最终甚至导致实例崩溃。
mysql> select * from information_schema.processlist where user='test' and command<>'sleep';
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------------------------------------------+
| 18 | test | localhost | test | Query | 59 | executing | select * from sbtest1 order by pad limit 1 |
| 20 | test | localhost | test | Query | 56 | Waiting for table metadata lock | alter table sbtest1 add column a int |
| 21 | test | localhost | test | Query | 52 | Waiting for global read lock | flush table with read lock |
| 22 | test | localhost | test | Query | 49 | Waiting for table metadata lock | select * from sbtest1 limit 1 |
| 23 | test | localhost | test | Query | 15 | Waiting for table metadata lock | select * from sbtest1 limit 1 |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
就算我们把备份放在从库执行,同样也不能完全避免;如果从库需要同步大事务 或 分担读压力,就有可能会阻塞FTWRL,进而再阻塞后续操作;另外,MTS和FTWRL的死锁bug,也可能会导致类似问题。
因此,总的来说,FTWRL还是一个重量级的锁,因为其既会被读写阻塞、又会阻塞读写,容易造成各种阻塞甚至实例崩溃。Oracle官方团队当然也意识到了这个问题,并在MySQL 8.0中提供了新的解决方案,让我们接着往下看。
在MySQL 8.0中,为了解决备份FTWRL的问题,引入了轻量级的备份锁;可以通过LOCK INSTANCE FOR BACKUP和UNLOCK INSTANCE,以获取和释放备份锁,执行该语句需要BACKUP_ADMIN权限。
(1)backup lock不会阻塞读写操作
session 1:
mysql> lock instance for backup;
Query OK, 0 rows affected (0.00 sec)
session 2:
mysql> select * from sbtest1 order by pad limit 1;
session 3:
mysql> insert into sbtest2 select * from sbtest1;
mysql> select * from information_schema.processlist where user='root' and command<>'sleep';
+----+------+-----------+------+---------+------+-----------+-------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+-----------+------+---------+------+-----------+-------------------------------------------------------------------------------------+
| 19 | test | localhost | test | Query | 8 | executing | select * from sbtest1 order by pad limit 1 |
| 20 | test | localhost | test | Query | 3 | executing | insert into sbtest2 select * from sbtest1 |
+----+------+-----------+------+---------+------+-----------+-------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
(2)不过,backup lock会阻塞大部分DDL操作,包括创建/删除表、加/减字段、增/删索引、optimize/analyze/repair table等
mysql> select * from information_schema.processlist where user='root' and command<>'sleep' order by id;
+----+------+-----------+------+---------+------+-------------------------+-------------------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+-----------+------+---------+------+-------------------------+-------------------------------------------------------------------------------------------------+
| 20 | test | localhost | test | Query | 270 | Waiting for backup lock | create table sbtest2 like sbtest1 |
| 21 | test | localhost | test | Query | 220 | Waiting for backup lock | alter table sbtest2 add column a int |
| 22 | test | localhost | test | Query | 253 | Waiting for backup lock | drop table sbtest2 |
| 35 | test | localhost | test | Query | 195 | Waiting for backup lock | alter table sbtest2 drop column a |
| 36 | test | localhost | test | Query | 165 | Waiting for backup lock | alter table sbtest2 add index idx_c(c) |
| 37 | test | localhost | test | Query | 144 | Waiting for backup lock | alter table sbtest2 drop index idx_c |
| 38 | test | localhost | test | Query | 123 | Waiting for backup lock | optimize table sbtest2 |
| 39 | test | localhost | test | Query | 102 | Waiting for backup lock | analyze table sbtest2 |
| 40 | test | localhost | test | Query | 75 | Waiting for backup lock | repair table sbtest2 |
| 41 | test | localhost | test | Query | 15 | Waiting for backup lock | truncate table sbtest2 |
+----+------+-----------+------+---------+------+-------------------------+-------------------------------------------------------------------------------------------------+
11 rows in set (0.00 sec)
总的来说,备份锁还是非常实用的,毕竟其不会影响业务的正常读写;至于备份锁和DDL操作的冲突,还是有很多方法可以避免,比如错开备份和变更的时间、通过pt-online-schema-change/gh-ost避免长时间阻塞等等。随着备份锁的引入,Oracle官方备份工具MEB 8.0和Percona开源备份工具XtraBackup 8.0,也是更新了对backup lock的支持,具体参考如下:
MEB 8.0:https://docs.oracle.com/cd/E17952_01/mysql-enterprise-backup-8.0-en/mysqlbackup.backup.html
XtraBackup 8.0:https://www.percona.com/doc/percona-server/8.0/management/backup_locks.html
备份锁,还是一个非常实用的新特性,可以解决非常多的日常生产问题,值得大家关注一下。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。