MySQL Master-slave replication (Master-slave)

1 Replication Overview

MySQL's built-in replication capabilities are the foundation for building large, high-performance applications. The distribution of MySQL data across multiple systems is done by copying data from one of the MySQL hosts to the other host (slaves) and re-executing it again. One server acts as the primary server during replication, while one or more other servers act as slave servers. The primary 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.

Note that when you make a copy, all updates to the tables in the replication must be made on the primary server. Otherwise, you must be careful to avoid conflicts between updates to tables on the primary server and updates made to tables on the server.

1.1 MySQL supported replication types:

　 (1) Statement-based replication: SQL statements executed on the primary server execute the same statement from the server. MySQL uses statement-based replication by default and is more efficient. Row-based replication is automatically selected as soon as it is found that it cannot be copied accurately.
(2) Row-based replication: Copy the changed content past, instead of executing the command from the server again. Support Starting from mysql5.0
(3) Mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

1.2 Copy solved questions

MySQL replication technology has some of the following features:
(1) Data distribution (distribution)
(2) Load balancing (load Balancing)
(3) Backup (Backups)
(4) High-availability and fault-tolerant lines of availability and failover

1.3 How replication works

as a whole, there are 3 steps to replication:

(1) Master changes the record to binary log (these are called binary log events, binary logs event);
(2) Slave copies the binary log events of master to its trunk logs (relay log);
(3) Slave redo the event in the trunk log and change the data to reflect its own.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/56/FD/wKiom1SOwveBlyTdAADpsdSzYhI977.jpg "title=" Mysql_ Master_slave.jpg "alt=" Wkiom1sowveblytdaadpsdszyhi977.jpg "/>

2. Replication Configuration   There are two MySQL database servers master and Slave,master as the primary server, slave is the same as the data information from the server, initial state, Master and slave, when the data in master is changed, Slave also follow the corresponding changes, so that the master and slave data information synchronization, to achieve the purpose of backup.

Points:

 The medium responsible for transmitting various modification actions in the main and from the server is the binary change log of the primary server, which records the various modifications that need to be transmitted to the slave server. Therefore, the primary server must activate the binary logging feature.    The slave must have sufficient permission to connect to the primary server and request that the primary server transfer the binary change log to it.     

This article is from the "Smurf Linux ops" blog, so be sure to keep this source http://jin771998569.blog.51cto.com/2147853/1590200

MySQL Master-slave replication (Master-slave)