共計(jì) 6223 個(gè)字符，預(yù)計(jì)需要花費(fèi) 16 分鐘才能閱讀完成。
這篇文章給大家分享的是有關(guān) mysql 5.7 GTID 如何實(shí)現(xiàn)主從配置的內(nèi)容。丸趣 TV 小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨丸趣 TV 小編過來看看吧。
binlog-format：二進(jìn)制日志的格式，有 row、statement 和 mixed 幾種類型；需要注意的是：當(dāng)設(shè)置隔離級(jí)別為 READ-COMMITED 必須設(shè)置二進(jìn)制日志格式為 ROW，現(xiàn)在 MySQL 官方認(rèn)為 STATEMENT 這個(gè)已經(jīng)不再適合繼續(xù)使用；但 mixed 類型在默認(rèn)的事務(wù)隔離級(jí)別下，可能會(huì)導(dǎo)致主從數(shù)據(jù)不一致；
log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port 和 report-host：用于啟動(dòng) GTID 及滿足附屬的其它需求；
master-info-repository 和 relay-log-info-repository：啟用此兩項(xiàng)，可用于實(shí)現(xiàn)在崩潰時(shí)保證二進(jìn)制及從服務(wù)器安全的功能；
sync-master-info：啟用之可確保無信息丟失；
slave-paralles-workers：設(shè)定從服務(wù)器的 SQL 線程數(shù)，根據(jù) cpu 核數(shù)設(shè)定；0 表示關(guān)閉多線程復(fù)制功能；
binlog-checksum、master-verify-checksum 和 slave-sql-verify-checksum：啟用復(fù)制有關(guān)的所有校驗(yàn)功能；
binlog-rows-query-log-events：啟用之可用于在二進(jìn)制日志記錄事件相關(guān)的信息，可降低故障排除的復(fù)雜度；
log-bin：啟用二進(jìn)制日志，這是保證復(fù)制功能的基本前提；
server-id：同一個(gè)復(fù)制拓?fù)渲械乃蟹?wù)器的 id 號(hào)必須惟一。
report-host：
The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.
report-port:
The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.
master-info-repository:
The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)
relay-log-info-repository：
This option causes the server to log its relay log info to a file or a table.
log_slave_updates：
Whether updates received by a slave server from a master server should be logged to the slave s own binary log. Binary logging must be enabled on the slave for this variable to have any effect.
master 服務(wù)器配置
編輯 master 的參數(shù)文件
#GTID parameter
gtid-mode=on
enforce-gtid-consistency=true
slave-parallel-workers=10
binlog-checksum=CRC32
binlog-format=ROW
log-slave-updates=true
report-port=3306
report-host=192.168.56.212
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
重啟 master 的 mysql 數(shù)據(jù)庫
[root@ray ~]# /data/3306/mysqld restart
Stoping MySQL…
Warning: Using a password on the command line interface can be insecure.
Starting MySQL…
mysql show global variables like %gtid%
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON | # 說明 gti 功能已啟動(dòng)
| gtid_owned | |
| gtid_purged | |
+————————–+——-+
5 rows in set (0.01 sec)
創(chuàng)建同步用戶
mysql GRANT REPLICATION SLAVE ON *.* TO rep @ % IDENTIFIED BY 123456
Query OK, 0 rows affected (0.78 sec)
mysql flush privileges;
Query OK, 0 rows affected (0.00 sec)
從服務(wù)器 slave 配置
my.cnf 參數(shù)文件配置
#GTID parameter
gtid-mode=on
enforce-gtid-consistency=true
slave-parallel-workers=10
binlog-checksum=CRC32
relay-log = /data/3307/logs/relay-log
relay-log-index = /data/3307/logs/relay-log.index
binlog-format=ROW
log-slave-updates=true
report-port=3307
report-host=192.168.56.212
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
sync_relay_log = 1
sync_relay_log_info = 1
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
relay_log_recovery = ON
重啟 mysql 數(shù)據(jù)庫
[root@ray ~]# /data/3307/mysqld restart
Stoping MySQL…
Warning: Using a password on the command line interface can be insecure.
Starting MySQL…
mysql show global variables like %gtid%
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+————————–+——-+
5 rows in set (0.56 sec)
change master to
master_host= 192.168.56.212 ,
master_user= rep ,
master_password= 123456 ,
master_port=3306,
master_auto_position = 1;
mysql change master to
– master_host= 192.168.56.212 ,
– master_user= rep ,
– master_password= 123456 ,
– master_port=3306,
– master_auto_position = 1;
Query OK, 0 rows affected, 2 warnings (0.59 sec)
mysql start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.212
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ray-bin.000009
Read_Master_Log_Pos: 588
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 797
Relay_Master_Log_File: ray-bin.000009
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: 588
Relay_Log_Space: 1175
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: 1
Master_UUID: 97e8847a-ffdf-11e6-87ed-08002736c224
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 more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 97e8847a-ffdf-11e6-87ed-08002736c224:1-2
Executed_Gtid_Set: 97e8847a-ffdf-11e6-87ed-08002736c224:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
參數(shù)：
master-info-repository=TABLE
relay-log-info-repository=TABLE
把 master.info 和 relay.info 保存在表中，默認(rèn)是 myisam 引擎，官方建議修改為 innodb
mysql use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql alter table slave_master_info engine=innodb;
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql alter table slave_relay_log_info engine=innodb;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql alter table slave_worker_info engine=innodb;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
忽略過濾表：
配置文件，需要重啟
replicate-ignore-table=test.t1
在線動(dòng)態(tài)修改，無需重啟
CHANGE REPLICATION FILETER REPLICATE_DO_DB=(DB1,DB2);
CHANGE REPLICATION FILETER REPLICATE_IGNORE_DB=(DB1,DB2);
CHANGE REPLICATION FILETER REPLICATE_DO_TABLE=(DB1.T1);
CHANGE REPLICATION FILETER REPLICATE_IGNORE_TABLE=(DB1.T1);
CHANGE REPLICATION FILETER REPLICATE_WILD_DO_TABLE=(DB1.T%);
CHANGE REPLICATION FILETER REPLICATE_WILD_IGNORE_TABLE=(DB%.T%);
CHANGE REPLICATION FILETER REPLICATE_REWRITE_DB=(FROM_DB,TO_DB);
感謝各位的閱讀！關(guān)于“mysql 5.7 GTID 如何實(shí)現(xiàn)主從配置”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！
