mysql gtid配置_mysql 5.7 GTID主从配置
binlog-format：二進制日志的格式，有row、statement和mixed幾種類型；需要注意的是：當設置隔離級別為READ-COMMITED必須設置二進制日志格式為ROW，現在MySQL官方認為STATEMENT這個已經不再適合繼續使用；但mixed類型在默認的事務隔離級別下，可能會導致主從數據不一致；
log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于啟動GTID及滿足附屬的其它需求；
master-info-repository和relay-log-info-repository：啟用此兩項，可用于實現在崩潰時保證二進制及從服務器安全的功能；
sync-master-info：啟用之可確保無信息丟失；
slave-paralles-workers：設定從服務器的SQL線程數，根據cpu核數設定；0表示關閉多線程復制功能；
binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：啟用復制有關的所有校驗功能；
binlog-rows-query-log-events：啟用之可用于在二進制日志記錄事件相關的信息，可降低故障排除的復雜度；
log-bin：啟用二進制日志，這是保證復制功能的基本前提；
server-id：同一個復制拓撲中的所有服務器的id號必須惟一。
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服務器配置
編輯master的參數文件
#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數據庫
[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功能已啟動
| gtid_owned???????????????|???????|
| gtid_purged??????????????|???????|
+--------------------------+-------+
5 rows in set (0.01 sec)
創建同步用戶
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)
從服務器slave配置
my.cnf參數文件配置
#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數據庫
[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)
參數：
master-info-repository=TABLE
relay-log-info-repository=TABLE
把master.info 和relay.info 保存在表中，默認是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
在線動態修改，無需重啟
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);
創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎總結
以上是生活随笔為你收集整理的mysql gtid配置_mysql 5.7 GTID主从配置的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： cad导出 dxf后中文不显示_CAD快
- 下一篇： enter sleep mode黑屏怎么