基于GTID的復(fù)制
簡介
基于GTID的復(fù)制是MySQL 5.6后新增的復(fù)制方式.
GTID (global transaction identifier) 即全局事務(wù)ID, 保證了在每個(gè)在主庫上提交的事務(wù)在集群中有一個(gè)唯一的ID.
在原來基于日志的復(fù)制中, 從庫需要告知主庫要從哪個(gè)偏移量進(jìn)行增量同步, 如果指定錯(cuò)誤會(huì)造成數(shù)據(jù)的遺漏, 從而造成數(shù)據(jù)的不一致.
而基于GTID的復(fù)制中, 從庫會(huì)告知主庫已經(jīng)執(zhí)行的事務(wù)的GTID的值, 然后主庫會(huì)將所有未執(zhí)行的事務(wù)的GTID的列表返回給從庫. 并且可以保證同一個(gè)事務(wù)只在指定的從庫執(zhí)行一次.
實(shí)戰(zhàn)
1、在主庫上建立復(fù)制賬戶并授予權(quán)限
基于GTID的復(fù)制會(huì)自動(dòng)地將沒有在從庫執(zhí)行的事務(wù)重放, 所以不要在其他從庫上建立相同的賬號(hào). 如果建立了相同的賬戶, 有可能造成復(fù)制鏈路的錯(cuò)誤.
| mysql> create user 'repl'@'172.%' identified by '123456'; |
注意在生產(chǎn)上的密碼必須依照相關(guān)規(guī)范以達(dá)到一定的密碼強(qiáng)度, 并且規(guī)定在從庫上的特定網(wǎng)段上才能訪問主庫.
| mysql> grant replication slave on *.* to 'repl'@'172.%'; |
查看用戶
| mysql> select user, host from mysql.user;+-----------+-----------+| user | host |+-----------+-----------+| prontera | % || root | % || mysql.sys | localhost || root | localhost |+-----------+-----------+4 rows in set (0.00 sec) |
查看授權(quán)
| mysql> show grants for repl@'172.%';+--------------------------------------------------+| Grants for repl@172.% |+--------------------------------------------------+| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.%' |+--------------------------------------------------+1 row in set (0.00 sec) |
2、配置主庫服務(wù)器
| [mysqld]log_bin = /var/log/mysql/mysql-binlog_bin_index = /var/log/mysql/mysql-bin.indexbinlog_format = rowserver_id = 101gtid_mode = ONenforce_gtid_consistency = ON#log_slave_updates = ON |
NOTE: 把日志與數(shù)據(jù)分開是個(gè)好習(xí)慣, 最好能放到不同的數(shù)據(jù)分區(qū)
enforce_gtid_consistency 強(qiáng)制GTID一致性, 啟用后以下命令無法再使用
create table ... select ...
| mysql> create table dept select * from departments;ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT. |
因?yàn)閷?shí)際上是兩個(gè)獨(dú)立事件, 所以只能將其拆分先建立表, 然后再把數(shù)據(jù)插入到表中
create temporary table
事務(wù)內(nèi)部不能創(chuàng)建臨時(shí)表
| mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> create temporary table dept(id int);ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions. |
