Master-Master互為主備

1：按照主從配置步驟將MasterB配置成MasterA的從庫(kù)；

2：確保MasterB沒(méi)有寫(xiě)入，通過(guò)show master status命令在MasterB上得到其同步點(diǎn)，再將MasterA配置成MasterB的從庫(kù)。

通常，為了簡(jiǎn)化邏輯，其中一個(gè)Master會(huì)設(shè)置為只讀，正常只通過(guò)另外一個(gè)Master進(jìn)行讀寫(xiě)。 若要兩邊都寫(xiě)，為了避免自增id沖突，一般會(huì)設(shè)置奇偶錯(cuò)開(kāi)，即一臺(tái)的自增ID均為奇數(shù)，另一臺(tái)均為偶數(shù)。

這里假設(shè)你已經(jīng)按照我第一篇文章做了主從。

配置實(shí)例：

1：在Master B(Slave)的配置文件中添加

port = 3307

log_bin = /var/lib/mysql/mysql-binlog

binlog_do_db = testSM

2：在MasterA(Master)的配置文件中添加

prot=3306

master-host = 10.4.5.9

master-user = gechong1

master-password = gechong1

master-port = 3307

master-connect-retry = 5

replicate-do-db = testSM

3：在Master B(Slave)上創(chuàng)建復(fù)制賬號(hào)gechong1

GRANT REPLICATION SLAVE ON *.* TO gechong1@'%' IDENTIFIED BY 'gechong1';

4：兩邊都重啟服務(wù)登陸數(shù)據(jù)庫(kù)

在Master A(Master)上查看

mysql> show master status;

+---------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------------+----------+--------------+------------------+

| mysql-binlog.000002 | 106 | test | |

+---------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Connecting to master

Master_Host: 10.4.5.9

Master_User: gechong1

Master_Port: 3306

Connect_Retry: 5

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: mysqld-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File:

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB: test

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: 0

Relay_Log_Space: 106

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2013

Last_IO_Error: error connecting to master 'gechong1@10.4.5.9:3306' - retry-time: 5 retries: 86400

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

在MasterB(Slave)上查看

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.4.14.168

Master_User: gechong

Master_Port: 3306

Connect_Retry: 5

Master_Log_File: mysql-binlog.000002

Read_Master_Log_Pos: 106

Relay_Log_File: mysqld-relay-bin.000006

Relay_Log_Pos: 254

Relay_Master_Log_File: mysql-binlog.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

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: 106

Relay_Log_Space: 558

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:

1 row in set (0.00 sec)

mysql> show master status;

+---------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------------+----------+--------------+------------------+

| mysql-binlog.000001 | 327 | | |

+---------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

可以看到 Master A：Slave_IO_Running: No

可以參照第一篇文檔，重新配置參數(shù)即可。

《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的mysql master master_MySQL主从架构之Master-Master互为主备的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。