Cluster-based MYsql master-slave service extension Mysql mutual master-slave (annular structure) and simple real-MySQL

Cluster-MYsql master-slave service extension Mysql mutual master-slave (annular structure) plus simple implementation of your own load balancer (3) Mysql Cluster

Note:

I have a very low level of qualifications and don't talk about it. I hope you will not care about it,
In addition, I will reply to the circular question in the comments below,
In fact, it is a good choice to solve problems in multiple regions.
I reorganized the configuration steps (master-slave AND ring ).
In my blog, if you are interested, you can check it out,

Http://my.oschina.net/u/1246814/blog/267518

Thank you for your guidance.

For example, I have a master mysql server, and the two servers add a server load balancer (if the server load balancer is very simple to talk about, it is actually a DNS forwarding, very simple to say, in fact, what about IP rotation)

When I read (query) data and Select statement, I only read data from the server on the slave server.

The master server is responsible for (add, delete, modify) This series of operations

By the way, why do we have to create multiple slave servers for the Select statement?

Because of the statistics, more than 70% of the operations in the B/S architecture are dominated by queries (to put it bluntly, it is the demonstration)

After we run the DML statement, the load balancer,

The request is forwarded to the master mysql server,

Is there a problem here?

We have inserted data to the master server, deleted the data, or updated the data,

Does it mean that the data in the service should also change?

Yes, the slave server must change. Otherwise, a problem will occur,

For example, if I delete a user,

I checked the deleted user and found that the user was not deleted at all. this is not funny,

The master-slave configuration is available.

Master-slave principle: mysql has a log called bin log (binary log ),
This log records all SQL statements that modify MYSQL. When an SQL statement is executed on the master server, it is passed to the slave server and then executed again.

To put it bluntly, the Dml statement recorded in the Bin log is directly executed from the server to execute the same Dml operation. this method is binary, and all the efficiency is very good.

When to use master/slave

1. backup (if there is a problem with the database, immediately go to the top)

2. Optimization (shunt read/write splitting)

Look

If you use Dml statements, you can directly access the primary database without going through the load balancer.

In other words, SLB is actually a matter of white, that is, dns rotation and IP rotation.

If we can, we can also implement it relatively simply, but this is a situation,

Let me give you an idea. using MVC as an example, I would like to give a rough and simple example of a column,

1. classify the SQL statements requested by the user in the parent Model.

1.1dml statement (update, insert, delelte)
1.2select statement
1.3dcl statement (data control statement)
1.4dtl statement (data transaction statement)
1.5ddl statement (data definition statement)

2. take the common dml and select statements as the column and take 3 mysql servers, 1 master mysql, 2 slave Mysql, and Linux as an example.

Uniformly classify and manage the SQL commands that the user requests to the database

Master server IP: 192.168.0.1

Slave server IP: 192.168.0.2

Slave server IP: 192.168.0.2

3. analyze SQL commands

If dml is used to directly connect to the server 192.168.0.1

What if it is a select statement?

1. First, we need to determine the load size of the two slave servers.

We must send the request to a server with a relatively small load, right?

Then, I connected two servers using a socket to establish a remote control client,

The top command is used to obtain some load data such as the CPU memory of the two servers,

Then, the query request is connected to one of the slave servers through some calculations and comparisons.

Then, over
Back to question

About the Mysql ring structure, mysql servers are mutually master slave.

According to statistics on the platform, more than 70% of the platforms in the B/S architecture are dominated by Select.

So what if my platform is very frequent (add, delete, and so on?

If it is a master-slave configuration, I only have one server to process dml statements. this is not enough, so the disadvantages of the master-slave server are exposed.

Then, the circular structure configuration of mysql can solve such similar problems.

The principle is to use bin logs

Three mysql servers

We can execute dml statements to any of the service servers,

Then, the Bin log records the Dml statement,

Execute the same Dml statement operation on another server.

Look chart

End