How do I set MySQL temporarily to read-only through the command line?

Question

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?

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

Solution 2

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

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.

Solution 3

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

Comments

[fancyPants]

Best answer, if the OP really wants to set the whole database in read-only mode. Title and actual question are confusing here. Anyway, +1 for simplest solution.

[sage]

To save others some searching, here's what I read before playing with these: read_only global (includes mention of FLUSH TABLES WITH READ LOCK;) and using these for backups.

[Phoenix]

After i did the above method, my database still in Read+Write mode only.

[David Rissato Cruz]

That should be the accepted answer for this question.