如何通过命令行将 MySQL 临时设置为只读？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18401120/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I set MySQL temporarily to read-only through the command line?
提问by Jimmy C
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 用户具有写入权限，但出于安全原因，我想暂时将其设置为只读状态。是否可以从命令行执行此操作？
回答by Flo Doe
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 权限，然后再授予它们。
回答by txyoji
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
https://dev.mysql.com/doc/refman/5.6/en/set-transaction.html
"READ ONLY" also offers a modest performance benefit.
“只读”还提供适度的性能优势。
回答by Sal00m
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
注意：查看报价，也许我忘记了什么
