Analysis of MySQL architectures commonly used in e-commerce, community, and games

AverageOr the MySQL architecture must be analyzed, designed, and optimized in combination with the business.
Therefore, no matter what the architecture and business requirements are combined to meet the needs, it is the best and cannot be generalized.
At the same time, you must also pay attention to data security (such as IPSec, ssh, and VPN transmission)

RegularSee the architecture for business segmentation, front-end caching, database/table sharding, hundreds of millions of queries,
Split the services, divide the BBS, web, and blog into several groups, and then form a master-slave multi-slave, read/write splitting method.
In addition, when designing a table, the Standby database usually acts as a backup query function.
As for read/write splitting, at the beginning of program design, reading and writing are performed through different IP entries. This is Idea 1, or the class is defined, or the proxy layer is used, such as MySQL-proxy.

MostlyData, read/write splitting at the application layer, MySQL replication, and many advantages, good controllability,
MySQL replication, this is King, at least for now, not for the future.
Compared with replication, the cluster is basically unnecessary in the core stage of the production environment, or is rarely used now.
Because the hardware cost invested in the early stage (relative to the master and slave) is relatively high, the general small project will not be used, and the cluster cost (mostly the maintenance cost) is relatively high.
However, with the release of subsequent versions and estimation of more and more cases, it is a very good sharding-nothing solution.


GameIn the play: Friends, rankings, counters, queues, and cache are suitable for implementation through redis.

As for redis's transaction function, you don't have to worry too much about it.

In addition, redis is much more stable than memcached.

E-commerceThe master-slave architecture is also used in the medium and production environments, and drbd + ha is used for Master backup.
The drbd solution is not recommended for the primary node or for high availability.
Drbd does not set automatic start, manual start during restart, and split-brain occurs rarely.
However, drbd is basically not restarted at work, and the server is not restarted. Basically, there is no split-brain problem.
Drbd plays a certain role in risk disaster tolerance, but it cannot be expanded. Combining with LVS is believed to be a perfect solution.
For example, LVS + keepalived can be used as scripts to remove slow or invalid slave MySQL machines,
In addition, LVS is the strongest among the software Load balancer instances, with more than 10 backend nodes. It is estimated that only LVS is competent.


RulesBig companies (such as Sina and Taobao)
1. Without clusters, MySQL clusters are not used much (currently, MySQL clusters can also be used)
2. The master and slave nodes can be multiple groups.
3. Each group may have one master, multiple slaves (1/n of business data)
4. 3 reading or writing in each group may be an rs of the front-end scheduler.
5. scheduler distribution can be grouped by hash, and data can be split by user ID. Of course, there are more advanced methods.
Tip: The Sina development manager admitted that their SAE platform is still master-slave, and even has a single point of time (monitoring and manual processing ))

ScaleMedium Companies (such as csdn)
1) MySQL reads and writes from multiple master-slave programs (not even implemented) and multiple groups. If a problem occurs, switch the master directly or automatically after the slave node is changed (implemented by a script or program)
2) drbd + ha for high availability (dual-master multi-slave, automatic failover M, normal standby M cannot provide services)
3) or dual-master, multi-slave, front-end combined read and write Load Balancing respectively