关于mysql:MySQL设置数据库为只读

62次阅读

共计 2741 个字符，预计需要花费 7 分钟才能阅读完成。

前言：

默认状况下，咱们的 MySQL 实例是可读写的。但有些状况下，咱们能够将整个实例设置为只读状态，比方做迁徙保护的时候或者将从库设为只读。本篇文章咱们来看下 MySQL 设置只读相干常识。

MySQL 零碎中，提供有 read_only 和 super_read_only 两个只读参数，参考官网文档，这里介绍下这两个参数的作用：

read_only 参数默认不开启，开启后会阻止没有 super 权限的用户执行数据库变更操作。开启后，一般权限用户执行插入、更新、删除等操作时，会提醒 –read-only 谬误。但具备 super 权限的用户仍可执行变更操作。

super_read_only 参数同样默认敞开，开启后不仅会阻止普通用户，也会阻止具备 super 权限的用户对数据库进行变更操作。

read_only 和 super_read_only 是有关联的，二者之间的关系如下：

设置 super_read_only=on，也就隐式地设置了 read_only=on。
设置 read_only=off，也就隐式地设置了 super_read_only=off。
能够独自开启 read_only 而不开启 super_read_only。

不过，从库开启 read_only 并不影响主从同步，即 salve 端依然会读取 master 上的日志，并且在 slave 实例中利用日志，保障主从数据库同步统一。（经测试，从库端开启 super_read_only 仍不影响主从同步。）

上面咱们具体来操作下，看下 read_only 参数的用法：

# 查看 read_only 参数
mysql> show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+

# 动静批改 read_only 参数（若想重启失效 则需将 read_only = 1 退出配置文件中）mysql> set global read_only = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

# read_only 开启的状况下 操作数据
# 应用超级权限用户
mysql> create table tb_a (a int);
Query OK, 0 rows affected (0.05 sec)
# 应用一般权限用户
mysql> create table tb_b (b int); 
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

# 开启 super_read_only，再次应用超级权限用户来操作数据
mysql> set global super_read_only = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'super_read_only';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| super_read_only | ON    |
+-----------------+-------+
mysql> create table tb_c (c int);  
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

# 敞开 read_only 参数
mysql> set global read_only = 0;
Query OK, 0 rows affected (0.00 sec)

除了 read_only 参数外，执行 flush tables with read lock 也可将数据库设置为只读状态，那么二者有什么区别呢？咱们先来理解下 flush tables with read lock 的作用。

执行此命令会给数据库加全局读锁，使得数据库处于只读状态，以下语句会被阻塞：数据更新语句（增删改）、数据定义语句（建表、批改表构造等）和更新类事务的提交语句。上面咱们来具体试验下：

# 执行 FTWRL
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.02 sec)

# 进行数据变更操作
mysql> insert into tb_a values (1);
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

# 解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb_a values (1);
Query OK, 1 row affected (0.01 sec)

值得注意的是，从库端执行 flush tables with read lock 会导致 SQL 线程卡住，主备提早。与开启 read_only 参数不同的是，执行 flush tables with read lock 后，其余客户端执行数据变更操作会继续期待而不是立刻报错，极其容易引起数据库 hang 住，执行这个命令还是要小心的。

以集体数据库运维教训来讲，个别只有从库须要设置只读状态，从库端倡议开启 read_only 或 super_read_only，防止人为写入。flush tables with read lock 实用于进行数据迁徙时，能够保障数据库不产生数据扭转，不过要留神及时解锁。

总结：

本篇文章次要介绍了 MySQL 只读状态相干常识，其实除了从库外，其余实例很少设置全局只读，只是遇到某种需要的状况下须要将数据库设为只读状态，写本篇文章的目标也是遇到此类需要时，能够有个参考。

正文完