MySQL database multi-instance master-Slave synchronization

This article mainly introduces the single server MySQL database multiple instances of the master-slave synchronization, general general master from the replication slave server on different machines, and listening ports are the default 3306 port.
I. Introduction to the Environment

Operating system: CentOS 6.5

Database version: MySQL 5.5.32

Main Library host Name: Mysql-master (172.18.10.222:3306)

From library host name: Mysql-slave (172.18.10.222:3307)

Second, master-slave synchronization principle Introduction

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/79/F7/wKiom1afQD_CwpynAAJwig6ergc496.jpg "title=" MySQL master-slave copy schematic. jpg "alt=" wkiom1afqd_cwpynaajwig6ergc496.jpg "/>

Simple description of Master-slave replication principle:

1. Execute start slave on slave server command line, turn on master-slave copy switch

2. The I/O thread of the slave server is then requested to connect to the master server by a user who is authorized by the copy permission on the master server and requests that the Binlog log content be sent after the Binlog log in the specified location

3. After the master server receives I/O requests from the slave server, the I/O thread on the master server reads the Binlog log content after the location of the slave request and returns the I/O thread to the slave server side of the sending request. The returned information, in addition to the Binlog log, includes the new Binlog file name and the next specified update location in Binlog after the return log content

4. After the slave server receives the log-related information returned by the master service, the I/O thread of this side writes the Binlog information to the end of the Relaylog file in turn, and logs the new Binlog file name and location information to the Master-info file. Notifies the master server where to return content from which log file the next time the request message is sent to master

5, slave server-side SQL thread real-time monitoring of the local Relaylog log new log content in a timely manner to resolve the Relaylog log and in the local database server sequential execution of the same SQL script as the Master service, to achieve real-time synchronization with the master service side results

Third, configure master-slave synchronization

3.1 Installing the database and configuring multiple instances

For specific installation configuration procedures, refer to configuring MySQL database multi-instance

3.2 Main Library open Binlog and configure Server-id

[Mysqld]log-bin =/data/3306/mysql-binserver-id = 1

MySQL database multi-instance master-Slave synchronization