Summary and arrangement of MySQL based on GTID master-slave construction

This article brings you relevant knowledge about mysql. It mainly introduces the master-slave construction of MySQL based on GTID. The article first synchronizes data through xtarbackup to expand the details of the article. Interested friends You can refer to it.

Recommended learning: mysql video tutorial

Use xtarbackup to synchronize data, and then set the master and slave based on GTID.

1. Use xtarbackup to back up the database

1.1 Advantages

Use xtarbackup to do the preliminary preparation of master and slave because xtarbackup backs up data and restores data very quickly, which is especially suitable for the amount of data. It has a large database backup, and its installation is very simple, and its use is also very simple.... (Bala, bala, I can't make up the nonsense).

1.2 Installation

Choose the specific version according to your specific situation. Just follow these few steps to install it. Isn't it very simple...

# rpm -Uvh https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-3.noarch.rpm
# yum list | grep percona
# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL 
# rpm -Uvh ftp://rpmfind.net/linux/epel/6/x86_64/libev-4.03-3.el6.x86_64.rpm
# yum install percona-xtrabackup –y

1.3 Use

1.3.1 Ordinary backup

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 /data/backupMysql/

1.3.2 tar Backup

(1), backup to local

# 不压缩
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --stream=tar /data/backupMysql/>/data/mysql.tar

# 压缩
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --stream=tar /data/backupMysql/ | gzip >/data/mysql.tar.gz

(2), backup to remote

# 不压缩
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --stream=tar /data/backupMysql/ | ssh root@192.168.1.7 \ "cat - >/data/mysql.tar

# 压缩
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --stream=tar /data/backupMysql/ | | ssh root@192.168.1.7 \ "gzip >/data/mysql.tar.gz

(3), decompression method

# 未经过压缩的文件解压
tar xvf mysql.tar -C /data

# 压缩过的文件解压
tar zxvf mysql.tar.gz -C /data

1.3.3 xbstream backup

(1), backup to local

# 不压缩
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --stream=xbstream /data/backupMysql/>/data/mysql.xbstream

# 压缩
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --stream=xbstream --compress /data/backupMysql/ >/data/mysql_compress.xbstream

(2) Backup to remote

# 不压缩
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --stream=xbstream /data/backupMysql/| ssh root@192.168.1.7 "xbstream -x -C /backup/stream"

# 压缩
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --stream=xbstream --compress /data/backupMysql/ | ssh root@192.168.1.7 "xbstream -x -C /backup/stream"

(3) Decompression method

#### 未压缩的
xbstream -x < mysql.xbstream -C /data

#### 压缩过的
# 1、先解压xbstream
xbstream -x < mysql_compress.xbstream -C /data
# 2、再解压qp压缩格式
for bf in `find . -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) && rm $bf; done

注：如果xtrabackup版本大于2.1.4，可以直接通过以下方式解压第二步。
innobackupex --decompress /data

1.3.4 Restore

First backup the original Unzip the compressed package to a directory, and then execute the following statement to restore.

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --copy-back /var/lib/mysql/backup/

Note: You can use the split-screen tool during the backup, decompression, and recovery process. I like to use screen.

2. Data synchronization based on GTID

2.1 The concept of GTID

• 1. Global transaction identifiers: global transaction identifiers.
• 2. GTID is a one-to-one correspondence for a transaction and is a globally unique ID.
• 3. A GTID is only executed once on a server to avoid data confusion or master-slave inconsistency caused by repeated execution.
• 4. GTID is used to replace the traditional replication method, and MASTER_LOG_FILE MASTER_LOG_POS is no longer used to enable replication. Instead, use MASTER_AUTO_POSTION=1 to start copying.
• 5. It is supported starting from MySQL-5.6.5 and will be improved after MySQL-5.6.10.
• 6. On the traditional slave side, binlog does not need to be turned on, but in GTID, the binlog on the slave side must be turned on in order to record the executed GTID (mandatory).

2.2 Composition of GTID

GTID = source_id:transaction_id source_id: used to identify the original server, that is, the unique server_uuid of the mysql server. Since the GTID will be passed to the slave, it can also be used Understood as source ID.

transaction_id: It is a sequence number of a transaction that has been submitted on the current server. It is usually a self-increasing sequence starting from 1. One value corresponds to one transaction.

Example: 3E11FA47-71CA-11E1-9E33-C80AA9429562:23 The first string is the server_uuid of the server, that is, 3E11FA47-71CA-11E1-9E33-C80AA9429562, and the last 23 is the transaction_id

2.3 Principle of GTID

1. When a transaction is executed and submitted on the main library side, a GTID is generated and recorded in the binlog together.

2. After the binlog is transferred to the slave and stored in the slave's relaylog, read the value of the GTID and set the gtid_next variable, which tells the slave the next GTID value to be executed.

3. The sql thread obtains the GTID from the relay log, and then compares the binlog on the slave side to see whether the GTID exists.

4. If there is a record, it means that the transaction with the GTID has been executed, and the slave will ignore it.

5. If there is no record, the slave will execute the GTID transaction and record the GTID to its own binlog. Before reading and executing the transaction, it will first check that other sessions hold the GTID to ensure that it is not executed repeatedly. .

6. During the parsing process, it will be judged whether there is a primary key. If not, use the secondary index. If not, use all scans.

2.4 Advantages of GTID

• 1. Easier implementation of failover, no need to look for log_file and log_pos as before.
• 2. Easier to build master-slave replication.
• 3. More secure than traditional copying.
• 4. GTID is continuous without holes, ensuring data consistency and zero loss.

2.5 Specific construction process

For the configuration of GTID, mainly modify several important parameters related to GTID features in the configuration file. The mysql version is recommended to be mysql-5.6.5 or above.

2.5.1 Open the master Gtid

The main configuration is as follows:

[mysqld]
#GTID:
server_id=135                #服务器id
gtid_mode=on                 #开启gtid模式
enforce_gtid_consistency=on  #强制gtid一致性，开启后对于特定create table不被支持

#binlog
log_bin=master-binlog
log-slave-updates=1    
binlog_format=row            #强烈建议，其他格式可能造成数据不一致

#relay log
skip_slave_start=1

2.5.2 Perform data backup on the master

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --stream=tar /data/backupMysql/ | | ssh root@192.168.1.7 \ "gzip >/data/mysql.tar.gz

2.5.3 Decompress the backed up data

tar zxvf /data/mysql.tar.gz -C /data/baskup

2.5.4 Configure the slave configuration file

[mysqld]
#GTID:
gtid_mode=on
enforce_gtid_consistency=on
server_id=143

#binlog
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row      #强烈建议，其他格式可能造成数据不一致

#relay log
skip_slave_start=1

2.5.5 Restore data

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --copy-back /data/backup

2.5.6 Get GTID node

more /data/backup/2018-02-08_15-03-18/xtrabackup_binlog_info

2.5.7 Configure master-slave

(1), authorize on master

grant replication slave on *.* to slaveuser@'192.168.1.7'  identified by "c2xhdmV1c2Vy";

(2), on Configuration on slave

stop slave;
SET GLOBAL gtid_purged="c5b5ffe7-ce66-11e7-9a19-00163e00013d:1-515758";
CHANGE MASTER TO MASTER_HOST='192.168.1.6',MASTER_PORT=3306,MASTER_USER='slaveuser',MASTER_PASSWORD='c2xhdmV1c2Vy',MASTER_AUTO_POSITION=1;
start slave;

2.6 已运行经典复制mysql服务器转向GTID复制

• a、按本文2.5.2描述配置参数文件；
• b、所有服务器设置global.read_only参数，等待主从服务器同步完毕；  mysql> SET @@global.read_only = ON;
• c、依次重启主从服务器；
• d、使用change master 更新主从配置；mysql> CHANGE MASTER TO > MASTER_HOST = host,  > MASTER_PORT = port, > MASTER_USER = user,   > MASTER_PASSWORD = password,   > MASTER_AUTO_POSITION = 1;
• e、从库开启复制  mysql> START SLAVE; f、验证主从复制

推荐学习：mysql视频教程

The above is the detailed content of Summary and arrangement of MySQL based on GTID master-slave construction. For more information, please follow other related articles on the PHP Chinese website!