mysql5.7GTID如何實現(xiàn)主從配置

這篇文章給大家分享的是有關(guān)MySQL 5.7 GTID如何實現(xiàn)主從配置的內(nèi)容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

創(chuàng)新互聯(lián)公司主要從事成都網(wǎng)站設(shè)計、成都網(wǎng)站制作、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)芙蓉,十多年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):028-86922220

binlog-format：二進(jìn)制日志的格式，有row、statement和mixed幾種類型；需要注意的是：當(dāng)設(shè)置隔離級別為READ-COMMITED必須設(shè)置二進(jìn)制日志格式為ROW，現(xiàn)在MySQL官方認(rèn)為STATEMENT這個已經(jīng)不再適合繼續(xù)使用；但mixed類型在默認(rèn)的事務(wù)隔離級別下，可能會導(dǎo)致主從數(shù)據(jù)不一致；

log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于啟動GTID及滿足附屬的其它需求；

master-info-repository和relay-log-info-repository：啟用此兩項，可用于實現(xiàn)在崩潰時保證二進(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)的所有校驗功能；

binlog-rows-query-log-events：啟用之可用于在二進(jìn)制日志記錄事件相關(guān)的信息，可降低故障排除的復(fù)雜度；

log-bin：啟用二進(jìn)制日志，這是保證復(fù)制功能的基本前提；

server-id：同一個復(fù)制拓?fù)渲械乃蟹?wù)器的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服務(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功能已啟動

| 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

在線動態(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如何實現(xiàn)主從配置”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！

當(dāng)前標(biāo)題：mysql5.7GTID如何實現(xiàn)主從配置
當(dāng)前網(wǎng)址：http://muchs.cn/article18/gceddp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、電子商務(wù)、軟件開發(fā)、網(wǎng)站營銷、網(wǎng)站收錄、微信公眾號

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容