Linux Enterprise-mysql Master-Slave synchronization, Gtid, semi-synchronous

I. MySQL master-slave synchronization

MySQL supports one-way, asynchronous replication, one server acting as the primary server during replication, and one or more other servers charging
When from the server. The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. This
Some logs can record updates sent to the slave server. When a primary server is connected from the server, it notifies the primary server
The location of the last successful update that the service read in the log. Receive from the server any updates that have occurred since then, and then seal
Lock and wait for the primary server to notify the new update.
Note that when you make a copy, all updates to the tables in the replication must be made on the primary server. Otherwise, you have to be careful,
To avoid conflicts between updates to tables on the primary server and updates made to tables on the server.
One-way replication facilitates robustness, speed, and system management:
1. The master server/slave server setting adds robustness. You can switch to a backup from the server when there is a problem with the primary server
2. Better customer response times can be achieved by slicing the load of client queries between the primary server and the server.
SELECT queries can be sent to the slave server to reduce the query processing load on the primary server. But modify
The statement of the data should still be sent to the master server so that the master server and the server remain synchronized. If the non-update query is primary, the
The load balancing policy is effective, but is typically an update query.
3. Another benefit of using replication is that you can use one to perform backups from the server without interfering with the primary server. During the backup process
The master server can continue to process the update.
MySQL provides database synchronization capabilities, which enable us to implement database redundancy, backup, recovery, load balancing, etc. are
be of great help.

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/07/D0/wKiom1nQ2Guh6xWZAAQCxQbWqrU520.png "title=" Screenshot from 2017-10-01 19-55-30.png "alt=" Wkiom1nq2guh6xwzaaqcxqbwqru520.png "/>

Two. Configuring the Environment

Server2 Main 172.25.29.2

Server3 from 172.25.29.3

1. Configure Server2

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/A6/80/wKioL1nQrP2Tt1oDAAAb5QNZOIk717.png "style=" float : none; "title=" screenshot from 2017-09-28 11-20-01.png "alt=" Wkiol1nqrp2tt1odaaab5qnzoik717.png "/>

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/07/CE/wKiom1nQrULjmtcmAABHalQTwYY899.png "style=" float : none; "title=" screenshot from 2017-09-28 11-20-13.png "alt=" Wkiom1nqruljmtcmaabhalqtwyy899.png "/>

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/A6/80/wKioL1nQrP6QEu-dAABR5pwoBMQ295.png "style=" float : none; "title=" screenshot from 2017-09-28 11-20-21.png "alt=" Wkiol1nqrp6qeu-daabr5pwobmq295.png "/>

Log-bin=mysql-bin start the binary log system
Binlog-do-db=test #二进制需要同步的数据库名, if you need to synchronize multiple libraries, such as synchronizing Westos
Library, add another line of "Binlog-do-db=westos", and so on
Server-id=1
#必须为 a positive integer value between 1 and 232–1
Binlog-ignore-db=mysql #禁止同步 MySQL Database

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/07/CE/wKiom1nQrUOBY_rrAABgu-LQgDk292.png "style=" float : none; "title=" screenshot from 2017-09-28 11-21-16.png "alt=" Wkiom1nqruoby_rraabgu-lqgdk292.png "/>

Enter MySQL to create an authorized user to view the master information

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/07/CE/wKiom1nQrUOD33g_AABU5t0p9mY570.png "style=" float : none; "title=" screenshot from 2017-09-28 11-24-34.png "alt=" Wkiom1nqruod33g_aabu5t0p9my570.png "/>

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/07/CE/wKiom1nQrUWhnHvHAABGfXgvCCg882.png "style=" float : none; "title=" screenshot from 2017-09-28 11-36-29.png "alt=" Wkiom1nqruwhnhvhaabgfxgvccg882.png "/>

2. Configure Server3

Configuration file just write the ID number

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/A6/80/wKioL1nQrP-Thj1YAAA8DywF05o166.png "style=" float : none; "title=" screenshot from 2017-09-28 11-25-25.png "alt=" Wkiol1nqrp-thj1yaaa8dywf05o166.png "/>

Restart the service, set the Server2 as the primary, note the Log_file file and Log_pso file location, see on Server2

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/07/CE/wKiom1nQrUSzSs3yAABSz8uIFJY738.png "style=" float : none; "title=" screenshot from 2017-09-28 11-25-48.png "alt=" Wkiom1nqruszss3yaabsz8uifjy738.png "/>

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/A6/80/wKioL1nQrP_jmKnKAABbif81KP8533.png "style=" float : none; "title=" screenshot from 2017-09-28 11-30-24.png "alt=" Wkiol1nqrp_jmknkaabbif81kp8533.png "/>

Starting from the database host, viewing the status

Slave_io_running:yes
Slave_sql_running:yes

If all is yes, the i/o,slave_sql thread from the library is turned on correctly. Indicates that the database is synchronizing

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/A6/80/wKioL1nQrQDwb5ldAACSz7c0eAc357.png "style=" float : none; "title=" screenshot from 2017-09-28 11-30-31.png "alt=" Wkiol1nqrqdwb5ldaacsz7c0eac357.png "/>

View synced data, show normal

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/07/CE/wKiom1nQrUWCRPlsAABS3qhg9Mo433.png "style=" float : none; "title=" screenshot from 2017-09-28 11-36-44.png "alt=" Wkiom1nqruwcrplsaabs3qhg9mo433.png "/>

Three. Settings for Gtid

Global transaction ID: Transaction identifiers. Gtid is a transaction one by one correspondence, and a globally unique ID. A gtid is executed only once on a server, avoiding duplication of data or the fact that the master is never consistent.
Gtid is used instead of the traditional copy method, and no longer uses Master_log_file+master_log_pos to turn on replication. Instead, use Master_auto_postion=1 to start copying. MYSQL-5.6.5 began to support the MySQL-5.6.10 after the start of perfection. On the traditional slave side, the Binlog is not open, but the binlog in the slave end of the gtid must be turned on to record the Gtid (mandatory) performed.

Advantage:

Simpler implementation of failover, without the need to find log_file and Log_pos before. More simple to build master-slave replication. More secure than traditional replication. Gtid is continuous, no voids, guaranteed data consistency, 0 loss.

Working principle:

(1) When a transaction is executed and committed on the main library side, the Gtid is generated and recorded in the Binlog log.
(2) Binlog transmitted to slave, and stored to slave relaylog, read this gtid value set gtid_next variable, that is, tell slave, the next Gtid value to execute.
(3) The SQL thread obtains gtid from relay log, and then compares Binlog on the slave side for that gtid.
(4) If there is a record stating that the Gtid transaction has been executed, the slave will be ignored.
(5) If there is no record, slave executes the Gtid transaction and logs the Gtid to its own binlog,
Before reading the execution transaction, the other session is checked to hold the Gtid, ensuring that it is not executed repeatedly.
(6) In the parsing process will determine whether there is a primary key, if not a two-level index, if not the full scan.

1. Install the high-version 5.7.19 MySQL

Low version does not support Gtid

Delete the previous version of the MySQL file

Configure the master/slave configuration first after installation is complete.

Configure the Server2,server3.

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/07/CF/wKiom1nQs8ujatMJAABew9T4XdY341.png "style=" float : none; "title=" screenshot from 2017-09-28 13-59-03.png "alt=" Wkiom1nqs8ujatmjaabew9t4xdy341.png "/>

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/A6/80/wKioL1nQs4biRBeuAABfv4CqUFE157.png "style=" float : none; "title=" screenshot from 2017-09-28 13-59-19.png "alt=" Wkiol1nqs4birbeuaabfv4cqufe157.png "/>

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/A6/80/wKioL1nQtFbw7Oq3AAEU9pOhnXc838.png "style=" float : none; "title=" screenshot from 2017-09-28 14-10-34.png "alt=" Wkiol1nqtfbw7oq3aaeu9pohnxc838.png "/>

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/07/CF/wKiom1nQtJuiLg03AABM6EpjY0E213.png "style=" float : none; "title=" screenshot from 2017-09-28 14-35-01.png "alt=" Wkiom1nqtjuilg03aabm6epjy0e213.png "/>

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/07/CF/wKiom1nQtJziUrLPAAAkKJrm4QI955.png "style=" float : none; "title=" screenshot from 2017-09-28 14-35-16.png "alt=" Wkiom1nqtjziurlpaaakkjrm4qi955.png "/>

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/A6/80/wKioL1nQtFfiX-e0AAA0lcTJLkI939.png "style=" float : none; "title=" screenshot from 2017-09-28 14-35-29.png "alt=" Wkiol1nqtffix-e0aaa0lctjlki939.png "/>

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/07/CF/wKiom1nQtJyzpN4TAAArrbObbic832.png "style=" float : none; "title=" screenshot from 2017-09-28 14-35-37.png "alt=" Wkiom1nqtjyzpn4taaarrbobbic832.png "/>

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/A6/80/wKioL1nQtFig0IPXAAD5xzzB110534.png "style=" float : none; "title=" screenshot from 2017-09-28 14-36-13.png "alt=" Wkiol1nqtfig0ipxaad5xzzb110534.png "/>

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/07/CF/wKiom1nQtJ3RF0ArAACUUSaE08s204.png "style=" float : none; "title=" screenshot from 2017-09-28 14-36-25.png "alt=" Wkiom1nqtj3rf0araacuusae08s204.png "/>

Complete configuration of master-slave replication

2. Configure Gtid

Configuring Server1 VIM/ETC/MY.CNF

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/A6/81/wKioL1nQzrLhQIQ3AAAt8_BKNC8830.png "style=" float : none; "title=" screenshot from 2017-09-28 15-01-13.png "alt=" Wkiol1nqzrlhqiq3aaat8_bknc8830.png "/>

Configuring Server2 VIM/ETC/MY.CNF

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/07/D0/wKiom1nQzvfRgNoZAAAVYt8hoJA934.png "style=" float : none; "title=" screenshot from 2017-09-28 15-02-42.png "alt=" Wkiom1nqzvfrgnozaaavyt8hoja934.png "/>

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/A6/81/wKioL1nQzrPADXSOAAA0rSMtm0Y818.png "style=" float : none; "title=" screenshot from 2017-09-28 15-02-59.png "alt=" Wkiol1nqzrpadxsoaaa0rsmtm0y818.png "/>

Log on to MySQL on Server3 to create Server2 master node

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/07/D0/wKiom1nQzvjRkNIFAACbhoLNdn8139.png "style=" float : none; "title=" screenshot from 2017-09-28 15-08-40.png "alt=" Wkiom1nqzvjrknifaacbholndn8139.png "/>

Start from service, view from status

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/A6/81/wKioL1nQzrOwsg_RAACq6Qx0Nmg251.png "style=" float : none; "title=" screenshot from 2017-09-28 15-08-47.png "alt=" Wkiol1nqzrowsg_raacq6qx0nmg251.png "/>

3. Testing

Creating Data on Server2

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/A6/81/wKioL1nQzrPT4YSRAABaPASpp8g774.png "style=" float : none; "title=" screenshot from 2017-09-28 15-13-43.png "alt=" Wkiol1nqzrpt4ysraabapaspp8g774.png "/>

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/07/D0/wKiom1nQzvjwNrvzAABeS_D7jco352.png "style=" float : none; "title=" screenshot from 2017-09-28 15-13-52.png "alt=" Wkiom1nqzvjwnrvzaabes_d7jco352.png "/>

View data on the Server3 on the Server2

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/A6/81/wKioL1nQzrPiiM5sAAB03su4dnQ008.png "style=" float : none; "title=" screenshot from 2017-09-28 15-17-00.png "alt=" Wkiol1nqzrpiim5saab03su4dnq008.png "/>

View the Gtid Update table from the machine, there are already update records

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/07/D0/wKiom1nQzvnDpmHdAABRtvl5AHY174.png "style=" float : none; "title=" screenshot from 2017-09-28 15-19-05.png "alt=" Wkiom1nqzvndpmhdaabrtvl5ahy174.png "/>

4. Turn on multi-threaded concurrent replication

Slave-parallel-type

Slave-parallel-workers

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/A6/81/wKioL1nQ1ALCPmgVAAA3ZU_0QBc828.png "style=" float : none; "title=" screenshot from 2017-09-28 15-39-41.png "alt=" Wkiol1nq1alcpmgvaaa3zu_0qbc828.png "/>

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/A6/81/wKioL1nQ1ALiOyS9AABCslugbIQ816.png "style=" float : none; "title=" screenshot from 2017-09-28 15-44-44.png "alt=" Wkiol1nq1alioys9aabcslugbiq816.png "/>

View Show processlist process after reboot, show 16

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/07/D0/wKiom1nQ1EegsbaNAACzkbUnsk8733.png "style=" float : none; "title=" screenshot from 2017-09-28 15-44-53.png "alt=" Wkiom1nq1eegsbanaaczkbunsk8733.png "/>

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/07/D0/wKiom1nQ1EeD3_fEAACsdzKeWho837.png "style=" float : none; "title=" screenshot from 2017-09-28 16-00-01.png "alt=" Wkiom1nq1eed3_feaacsdzkewho837.png "/>

Number of workers is 16

Four. Semi-synchronous semi-synchronous is mainly to ensure data integrity to prevent data loss

1. Semi-synchronous replication concept

Before we explain the semi-synchronous replication, let's take a look at what is synchronous replication? Synchronous replication: Synchronous replication can be defined as data being submitted to one or more machines at the same time, usually through a well-known "two-phase commit". While this does give you consistency in multiple systems, it also degrades performance due to increased additional message exchange. MySQL, which uses the MyISAM or InnoDB storage engine, does not natively support synchronous replication, but some technologies, such as the Distributed Replication Block device (DRBD), can provide synchronous replication on the underlying file system. Allow the second MySQL server to take over if the primary server is lost (using a replica of the second server). Learn about synchronous replication We're going to take a look at what is semi-synchronous replication?

Starting with MYSQL 5.5, semi-automatic replication is supported. Previous versions of MySQL replication were asynchronous (asynchronous), and the master library did not Tabeku progress after performing some transactions. If the repository is unfortunate, and more unfortunately, the main library is now crash (for example, downtime), then the data in the standby library is incomplete. In short, in the event of a failure of the main library, we cannot use the standby to continue to provide data-consistent services. Semisynchronous Replication (semi-synchronous replication) is to some extent guaranteed that committed transactions have been passed to at least one repository. Semi synchronous, only guarantees that the transaction has been passed to the standby, but does not ensure that it has been completed on the standby.

In addition, there is a situation that causes the master and standby data to be inconsistent. In a session, when a transaction is committed on the main library, it waits for the transaction to pass to at least one repository, and if the main library crash during the wait, it is also possible that the repository is inconsistent with the main library, which is fatal. If the primary and standby network fails or the standby is hung, the main library will continue after the transaction commits for 10 seconds (the default value of Rpl_semi_sync_master_timeout). At this point, the main library will change back to its original asynchronous state.

After MySQL loads and opens the Semi-sync plugin, each transaction waits for the repository to receive the log before returning it to the client. If you do a small transaction, and the latency of the two hosts is small, then Semi-sync can achieve 0 data loss in the event of a small loss of performance.

similarities and differences between asynchronous and semi-synchronous

By default, MySQL replication is asynchronous, and all update operations on Master do not ensure that all updates are copied to slave after they are written to Binlog. Asynchronous operations are highly efficient, but there is a high risk of data synchronization and possibly loss of data in the event of a master/slave problem.

MYSQL5.5 introduces the semi-synchronous replication function to ensure that at least one slave data is intact when the master is in trouble. In the case of time-outs, it is also possible to temporarily transfer to asynchronous replication, guaranteeing the normal use of the business until a salve is catching up and continuing to switch to semi-synchronous mode.

2. Turn on the semi-sync on the host Server2

Add a semi-synchronous plug-in

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/A6/81/wKioL1nQ1dSBSqvOAABEM0CcejI673.png "style=" float : none; "title=" screenshot from 2017-09-28 16-20-56.png "alt=" Wkiol1nq1dsbsqvoaabem0cceji673.png "/>

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/07/D0/wKiom1nQ1hnDK89oAAA11abI0c8088.png "style=" float : none; "title=" screenshot from 2017-09-28 16-21-02.png "alt=" Wkiom1nq1hndk89oaaa11abi0c8088.png "/>

View the semi-sync status is off

Turn on Half-sync

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/A6/81/wKioL1nQ1dSyLgHCAAEWjzp8Z1k773.png "style=" float : none; "title=" screenshot from 2017-09-28 16-23-16.png "alt=" Wkiol1nq1dsylghcaaewjzp8z1k773.png "/>

3. On the host Server3 turn on half-sync

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/07/D0/wKiom1nQ1hrT1-raAAA3bjt3x0c970.png "style=" float : none; "title=" screenshot from 2017-09-28 16-25-52.png "alt=" Wkiom1nq1hrt1-raaaa3bjt3x0c970.png "/>

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/07/D0/wKiom1nQ1hrxGJOeAABYjvpLUG0703.png "style=" float : none; "title=" screenshot from 2017-09-28 16-25-59.png "alt=" Wkiom1nq1hrxgjoeaabyjvplug0703.png "/>

4. On the host Server3 Restart MySQL io interface is OK

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/A6/81/wKioL1nQ1dWglWeSAAA0_Vh9JE0334.png "style=" float : none; "title=" screenshot from 2017-09-28 16-27-20.png "alt=" Wkiol1nq1dwglwesaaa0_vh9je0334.png "/>

5. Test the Half-sync

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/07/D0/wKiom1nQ2JWDi8xlAABBcfG2REI213.png "style=" float : none; "title=" screenshot from 2017-09-28 16-18-50.png "alt=" Wkiom1nq2jwdi8xlaabbcfg2rei213.png "/>

Host semi-synchronous state is turned on

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/A6/81/wKioL1nQ2FDC0MpKAAD-_xP3Ss4578.png "style=" float : none; "title=" screenshot from 2017-09-28 17-06-18.png "alt=" Wkiol1nq2fdc0mpkaad-_xp3ss4578.png "/>

Host creation data is quickly synced to the slave server3

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/07/D0/wKiom1nQ2JaQahdQAADsg4-S6cg589.png "style=" float : none; "title=" screenshot from 2017-09-28 17-06-28.png "alt=" Wkiom1nq2jaqahdqaadsg4-s6cg589.png "/>

View slave semi-sync status on

Close the IO interface of the Server3

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/A6/81/wKioL1nQ2FGQNHBwAABKcngkuXQ340.png "style=" float : none; "title=" screenshot from 2017-09-28 17-08-02.png "alt=" Wkiol1nq2fgqnhbwaabkcngkuxq340.png "/>

Inserting data on the host Server2

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/07/D0/wKiom1nQ2JaQd_aIAAAo-BrqBCE828.png "style=" float : none; "title=" screenshot from 2017-09-28 17-08-36.png "alt=" Wkiom1nq2jaqd_aiaaao-brqbce828.png "/>

Server3 unresponsive after waiting for 10s semi-sync, server2 to asynchronous transfer

The host already has the data

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/A6/81/wKioL1nQ2FHTAaAYAABStYBKbIk194.png "style=" float : none; "title=" screenshot from 2017-09-28 17-10-33.png "alt=" Wkiol1nq2fhtaaayaabstybkbik194.png "/>

Viewing data inserted from the slave machine without the server2 on the host

Start the IO interface again and the data is synchronized.

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/A6/81/wKioL1nQ2FGRCo7wAABzON4aFdg216.png "style=" float : none; "title=" screenshot from 2017-09-28 17-10-42.png "alt=" Wkiol1nq2fgrco7waabzon4afdg216.png "/>

Viewing the slave's semi-synchronized state

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/07/D0/wKiom1nQ2JbwomcQAADNa285_Es916.png "style=" float : none; "title=" screenshot from 2017-09-28 17-26-59.png "alt=" Wkiom1nq2jbwomcqaadna285_es916.png "/>

Linux Enterprise-mysql Master-Slave synchronization, Gtid, semi-synchronous