MySQL GTID (i)

MySQL GTID Series one by one. Gtid Related Concepts Gtid: The global transaction identifier, the MySQL5.6 version begins with the heavyweight feature introduced in master-slave replication. For each commit transaction, the current thread of execution will have the format of the only gtid,gtid in a given replication environment as follows:

GTID = source_id:sequence_idsourceid:主服务器的唯一标识,通常用server_uuid来表示。sequence_id:事务提交时由系统顺序分配的序列号,在Binlog中是递增且连续有序。show master status \G

1.1 Gtid life cycle 1.MASTER generates Gtid and is saved to Binlog. 2. Send Binlog to Slave, save to relay Log3.sql thread get relay from Gtid log, set to Gtid_next, and compare slave on binlog to see if there is a record. 4. If there is a record stating that the Gtid transaction has been executed, slave will ignore it. 5. If there is no record, slave executes the Gtid transaction from the relay log and logs it to binlog. 6.SLAVE does not generate GTID1.2 Gtid Advantages 1. A transaction corresponding to a unique ID, a gtid on a server will only execute once 2.GTID is used to replace the traditional method of replication, gtid replication and normal replication mode is the most different than the need to specify the binary file name and location 3. Reduce manual intervention and reduce service downtime when the host is hung Software from a large number of standby machines to upgrade a standby machine two. Build Gtid master-Slave 2.1 Related parameter description

server_id: 跟传统的主从一样，每个实例不能一样gtid_mode: 设置为ON，开启GTID模式enforce_gtid_consistency:设置为ON,保证GTID一致性log_bin: 跟传统的主从一样必须打开log_slave_updates:建议开启 可以用来级联复制binlog_format:强烈建议设置为ROW,否则会造成数据不一致#skip-slave-start:设置为1,SLAVE启动时,不自动开启复制

2.2 Opening Gtid

Before MySQL5.7.6, you must restart the database if the database is already started. The steps are as follows:

1.关闭MASTER的写入2.SLAVE设置参数 skip-slave-start=1,避免SLAVE启动后,继续使用传统的复制模式。3.按照上节所述,所有MySQL实例修改配置,开启GTID模式4.重启所有MySQL数据库

After the MySQL5.7.6, it can be adjusted online, no need to restart.

2.3 Building Master-Slave

In three different situations:

A.master new building, direct use of change MASTER;

CHANGE MASTER TO MASTER_HOST='***',                 MASTER_PORT=***  ,                 MASTER_USER='***',                 MASTER_PASSWORD='***',                 

B.master run soon, Binlog remain intact, direct use of change MASTER, if the amount of data is large, synchronization time is too long, and may lead to network pressure; C.master has a large amount of data, the original binlog may be deleted

Environment

CENTOS 7.2MySQL5.7.22

1. Download and install Xtrabackup

#下载XtraBackupwget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm#安装所需包yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL rsync perl-Digest-MD5 libev#安装XtraBackuprpm -ivh percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

2. All MySQL modification configuration, can be modified online, while modifying the configuration file for the next effective

3. Back up the database, get master data and Gtid range

#因为主库才开启GTID,模拟一些事务,使其产生一些binlogcreate table test(id int);insert into test (id)values(1),(2) #备份数据innobackupex --user=root --password=root --port=3306 /MySQL/my3306/data/fullbackup/#查看gtid范围[[email protected] 2018-07-26_23-23-00]# cat xtrabackup_binlog_info binlog.000012   

4.COPY backup data to Slave, build slave instance

#COPY数据scp -r 2018-07-26_23-23-00 [email protected]:/MySQL/my3306/fullbackup/#停止SLAVEservice mysqld stop#从库将原有数据文件夹重命名到新位置，并创建原文件夹 mv /MySQL/my3306/data /MySQL/my3306/databakmkdir -p /MySQL/my3306/data#innobackupex apply-loginnobackupex --apply-log --user=root --password=root --port=3606 /MySQL/my3306/fullbackup/2018-07-26_23-23-00/#innobackupex copy 恢复的文件到原来的数据位置innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /MySQL/my3306/fullbackup/2018-07-26_23-23-00/#给目录并赋权chown -R mysql:mysql /MySQL/my3306/data

5. Set up synchronization users, skip Gtid, build master-Slave

#主从库建立同步用户grant replication slave,replication client on *.* to 'rep'@'%' identified by 'rep';#启动SLAVE实例,设置gtid_purged的值,跳过GTID范围service mysqld startSET @@GLOBAL.GTID_PURGED='9760cb92-693e-11e8-85bf-000c29b55cf0:1-2'#利用CHANGE MASTER语句,配置主从复制CHANGE MASTER TO MASTER_HOST='192.168.2.144',                 MASTER_PORT=3306  ,                 MASTER_USER='rep',                 MASTER_PASSWORD='rep',                 MASTER_AUTO_POSITION=1; #启动SLAVE复制,SLAVE会自动跳过GTID范围,拉取最新的GTID信息START SLAVE;#查看主从情况SHOW SLAVE STATUS \G

6. Verify Master-Slave synchronization

Mysql> Show slave status \g;*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              t:192.168.2.144 Master_user:rep master_port:3306 connect_retry:60                master_log_file:binlog.000012 read_master_log_pos:1561 relay_log_file:relaylog.000002            relay_log_pos:1387 relay_master_log_file:binlog.000012 Slave_io_running:yes        Slave_sql_running:yes Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:                   replicate_ignore_table:replicate_wild_do_table:replicate_wild_ignore_table:last_errno:0 last_error:skip_counter:0 exec_master_log_pos:1561 relay_log_sp          ace:1587 Until_condition:none Until_log_file:       until_log_pos:0 Master_ssl_allowed:no Master_ssl_ca_file:master_ssl_ca_path: master_ssl_cert:master_ssl_cipher:master_ssl_key:seconds_behind_master:0m Aster_ssl_verify_server_cert:no last_io_errno:0 Last_io_error:last_sql_err no:0 last_sql_error:replicate_ignore_server_ids:master_server_id:101 Mas                    TER_UUID:9760CB92-693E-11E8-85BF-000C29B55CF0 Master_info_file:/mysql/my3306/data/master.info sql_delay:0 Sql_remaining_delay:null Slave_sql_running_state:slave have read all relay log;      Waiting for more updates master_retry_count:86400 Master_bind:last_io_error_timestamp: Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set : 9760cb92-693e-11e8-85bf-000c29b55cf0:3-6 executed_gtid_set:9760cb92-693e-11e8-85bf-000c29b55cf0:1-6 Auto_position:1 R Eplicate_rewrite_db:channel_name:master_tls_version:1 row in Set (0.00 sec)

MySQL GTID (i)