- 論壇徽章:
- 0
|
1、GTID 是多節(jié)點間事務(wù)的唯一標(biāo)記，是使用 UUID+事務(wù)ID ，是在 mysql5.6 里新出現(xiàn)的功能。如需啟用需要參數(shù)配置：
gtid-mode=ON
enforce-gtid-consistency = ON
log-slave-updates = 1
2、mysql 復(fù)制使用gtid 時，在備機設(shè)置如下：
mysql> change master to
-> master_host=host_ip,
-> master_port=host_port,
-> master_user=masterhost_user,
-> master_password=masterhost_password,
-> master_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.04 sec)
備機 不需要指定主庫的具體的 binlog file, binlog pos 位置，使用 master_AUTO_POSITION=1 就可以了。
使用 gtid 搭建備庫時，必須主備庫的 server_id , uuid 不一致。
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306.000001
Read_Master_Log_Pos: 893
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 1103
Relay_Master_Log_File: mysql3306.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
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: 893
Relay_Log_Space: 1276
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 4a5c96c2-bd21-11e5-9ecf-000c295c61b1
Master_Info_File: /u01/mysql/data/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: 4a5c96c2-bd21-11e5-9ecf-000c295c61b1:1-3
Executed_Gtid_Set: 4a5c96c2-bd21-11e5-9ecf-000c295c61b1:1
Auto_Position: 1
3、當(dāng)使用 gtid 同步報錯時，無法正常使用 sql_slave_skip_counter 參數(shù)來跳過，解決方法是：
主庫：
mysql> select * from aaa;
+-----+------+-------+------+
| id | name | score | age |
+-----+------+-------+------+
| 1 | aa | 100 | NULL |
| 2 | b | 22 | NULL |
| 10 | e | 200 | NULL |
| 22 | c | 34 | NULL |
| 100 | eee | 200 | 100 |
+-----+------+-------+------+
5 rows in set (0.00 sec)
mysql> update aaa set name='ffff' where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
備庫：
mysql> select * from aaa;
+-----+------+-------+------+
| id | name | score | age |
+-----+------+-------+------+
| 2 | b | 22 | NULL |
| 10 | e | 200 | NULL |
| 22 | c | 34 | NULL |
| 100 | eee | 200 | 100 |
| 200 | aa | 100 | NULL |
+-----+------+-------+------+
5 rows in set (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306.000001
Read_Master_Log_Pos: 1421
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 1367
Relay_Master_Log_File: mysql3306.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: test
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table test.aaa; Can't find record in 'aaa', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql3306.000001, end_log_pos 1390
Skip_Counter: 0
處理方法：
(1)、設(shè)置參數(shù)sql_slave_skip_counter=1 處理問題
mysql> set global sql_slave_skip_counter=1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
mysql>
可以看出 sql_slave_skip_counter 報錯,無法解決。
(2)、設(shè)置參數(shù)slave_exec_mode='IDEMPOTENT' 處理問題
mysql> show variables like '%exec%';
+-----------------+--------+
| Variable_name | Value |
+-----------------+--------+
| gtid_executed | |
| slave_exec_mode | STRICT |
+-----------------+--------+
2 rows in set (0.00 sec)
mysql> set global slave_exec_mode='IDEMPOTENT';
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306.000001
Read_Master_Log_Pos: 1421
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 448
Relay_Master_Log_File: mysql3306.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
可以看到可以正確解決問題。
(3)、設(shè)置 gtid_next 處理問題
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> set gtid_next='4a5c96c2-bd21-11e5-9ecf-000c295c61b1:6';
Query OK, 0 rows affected (0.00 sec)
mysql> begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set gtid_next="AUTOMATIC";
Query OK, 0 rows affected (0.00 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306.000001
Read_Master_Log_Pos: 1672
Relay_Log_File: mysql-relay-bin.000007
Relay_Log_Pos: 448
Relay_Master_Log_File: mysql3306.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
也可以問題解決!
4、復(fù)制由 GTID 轉(zhuǎn)成普通模式時，只需要執(zhí)行：
mysql> change master to
-> MASTER_AUTO_POSITION=0;
然后使用正常的方法設(shè)置同步即可。 |
|
免費注冊
發(fā)表于 2016-01-29 22:44