MySQL Master-slave replication

CorporateLinuxoperation and maintenance scene data synchronization scheme1.1file-level synchronization scenarios for different machines

• The SCP/SFTP/NC command enables remote data synchronization.

• The FTP/HTTP/SVN/NFS server is built , and the data can be synchronized to the server on the client.

• Build Samba file Sharing service, which can then synchronize data to the server on the client.

• Data synchronization can be realized by using rsync/csync2/union and so on.

Hint:Union can realize bidirectional synchronization,Csync2 can realize multi-machine synchronization. The above file synchronization method can realize timing and real-time data synchronization if combined with any or inotify,sersync functions.

• extension idea: File level can also be implemented as a container using software such as Mysql,mongodb.

• Extension idea: The program writes the data to two servers simultaneously, the double write is a synchronization mechanism.

features: Simple, convenient, inefficient, and file system level, but synchronized nodes can provide access. The software's own synchronization mechanism (MySQL,oracle,Mongdb,ttserver,redis ... ), put the file in the database, sync to the library, and then take the file out.

1.2file system level synchronization scheme for different machines1.2.1drbdSynchronizing Data

DRBD is based on file system synchronization, which is equivalent to network RAID1and can synchronize almost any business data. MySQL database is the official recommendation DRBD synchronization data, all single point services such as:Nfs,mfs (DRBD), MySQL, etc. can be used with DRBD.

1.2.2Database Synchronization Scenarios2.1MySQL Master-slave replication

MySQL master-slave replication scheme, and the above file and file system level synchronization is similar, is the transmission of data. but MySQL does not need third-party tools, but its own synchronous replication function, the other point,mysql master-slave replication is not directly on the disk file synchronization, but the logical Binlog The log synchronizes the process to local execution in the app.

2.1.1 MySQLIntroduction to Master-slave replication

MySQL Database supports one-way, bidirectional, chained cascade, and other different scenarios of replication. During the replication process, one server acts as the primary server (Master), and one or more other servers act as slave servers (salve).

replication is unidirectional: MèS, but also bidirectional mÓm, of course, can also be multi- m loop synchronization.

If chained cascade replication is set,the slave (slave) server itself, in addition to acting as a slave server, also acts as the primary server from the server under it.

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/9C/26/wKioL1lsrMvyePB2AADeSw1yhbc135.jpg-wh_500x0-wm_ 3-wmp_4-s_4003705682.jpg "title=" 1.jpg "alt=" Wkiol1lsrmvyepb2aadesw1yhbc135.jpg-wh_50 "/>

1. one-way master-slave replication logic diagram, which can only write data on the master side.

2. Bidirectional synchronous logic diagram, which can write data on the master or Master2 side.
   

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/9C/26/wKiom1lsrRexIotiAAD3rTZJhic740.jpg-wh_500x0-wm_ 3-wmp_4-s_1066239745.jpg "title=" 2.jpg "alt=" Wkiom1lsrrexiotiaad3rtzjhic740.jpg-wh_50 "/>

3. Linear cascade unidirectional Dual-Master synchronization logic diagram, this schema can only be written on the master side of the data.

2.1.2 MySQLIntroduction to Master-slave replication principle

 mysql master-slave replication is an asynchronous replication process (although in general the feeling is real-time), the data will be from a mysql database ( master Mysql database ( slave Master and Slave sql io thread) in slave I/o thread) in master

to implement MySQL Master- slave replication, first must open the master's Binlog record function, otherwise it will not be implemented. Because the entire replication process is essentially slave getting the Binlog log from the master , and then performing the acquired Binlog in the same order on slave Various SQL operations that are logged in the log .

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/9C/26/wKioL1lssnuwZdI6AAHswspYMuc023.jpg-wh_500x0-wm_ 3-wmp_4-s_3127749478.jpg "title=" 3.jpg "width=" 780 "height=" "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:780px; height:400px; "alt=" Wkiol1lssnuwzdi6aahswspymuc023.jpg-wh_50 "/>

2.1.3Master-Slave replication application Scenario

Scenario 1: Real-time data backup from the server as the primary server

the configuration of the master-slave replication architecture can greatly enhance the robustness of the MySQL database architecture. For example, when there is a problem with the primary server, we can manually or automatically switch to a service from the server, where the data from the server and the primary database at the time of the outage are almost identical.

This is similar NFS Storage Data is synchronized to the Backup NFS server via Inotify+rsync , except that the MySQL replication Scheme is its own tool.

Use MySQL replication function to do data backup, in the case of hardware failure, software failure, the data backup is valid, but for human execution drop,delete and other statements delete data, the backup function from the library is useless, Because the deleted statement is also executed from the server.

Scenario 2: master-Slave server for read-write separation, load balancing from server

The master-Slave server architecture is available through programs ( PHP,Java, etc.) or agent software (mysql-proxy,amoeba) to enable the user (client) request read-write separation, that is, from the server only to process the user's Select query request, reduce user query response time and read and write at the same time on the primary server to bring access to the pressure. The updated data (for example , update,insert,delete statement) is still given to the primary server for processing, ensuring that the primary server and the server remain in real-time synchronization.

Scenario 3: Split multiple slave servers according to business importance

can put a few different from the server, according to the company's business to split. For example, there is a slave server that provides query services for external users, a slave server that internal DBAs use to back up data, and background, scripts, log analysis, and a server for developers to query for access from within the company. In addition to reducing the pressure on the primary server, this split can make the database independent of external user browsing, internal User Service processing, and DBA personnel backup.

2.1.4Summary of Master-slave replication principle

Master-slave replication is asynchronous logic SQL Statement-level replication.

when copying, the main library has a I/O threads, from the library have two threads,I/O, and SQL threads.

the necessary condition for master-slave replication is that the main library should turn on the record binlog function.

as a copy of all MySQL node Sever-id cannot be the same.

The Binlog file only records SQL statements that have changed from the database (changes from the contents of the primary database) and does not log any query (select,show) statements.

MySQL Master-slave replication