Greetings to all, we currently run an online store.
We are looking to expand our store's reach to China, South Korea, and potentially other countries in the future.

We aim to "distribute" a PHP site across several hosting sites so that when a user visits from China, for instance, they are directed to a site served from a server based in China. This is intended to enhance speed and efficiency. However, we need to devise a centralized database for the websites. Any suggestions would be greatly appreciated.

It appears like what you're trying to implement is a network of international servers (commonly known as a Content Delivery Network or CDN), while maintaining a centralized database. This certainly can help improve your site performance by ensuring that users are served content from the server location that's physically closest to them. Below, I will outline a general approach to accomplish this:

Content Distribution and Replication: To distribute your PHP-based website across various servers, you can use cloud service providers such as Amazon AWS, Google Cloud, or Microsoft Azure. These providers have data centers worldwide, including China and South Korea. You can use their services to replicate your web pages across these servers.

Note that in China, however, you will want to consider using a local provider like Alibaba Cloud because foreign companies can face strict regulations and challenges for hosting there.

Database Centralization: For centralizing your database, consider the following:

Master-Slave Configuration: You could employ a master-slave architecture, whereby you have a master database that is responsible for handling writes and multiple read replicas (slaves). The read replicas can be geographically distributed, aligning with the locations of your servers. This approach provides read scalability and data locality.

Data Sharding: Look into the possibility of sharding the database if your volumes of data are likely to be very high. Sharding breaks your data into smaller chunks, which allows it to be spread across multiple servers.

Database Synchronization: Consider employing database replication techniques to synchronize data across these geographical locations. This way, you have redundancy of your data and can ensure high availability, failure recovery, and improved access times.

Load Balancing: Employ a local load balancer in each geographical region (like Nginx, HAProxy, or cloud provider load balancer services) to distribute network traffic efficiently over your servers in that region.

Geo DNS: Use a geo-DNS service that can serve DNS responses based on the geographical location of the user requesting it. You'd set different A records for different regions and the DNS would direct the user to the server closest to them. Services like AWS Route 53 or Cloudflare provide these features.

Dealing with data regulations: If you're managing customer data across borders, ensure you're compliant with each region's data protection laws. For example, GDPR in Europe, and CSL (Cybersecurity Law) in China.

Your overall architecture would involve running copies of your application in various regions, with local read replica databases where needed, but writing back to a central master database. Any shared content (like images uploaded by users) would need to be replicated across regions.

To expand on the points above and provide you with more in-depth information:

Content Distribution and Replication: You need to consider several factors when deciding between using a CDN or hosting junior versions of your site on local servers. A CDN (Content Delivery Network) essentially makes static versions of your content (HTML, images, scripts) that it serves to users from a local server. However, a CDN does not execute server-side code, such as PHP.

If the primary source of latency in your service is the time it takes for your server to deliver basic content (HTML, Scripts, etc.), then a CDN is a good solution. On the other hand, If you need to execute server-side code locally, you will need to set up local servers.

Database Centralization: There are different ways to centralize your data, depending on your specific needs. If you're operating with a global audience, there can be significant latency when retrieving data from a central database.

Database Caching: You could mitigate this latency by using a distributed caching system such as Redis, which can reside in the same datacenter as your servers to provide fast data lookup.

Sharding: This is a type of database partitioning that separates very large databases into smaller, faster, more easily managed parts called data shards. Shards are essentially horizontal partition rows databases, or tables into smaller pieces, called partitions. This is instrumental in improving the speed of search operations.

Load Balancing: The role of a load balancer is to distribute traffic evenly or according to a certain set of rules, across multiple servers to prevent any one server from becoming a bottleneck. This results in increased capacity and reliability of applications.

Deployment of the load balancer highly depends on your architecture. For instance, if the majority of your users are from a specific region, it makes sense to keep the load balancer in that respective region. It would help in routing application requests faster.

Geo DNS: This technique determines the geographic source of a DNS query and returns different results depending on the origin of the query. This can be used to guide traffic from a specific geographical area to a server in that region.

Automated Deployment: Consider using a continuous integration/continuous deployment (CI/CD) workflow. With services like Jenkins, or cloud solutions like AWS CodePipeline or Google Cloud's Cloud Build, you can automatically keep all of your servers' codebase synchronised.

Data regulations: Ensure that you are up-to-date and compliant with all regional data regulations. An example is the Personal Information Protection Law (PIPL) in China which is an equivalent to GDPR in Europe.

If you need further depth, let's take a look at essential parts of your query in detail:

Data Consistency: In a distributed system like the one you're proposing, you will face challenges in maintaining data consistency. Keeping data in sync across all your servers can be a substantial challenge, especially when considering how to handle write operations that need to be updated on global read replicas. Conflict resolution is a critical consideration in such a system.

Wherever possible, try to favour eventual consistency and 'write locally, read globally' policies. With eventual consistency, it's accepted that not all copies of your data are the same at all times, but they will eventually be the same after all updates have propagated.

Failover strategies and Redundancy: What happens if one regional server goes down? How does your system respond? An ideal system would have redundancy in place so that if one server fails, its load can be distributed to others in the region. Having robust monitoring and alerting in place is also essential to detect and rectify such issues quickly.

Session Management: In an e-commerce setup, tracking user sessions and providing consistent user experiences is critical. When network latency is a concern, maintaining user sessions between geographically distributed servers can be challenging. Solutions like sticky sessions, where user requests are directed back to the same server, can help. Implementing global server-side session management would also be beneficial.

Database Technologies: Your choice of database technology will also play a significant role in how you achieve your goals:

SQL databases: MySQL (and its fork MariaDB) and PostgreSQL support various forms of replication and sharding. These could be useful features.

NoSQL databases: MongoDB, CouchDB, Cassandra, and others come with built-in support for data replication and distribution features and might be more suited to highly distributed applications.

Payment Processing: How do you intend to handle payments? Different countries can have different preferred payment systems. For example, South Korea and China have their specific digital payment technologies that differ significantly from what is widely used in western countries. You may need to integrate multiple payment processing systems into your application.

Localisation and Internationalisation: Aside from technical concerns, you also need to present your website in the local language and consider cultural factors. This is not just about translating the text but about adapting your user interface and even certain offerings to suit local preferences.

The path you're setting upon is not a straightforward one, but it's definitely achievable with careful planning and the right technical expertise. Consider engaging a technology solutions company or a systems architect with experience in designing high-availability, distributed web applications to help you plan and implement your solution.

The advice on CDN and DNS is largely misguided.

It's incorrect to argue that DNS doesn't function - it does, but it tends to be overzealously cached.

CDNs can be beneficial, but mostly just for imagery. In an ecommerce context, it is the platform's back-end, not the image content, that bogs down the performance.

The proposition to utilize proxying is utterly impractical; it ostensibly leads to threefold latency.

Instead, establish numerous mirrored versions of the site and let the users select their preferred one, guided by GeoIP suggestions.

In my view, the ideal approach here would be to generate first and second level domains (or local extensions such as /eu, /ch, /ru, etc.) where each distinctive internet domain accommodates a site copy. It would be smart to work on shared assets (such as images, stylesheets, codes, user tables) and clone them using rsync or any other similar tool, while precisely partitioning other elements (like product databases, stock balances, pictures, product descriptions) to avoid unnecessary duplications.

About Geodyns, in spite of the global reach, its benefit might be relatively limited. However, in territories with varied countries, it might be useful for directing users to their local store as a feature among other solutions. A Content Delivery Network (CDN) could be beneficial for dispensing static content, especially if dealing with a considerable number of images or even promotional videos about the product. They can be distributed effectively using a CDN.

If you're feeling adventurous, you might consider establishing a geo-distributed database. This could be a feasible option or not, largely depending on the database engine you're using. With MySQL, an active-active replication scenario may not be as functional. However, if your system runs NoSQL, then such a configuration is possible and could offer a balanced load distribution.

Traffic is intelligently distributed across these servers to a single IP using different protocols.