mysql6_GTID_in_a_nutshell.pdf
《mysql6_GTID_in_a_nutshell.pdf》由会员分享,可在线阅读,更多相关《mysql6_GTID_in_a_nutshell.pdf(43页珍藏版)》请在三一文库上搜索。
1、MySQL 5.6 GTID in a nutshell Miguel ngel Nieto Percona Live University - Toronto Who am I? Miguel ngel I live in the north of Spain Support Engineer at Percona Hobbies: Scuba Diving Videogames American TV Series Beers Agenda Im going to answer the following questions and give a detailed overview t
2、hat will let us to start working with it: What is GTID? What problems GTID solves? How can I implement it? How can I repair it? How can I use it for HA and Failover? Take in account. What is GTID? 8182213e-7c1e-11e2-a6e2-080027635ef5:1 What is GTID? What is GTID? 8182213e-7c1e-11e2-a6e2-080027635
3、ef5:1 Not impressed And that thing is a GTID? What is GTID? 8182213e-7c1e-11e2-a6e2-080027635ef5:1 SID. This is the servers 128 bit identification number (SERVER_UUID). It identifies where the transaction was originated. Every server has its own SERVER_UUID. What is GTID? 8182213e-7c1e-11e2-a6e2-0
4、80027635ef5:1 GNO. This is the transaction identification number. It is a sequence number that increments with every new transaction. What is GTID? This is how we can see the GTID inside the binary logs: The GTID is replicated to Slave servers. # at 300 #130221 13:08:58 server id 101 end_log_pos 34
5、8 CRC32 0xc18cdbda GTID commit=yes SET SESSION.GTID_NEXT= 8182213e-7c1e-11e2-a6e2-080027635ef5:2/*!*/; # at 348 BEGIN insert into t values(1) COMMIT/*!*/; # at 565 #130221 13:09:03 server id 101 end_log_pos 613 CRC32 0x5b25189e GTID commit=yes SET SESSION.GTID_NEXT= 8182213e-7c1e-11e2-a6e2-080027635
6、ef5:3/*!*/; # at 697 BEGIN insert into t values(100) COMMIT/*!*/; What problems GTID solves? bin-log.000407 10983 bin-log.000010 4 bin-log.001021 1098 bin-log.000133 984 bin-log.00333 19833 INSERT INTO t VALUES(100); MASTER1 SLAVE 1 SLAVE 2 What problems GTID solves? bin-log.000419 2083 bin-log.00
7、0032 1033 bin-log.001021 1098 bin-log.000201 388 bin-log.00333 19833 CHANGE MASTER TO MASTER_LOG_FILE=? MASTER_LOG_POS=? MASTER1 SLAVE 1 SLAVE 2 What problems GTID solves? uuid1:383uuid1:383 uuid1:383uuid1:383 uuid1:383 INSERT INTO t VALUES(100); SLAVE 2 SLAVE 1 MASTER1 What problems GTID solves?
8、uuid1:398uuid1:398 uuid1:381uuid1:398 uuid1:381 CHANGE MASTER TO MASTER_AUTO_POSITION = 1 SLAVE 2 SLAVE 1 MASTER1 What problems GTID solves? It is possible to identify a transaction uniquely across the replication servers. Make the automation of failover process much easier. There is no need to do
9、calculations, inspect the binary log and so on. Just MASTER_AUTO_POSITION=1. At application level it is easier to do WRITE/READ split. After a write on the MASTER you have a GTID so just check if that GTID has been executed on the SLAVE that you use for reads. Development of new automation tools isn
10、t a pain now. How can I implement it? Three variables are needed in ALL servers of the replication chain gtid_mode: It can be ON or OFF (not 1 or 0). It enables the GTID on the server. log_bin: Enable binary logs. Mandatory to create a replication environment. log-slave-updates: Slave servers must
11、log the changes that comes from the master in its own binary log. enforce-gtid-consistency: Statements that cant be logged in a transactionally safe manner are denied by the server. How can I implement it? enforce-gtid-consistency -CREATE TABLE . SELECT statements. -CREATE TEMPORARY TABLE inside tr
12、ansactions. -Transactions that mixes updates on transactional and non-transactional tables. ERROR 1787 (HY000): When ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. ERRO
13、R 1785 (HY000): When ENFORCE_GTID_CONSISTENCY = 1, updates to non- transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables. ERROR 1786 (HY000): CREATE TABLE . SELECT is forbidden whe
14、n ENFORCE_GTID_CONSISTENCY = 1. How can I implement it? MASTER 1 SLAVE 1SLAVE 2 New replication from scratch How can I implement it? 1) Create replication user on the master server. 2) Configure the parameters on all three servers: - gtid_mode - log_bin - log-slave-updates - enforce-gtid-consisten
15、cy - server_id 3) Start all mysql services. 4) CHANGE MASTER TO. with MASTER_AUTO_POSITION=1 on the two slave servers. New replication from scratch How can I implement it? MASTER 1 SLAVE 1SLAVE 2 log-bin=mysql-bin server-id=1 gtid_mode=ON log-slave-updates enforce-gtid-consistency log-bin=mysql-bin
16、 server-id=101 gtid_mode=ON log-slave-updates enforce-gtid-consistency log-bin=mysql-bin server-id=102 gtid_mode=ON log-slave-updates enforce-gtid-consistency How can I implement it? MASTER 1 SLAVE 1SLAVE 2 CHANGE MASTER TO MASTER_HOST=“127.0.0.1“, MASTER_PORT=18675, MASTER_USER=“msandbox“, MASTER_
17、PASSWORD=“msandbox“, MASTER_AUTO_POSITION=1; How can I implement it? 1. Set the master as read_only and wait until the slaves catch up. 2. Stop all servers. 3. Configure the GTID variables in f. 4. Start all the servers: -Master should start in read_only mode. -Slaves should start with skip_slave_s
18、tart. 5. CHANGE MASTER with MASTER_AUTO_POSITION=1 on the slaves. 6. START SLAVE; on slave servers. 7. SET GLOBAL read_only=0; on master server. Move already running replication to GTID How can I implement it? Now we run two transactions on the master: CREATE TABLE t (i INT); INSERT INTO t VALUES(1
19、); This is the status of slaves: slave1 show slave statusG Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 550 Relay_Log_File: mysql_sandbox18676-relay-bin.000002 Relay_Log_Pos: 760 Relay_Master_Log_File: mysql-bin.000001 Master_Server_Id: 1 Master_UUID: 1c9cdcc8-7c33-11e2-a769-080027635ef5 R
20、etrieved_Gtid_Set: 1c9cdcc8-7c33-11e2-a769-080027635ef5:1-2 Executed_Gtid_Set: 1c9cdcc8-7c33-11e2-a769-080027635ef5:1-2 Auto_Position: 1 How can I implement it? Now we have new variables to check: -gtid_executed (ro): shows the transactions that have been executed in this server. 1c9cdcc8-7c33-11e2
21、-a769-080027635ef5:1-3 -gtid_purged (ro): shows the transactions that have been purged from the binary log (purge binary logs to.). 1c9cdcc8-7c33-11e2-a769-080027635ef5:1-2 -gtid_next: the next GTID that will be used. SET SESSION.GTID_NEXT= 8182213e-7c1e-11e2-a6e2-080027635ef5:2/*!*/; How can I rep
22、air it? Even with GTID we have the same problem. MySQL replication can easily fail. The procedure to repair a replication is slightly different from the regular replication based on binary log position. There is a very good blog post written by a very good blogger that explains how to repair it: htt
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mysql6_GTID_in_a_nutshell
链接地址:https://www.31doc.com/p-3330765.html