4

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?

  • 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 Aug 22 '14 at 16:33
6

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;

  • 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 Aug 22 '14 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. – RolandoMySQLDBA Aug 22 '14 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 Aug 22 '14 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? – Question Overflow Aug 23 '14 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 Aug 24 '14 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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