Search code examples
mysqllockingflushunlock

Mysql - lock and flush pending data for a specific table

I need to temporarily rename a table. There are inserts to this table all the time. So the idea is to lock the table for writing and reading, then flush all the possible pending inserts, then rename the table, do a couple of things, rename the table back and unlock it so it will be usable again.

What steps should one take to achieve that (or closest as possible) task?

Thanks!

Solution

  • Ok, figured it out.

    lock tables sometable write;
flush tables sometable;
unlock tables; rename table `sometable` to `sometable_locked`;
-- do something useful
rename table `sometable_locked` to `sometable`;

    Or

    lock tables sometable write;
flush tables sometable;
alter table `sometable` rename to `sometable_locked`;
-- do something useful
unlock tables; alter table `sometable_locked` rename to `sometable`;