您好,登錄后才能下訂單哦!
1.什么是GTID?
GTID(Global Transaction ID)是對(duì)于一個(gè)已提交事務(wù)的編號(hào),并且是一個(gè)全局唯一的編號(hào);
GTID實(shí)際上是由UUID+TID組成的。其中UUID是一個(gè)MySQL實(shí)例的唯一標(biāo)識(shí)。TID代表了該實(shí)例上已經(jīng)提交的事務(wù)數(shù)量,并且隨著事務(wù)提交單調(diào)遞增;
#查看本數(shù)據(jù)庫(kù)實(shí)例的uuid號(hào):
root@localhost [(none)]>select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 83373570-fe03-11e6-bb0a-000c29c1b8a9 |
+--------------------------------------+
#也可以通過(guò)系統(tǒng)層面查看uudi:
[root@Darren2 data]# cat /data/mysql/mysql3306/data/auto.cnf
[auto]
server-uuid=83373570-fe03-11e6-bb0a-000c29c1b8a9
#linux中可以通過(guò)uuidgen產(chǎn)生隨機(jī)uuid,mysql中可以通過(guò)select uuid()產(chǎn)生;
如:
[root@Darren2 ~]# uuidgen
eceac2d7-4878-429b-81ca-e6aea02b1739
root@localhost [(none)]>select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| bc959381-1c89-11e7-8786-000c29c1b8a9 |
+--------------------------------------+
GTID的限制
(1)不支持非事物引擎
(2)不支持create table ... select語(yǔ)句復(fù)制(主庫(kù)直接報(bào)錯(cuò))
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
(3)不支持一個(gè)sql同時(shí)更新一個(gè)事物引擎和非事物引擎的表;
(4)在一個(gè)復(fù)制組中,必須要求統(tǒng)一開(kāi)啟GTID或是關(guān)閉GTID;
(5)開(kāi)啟GTID需要重啟(5.7支持在線切換);
(6)開(kāi)啟GTID后就不再使用原來(lái)的傳統(tǒng)的復(fù)制方式;
(7)對(duì)于create temporary table和drop temporary talbe語(yǔ)句不支持(不報(bào)錯(cuò),但是也不存在表);
(8)不支持sql_slave_skip_counter;
2.環(huán)境配置
master | slave | |
數(shù)據(jù)庫(kù)版本 | 5.7.16 | 5.7.16 |
IP | 192.168.91.18 | 192.168.91.20 |
serverid | 330618 | 330620 |
端口號(hào) | 3306 | 3306 |
3.配置文件參數(shù)設(shè)置
(1)master:
配置文件中設(shè)置:
server-id = 330618
binlog_format = row
log-bin = /data/mysql3306/logs/mysql-bin
#GTID
gtid_mode=on
enforce-gtid-consistency=on
(2)slave:
配置文件中設(shè)置:
server-id = 330620
binlog_format = row
relay-log=relay-bin
relay-log-index=relay-bin.index
read_only = on
#復(fù)制進(jìn)程就不會(huì)隨著數(shù)據(jù)庫(kù)的啟動(dòng)而啟動(dòng)
skip_slave_start=1
#如果這個(gè)從庫(kù)還有從庫(kù),需要開(kāi)啟這個(gè)參數(shù)
log_slave_updates=0
#GTID
gtid_mode=on
enforce-gtid-consistency=on
4.主庫(kù)創(chuàng)建用戶
master:
創(chuàng)建rep用戶:
create user rep@'192.168.91.%' identified by '147258';
grant replication slave on *.* to rep@'192.168.91.%';
flush privileges;
5.備份還原初始化
(1)主庫(kù)備份數(shù)據(jù)庫(kù):
mysqldump -uroot -p147258 --single-transaction --master-data=2 -A > /tmp/master.sql
scp master.sql root@192.168.91.20:/tmp/
(2)把備份文件maser.sql,還原到slave:
mysql -uroot -p147258 < master.sql
#注意:
備份文件中有這么一條命令:
SET @@GLOBAL.GTID_PURGED='83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10908';
在還原的時(shí)候使用,表示從庫(kù)還原之后GTID就會(huì)達(dá)到10908,在10908之前的事物不需要同步過(guò)來(lái),從10909開(kāi)始同步事物;
6.從庫(kù) master to
(1)添加主庫(kù)信息到從庫(kù)slave:
change master to
master_host='192.168.91.18',
master_port=3306,
master_user='rep',
master_password='147258',
master_auto_position=1;
(2)啟動(dòng)從庫(kù)
root@localhost [(none)]>start slave;
(3)查看主庫(kù)信息
root@localhost [testdb]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000006 | 1120 | | | f4b6894e-c7fd-11e6-aaf8-000c29aacb77:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
(4)查看從庫(kù)信息
root@localhost [(none)]>show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 330621 | | 3306 | 330618 | 5af344c7-c861-11e6-ad80-000c290f28e2 |
| 330620 | | 3306 | 330618 | 31ba9bcb-c861-11e6-ad7f-000c29cc71ad |
+-----------+------+------+-----------+--------------------------------------+
(5)查看復(fù)制狀態(tài)
slave:
root@localhost [testdb]>show slave status\G
7.測(cè)試
master:
root@localhost [(none)]>use testdb;
root@localhost [testdb]>create table t1(id int,name char(10));
root@localhost [testdb]>insert into t1 values(1,'aaa'),(2,'bbb');
slave:
root@localhost [testdb]>select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
+------+------+
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。