How to setup a script that automatically clones the DB every 2 hours? I'm planning to build a system that will have 30+ tables and 100+ million rows in a few of those. Going to use MySQL - InnoDB (any better alternative for this?)

My scripts are going to add a couple of hundreds of thousands of clicks to the database every day. On the other hand, I'd like to do heavy database queries during the day as well.

What I came us with is to have two different servers. Server A would take all the clicks and store them and Server B would work on retrieving the results.

Question A: Is this the right approach to do?
Question B: Is it possible to set up a script that's cloning the database over from Server A to Server B - so the data is semi-up to date?
You can't insert into one MySQL instance and expect the data to show up on another instance -- at least not without somehow inserting on the second instance.  But this defeats your original goal -- a performance problem caused by battling inserts and selects.

A Master-Slave setup does what I just described -- You insert on the Master; that insert is replicated to the Slave, where it is again executed.  Then you read from the Slave.  So, problem not solved.

First of all, you must use InnoDB, not MyISAM.

Are your "clicks" counters?  Or are they rows containing more information than just "1 more click for X"?  Assuming just a counter, such as INT UNSIGNED, ...

Second, keep the click counters in a table by themselves.  This table would have a PRIMARY KEY that is the same as the main table (with the possible exception of AUTO_INCREMENT.

But if that is not enough, then consider ways of collecting the clicks and applying them "after a while".  One ways would be a variant on http://mysql.rjweb.org/doc.php/staging_table .  This link would also provide a good way to gather whole rows instead of just a counter.

How many clicks per second do you get?  "Hundreds of thousands per day is only a few per second.  Do you really have a problem?