Copy of MySQL Series 1----principle

MySQL Replication Introduction:

MySQL supports one-way, asynchronous replication, during which one server acts as the primary server, and one or more other servers act as slave servers.

The MySQL master server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates.

In the current production work, most of the MySQL master-slave synchronization is asynchronous replication method, that is, not strictly real-time data synchronization.

If you want to set up a chained replication server, you can also act as the primary server from the server itself.

It is important to note that when you make a copy, all updates to the tables in the replication must be made on the primary server to avoid conflicts between updates to the tables on the primary server and updates made to the tables on the server.

Introduction to MySQL Replication architecture

In addition to the master-slave replication (synchronization) mentioned above, the MySQL replication architecture also has primary master replication (synchronization) and multi-Master loop Replication (synchronization).

Assuming that we use ring or chained cascade replication, our slave (Slave) server itself, in addition to acting as a slave server, also needs to act as the primary server from the server under it.

The relevant master-slave replication architecture, the primary master replication architecture, and the ring-chained cascade replication Architecture diagram, we can look at the following legend:

1, one-way master-Slave synchronization Logic architecture diagram

650) this.width=650, "src=" http://s3.51cto.com/wyfs02/M02/41/01/wKiom1PP5duCZvPUAAD_TqGT-V0938.jpg "title=" one-way primary to the. PNG "alt=" wkiom1pp5duczvpuaad_tqgt-v0938.jpg "/>

2. Bidirectional main master synchronization logic architecture diagram

650) this.width=650, "src=" Http://s3.51cto.com/wyfs02/M01/41/01/wKiom1PP5ePg3n7hAADHqj0fu24557.jpg "title=" two-way main to. PNG "alt=" wkiom1pp5epg3n7haadhqj0fu24557.jpg "/>

3. Linear cascade unidirectional Dual-Master Synchronization Logic architecture diagram

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/41/01/wKioL1PP5weBmc2AAAEGGx-UnBI747.jpg "title=" linear level. PNG "alt=" wkiol1pp5webmc2aaaeggx-unbi747.jpg "/>

4, Loop Cascade one-way multi-master synchronization Logic architecture diagram

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/41/01/wKioL1PP5xORuzmaAAFEC8hKDXU777.jpg "title=" Clipboard.png "alt=" Wkiol1pp5xoruzmaaafec8hkdxu777.jpg "/>

5, Ring Cascade One-way multi-master multi-slave synchronous logical architecture diagram

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/41/01/wKioL1PP5zayhZvqAAIke6ejkwQ489.jpg "title=" Clipboard2.png "alt=" Wkiol1pp5zayhzvqaaike6ejkwq489.jpg "/>

Benefits of MySQL Replication:

Here is the main example of a master-slave copy:

MySQL Master-slave replication helps increase the robustness of the entire database, improves access speed, and is easy to maintain and manage.

1, increase the robustness of

The master server/slave server setting adds robustness. When there is a problem with the primary server, you can switch to a backup from the server.

2, Improve access speed

Better customer response times can be achieved by slicing the load of processing customer queries between the primary server and the slave server. SELECT queries can be sent to the slave server to reduce the query processing load on the primary server. However, the statements that modify the data should still be sent to the master server so that the master server and the server are synchronized. If the non-update query is primary, the load balancing policy is effective, but is typically an update query. This is also the common read-write separation of our production.

3. Easy Maintenance Management

In-house development and maintenance personnel, you can do background access, data analysis, and script operations from a server, especially with a backup from the server, which does not interfere with the performance of the primary server. During the backup process, the master server can continue to process the update operation.

Application Scenarios for MySQL replication:

Here is also the main example of a master-slave replication Production application scenario:

1) master-slave servers are backed up by each other

The configuration of the master-slave server architecture can greatly enhance the robustness of the database architecture. For example, when there is a problem with the primary server, we can manually or automatically switch to continue serving from the server.

2) master-slave server read-write separation, share site pressure

The master-Slave server architecture can read and write the user (client) request through the program or the agent software, that is, by simply processing the user's Select query request from the server, reduce the user query response time and read and write simultaneously on the primary server pressure. For the updated data (Update,insert,delete) is still given to the primary server for processing, ensuring that the primary server and the server remain in real-time synchronization.

If the site is non-update (to browse mainly) mainly business, such as blog or home page display and other services, query requests more, at this time from the server read and write separation, load balancing strategy will be very effective, this is the read-write separation database structure.

3) separate and share the pressure according to the server split business

The production can be divided into several different slave servers, according to the company's business. For example, there are slave servers that provide query services for external users, slave servers that DBAs use to back up, and background, scripts, log analysis, and developer services from servers that provide insider access to the company. Such a split will not only reduce the pressure on the primary server, but also allow external users to browse, internally handle internal corporate user business and DBA backup business.

Please see below the master-slave architecture production environment split the use case from the Server Division service:

Master┌--> Slave1---provide services to external users (browse posts, visit blogs, browse articles) ├--> Slave2 to external users (browse posts, visit blogs, browse articles)             ├--> Slave3---provide services to external users (browse posts, visit blogs, browse articles) ├--> Slave4 to internal managers (background access, scripting tasks, data analysis, developer browsing) └--> Slave5--turn on Binlog from server for incremental backup and recovery

How MySQL data is replicated

MySQL replication tracks all changes to the database (updates, deletions, and so on) based on the primary server in the binary log. Therefore, to replicate, you must enable binary logging on the primary server.

Each slave server receives a saved update from the primary server that the primary server has logged to the binary log so that the server can perform the same updates to its copy of the data.

It is important to recognize that the binary log is only a record that starts at a fixed point in time when binary logs are enabled. Any settings from the server require a copy of the database on the primary server (when binary logging is enabled on the primary server). If you start from the server, its database is different from the state of the boot binary log on the primary server, which is likely to fail from the server.

A common way to copy data from a master server to a slave server is to dump the SQL on the primary server using mysqldump and then recover from the server. It is important to note that before the primary server uses mysqldump for SQL dumps, the lock table operation is required and the Binlog log and location points are logged!

the schematic diagram for MySQL data replication is as follows: (this figure comes from the high-performance MySQL book)

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m00/41/01/wkiom1pp5nygaxkgaaf12fakwy0875.jpg "title=" Clipboar4d.png "alt=" Wkiom1pp5nygaxkgaaf12fakwy0875.jpg "/>

From, we can see that the MySQL database replication needs to start three threads to implement:

One is on the primary server, and the other two is on the slave server.

1. When the start SLAVE is executed from the server, the master-slave copy switch is turned on

2. At this point, an I/O thread is created from the server itself, a request to connect to the primary server via the authorized replication user right on the primary server, and a specified location point from the specified Binlog log file (log file name and location point information is the change when the master-slave Replication Service is configured The Binlog log (i.e. SQL statement) is sent after the Master command is given);

3. After the primary server receives the I/O thread request from the server, the primary server creates a Binlog dump thread that reads the Binlog log information after the specified location point in the specified Binlog log file according to the information requested from the server I/O thread. It is then returned to the I/O thread from the server. The information returned, in addition to the Binlog log content, includes the most recent Binlog file name on the primary server and the latest specified update location point in the Binlog (that is, the Binlog file name and location point information requested next time from the server).

4, when the I/O thread from the server obtains the log contents and the latest log file name and location points sent to the Binlog dump line Cheng on the primary server, it writes the Binlog log contents sequentially to the relay from the server itself. Log (that is, the trunk log) file (mysql-relay-bin.xxxx), and the new Binlog file name and location point information to the Master-info file so that the next time the latest Binlog log of the master server is read, The ability to tell the primary server what location of the file from the most recent Binlog log begins to request a new Binlog log content.

5, the SQL thread from the server will detect the local Relay log in real-time to see if there is a new added log content, if it will be in a timely manner to parse the contents of the Relay log file into the main server has executed the contents of the SQL statement, and in the order of the server itself to execute the corresponding SQ L statement, the applied log is automatically cleaned up after the application is completed.

As a result, the read and execute statements from the server are divided into two separate tasks. If the statement execution is slow, the statement read task does not slow down. For example, if the server has not been running for some time, when booting from the server, its I/O thread can quickly request all binary log content from the master server, even if the SQL thread lags far behind. If the server stops before the SQL thread executes all the requested statements, the I/O thread has at least claimed everything so that a secure copy of the statement is saved to the local relay log from the server for execution from the next boot of the server. This allows the binary log on the primary server to be emptied, since it is no longer necessary to wait for its contents to be requested from the server.

6. After a series of processes above, MySQL will be able to ensure that the same SQL statements are executed on both the primary server and the slave server. In the case of a normal replication state, the primary server and the data from the server are exactly the same. Although the Mysql synchronization mechanism will have some special situations, there are solutions in most cases, so there is no need to worry. For more detailed information and more authoritative information, please refer to the official manual for instructions.

This article is from the "not only Linux" blog, so be sure to keep this source http://nolinux.blog.51cto.com/4824967/1528519

Copy of MySQL Series 1----principle