Building a horizontally scalable MySQL infrastructure using InnoDB Cluster involves setting up a cluster of MySQL servers where data is automatically replicated across nodes, providing both high availability and the foundation for scaling out read operations. Here's a step-by-step guide to building this setup:

1. Understand InnoDB Cluster Components

• MySQL Servers: Acts as the nodes of the cluster, each running an instance of MySQL.
• MySQL Group Replication: Provides distributed state management and replication among cluster nodes.
• MySQL Router: Directs application queries to appropriate nodes in the cluster.
• MySQL Shell: Used for managing the cluster.

2. Preparation

• Choose the Nodes: Select a minimum of three nodes for fault tolerance. These nodes should have similar hardware and network configurations.
• Network Setup: Ensure all nodes can communicate with each other over a reliable and low-latency network.
• MySQL Installation: Install MySQL Server on each node. Make sure it’s the same version and configured to use the InnoDB storage engine.

3. Configure Group Replication

• Server IDs: Each MySQL instance should have a unique server_id.
• Enable Group Replication Plugin: Enable the Group Replication plugin on each node.
• Configure Replication Settings: Set the necessary replication-related parameters, such as group_replication_group_name, group_replication_ip_whitelist, and group_replication_local_address.

4. Initialize the Cluster

• Bootstrap the Cluster: Start the first node with Group Replication in "bootstrap" mode to create a new cluster.
• Add Nodes to the Cluster: Add additional MySQL instances to the cluster, configuring them to join the group replication.

5. Set Up MySQL Router

• Install MySQL Router: Install it on the application server or on a separate node.
• Configure Routing: Configure MySQL Router to understand the topology of your InnoDB Cluster and route read and write operations appropriately.

6. Scaling and Load Balancing

• Read Scaling: Direct read queries to secondary nodes for load balancing. Writes should go to the primary node.
• Additional Nodes: Add more nodes to the cluster for increased read capacity.

7. Monitoring and Management

• Cluster Monitoring: Regularly monitor the health and performance of the cluster.
• Backup and Recovery: Implement a backup and recovery strategy for the entire cluster.

8. High Availability Considerations

• Automatic Failover: InnoDB Cluster provides automatic failover. Ensure the application is compatible with this and can handle transient connectivity issues.
• Geographic Redundancy: For higher availability, consider deploying nodes across different data centers.

9. Testing

• Failover Testing: Test the failover process to ensure the cluster behaves as expected during node failures.
• Load Testing: Simulate application load to ensure the cluster scales as required and meets performance expectations.

10. Maintenance and Upgrades

• Regular Updates: Keep the MySQL instances and MySQL Router up to date.
• Cluster Upgrades: Plan and execute upgrades with minimal downtime, possibly using rolling upgrades.

Conclusion

Building a horizontally scalable MySQL infrastructure using InnoDB Cluster involves careful planning and configuration of MySQL Group Replication, MySQL Router, and MySQL Servers. It provides high availability and scalability for read-heavy workloads. Regular monitoring and maintenance are crucial to ensure the cluster's health and performance.