MySQL-5.6.14 based on Gtid and multi-threaded replication

Experimental environment rhel-6.6-x86_64

Two hosts master 192.168.1.5 Hostname:master.mingxiao.info

SLAVE 192.168.1.6 Hostname:slave.mingxiao.info

Gtid Introduction

GTID (Global Transaction Identifier) is a globally unique identifier created at each transaction commit.

Gtid is made up of Uuid:tid.

How did MySQL's UUID get it? When starting MySQL, try to read the UUID in the DATADIR/AUTO.CNF, if not found, generate a new UUID, and save to the auto.cnf file. MySQL5.6 version, copy, master and slave until the corresponding UUID, can be seen through show SLAVE hosts.

The TID (transaction_id) is the number of transactions that have been committed, incremented with the commit of the transaction.

Gtid effect

• You can locate the instance on which the transaction was committed.

• Facilitates replication when failover

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6C/DC/wKioL1VUhCnhvALwAABAxMZK1GQ041.jpg "title=" Failover.png "alt=" Wkiol1vuhcnhvalwaabaxmzk1gq041.jpg "/>

If server A fails, you need to switch the business to Server B. At the same time, we need to change the copy source of server C to server B, modify can use change MASTER to master_host= ' xxx ', master_user= ' xxx ', master_user= ' xxx ', master_ password= ' xxx ', master_log_file= ' xxx ', master_log_pos= ' xxx ' can. Because the same transaction has different binlog names and locations on each machine, it is difficult to find the server c What are the Master_log_file and master_log_pos that correspond to the current sync stop point?

However, after Gtid, it becomes very simple, because the gtid of the same transaction is consistent across all nodes, the Gtid on the current stop of server C can uniquely locate Gtid on Server B. Due to the advent of the Master_auto_position function, we do not need the specific value of Gtid, direct use of change MASTER to master_host= ' xxx ', master_user= ' xxx ', master_ Password= ' XXX ', master_auto_position=1; You can complete failover work.

Specific steps

Specific steps

1. Synchronization Time

2. Two hosts initialize MySQL separately

3. Modify the configuration file

Master on MY.CNF

[Mysqld]datadir=/mydata/datasocket =/tmp/mysql.sockinnodb-file-per-table = 1server_id = 1log-bin= master-bingtid-mode=onbinlog-format=rowlog-slave-updates=trueenforce-gtid-consistency= truemaster-info-repository=tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-workers= 2binlog-checksum=crc32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events= 1report-port=3306port=3306report-host=master.mingxiao.infosql_mode=no_engine_substitution,strict_trans_tables

Slave on MY.CNF

[mysqld]log-bin=master-binbinlog-format=rowlog-slave-updates=truegtid-mode=onenforce-gtid-consistency= truemaster-info-repository=tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-workers= 2binlog-checksum=crc32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events= 1server-id=11report-port=3306port=3306log-bin=mysql-bin.logdatadir=/mydata/datasocket=/tmp/ Mysql.sockreport-host=slave.mingxiao.infosql_mode=no_engine_substitution,strict_trans_tables

On Master

MySQL > GRANT REPLICATION SLAVE on * * to ' repluser ' @ ' 192.168.1.6 ' identified by ' xiaoming '; mysql > FLUSH privileges ;

Slave on

MySQL > GRANT MASTER to master_host= ' 192.168.1.5 ', master_user= ' repluser ', master_password= ' xiaoming ', master_auto_ Position=1;mysql > START SLAVE;

Can.

View the information from the server on master.

mysql> SHOW SLAVE hosts;+-----------+---------------------+------+-----------+-------------------------------- ------+| server_id | Host | Port | master_id | Slave_uuid |+-----------+---------------------+------+-----------+------------------------------        --------+| 11 | Slave.mingxiao.info |         3306 | 1 | 20459c95-f484-11e4-82a4-000c29c05a8a |+-----------+---------------------+------+-----------+-------------------- ------------------+1 Row in Set (0.00 sec)

View from server status in slave

mysql> show slave status\g*************************** 1. row ****************                slave_io_ state: waiting for master to send event                   Master_Host: 192.168.1.5                   master_ user: repluser                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: master-bin.000004           read_master_log_pos: 332                relay_log_file: slave-relay-bin.000007                 relay_log_pos: 451        relay_ master_log_file: master-bin.000004              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: 332               Relay_Log_Space: 955               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: 0master_ssl_verify_server_ cert: no                 last_io_errno: 0                 Last_IO_Error:                 last_sql_errno:&nbsP;0               last_sql_error:    Replicate_Ignore_Server_Ids:               Master_Server_Id: 1                   Master_UUID: e168e12e-f5fc-11e4-8c3d-000c29b4e41b              master_info_file: mysql.slave_ master_info                     sql_delay: 0          sql_ Remaining_delay: null      slave_sql_running_state: slave has  read all relay log; waiting for the slave I/O thread  To update it           master_retry_count: 86400                   Master_Bind:        last_io_error_timestamp:      last_sql_error_ timestamp:                 Master_ssl_crl:            master_ssl_crlpath:             Retrieved_Gtid_Set:  e168e12e-f5fc-11e4-8c3d-000c29b4e41b:1-5             Executed_Gtid_Set:                  Auto_Position: 11 row in set  (0.00 SEC)

This article is from the "on the Road" blog, please be sure to keep this source http://mingxiao.blog.51cto.com/8124243/1651379

MySQL-5.6.14 based on Gtid and multi-threaded replication