MySQL 8.0新特性 — 备份锁
原创MySQL 8.0新特性 — 备份锁
原创
前言
在MySQL 8.0中，引入了一个轻量级的备份锁，这个锁可以保证备份一致性，而且阻塞的操作相对比较少，是一个非常重要的新特性，接下来我们就来了解一下。
FTWRL
我们知道，在之前版本中，在物理备份的最后阶段，需要执行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中提供了新的解决方案，让我们接着往下看。
Backup Lock
在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 删除。