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

mysql shell command-line readonly
Jimmy C picture Jimmy C · Aug 23, 2013 · Viewed 54.4k times · Source

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?

Answer

Flo Doe picture Flo Doe · Aug 23, 2013

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.

Related questions

How to create a database from shell command?

I'm looking for something like createdb in PostgreSQL or any other solution that would allow me to create database with a help of a shell command. Any hints?

Read More
Shell - one line query

I need to execute a mysql query in one line using bash. It should be something like this: mysql database --user='root' --password='my-password' < query.file But instead of the < query.file it would like to use a …

Read More
Access mysql remote database from command line

I have a server with Rackspace. I want to access the database from my local machine command line. I tried like: mysql -u username -h my.application.com -ppassword But it gives an error: ERROR 2003 (HY000): Can't connect to MySQL …

Read More