如何通过命令行将MySQL临时设置为只读？

I'm creating a bash script which, among other things, gathers some data from a MySQL database. My MySQL user has write privileges, but for safety reasons I would like to temporarily set it to a read only state. Is it possible to do this from a command line?

我正在创建一个bash脚本，除其他外，它从MySQL数据库中收集一些数据。我的MySQL用户具有写权限，但出于安全考虑，我想暂时将其设置为只读状态。是否可以从命令行执行此操作？

3 个解决方案

#1

To answer your original question, you can put your whole database to read only mode by this commands:

要回答原始问题，可以通过以下命令将整个数据库置于只读模式：

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = 1;

and back to normal mode with:

并返回正常模式：

SET GLOBAL read_only = 0;
UNLOCK TABLES;

Beware that this is an operation which will have deep impact on the behavior of the database. So before executing this, read the available documentation to the commands above. A much more common way is to revoke DML privileges from the specific user and afterwards grant them back.

请注意，这是一项对数据库行为产生深远影响的操作。因此，在执行此操作之前，请阅读上述命令的可用文档。一种更常见的方法是从特定用户撤消DML权限，然后将其授予回授权。

#2

Well, if the user right now has all privileges first, you need to revoke it

好吧，如果用户现在拥有所有权限，则需要撤销它

$>mysql -u DB_USER -pDB_PASS --execute="REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'YOUR_USER';"

After that you give him, the select permission

之后你给他，选择权限

$>mysql -u DB_USER -pDB_PASS --execute="GRANT SELECT ON 'YOUR_DATABASE'@.* TO 'YOUR_USER'@'%';FLUSH PRIVILEGES;"

Do your stuff and after that grant privileges again to the user

做你的东西，然后再授予用户权限

$>mysql -u DB_USER -pDB_PASS --execute="GRANT ALL ON 'YOUR_DATABASE'@.* TO 'YOUR_USER'@'%';FLUSH PRIVILEGES;"

And that's all folks

这就是所有人

NOTE: Review for quotation, perhaps i forgot something

注意：检查报价，也许我忘了一些东西

#3

If you're using MySQL 5.6 or newer and InnoDB, you can make a session read-only.

如果您使用的是MySQL 5.6或更高版本以及InnoDB，则可以将会话设置为只读。

SET SESSION TRANSACTION READ ONLY;

https://dev.mysql.com/doc/refman/5.6/en/set-transaction.html

"READ ONLY" also offers a modest performance benefit.

“READ ONLY”也提供了适度的性能优势。

#1