How to refresh a MySQL table without website interruption?
high-availability, mysql, mysqlimport
Solution
A little known fact is that you can concatenate more than one rename statement in mysql and it will perform all of the name changes atomically.
create table newtable like oldtable;
insert into newtable .......
rename table oldtable to deleteme, newtable to oldtable;
drop table deleteme;
The rename table will block readers (in fact it will block any resolution of the name) but should be a very fast operation.
Problem
Once a day I need to update an MySQL table with a new file downloaded from the Net using ftp and then mysqlimport. However, I want my website to keep running smoothly during the mysqlimport operation, which takes quite some time (it's a big table). What would be a good way to assure that users do not wait for the import to finish? I am thinking of importing to a temporary table and then renaming it. Is that a good plan?