Revoke write privileges vs setting database to readonly I am planning to do a database migration to a new server. During the transition stage, I do not want Apache user to be able to write anything to the database. I have two options.


Revoke write privileges:

REVOKE INSERT, UPDATE ON `mydb`.* FROM 'apache'@'localhost';
FLUSH PRIVILEGES;

Set database to read-only mode:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = 1;



Are there any practical considerations for choosing one over the other?
I would choose Option 2

If you use Option 1 and revoke privileges, you have to put them back. The mysql grant tables are MyISAM. Should any crash, human error, or other unexpected event corrupt the tables, you have a mess to clean up.
Only those with SUPER privilege can perform writes when read_only is enabled. SUPER is not a database-level grant.
With Option 2, grants remain unchanged on disk and in memory.

Option 2 could also be written as
FLUSH TABLES;
SET GLOBAL read_only = 1;

That way, you only have to run SET GLOBAL read_only = 0; to get writes going again. FLUSH TABLES WITH READ LOCK does not halt InnoDB writes to Transaction Logs, Redo Logs, an Undo Logs. That's why SET GLOBAL read_only = 1; is your friend.
CAVEAT
In my early days a a DBA, I used to do this:
CREATE TABLE mysql.usercopy LIKE mysql.user;
CREATE TABLE mysql.root     LIKE mysql.user;
INSERT INTO mysql.usercopy SELECT * FROM mysql.user;
INSERT INTO mysql.root SELECT * FROM mysql.user WHERE user='root';
TRUNCATE TABLE mysql.user;
INSERT INTO mysql.user SELECT * FROM mysql.root;
FLUSH PRIVILEGES;

When I was done, I did this
TRUNCATE TABLE mysql.user;
INSERT INTO mysql.user SELECT * FROM mysql.usercopy;
FLUSH PRIVILEGES;

Then, I discovered SUPER.
EPILOGUE
Go with Option 2 as is, or change FLUSH TABLES WITH READ LOCK; to FLUSH TABLES;