技術文章 » windows下實現mysql的主主(主-從)複製

windows下實現mysql的主主(主-從)複製

2020-10-25 14:00:53
  • mysql 伺服器允許將一個伺服器設定為主伺服器，另外設定多個從資料庫伺服器允許將來自一個mysql資料庫伺服器的資料複製到一個或多個mysql資料庫的伺服器上
  • mysql主從是非同步複製的過程
  • 底層是什麼東西在支撐這主從複製？

       答： master 開啟bin-log功能，紀錄檔檔案用於記錄資料庫的讀寫增刪

在主從設定中一共需要三個執行緒：

  • master： IO執行緒： 

主伺服器的上面的任何修改都會通過自己的IO 執行緒儲存在二進位制紀錄檔裡面 bin-log

  • slave 開啟： IO執行緒： 

從伺服器上也會開啟一個IO Thread 通過設定好的使用者名稱和密碼，連線到主伺服器上面請求讀取二進位制紀錄檔，然後把讀取到的內容存在原生的一個Realy log (中繼紀錄檔) 裡面

  • slave開啟：SQL執行緒：

從伺服器上面同時開啟一個sql Theard 定時檢查Realy log 如果有發現有更新立即把更新的內容在本機的資料庫上面執行一遍

三個執行緒的工作原理：

         slave開啟 IO執行緒 和 sql 執行緒 ，slave 負責通過IO執行緒連線master 並且請求某個bin-log position之後的內容，master 伺服器收到slave IO執行緒發來的紀錄檔的請求資訊，io執行緒將bin-log內容和position返回給slave IO執行緒

        slave伺服器收到bin-log紀錄檔內容，將bin-log紀錄檔內容寫入relay-log中繼紀錄檔，建立一個master.info的檔案，該檔案記錄了master ip 使用者名稱 密碼 master bin-log名稱，bin-log position。

         slave端開啟SQL執行緒，實時監控relay-log紀錄檔內容是否有更新，解析檔案中的SQL語句，在slave資料庫中去執行。

上圖：

 

下面我們來看一下如何設定，在設定之前我們要保證mysql是安裝在兩個伺服器上的，

這裡我用的兩臺伺服器分別是

master: 10.55.185.80

slave: 10.55.185.81

第一步：

設定mysql檔案 my.ini 解釋：

設定說明：
#[必須]伺服器唯一ID，每臺伺服器需不同
server-id = 1
#[必須]啟用二進位制檔案
log-bin = /home/mysql/mysql-bin
#[不是必須]二進位制檔案啟用混合模式
binlog_format = mixed
#[不是必須]二進位制檔案過期時間，單位是天
expire-logs-days = 14
#[不是必須]當每進行1次事務提交之後，MySQL將進行一次磁碟同步指令來將binlog_cache中的資料強制寫入磁碟
sync-binlog = 1
#[不是必須]只將對應的資料庫變動寫入二進位制檔案。如果有多個資料庫可用逗號分隔,或者使用多個binlog-do-db選項
binlog-do-db = test,androidpnserver
#[必須]不需要記錄二進位制紀錄檔的資料庫。如果有多個資料庫可用逗號分隔，或者使用多個binlog-do-db選項。一般為了保證主主同步不衝突，會忽略mysql資料庫。
binlog-ignore-db = mysql,information_schema,performance_schema
#[必須]做主主備份的時候，因為每臺資料庫伺服器都可能在同一個表中插入資料，如果表有一個自動增長的主鍵，那麼就會在多伺服器上出現主鍵衝突。
#解決這個問題的辦法就是讓每個資料庫的自增主鍵不連續。上面兩項說的是，假設需要將來可能需要10臺伺服器做備份，將auto-increment-increment設為10。而auto-increment-offset=1表示這臺伺服器的序號。從1開始，不超過auto-increment-increment。
auto-increment-increment = 10
auto-increment-offset = 1

 主伺服器的mysql組態檔內容：

[mysqld]
server-id=1
log-bin = mysql-bin
binlog-do-db = bd-ecxel
binlog-ignore-db = mysql,information_schema,performance_schema
auto-increment-increment = 10
auto-increment-offset = 2

從伺服器的mysql組態檔內容：

[mysqld]
server-id=2
log-bin = mysql-bin
binlog-do-db = bd-ecxel
binlog-ignore-db = mysql,information_schema,performance_schema
auto-increment-increment = 10
auto-increment-offset = 2

注： 這裡的server-id 要確保唯一

第二步：

首先將mysql 重新啟動：

#停止mysql服務
net stop mysql
#開啟mysql服務
net start mysql
進入mysql
mysql -u root -p

注意： 在資料庫主從設定之前要確保主從的資料庫暫時是同步的，可以先把主伺服器的資料庫開鎖，禁止對資料庫做增刪改操作

第三步：

首先重新整理，停止執行緒
mysql>stop slave;

mysql>reset slave;
重新整理mysql的系統許可權相關表
mysql>flush privileges;

 第四步：

在主伺服器上建立同步賬號：（如果做的是主-從複製那麼就只需要在主伺服器上進行建立同步賬號

如果做的是主-主複製，那麼主從都需要建立同步賬號）

grant replication slave on *.* to '<userName>'@'<hostIp>' identified by '<passWord>';`
​
# userName     使用者名稱，預設root
# passWord     使用者密碼（均為資料庫賬戶，密碼）
# hostIp          需要同步的主機IP，可以寫%，表示全部

範例：

grant replication slave on *.* to 'root'@'%' identified by '123456';

 第五步：

在主伺服器上檢視master狀態，記錄二進位制檔名  ，  注意記住 File 和 Position 在從機上面會用到

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

第六步：

設定從庫同步，注意該處的同步點，就是主庫show master status資訊裡的| File| Position兩項

（如果是主-從那麼只需要在從機上進行同步，如果是主主那麼都需要執行這步） 


mysql> CHANGE MASTER TO
    ->     MASTER_HOST='10.55.185.80',
    ->     MASTER_USER='root',
    ->     MASTER_PASSWORD='123456',
    ->     MASTER_LOG_FILE='mysql-bin.000003',
    ->     MASTER_LOG_POS=73;

主機啟動slave 同步執行緒

mysql>stop slave;
mysql>start slave;

主機檢視 slave 狀態：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.55.185.80
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 11662
               Relay_Log_File: mysqld-relay-bin.000022
                Relay_Log_Pos: 11765
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
        ...

這個就是從機的兩個執行緒 如果都為yes那麼就是成功！

補充： 如果在檢視狀態的時候有錯誤，那麼就可以根據錯誤碼來檢測錯誤的原因，

一般錯誤的解決方式有一下兩種：

第一種：忽略錯誤後，繼續同步


stop slave;
#表示跳過一步錯誤，後面的數位可變
set global sql_slave_skip_counter =1;
start slave;
之後再用mysql> show slave status\G 檢視：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok，現在主從同步狀態正常了。

第二種：在組態檔中指定跳過錯誤程式碼，繼續同步

主鍵衝突、表已存在等錯誤程式碼如1062,1032,1060等，可以在mysql主組態檔指定略過此類異常並繼續下條sql同步，這樣也可以避免很多主從同步的異常中斷

[mysqld]

slave-skip-errors = 1062,1032,1060

重新啟動mysql

service mysqld restart

之後再用mysql> show slave status\G 檢視：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes