MySQL InnoDB – Revoke Write Privileges vs Setting Database to Read-Only

innodbmigrationMySQLpermissionsread-only-database

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?

Best Answer

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;

Related Question