How to update MySQL table without breaking website?

Question

Once a day, I need to update the MySQL table with a new file downloaded from the network using ftp and then mysqlimport. However, I want my site to run smoothly during the mysqlimport operation, which takes quite a lot of time (this is a large table). What would be a good way to ensure that users do not wait for the import to complete? I am going to import a temporary table and rename it. Is this a good plan?

Answer 1

A little-known fact is that you can combine more than one rename statement in mysql and it will do all 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 blocks any name resolution), but should be very fast.

Answer 2

If this is an incremental update case, you can simply activate the insertion of new data via cron / scheduling.

Download XYZ records every minute, see if the previous update has finished, create a lock file, insert delay, rinse and repeat.

If you want to get real information about this, update the script, check the server load at runtime and adjust the import quantity accordingly.

, .