FLUSH TABLES WITH READ LOCK和set global read_only=1的区别

最新推荐文章于 2025-05-30 22:00:09 发布

kunatnet

最新推荐文章于 2025-05-30 22:00:09 发布

阅读量3.1k

点赞数

分类专栏： mysql

mysql 专栏收录该内容

31 篇文章

订阅专栏

本文详细介绍了MySQL中设置全局只读模式的方法及其效果，对比了使用set global read_only与FLUSH TABLES WITH READ LOCK的区别，并通过示例展示了不同场景下只读锁的行为。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

set global read_only是全局级别的,置为1之后root用户还可以写，其他用户不能写

FLUSH TABLES WITH READ LOCK是表级别的（使用UNLOCK tables解锁），执行之后所有用户都不能写，但是在flush tables以后lock就失效了，而set global read_only不会失效

set global read_only=true或set global read_only=on或set global read_only=1一样

set global read_only=false或set global read_only=off或set global read_only=0一样

1: select @@read_only \G
->@@read_only: 0
1: CREATE TABLE IF NOT EXISTS bla (id BIGINT(16), PRIMARY KEY(id));
1: LOCK TABLE bla READ;
2: set global read_only=0;
->Will block until the lock from connection 1 is released.

1: UNLOCK TABLES;
2: set global read_only=1;
1: select @@read_only \G
->@@read_only: 1
1: LOCK TABLE bla READ;
2: set global read_only=1;
->Will block until the lock from connection 1 is released.

1: UNLOCK TABLES;
1: select @@read_only \G
->@@read_only: 1
1: LOCK TABLE bla READ;
2: set global read_only=0;
->Will block until the lock from connection 1 is released.