Periodically replacing a running MySQL database

Question

I've got a large-ish MySQL database which contains weather information. The DB is about 3.5 million rows, and 486 MB in size.

I get a refresh of this data every 6 hours, in the form of a mysql dump file that I can import. It takes more than 2 minutes to import the data and probably a similar amount of time to create the index.

Any thoughts on how I can import this data while still keeping the DB available and not losing responsiveness? My first thought was two have two databases within the same MySQL instance. I'd be running off DB1 and would load data into DB2 and then switch. However, I'm concerned that the load process would make DB1 unresponsive (or significantly slow).

So, my next thought is two have two different MySQL instances running, on different ports. While DB instance 1 is serving queries, DB instance 2 can be loaded with the next dataset. Then on the next query, the code switches to DB2.

That seems like it would work to me, but I wanted to check with some who have tried similar things in the past to see if there were any "gotchas" I was missing.

Thoughts?

Answer 1

Have two databases and switch between them after the import finishes each time.

Load on one database shouldn't make the other database unresponsive. 486MB is not too big for it all to fit in memory a couple of times over - depending I guess on whether you're in a small virtual server.

But even so, two MySQL instances on one server shouldn't present any differences in performance than two databases on one instance, except that two instances may actually take more memory and be more complicated to set up.