MySQL database master-slave replication (i)

    MYSQL Master-slave replication principle

1 Replication Overview

      mysql 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 . Support 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.

The first part of the process is the master record binary log. Master records these changes in two logs before each transaction update data is complete. MySQL writes the transaction serially to the binary log, even if the statements in the transaction are cross-executed. After the event is written to the binary log, master notifies the storage engine to commit the transaction.
The next step is to slave copy the binary log of master to its own trunk logs. First, slave starts a worker thread--i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event. The I/O thread writes these events to the relay log.
The SQL slave thread (SQL slave thread) handles the last step of the process. The SQL thread reads events from the log and replays the events in them to update the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.
In addition, there is a worker thread in master: As with other MySQL connections, slave opening a connection in master will also cause master to start a thread. The replication process has a very important limitation-replication is serialized on slave, meaning that parallel update operations on Master cannot operate concurrently on slave.

2. Replication Configuration

There are two mysql database server master and Slave,master as the primary server, slave from the server, the initial state, master and slave the same data information, when the data in master changes, Slave also follow the corresponding changes, so that the master and slave data information synchronization, to achieve the purpose of backup.

key:
The media responsible for transferring various modification actions in the master and slave servers is the binary change log of the primary server. This log 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.

environment:
master and slave is the same as 5. 7

operating system: centos7.2 master ip Address: 192.168.0.6

S lave IP Address: 192.168.0.8

One: (1) Set up an event synchronization environment to build a time synchronization server on the master node

installation Ntp

[[email protected]/]# yum-y install NTP

(2) Configuring NTP

[Email protected]/]# vim/etc/ntp.conf

Restart Service

(2) synchronizing time on a slave node

[[email protected]/]# yum-y install ntpdate[[email protected]/]#/usr/sbin/ntpdate 192.168.0.6

closing firewalled on each server can also be open

[Email protected]/]# Setenforce 0

[Email protected]/]# Systemctl stop Firewalld.service

Installing the database process is not much of a description

(2) Configuring the MySQL Master master server

Modify or add the following in /etc/my.cnf

[Email protected]/]# systemctl restart mysqld

log in to MySQL program to authorize from server

which The file column displays the log name position displays the compilation amount of the two values that need to be slave from the node when it is configured from the server to be updated on master

(2) Configure the slave server

Modify or add the following content in/ETC/MY.CNF the server ID cannot be the same as starting the service

(2) login mysql configuration sync

Change the Master-log-file and Master-log-pos parameters in the following command according to the primary server results

Start synchronization

mysql> start slave;

Mysql> show Slave status\g;

(2) Verifying master-slave copy effects

Log on to the primary server MYSQL Create library in view from server

viewing effects from the server whether master-slave replication succeeds

later update MySQL database read/write separation

MySQL database master-slave replication (i)