Small and medium-sized enterprises can refer to the database architecture-mysql

Introduction

Database is widely used in many internet companies, different companies, the use of posture is not the same, especially large companies, various self-research structure, envy others. However, as small and medium-sized enterprises, due to Division of labor and team size constraints, it is difficult to achieve self-research, most of the time, using open source architecture.

At present, in the Internet company, Mysql+redis+mongodb almost has become standard, in the respective scene, has been widely used. This article mainly introduces the above MySQL database common structure.

Mysql Architecture

MySQL database, as the most popular open source relational database, is widely used in internet companies. Currently, MySQL version branches are numerous, I suggest you can consider using Percona server or MARIADB. This article takes Percona server5.6 as an example, the highly available architecture can be referenced as follows:

Figure 1

As shown, the application obtains through qconf to MHA VIP,MHA implementation of MySQL main library high availability, MySQL architecture mainly from the replication architecture, can be a master one from, can also be a master multi-slave.

Qconf is Qihoo 360 open source Configuration/service center middleware, especially service center functionality, ideal for database and application decoupling. qconf GitHub Address:

Https://github.com/Qihoo360/QConf

At present, qconf development has been very stable, almost no need two times development, can rest assured use.

QCONF provides a rich SDK that covers Java, PHP, Python, go, and so on, as DBAs can use PHP or Python to invoke the qconf API to manage qconf nodes. This article takes PHP as an example,

zk= "<zookeeper cluster >"

service_path=/db/mysql/vip_3306;

Service_mysql= "10.11.12.13:3306";

"Register MHA VIP Service"

$QZK = Newqconfzk ("$zk");

$qzk->serviceadd ($service _path, $service _mysql, qconf_status_up);

"Logoff qconf Node"

$qzk->servicedelete ($service _path, $service _mysql);

"Client Access qconf"

Migration: The client needs to deploy the appropriate SDK

$qconf _val =qconf::gethost ($service _mysql);

The results are as follows:

String (15) "10.11.12.13:3306"

Obviously, it's very easy for clients to use qconf

Description: Qconf relies on zookeeper cluster, the recommended production environment zookeeper cluster members of 5, so as to ensure that 2 nodes at the same time, the Zookeeper service is still available.

MHA Architecture

MHA as one of MySQL's most popular high-availability scenarios, the frame composition is as follows:

Figure 2

, the MHA architecture is simple and clear.

The MHA node nodes need to be deployed on master and slave, and MHA manager nodes and node nodes need to be deployed on the management machine. MHA requires the root account SSH interoperability, so, need to ensure that the server is in Safe mode, otherwise, there is a great security risk. In order to avoid MHA because of network jitter caused by the wrong switch, we recommend to modify the retry policy, the author set to 2s detection once, probing 60 times. If you want to enable MHA auto-switching mode, you need to start the Masterha_manager monitoring process.

Read/write separation

If you want MySQL master-slave read-write separation, then you can refer to the following architecture:

Figure 3

As shown, compared to Figure 1, add a layer of proxy, where the proxy can be Qihoo 360 open source Atlas or the United States review two times the development of Dbproxy,

The Qihoo Atlas GitHub:

Https://github.com/Qihoo360/Atlas

American group Reviews Dbproxy GitHub:

Https://github.com/Meituan-Dianping/DBProxy

These two middleware supports read-write automatic separation, load balancing from library, automatic upper and lower line from library failure, security control and so on, more mature and stable.

Summarize

For ordinary business, you can use the architecture shown in Figure 1, simple and stable, if you need to read and write separation, you can consider the Figure 3 architecture. For most small and medium-sized companies, the above architecture can be used as a reference for MySQL high availability and application and database decoupling purposes. We have already used the above structure in the production environment and are currently very stable

Small and medium-sized enterprises can refer to the database architecture-mysql