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';
  2. Set database to read-only mode:

    SET GLOBAL read_only = 1;

Are there any practical considerations for choosing one over the other?

  • Just a side note, read-only is usually required and recommended for the actual switchover period, but there are many ways to do a migration in R/W mode for most of the migration process, if needed. You probably knew that and/or it is unnecessary, but just in case!
    – jynus
    Commented Aug 22, 2014 at 16:33

1 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

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.


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;

When I was done, I did this

TRUNCATE TABLE mysql.user;
INSERT INTO mysql.user SELECT * FROM mysql.usercopy;

Then, I discovered SUPER.


Go with Option 2 as is, or change FLUSH TABLES WITH READ LOCK; to FLUSH TABLES;

  • 1
    There are some cases in which you may want to go one way or another, but there is little context in the question to choose one over the other. Rolando has some legitimate ideas to have into account. Regarding the Epilogue, I would just suggest taking out the flush tables completely- OP doesn't say that he requires memory-disk syncronization (maybe he is using mysqldump), and as Rolando says well, it has little effect for an InnoDB-only DB (except on .frms).
    – jynus
    Commented Aug 22, 2014 at 16:28
  • 1
    @jynus You are right for the all-InnoDB database. For the brave souls still using MyISAM, FLUSH TABLES; is still a must. Commented Aug 22, 2014 at 16:32
  • 1
    ...if you are going to play around with the filesystem (e.g., a binay import/export), that is not mentioned. Migration may mean logical, (mysqldump, replication), in which case it is not needed. We do not know. If it is a binary copy, the FLUSH TABLES must be done after stoping writes (for example, after setting the read-only option), aka FTWRL.
    – jynus
    Commented Aug 22, 2014 at 16:38
  • @jynus and Rolando, I am using LVM snapshot to backup the DB files which are InnoDB except for mysql grant table. I am little confused. Do I need FLUSH TABLES or FLUSH TABLES WITH READ LOCK after or before SET GLOBAL read_only = 1? Commented Aug 23, 2014 at 2:20
  • @Question Overflow if you are going to use LVM snapshots, use FLUSH TABLES WITH READ LOCK to make the non-transactional objects consistent with disk (.frm, MyISAM files) and prevent against further modifications. If you do not intend to continue writing afterwards, SET GLOBAL read_only = 1 before the lock, so writes fail instead of being locked.
    – jynus
    Commented Aug 24, 2014 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.