MySQL Replication (master-slave copy) Experimental learning notes

1. Theoretical part

1.1, the concept of MySQL replication:

Enables data from one MySQL database server (the master) to being replicated to one or more MySQL database servers (the slaves) .

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/73/6D/wKioL1X9_eyijIegAAFCPvsga28580.jpg "title=" Relication.png "alt=" Wkiol1x9_eyijiegaafcpvsga28580.jpg "/>

1.2, MySQL Replication the Realization Foundation:

Binary log (binary logs) is the basis for MySQL master-slave replication

Binary Log concept: All statements, whether explicit or implied, that can cause changes to the database, are recorded in binary log.

The log files involved in the master-slave backup are:

Binary log (binary logs)

Relay log (trunk log)

1.3. mysql Replication classification:

1) Master-Slave

2) Primary Master

1.4. mysql Replication business value :

The first way the server is expanded is:

1) Scale up: Improve server performance for legacy applications

2) Scale out: Original server and application unchanged, add server.

The server is experiencing performance bottlenecks, the cost of scaling up is too high, assuming that the server's existing price is M, performance expansion n times, the cost is often m*n times more than the cost of more than to improve the performance of n times.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/73/70/wKiom1X9-vqz1xLiAAJYc1SOePE969.jpg "title=" scale Out.jpg "alt=" Wkiom1x9-vqz1xliaajyc1soepe969.jpg "/>

1.5. mysql Cluster type

Cluster classification:

1) Load balancing (load balanced cluster), both servers are available at the same time.

2) High availability (highly available cluster), is a redundant mechanism, only one service.

3) High preformance (performance cluster), examples such as supercomputers, will be very large computing requirements, automatically divided into n slices, each piece to a computer processing, processing completed and then returned to the results of the computer re-integration of cutting.

MySQL has both load-balancing capability and high-availability features, and in a sense, load balancing itself has a highly available feature, but high availability does not provide the ability to load balance.

1.6. mysql Synchronous asynchronous problem

Synchronous Asynchronous Concepts:

Async (asynchronous)-the communication is returned without acknowledgment, and the sender does not have to wait for the receiver to respond to the task and abort, which can provide better performance.

Sync-The communication has a confirmation signal to return, the sender waits for the receiver to respond to the task before aborting.

MySQL master-slave architecture belongs to asynchronous architecture

1.7, the implementation of master-slave replication related processes

MySQL master-slave process:

1) The dump thread, which is responsible for skewing the data to the thread from the server.

2) I/O thread threads are responsible for receiving the thread that the primary server pours over.

3) An SQL thread thread that reads data from the log and applies the data locally to the threads.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/73/6E/wKioL1X-FoyREYG_AAHLw103IlM225.jpg "title=" Master-slave.png "alt=" Wkiol1x-foyreyg_aahlw103ilm225.jpg "/>

1.8, MySQL replication configuration process Introduction

MySQL Replication configuration process:

1) Enable binary logging on the primary server (enabled by default)

2) enable the relay log from the server (not enabled by default)

3) A separate identity for each server (server ID, 1 by default)

4) Create a copy of the system account with copy permissions.

5) master-Slave SSL authentication (encryption)

Account established by the master service (used from the server):

Replication client: Get Primary server work information (ID number, secondary file,) from master server

Replication salve: Gets the binary information from the primary server.

Note: the recommendation from the server is higher than the primary server version.

2. Experimental part

Cond......

This article is from the "Pragmatic People" blog, please be sure to keep this source http://tanzhenchao.blog.51cto.com/2420395/1696474

MySQL Replication (master-slave copy) Experimental learning notes