MySQL shell/客户端：只读访问或“安全历史记录”选项？
昨天我正在编写一个 shell 脚本来执行一些中等复杂的表插入到 MySQL 数据库的操作。当然，我保持一个 mysql 客户端 shell 窗口打开，以运行 describe 命令、示例查询，并在测试周期之间删除我的测试行。
是的，这是在实时生产数据库上。
当我完成编码后，我要求一位同事在运行脚本来处理批处理条目之前检查我的工作。我们仔细检查了所有内容，他同意这对他来说看起来很合适，然后我就开始了剧本。没问题。
然后我回到实时 shell，从历史记录中提取一行，更改 where 子句以查看结果插入，然后点击 [Enter] ...
...不幸的是我没有查看我正在编辑的整个命令！这是一个delete 语句，而不是一个select 语句。
哎呀。
好的，所以我知道我可以设置一个不同的 DBMS 服务器，将该数据库的转储恢复到该服务器，并在其上完成所有测试。我们都知道，这样会更安全、更有纪律。我们也都知道，有时在现场商店工作更方便。 （在这种情况下，风险比您预期的要低一些......有问题的数据库用于批处理，我们能够在删除<后10分钟内从仅20分钟前的备份进行恢复/代码>）。
然而，这让我想起一个问题：mysql 客户端 shell 是否有某些选项（或某些补丁）使其成为只读？是否有一些选项可以修改它在交互历史记录中存储的内容？ （将 delete 和 drop table 以及类似的“危险”语句标记为“在重新执行之前提示验证？”的东西是否 mysql客户端有类似于 bash 的 HISTIGNORE 功能吗？
什么是更安全的方法来完成此类工作（缺少在数据库的完全独立的开发副本上工作）？
Yesterday I was working on a shell script to perform some moderately complex table insertions to a MySQL database. Naturally I was keeping a mysql client shell window open to for running describe commands, sample queries, and to remove my test rows between test cycles.
Yes this was on a live, production database.
At the point where I was done with my coding I asked a colleague to look over my work before running the script to process a batch entries. We went over everything, he agreed that it looked right to him, and I fired off the script. No problem.
Then I went back to my live shell, pulled up a line from the history, changed the where clause to look at the resulting insertions, and hit [Enter] ...
... unfortunately I hadn't looked at the whole command I was editing! It was a delete statement rather than a select.
Ooops.
Okay, so I know that I could have set up a different DBMS server, restored a dump of this DB to that and done all my testing thereon. We all know that this it would have been the safer and more disciplined. We also all know that sometimes it's more expedient to work on a live store. (In this case the risks are somewhat lower than you might expect ... the DB in question is used for batch processing and we were able to restore from a backup that was only 20 minutes old within 10 minutes of the delete).
However, this brings to mind a question: Is there some option (or some patch) to the mysql client shell that would it read-only? Is there some option to modify what it stores in its interactive history? (Something which would mark delete and drop table and similarly "dangerous" statements as "prompt for verification before re-executing?" Does the mysql client have something similar to the bash HISTIGNORE feature?
What is a safer way to do this sort of work (short of working on a totally separate developmental copy of the DB)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您想要完全只读访问权限，请创建一个没有任何写入权限的用户帐户。
或者只是习惯在事务中工作。 :) 当您打开 shell 时输入
BEGIN，只有在您确实需要时才输入COMMIT。If you want completely read-only access, create an user account without any write privileges.
Alternatively just get used to working in a transaction. :) Type
BEGINwhen you open the shell, and onlyCOMMITif/when you really want to.--safe-updates 选项可以帮助您实现这一目标。
最好的方法是创建一个新用户进行只读访问并只允许 SELECT。例如：
The --safe-updates option gets you part way there.
The best way would be to create a new user for read only access and only allow SELECT. For example: