配置MySQL为只读数据库

0    73    5

👉 本文共约1175个字,系统预计阅读时间或需5分钟。

相关参数说明


在MySQL 数据库中,只读有关的参数有如下:

这里需要注意一点,从库开启 read_only/ super_read_only 并不影响主从同步,即 salve 端仍然会读取 master 上的日志,并且在 slave 实例中应用日志,保证主从数据库同步一致。

read_only:
参数默认不开启,开启后会阻止没有 super 权限的用户执行数据库变更操作。开启后,普通权限用户执行插入、更新、删除等操作时,会提示 —read-only 错误。但具有 CONNECTION_ADMIN 和 SUPER权限的用户仍可执行变更操作。

If the read_only system variable is enabled, the server permits no client updates except from users who have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege). This variable is disabled by default.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only

super_read_only:
默认关闭,开启后不仅会阻止普通用户,也会阻止具有 CONNECTION_ADMIN 和 SUPER权限的用户对数据库进行变更操作。

If the read_only system variable is enabled, the server permits no client updates except from users who have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege). If the super_read_only system variable is also enabled, the server prohibits client updates even from users who have CONNECTION_ADMIN or SUPER.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only

transaction_read_only:

The transaction access mode. The value can be OFF (read/write; the default) or ON (read only).
The transaction access mode has three scopes: global, session, and next transaction. This three-scope implementation leads to some nonstandard access-mode assignment semantics, as described later.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_transaction_read_only

innodb_read_only:

tarts InnoDB in read-only mode. For distributing database applications or data sets on read-only media. Can also be used in data warehouses to share the same data directory between multiple instances.
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_read_only

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

操作示例


查看 read_only 参数

动态修改 read_only 参数 (若想重启生效 则需将 read_only = 1 加入配置文件中)

动态修改 super_read_only 参数 (若想重启生效 则需将 read_only = 1 加入配置文件中)

开启 super_read_only

开启read_only

参考

本文链接:https://www.cndba.cn/cndba/dave/article/108033

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

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

16 − 6 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部