Revoke write privileges vs setting database to readonly

Question

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.

1. Revoke write privileges:

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

2. 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?

Answer 1

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;