MySQL master-slave replication principle Configuration

MySQL Replication introduction

MySQL replication is an asynchronous process that replicates from the server to one or more slave servers, and the entire replication process is done primarily by three threads , one I/O thread on the primary server side, and another two threads (SQL thread and IO thread ) on the from service side. To implement MySQL replication, first turn on MySQL's Binary Log function on the primary server

The details of the replication process are as follows

1. First thread attached the primary server from the I/O line on the server, and then requests the log contents from the specified log file
2. After receiving the I/O thread request from the server, the primary server reads the specified log information from the requested information on its own I/O thread and returns the I/O thread to the slave server.
3. After receiving the information from the I/O thread of the server, Writes the retrieved log content sequentially to the end of the server-side trunk log file (similar to mysql-relay-bin.xxxxx), and logs the file name and location of the binary log read to the primary server to a file named Master-info so that it can be quickly located from the next read Start reading log information backwards
4, from the server's SQL thread detected in the trunk log File New additions, the contents of the trunk log file will be resolved immediately, the contents of the log to resolve the SQL statement, and then execute the SQL, because the server side and the main server side of the same SQL operation, so the data on both ends is exactly the same , the entire replication process ends

MySQL replication technology has many implementation architectures in practical applications.

One Master one from: A primary server and one from the server, which is the most common architecture
One master multi-slave: one master server with two or more two slave servers, often in a business environment where write operations are infrequent and the query volume is larger
Dual-Master interoperability: two MySQL servers each other as their own home server, and also as the other side of the server to replicate
Dual Master Multi-Slave: is the dual master mutual preparation, and then add multiple slave servers

There are a number of rules that must be followed before MySQL replicates various deployments

1, at the same time can only have one master server for write operations
2. A primary server can have multiple slave servers
3, whether the primary server or from the server, to ensure that the respective server ID unique
4, one from the server can be obtained from the server update information to other slave servers, and so on

One master one from the configuration

IP Address	Hostname	System version	Database version	Role
192.168.88.1	C1.heboan.com	CentOS7.2.1511	5.5.56-mariadb	Master
192.168.88.2	C2.heboan.com	CentOS7.2.1511	5.5.56-mariadb	Salve

Installation

Yum-y Install mariadb-server mariadbsystemctl start mariadbmysqladmin-uroot-p password ' 123456 ' # set root password # directory structure after installation is complete /var/lib/mysql/data file storage path, customizable/etc/my.cnf configuration file/usr/lib64/mysql       library file path/usr/bin/mysql* binary executable path/var/log/mariadb //var/log/mariadb/mariadb.log  log file address

　　

　　

MySQL master-slave replication principle Configuration