The project has a desire to distribute geographically and starts with one of its components: MySQL. It is interesting to hear from those who have hands-on experience with this database and may not have theoretical knowledge of geographically distributed balancing schemes.

The current scheme is something like this: one web server and two database servers in "master-slave" mode. Read-only requests are directed to one server, while write-only requests are sent to the other. Both database servers are placed side by side and connected through a cross network. The idea is to make the scheme more complex by introducing additional servers in another country and setting up database replication. Although the channels are suitable, the delays are already higher compared to connecting pop-to-pop servers. Has anyone implemented such schemes? What can you share about it?

- Is this feasible or are there any known issues?
- Can replication traffic be optimized to reduce the strain on the channel?
- Is it advisable to use MySQL's built-in SSL or should everything be encapsulated in OpenVPN?
- What challenges (whether hidden or obvious) should be expected when implementing master-master replication?
- Who can provide insights into different cluster database types in MySQL?

I would like to emphasize that practical knowledge is of greater interest to me than theoretical knowledge.

Distributing a geographically distributed MySQL database scheme can be challenging, but it is certainly feasible. Let's address your questions one by one:

1. Feasibility and known issues: Implementing a geographically distributed database scheme like the one you described is possible, but there are a few issues to consider. First, cross-network latency can affect performance, so you may experience higher response times compared to pop-to-pop server connections. Second, ensuring data consistency and minimizing conflicts between master and slave databases can be complex.

2. Optimizing replication traffic: To reduce strain on the network channel, you can implement techniques like asynchronous replication or compression of replication traffic. Asynchronous replication allows you to delay replication updates to minimize the impact on network bandwidth. Compression can help reduce the size of replication traffic, making it more efficient.

3. SSL or OpenVPN: Whether to use MySQL's built-in SSL or encapsulate everything in OpenVPN depends on your specific security requirements. MySQL's built-in SSL provides encryption for communication between servers, while OpenVPN offers a more comprehensive solution that encapsulates all network traffic. Consider the sensitivity of your data and the level of security you need to make an informed decision.

4. Challenges of master-master replication: Implementing master-master replication introduces complexities such as conflict resolution and data consistency. Conflicts arise when both masters receive write requests simultaneously, and resolving them can be challenging. You'll need to carefully plan conflict detection and resolution mechanisms to ensure data integrity.

5. Insights into different cluster database types: Different cluster database types in MySQL, like Galera Cluster or Group Replication, offer various features and trade-offs. Practical insights from those who have hands-on experience can provide valuable information about their performance, scalability, and ease of management.


Here are some additional points to consider when implementing a geographically distributed MySQL database scheme:

6. Load balancing: As your scheme expands to include additional servers in another country, you'll need to consider load balancing algorithms to distribute read and write requests effectively. Different algorithms such as round-robin, least connections, or IP hash can help achieve load balancing across multiple database servers.

7. Data synchronization: Ensuring data consistency across geographically distributed database servers can be challenging. You may need to implement mechanisms like multi-master replication or distributed transactions to synchronize data between different server locations.

8. Network reliability and latency: Geographically distributed databases heavily rely on network connectivity. It is crucial to have reliable network connections with low latency to minimize the impact on performance and ensure data availability across different regions.

9. Backup and disaster recovery: Implementing a robust backup and disaster recovery strategy becomes even more important when dealing with geographically distributed databases. Regular backups, replication monitoring, and automated failover systems are essential to mitigate the risk of data loss and ensure business continuity.

10. Compliance and legal considerations: Depending on the countries involved in your geographically distributed setup, you may need to comply with different data privacy and protection regulations. Consider factors like data residency requirements, cross-border data transfers, and legal implications when setting up and managing your distributed database infrastructure.

In my previous job, we had a setup where one master controlled slaves located in different regions. However, we faced challenges in balancing the database at the global level, as most of the access was local. While it was still possible to function, replication would often lag behind and not consistently. Furthermore, the reliability of communication channels between servers was not as reliable as desired. Whenever there was a disruption in the connection between the servers, the replica would go down. Consequently, the ability to write data to the master remotely would periodically disappear.

Therefore, I strongly recommend implementing basic monitoring tools first and evaluating how they perform in your specific case. This will help you assess their suitability and determine if any adjustments are needed. If you incorporate replication delay time schedules and control master availability from each point where you intend to write data, it may not make life easier but certainly more predictable.

Additionally, there may be issues with code that assumes no delays. For example, when a new user registers, their information is stored in the master database, but it may not immediately propagate to the slave. This seemingly trivial problem can lead to more intricate complications.

To enhance the accessibility for local users and facilitate their access to removed database servers, a recommended approach is to split the database into two sections.

The first section, referred to as the "core," is typically synchronized regularly. Meanwhile, the second section functions as a local geographical extension that operates somewhat independently.

It can be synchronized without excessive concern, thereby significantly streamlining operations.

I can provide insights into the distributed database scheme using MySQL.

Feasibility and Known Issues:
Introducing additional servers in another country for database replication is feasible, but several known issues need consideration. High latency across continents can impact the performance of the distributed database system. Ensuring data consistency and dealing with potential network interruptions between geographically dispersed servers are critical challenges. Moreover, legal and compliance aspects related to cross-border data transfer and storage, such as data protection laws and regulations, should be thoroughly addressed and adhered to.

Optimization of Replication Traffic:
To reduce strain on the network channel, optimizing replication traffic requires a multi-faceted approach. This involves implementing efficient data compression techniques to minimize the volume of data being replicated, minimizing unnecessary data transfer through careful schema design and query optimization, and considering network-level optimizations like traffic shaping, Quality of Service (QoS) prioritization, and route optimization to prioritize and streamline replication traffic.

SSL vs OpenVPN:
MySQL's built-in SSL provides encryption for replication traffic, ensuring data security during transmission. However, depending on the specific security and compliance requirements, encapsulating the entire communication within OpenVPN might be necessary. OpenVPN offers a robust and flexible solution for securing and managing network traffic, including replication traffic, especially when additional layers of security and compliance standards need to be met.

Challenges of Master-Master Replication:
Implementing master-master replication brings forth several challenges that demand meticulous planning and execution. These challenges include conflict resolution, ensuring write atomicity across distributed nodes, and maintaining data integrity. Addressing these complexities requires a careful schema design that accounts for potential conflicts, robust synchronization mechanisms using techniques such as GTID-based replication, and effective error handling and monitoring strategies to detect and resolve replication issues promptly.

Insights into Different Cluster Database Types:
MySQL offers diverse clustering solutions, each catering to specific use cases and requirements. NDB Cluster provides high availability and scalability, Group Replication simplifies multi-master setups by offering strong consistency guarantees, and InnoDB Cluster integrates various MySQL technologies to provide an integrated, high-availability solution. Understanding the strengths and limitations of each clustering option is crucial for making informed decisions based on the specific needs and challenges of the distributed database environment.

Practical knowledge gained from hands-on experience is invaluable for successfully navigating the complexities of geographically distributed database systems. It allows for the implementation of optimal performance and reliability while addressing the intricate challenges associated with such deployments.

I think you're heading down a rabbit hole with this setup. Master-master replication across geographic locations is a recipe for disaster. The latency alone will cause more problems than it's worth.

MySQL's built-in SSL is fine, but it's not a silver bullet. You'll need to invest in serious network optimization to make this work, and even then, it's a gamble. OpenVPN might help, but it's just a Band-Aid on a bullet wound. If you're determined to do this, make sure you have a solid understanding of MySQL's replication mechanics and be prepared to spend countless hours troubleshooting.