Mysql – How did you setup MySQL Replication with autofailover that is app transparent

There's a lot to consider. I'm going to narrow this down to a single problem and offer one solution, although there's many approaches.

Problem: You need AD-HOC queries on lots of rows.

First, setup a server to handle writes and a replicated slave (running some flavor of MySQL 5.5.x) to handle reads. My personal preference is Percona Server. Your mileage may vary.

Once you have replication caught up, ALTER your slave table to: engine=InnoDB, ROW_FORMAT=COMPRESSED, partition by date, and index the data to optimize your query WHERE clauses. Each of these have an optimization for your case and there is further tuning required.

Finally, setup a schedule to archive/shard the data when it falls out of scope. Say 2-3 years. Depends on your SLA for providing back reporting. You can always union it if you have to.

MySQL 5.6 will have Memcached built in which will allow you to store pre-processed results. Map/Reduce is great, as long as it's not the first run of the reduce function. NoSQL is great as long as the data is already processed. Neither is really your problem.

Additions 2012-06-13: Since it appears my recommendations have stirred another response, I'll submit my reasons:

1. Why InnoDB in this case? With the constant insertions of many rows, you can use InnoDB to avoid implicit read locks at the table level.
2. Why COMPRESSED + Barracuda? "The compression means less data is transferred between disk and memory, and takes up less space in memory. The benefits are amplified for tables with secondary indexes, because index data is compressed also."
3. Why partition by date? Reduce scan breadth.
4. Why index properly? Decrease search times by reducing cpu bottleneck through elimination of full table scans.

When you shifted the IP within your application, any DB Connections that were open at the moment were totally unaware of the move. A quick netstat | grep -i mysql would reveal that on Master1.

You would be better off doing the cutover of the IP as follows:

• On Master1
  • FLUSH HOSTS;
  • service mysql stop
• Change the IP in the App
• On all web servers, service httpd restart

In order to safeguard the app from having to edit it for the sake of assigning a new IP, try using a DB VIP instead.

For example:

• Master1 is 10.1.2.30
• Master2 is 10.1.2.40
• Use 10.1.2.70 as the DBVIP

Here is what you can setup

• On Master1, run ip addr add 10.1.2.70/24 dev eth1
• Use 10.1.2.70 in your app

Then, when it is time to cutover, do the following

• On Master1
  • FLUSH HOSTS;
  • service mysql stop
  • ip addr del 10.1.2.70/24 dev eth1
• On Master2
  • ip addr add 10.1.2.70/24 dev eth1
  • SHOW SLAVE STATUS\G and make sure all final SQL statements from Master1 executed
• On all web servers, service httpd restart

That way, a cutover would not involve editing any part of the app. Please notice that I did not mention running STOP SLAVE anywhere because this would allow any final SQL statements to flow over from Master1 to Master2 once mysql is stopped on Master1.