MySQL master-slave replication with Gtid master-slave replication

1. Master-slave replication

1.1 Principle

The Main Library opens the Binlog function and authorizes the main library to connect from the library, obtains the relevant synchronization information from the library through change master, and then connects the main library for verification, and the main library IO thread is based on the request from the library slave thread. Starting from the location of the record Master.info start to fetch information, at the same time, the location point and the latest location and the Binlog information sent from the library IO thread, from the library to the relevant SQL statements in Relay-log, and finally from the SQL thread of the library to apply the SQL statements in the Relay-log from the library , the entire synchronization process is complete, and then the process is repeated indefinitely.

1.2 Effects

1) Secondary backup

2) high availability, dual master multiple slave

3) load sharing, read/write separation

2. Deploying Master-slave replication

2.1 Main Library Create authorized user Repl

mysql> grant replication Slave on * * to [e-mail protected] ' 10.0.0.% ' idnetified by ' 123456 ';

2.2 Preparation (if the new environment, this step is omitted; If the main library has been running for some time, you need to back up the main library data to the slave library)

Primary Library Backup

[Email protected] ~]# mysqldump-a--master-data=2-p >/tmp/full1.sql

Recovering from a library

[Email protected] ~]# SCP 172.16.1.102:/tmp/full.sql/tmp

Mysql> set sql_log_bin=0;

Mysql> Source/tmp/full.sql;

2.3 Main Library Open Binlog

[Email protected] ~]# VIM/ETC/MY.CNF

Log_bin=/data/mysql/mysql-bin

Binlog_format=row

[Email protected] ~]#/etc/init.d/mysqld restart

2.4 Establishing a connection to the main library from the library

mysql> change MASTER to

master_host= ' 172.168.1.102 ',

master_user= ' Repl ',

master_password= ' 123456 ',

master_port=3306,

master_log_file= ' mysql-bin.000002 ',

master_log_pos=656;

2.5 Copying starting from the library

mysql> start slave;

2.6 Check

Mysql> show Slave status\g;

Slave_io_running:yes

Slave_sql_running:yes

Note: Only all yes is successful; otherwise it fails.

3.Gtid Master-slave replication

3.1 Introduction

Gtid is a global transaction ID that has been successfully executed based on the original MySQL server, combined with the server ID and transaction ID. This global transaction ID is unique not only on the original server, but also on all MySQL servers where the master-slave relationship exists. It is this feature that makes MySQL master-slave replication easier and database consistency more reliable

3.2 Concepts

1) Global transaction ID: Transaction identifiers.

2) Gtid is a transaction one by one correspondence, and a globally unique ID.

3) A gtid is executed only once on a server, avoiding duplication of data or the fact that the master never agrees.

4) 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.

5) MySQL-5.6.5 began to support, after MySQL-5.6.10 began to improve.

6) in the traditional slave end, Binlog is not open, but in the Gtid slave end of the binlog is must be opened, the purpose is to record the execution of Gtid (mandatory).

3.3 Advantages

1) Simpler implementation of failover, do not need to find log_file and Log_pos before

2) Easier to build master-slave replication

3) More secure than traditional replication

4) Gtid is continuous without voids, guaranteed data consistency, 0 loss

3.4 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) After the Binlog is transferred to slave and stored to slave relaylog, the Gtid variable is read, which tells Gtid_next the next slave value to execute.

3) SQL thread gets gtid from relay log and then compares Binlog on slave side for that Gtid

4) If there is a record stating that the Gtid transaction has been executed, slave will ignore

5) If there is no record, slave executes the GTID transaction and logs the GITD to its own binlog, checking that the other session holds GTID before reading the execution transaction, ensuring that it is not repeated.

6) In the parsing process will determine whether there is a primary key, if not a two-level index, if not the full scan

4. Deploying Gtid Master-slave replication

4.1 Cleaning up the environment

rm-rf/application/myql/data/*

4.2 Building a master-slave environment

----------Master Node----------

Vim/etc/my.cnf

[Mysqld]

Basedir=/application/mysql

Datadir=/application/mysql/data

Server-id=1 #同一个复制拓扑中的所有服务器的id号必须唯一

Log-bin=mysql-bin

Socket=/tmp/mysql.sock

Binlog-format=row #二进制日志格式, it is strongly recommended for ROW

Gtid-mode=on #启用gtid类型, otherwise the normal replication architecture

Enforce-gtid-consistency=true #强制gtid的一致性

Log-slave-updates=1 #slave更新是否记入日志

Skip-name-resolve

[Client]

Socket=/tmp/mysql.sock

----------slave1----------

Vim/etc/my.cnf

[Mysqld]

Basedir=/application/mysql

Datadir=/application/mysql/data

server-id=2

Binlog-format=row

Gtid-mode=on

Enforce-gtid-consistency=true

Log-bin=mysql-bin

Log_slave_updates = 1

Socket=/tmp/mysql.sock

Skip-name-resolve

[Client]

Socket=/tmp/mysql.sock

----------Slave2----------

Vim/etc/my.cnf

[Mysqld]

Basedir=/application/mysql

Datadir=/application/mysql/data

Server-id=3

Binlog-format=row

Gtid-mode=on

Enforce-gtid-consistency=true

Log-bin=mysql-bin

Log_slave_updates = 1

Socket=/tmp/mysql.sock

Skip-name-resolve

[Client]

Socket=/tmp/mysql.sock

4.3 Initialize master, slave all nodes, and start

/application/mysql/scripts/mysql_install_db--basedir=/application/mysql--datadir=/application/mysql/data--user =mysql

/etc/init.d/mysqld start

4.4master Node Authorized user Repl

mysql> gant replication Slave on * * to [e-mail protected] ' 10.0.0.% ' identified by ' 123456 ';

4.5slave node creates a connection to the master node

Mysql> Change Master to

>master_host= ' 172.16.1.102 ',

>master_user= ' Repl ',

>master_password= ' 123456 ',

>master_auto_position=1;

4.6slave node Turn on replication

mysql> start slave;

4.7slave Node View status

Mysql> show Slave status\g;

MySQL master-slave replication with Gtid master-slave replication