MySQL shell/client: Read-only access, or "safe history" option?

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] ...

..开发者_JAVA技巧. 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)?

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 BEGIN when you open the shell, and only COMMIT if/when you really want to.

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:

    GRANT SELECT ON *.* TO readonly@'%' IDENTIFIED BY 'password';
    FLUSH PRIVILEGES;