发布于 由admin

MySQL运维与监控之：`MySQL`的`read_only`和`super_read_only`：其在数据库管理中的权限控制。

MySQL运维与监控之：MySQL的read_only和super_read_only：其在数据库管理中的权限控制

大家好！今天我们来深入探讨MySQL中两个重要的系统变量：read_only和super_read_only。它们在数据库管理中扮演着关键的角色，用于控制数据库的读写权限，尤其是在主从复制架构中。理解它们的工作原理和应用场景对于保障数据一致性和系统稳定性至关重要。

1. read_only: 基本只读模式

read_only是一个全局系统变量，用于设置MySQL实例为只读模式。 当read_only被设置为ON（或者1）时，MySQL实例将拒绝所有修改数据的操作，包括INSERT、UPDATE、DELETE，以及CREATE、ALTER、DROP等DDL语句。

用途：

  • 主从复制： 在主从复制架构中，从服务器通常设置为read_only，以避免从服务器上的数据被意外修改，从而保持与主服务器的数据一致性。
  • 数据备份： 在进行数据备份时，可以将数据库设置为read_only，以确保备份的数据是一致的，避免在备份过程中有数据被修改。
  • 维护模式： 在进行数据库维护操作（例如升级、迁移）时，可以将数据库设置为read_only，以防止用户修改数据，影响维护操作的进行。
  • 只读查询： 有些应用场景只需要进行只读查询，可以将数据库设置为read_only，以提高安全性，防止误操作。

设置方式：

-- 查看当前read_only的值
SHOW GLOBAL VARIABLES LIKE 'read_only';

-- 设置read_only为ON
SET GLOBAL read_only = ON;

-- 设置read_only为OFF
SET GLOBAL read_only = OFF;

注意事项：

  • read_only是一个全局变量，修改它会影响整个MySQL实例。
  • 具有SUPER权限的用户仍然可以执行修改数据的操作，即使read_only被设置为ON。

示例：

假设我们有一个主从复制架构，主服务器的IP地址是192.168.1.100，从服务器的IP地址是192.168.1.101。

  1. 在主服务器上创建数据库和表：
-- 在主服务器上执行
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);
INSERT INTO users (name) VALUES ('Alice');
  1. 配置从服务器进行复制 (假设已经完成了复制配置，这里只展示设置read_only)：
-- 在从服务器上执行
-- 查看当前read_only的值
SHOW GLOBAL VARIABLES LIKE 'read_only';

-- 设置read_only为ON
SET GLOBAL read_only = ON;

-- 尝试在从服务器上插入数据（会报错）
INSERT INTO testdb.users (name) VALUES ('Bob'); -- 报错：Error Code: 1290. The MySQL server is running with the --read-only option so it cannot execute this statement

-- 查看从服务器上的数据（只有Alice）
SELECT * FROM testdb.users;

上面的示例演示了当read_only被设置为ON时，从服务器拒绝了INSERT操作。

2. super_read_only: 更严格的只读模式

super_read_only是MySQL 8.0引入的一个新的全局系统变量，它比read_only更加严格。 当super_read_only被设置为ON（或者1）时，MySQL实例将拒绝所有修改数据的操作，包括具有SUPER权限的用户。 这意味着即使拥有SUPER权限的用户也无法在数据库上执行INSERT、UPDATE、DELETE等操作。

用途：

  • 防止误操作： super_read_only可以防止具有SUPER权限的用户在不知情的情况下修改数据。
  • 强制只读： 在某些情况下，我们需要强制数据库处于只读模式，即使是管理员也无法修改数据，这时可以使用super_read_only。
  • 增强安全性： super_read_only可以增强数据库的安全性，防止恶意用户利用SUPER权限修改数据。
  • 自动化运维： super_read_only 能够更好地支持自动化运维工具，例如在进行数据迁移或者备份时，可以确保在整个过程中没有数据写入，即使自动化工具拥有 SUPER 权限。

设置方式：

-- 查看当前super_read_only的值
SHOW GLOBAL VARIABLES LIKE 'super_read_only';

-- 设置super_read_only为ON
SET GLOBAL super_read_only = ON;

-- 设置super_read_only为OFF
SET GLOBAL super_read_only = OFF;

注意事项：

  • super_read_only是一个全局变量，修改它会影响整个MySQL实例。
  • 只有具有SYSTEM_VARIABLES_ADMIN权限的用户才能修改super_read_only的值。
  • super_read_only的优先级高于read_only。 如果super_read_only被设置为ON，则read_only的值将被忽略。

示例：

假设我们已经设置了super_read_only = ON，并且有一个具有SUPER权限的用户admin。

-- 以admin用户登录
-- 尝试插入数据（会报错）
INSERT INTO testdb.users (name) VALUES ('Bob'); -- 报错：Error Code: 1290. The MySQL server is running with the --super-read-only option so it cannot execute this statement

-- 尝试修改数据（会报错）
UPDATE testdb.users SET name = 'Charlie' WHERE id = 1; -- 报错：Error Code: 1290. The MySQL server is running with the --super-read-only option so it cannot execute this statement

上面的示例演示了即使具有SUPER权限的用户也无法在super_read_only被设置为ON时修改数据。

3. 权限控制的差异对比

为了更清晰地理解read_only和super_read_only之间的差异，我们使用表格进行对比：

特性 read_only super_read_only
适用版本 所有MySQL版本 MySQL 8.0及以上
权限要求 无特殊权限要求 需要SYSTEM_VARIABLES_ADMIN权限
影响范围 整个MySQL实例 整个MySQL实例
是否允许SUPER用户修改 允许 不允许
主要用途 主从复制、数据备份、维护模式 强制只读、防止误操作、增强安全性、自动化运维
优先级 低于super_read_only 高于read_only

4. 应用场景案例分析

案例1：自动化数据迁移

假设我们需要将一个大型数据库从一个服务器迁移到另一个服务器。 为了确保数据的一致性，我们需要在迁移过程中阻止所有的数据写入操作。

  1. 在源服务器上设置super_read_only = ON，阻止所有的数据写入操作，包括具有SUPER权限的用户。
-- 在源服务器上执行
SET GLOBAL super_read_only = ON;
  1. 使用mysqldump或其他工具将数据导出到目标服务器。
mysqldump -u root -p --all-databases > all_databases.sql
  1. 在目标服务器上导入数据。
-- 在目标服务器上执行
mysql -u root -p < all_databases.sql
  1. 在源服务器上设置super_read_only = OFF，恢复数据写入操作。
-- 在源服务器上执行
SET GLOBAL super_read_only = OFF;

案例2：只读审计数据库

假设我们需要创建一个只读的审计数据库，用于存储用户的操作日志。 为了防止用户篡改审计数据，我们需要强制数据库处于只读模式。

  1. 创建一个新的MySQL实例，用于存储审计数据。

  2. 设置super_read_only = ON，强制数据库处于只读模式。

-- 在审计数据库实例上执行
SET GLOBAL super_read_only = ON;
  1. 配置应用程序将审计日志写入到该数据库。

案例3：主从复制环境下的灵活控制

在一些特殊的主从复制环境下，我们可能需要从库执行一些特殊的维护操作，例如重建索引。 这时，如果直接设置 read_only = OFF，可能会导致数据不一致。 一个更安全的选择是：

  1. 临时关闭复制： STOP SLAVE;
  2. 设置 read_only = OFF;
  3. 执行维护操作。
  4. 设置 read_only = ON;
  5. 启动复制： START SLAVE;

这种方式允许在维护期间进行写操作，但必须确保这些操作不会影响数据的一致性。 更好的方式，通常是通过切换到另一台可写的从库进行维护，避免影响线上只读查询服务。

5. 使用编程语言进行管理

我们可以使用编程语言（如Python）来管理read_only和super_read_only。 以下是一个使用Python的mysql.connector库来设置read_only的示例：

import mysql.connector

def set_read_only(host, user, password, read_only):
    try:
        mydb = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database='mysql' # 需要连接到mysql数据库才能设置全局变量
        )

        mycursor = mydb.cursor()

        sql = "SET GLOBAL read_only = %s"
        val = (read_only,)

        mycursor.execute(sql, val)

        mydb.commit()

        print(mycursor.rowcount, "record(s) affected")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if mydb:
            mycursor.close()
            mydb.close()

# 示例用法
set_read_only("192.168.1.101", "root", "password", 'ON') # 设置 read_only 为 ON

类似地，我们可以使用Python来管理super_read_only，但需要确保用于连接MySQL的用户具有SYSTEM_VARIABLES_ADMIN权限。

6. 如何选择使用哪个变量？

选择使用read_only还是super_read_only取决于具体的需求和场景。

  • 如果只需要在主从复制架构中保护从服务器的数据，或者在数据备份和维护期间防止数据被修改，可以使用read_only。
  • 如果需要强制数据库处于只读模式，即使是具有SUPER权限的用户也不能修改数据，或者需要增强数据库的安全性，防止恶意用户利用SUPER权限修改数据，可以使用super_read_only。
  • 如果需要在自动化运维场景中确保数据的一致性，可以使用super_read_only。

在实际应用中，建议根据具体的安全需求和业务场景，谨慎选择合适的只读模式。

7. read_only 与 super_read_only: 保障数据一致性

read_only 和 super_read_only 是数据库管理中重要的权限控制工具，能够保障数据一致性，尤其是在主从复制、数据备份和自动化运维等场景下。

8. 灵活运用，安全第一

理解 read_only 和 super_read_only 的差异和应用场景，有助于我们灵活地控制数据库的读写权限，提升数据库的安全性。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注