Master-slave replication database based on Gtid

master-slave replication database based on GtidGlobal Identity GTID (transaction identifier)In order to achieve strong consistency of the primary and standby database

GTID = source_id:transaction_id


TRANSACTION_ID is a serial number that represents the nth transaction executed on this main library.

Server_uuid is automatically generated by the system, used to replace the server_id, because source_id is manually set, there may be conflicts

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/8F/59/wKiom1jbdd7S4cJPAAAiUu8I5Pk550.png "title=" Screenshot from 2017-03-29 15-53-21.png "alt=" Wkiom1jbdd7s4cjpaaaiuu8i5pk550.png "style=" float:none; "/>

installation and initialization of the database

server33,44:

mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm

Yum Install *-y

/etc/init.d/mysqld start

Startup will automatically initialize the password, if not shown, you can go to the log to see

Tail-n 3/var/log/mysqld.log

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/8F/57/wKioL1jbddqSSsl0AACGtJ6Psrs622.png "title=" Screenshot from 2017-03-29 14-49-22.png "style=" Float:none; "alt=" Wkiol1jbddqsssl0aacgtj6psrs622.png "/>

Create root User

mysql> alter user [email protected] identified by ' westos+007 ';

gtid configuration of Master-slave database

Server33:

Vim/etc/my.cnf

Server-id=3
Gtid-mode=on
Enforce-gtid-consistency

Log-bin=mysql-bin
Binlog-do-db=test

Log-slave-updates

Authorizing replication users in the 172.25.88.0 network segment

mysql> grant replication Slave on * * to [e-mail protected] ' 172.25.88.% ' identified by ' westos+007 ';

Server44:

Vim/etc/my.cnf

Server-id=4
Read-only

Gtid-mode=on
Enforce-gtid-consistency

Log-slave-updates #开启从库改变记录在log The properties of the-bin log, from the library may also be the main library of other databases

Set Server33 to Master,

mysql> Change Master to master_host= ' 172.25.88.33 ', master_user= ' Miao ', master_password= ' westos+007 ', master_auto_ Position=1; #自动定位pos
mysql> start slave;

Verify that synchronization is successful

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/8F/57/wKioL1jbdduy2fNBAADoCIK8RbE554.png "title=" Screenshot from 2017-03-29 15-20-00.png "style=" Float:none; "alt=" Wkiol1jbdduy2fnbaadocik8rbe554.png "/>

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/8F/59/wKiom1jbddziq8gyAAAy9Mqkgxg867.png "title=" Screenshot from 2017-03-29 15-20-09.png "style=" Float:none; "alt=" Wkiom1jbddziq8gyaaay9mqkgxg867.png "/>

Check if it is Gtid mode

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/8F/57/wKioL1jbdd6RV1F6AABTOk8ysjA303.png "title=" Screenshot from 2017-03-29 15-36-07.png "style=" Float:none; "alt=" Wkiol1jbdd6rv1f6aabtok8ysja303.png "/>

the difference between Gtid and classic master-slave

1. Do not manually position POS and Binlog, add parameter master_auto_position=1
2. Multithreading concurrent replication, slave-parallel-workers=0,1,2 (disabled, single thread, two threads)

The master database has changes such as DML, which is stored in Bin-log,
Backup to the primary database, network I/O to master to fetch data to slave
So the Classic mode is generally manually set to slave backup, because it is important to ensure that the master is the same as the data, automatically prone to problems.
Recommended use of mysql5.7

SQL Four languages

1.DDL (definition) Database definition language
DDL does not require a commit.
Create,alter,drop,truncate (Fast emptying SQL table), Comment,rename

2.DML (manipulation) Data manipulation language
DML requires a commit.
Select,insert,update,delete,merge (change), Call,explain Plan,lock TABLE




4.TCL (Transaction control Language) transaction Control Language
SavePoint set save point, ROLLBACK rollback, set TRANSACTION

debug: Show slave status \g I/O and SQL not yes

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/8F/57/wKioL1jbdd_RPoYVAAAVYrs97BY559.png "title=" Screenshot from 2017-03-29 16-15-47.png "alt=" Wkiol1jbdd_rpoyvaaavyrs97by559.png "style=" float:none; "/>

I/o:selinux,iptables, Authorization
SQL: Master-Slave data inconsistency

This article from "12049878" blog, declined reprint!

Master-slave replication database based on Gtid