天天看點
傳回

基于bin-log&position搭建主從架構MySQL一般我們可以像下面這樣，在my.cnf中的[MySQLd]的啟動參數中添加如下内容通過如下語句檢視目前MySQL配置的變量通過如下指令可以檢視到出現的errorno常見的errorno停掉slave進入master停止master的寫操作檢視master中目前bin-log和position切換回slave從新根據最新的position和bin-log進行同步進入master，開啟master的寫操作

基于bin-log&position搭建主從架構MySQL

目錄

一、MySQL主從搭建

二、主庫

2.1、确定主庫的binlog是否開啟

2.2、騷氣的指令

2.3、記錄主庫的master狀态

三、從庫

3.1、從庫和主庫保持同步

3.2、開啟主從同步

3.3、從庫：如何斷開主從

3.4、主庫：如何斷開主從

四、中斷處理

4.1、Slave_IO_Running異常

4.2、Slave_Sql_Running異常

五、流程

六、可能會遇到的問題

6.1、問題一：

6.2、問題二：

6.3、問題三：

6.4、問題四：

6.5、問題五：

6.6、問題六：

6.7、問題七：

一、MySQL主從搭建#

搭建主從架構的MySQL常用的有兩種實作方式：

基于binlog的fileName + postion模式完成主從同步。

基于gtid完成主從同步搭建。

本篇就介紹如何使用第一種方式完成MySQL主從環境的搭建。

基于fileName和position去實作主從複制，所謂的fileName就是bin-log的name，position指的是slave需要從master的binlog的哪個位置開始同步資料。

這種模式同步資料方式麻煩的地方就是需要我們自己通過如下的指令去查找應該從哪個bin-log的哪個position去開始同步。

二、主庫#

2.1、确定主庫的binlog是否開啟#

指令：show variables like 'bin-log'

原因：了解MySQL中常見的三個日志：

單機MySQL的undolog日志中記錄着如何将現有的資料恢複成被修改前的舊資料。

單機MySQL的redolog. 中記錄事物日志。

主從模式的MySQL通過bin-log日志同步資料。

2.2、騷氣的指令#

Copy

grant replication slave on . to MySQLsync@"127.0.0.1" identified by "MySQLsync123";

這條指令是在幹什麼呢？

捋一下思路：我們做主從同步，在主庫這邊我們其實會單獨建立一個賬号用于實作主從同步。下面的指令其實就會幫我們建立出 username=mysqlsync password=mysqlsync123的賬戶專門使用者主從同步使用。

執行完上面的指令後，執行如下的指令檢視上面的grant執行結果：

select user, host from mysql.user like '%mysqlsync%'

2.3、記錄主庫的master狀态#

注意主庫的檢視主庫目前是第幾個binlog，已經資料的position。

因為一會從庫就是根據這兩個資訊知道自己該從主庫的第幾個binlog的什麼positon開始同步。

三、從庫#

3.1、從庫和主庫保持同步#

從庫執行change語句，和主庫保持同步

CHANGE MASTER TO

MASTER_HOST='10.157.23.158',
MASTER_USER='mysqlsync',
MASTER_PASSWORD='mysqlsync123',
MASTER_PORT=8882,
MASTER_LOG_FILE='mysql-bin.000008',
MASTER_LOG_POS=1013;

MASTER_HOST = '${new_master_ip}',

MASTER_USER = '${user}',

MASTER_PASSWORD = '${password}',

MASTER_PORT = ${new_master_port},

master_auto_position = 1;

MASTER_HOST = '10.157.23.123',

MASTER_USER = 'mysqlsync',

MASTER_PASSWORD = 'mysqlsync123',

MASTER_PORT = 8882,

3.2、開啟主從同步#

start slave

show slave status G

當我們可以看到 io線程和sql線程的狀态都是yes時，說明此刻主從同步已經搭建完成了。

3.3、從庫：如何斷開主從#

stop slave io_thread

stop slave sql_thread

3.4、主庫：如何斷開主從#

把用于進行主從同步的賬号删除就好了

drop user ${user}@${slave_ip}

​

四、中斷處理#

中斷處理部分說的是，一開始我們搭建主從很可能并不是一番風順的，就比如上面的Slave_IO_Running和Slave_SQL_Running很可能處于NO的狀态。下面介紹一下常見的解決方式。

4.1、Slave_IO_Running異常#

Slave_IO_Running：no/connecting

這說明從庫連接配接不上主庫，或者是一直處于正在連接配接的狀态。

可能是主庫沒有對從庫進行授權，如果已經授權了那麼重新開機一下salve。

另一種原因就是master和slave的mysqld相關配置檔案中，配置了相同server_id。

還有可能你在執行change master指令時，輸入的主庫相關的資訊本來就是錯誤的。

4.2、Slave_Sql_Running異常#

Slave_Sql_Running:no

一般這種情況是bin-log中的sql出問題了。

第一種情況：可能我們配置了slave隻能讀，但是卻有寫請求打過來了，導緻slave不能繼續往下執行。

第二種解決思路：讓slave跳過有問題的這個事件，但是還是得把事件的原因查明白，不然不推薦直接跳過這個事件。

stop slave;

set global sql_slave_skip_counter=1;

start slave;

第三種思路：我們提前配置好錯誤号機制，當slave在同步的過程中，碰到我們配置的錯誤号采取自動跳過的機會而不再去預設的終止同步資料。

一般我們可以像下面這樣，在my.cnf中的[MySQLd]的啟動參數中添加如下内容

--slave-skip-errors=1062,1053

--slave-skip-errors=all

--slave-skip-errors=ddl_exist_errors

通過如下語句檢視目前MySQL配置的變量

MySQL> show variables like 'slave_skip%';

通過如下指令可以檢視到出現的errorno

show slave status; # 觀察Last_Errno

常見的errorno

1007：資料庫已存在，建立資料庫失敗

1008：資料庫不存在，删除資料庫失敗

1050：資料表已存在，建立資料表失敗

1051：資料表不存在，删除資料表失敗

1054：字段不存在，或程式檔案跟資料庫有沖突

1060：字段重複，導緻無法插入

1061：重複鍵名

1068：定義了多個主鍵

1094：位置線程ID

1146：資料表缺失，請恢複資料庫

1053：複制過程中主伺服器當機

1062：主鍵沖突 Duplicate entry '%s' for key %d

第四種思路：手動給slave調整fileName和position的位置（如何允許放棄之前的一部分資料，而從目前最新的資料開始同步）

停掉slave

slave stop

進入master

停止master的寫操作

檢視master中目前bin-log和position

show master status；

切換回slave從新根據最新的position和bin-log進行同步

進入master，開啟master的寫操作

五、流程#

通過fileName和position完成定位，從庫會向主庫發送指令，BINLOG_DUMP ，指令中包含有positon和fileName， 主庫擷取到這些資訊之後，指定name到指定position往從庫發送bin-log

六、可能會遇到的問題#

6.1、問題一：#

change master時報錯了

報錯說：ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.

原因是我之前使用過gtid進行同步資料，當時将master_auto_position設定成了1，再想使用手動指定position的主從同步方式需要得像下面這樣，change回去。

MASTER_AUTO_POSITION=0;

6.2、問題二：#

如果我随便寫了個position再搭建主從時，會發生什麼？

下面的 MASTER_LOG_POS = 1003 就是我随便寫的一個position，然後你可以看到兩個現象

Slave_IO_Running : No

Last_IO_Error 位置報了個嚴重的錯誤

mysql> CHANGE MASTER TO

->        MASTER_HOST='10.157.23.xxx',
->         MASTER_USER='mysqlsync',
->        MASTER_PASSWORD='mysqlsync123',
->        MASTER_PORT=8882,
->        MASTER_LOG_FILE='mysql-bin.000008',
->       MASTER_LOG_POS=1003;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave statusG;

1. row **

Slave_IO_State:
              Master_Host: 10.157.23.158
              Master_User: mysqlsync
              Master_Port: 8882
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000008
      Read_Master_Log_Pos: 1003
           Relay_Log_File: relay-log.000002
            Relay_Log_Pos: 320
    Relay_Master_Log_File: mysql-bin.000008
         Slave_IO_Running: No
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table: mysql.%,test.%

Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 1003
          Relay_Log_Space: 521
          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: 1236
            Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'mysql-bin.000008' at 1003, the last event read from './mysql-bin.000008' at 123, the last byte read from './mysql-bin.000008' at 1022.'
           Last_SQL_Errno: 0
           Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2787871625
              Master_UUID: a5f1d6b2-8f9a-11ea-8138-b8599f2ef058
         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: 200529 10:22:46
 Last_SQL_Error_Timestamp:
           Master_SSL_Crl:
       Master_SSL_Crlpath:
       Retrieved_Gtid_Set:
        Executed_Gtid_Set: 00c755a6-7a07-11ea-8701-b8599f2ef058:33-222,

40efcb1b-7a1f-11ea-84ac-b8599f229b38:1-20,

7e2dcb21-7d3b-11ea-aa0c-b8599f2ef058:1-18,

9e6027f2-7ae9-11ea-ac13-b8599f2ef058:1409-7176,

a5f1d6b2-8f9a-11ea-8138-b8599f2ef058:6-9:12-13:15,

e90fdd54-7e04-11ea-8b23-b8599f2ef058:1-11

Auto_Position: 0
     Replicate_Rewrite_DB:
             Channel_Name:
       Master_TLS_Version:

1 row in set (0.00 sec)

6.3、問題三：#

假設我們有這樣的場景：

場景：現在主庫有7條資料，從庫有5條資料，搭建主從時如何讓從庫從第六條開始同步？

這種情況僅僅是我們在做這種小實驗，為啥這樣說呢？如果是為線上的業務搭建搭建主從MySQL的話，大機率我們會清空主庫然後再做同步。如果資料很重要，我們會對主庫中的資料進行一次全量拷貝到從庫（拷貝var包）。再做主從同步。

線上上的環境中，主從的資料是會強一緻的，從庫隻會接受業務方的讀流量，也許網絡環境很惡劣從庫同步的速度明顯比主庫寫入到速度低，但是隻要從庫沒有說跳過了某個binlog而少同步了某條記錄，我們都可以認為它們是正常的主從同步。不會出現主從中斷的情況。

線上的環境中什麼情況下會出現主從中斷呢？比如說，從庫同步資料時，從庫同步binlog時丢了一條資料，這時業務上突然來了條update語句，要更新資料，然後從庫美滋滋的回放在主庫dump過來的binlog時發現，竟然自己沒有需要更新的這條記錄，就會報錯，這時為了業務止損，我們要在第一時間下線從庫，然後去分析哪裡出現問題了。

針對這個實驗我們這樣去binlog中檢視第5，6條資料的position，然後在從庫中使用相應的position完成主從資料的同步。

進入主庫，通過下面的指令檢視binlog

mysqlbinlog --no-defaults -vv --base64-output=decode-rows ../var/mysql-bin.000008 | less

找到了指定的binlog和指定的end_log_pos

比如從庫中沒有第10，11條資料，我們就能通過end_log_pos = postion = 1013完成定位。

MASTER_HOST='10.157.23.158',
MASTER_USER='mysqlsync',
MASTER_PASSWORD='mysqlsync123',
MASTER_PORT=8882,
MASTER_LOG_FILE='mysql-bin.000008',
MASTER_LOG_POS=1013;

開啟同步，并檢視狀态

show slave statusG;

再去檢視從庫就能發現，從你指定的position開始往後和主庫的資料保持同步的。

6.4、問題四：#

問：主從接流量的情況是怎樣的？業務的CRUD請求是如何被主從平分消費的？

答：預設這種架構下是讀寫分離，也就是說，僅讀流量會打到從庫中

問：那如果我們在從庫所在的機器上本地登陸，然後手動執行删除的操作能成功嗎？

答：是的，可以執行成功。

問：我可以簡單粗暴的限制從庫僅讀嗎？

答：可以的，像下面這樣

mysql> show variables like '%read_only%';
Variable_name Value
innodb_read_only OFF
read_only
super_read_only
transaction_read_only
tx_read_only

5 rows in set (0.00 sec)

set global read_only=0; #關閉隻讀,可以讀寫

set global read_only=1; #開始隻讀模式

6.5、問題五：#

假設主庫中有1～7 共7條資料，從庫中有1～5五條資料。也就是說，主庫從庫中前五條資料一樣，但是主庫比從庫多了兩條新資料。

這時我們搭建主從同步時搞一搞事情，重複這個動作：在從庫斷開同步，然後查到主庫第一個binlog中的資料的記錄，确定我們要查找的position，再重新建構主從環境。觀察一下從庫這邊資料的同步情況，以及會出現什麼問題？從庫這邊的資料會成為double嗎？

答：資料不會double的

6.6、問題六：#

假設從庫執行changemaster時，主庫MASTER_HOST填錯了:

在檢視slave 狀态時，我們可以看到Last_IO_Error列有報錯提示： error connecting to master

mysql> CHANGE MASTER TO

->     MASTER_HOST='10.157.23.158',
->     MASTER_USER='mysqlsync',
->     MASTER_PASSWORD='mysqlsync123',
->     MASTER_PORT=8882,
->     MASTER_LOG_FILE='mysql-bin.000008',
->     MASTER_LOG_POS=1013;           
Slave_IO_State: Connecting to master
              Master_Host: 10.157.23.123
              Master_User: mysqlsync
              Master_Port: 8882
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000008
      Read_Master_Log_Pos: 1003
           Relay_Log_File: relay-log.000001
            Relay_Log_Pos: 4
    Relay_Master_Log_File: mysql-bin.000008
         Slave_IO_Running: Connecting
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:           
Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 1003
          Relay_Log_Space: 154
          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           
Last_IO_Errno: 2003
            Last_IO_Error: error connecting to master '[email protected]:8882' - retry-time: 60  retries: 1
           Last_SQL_Errno: 0
           Last_SQL_Error:           
Master_Server_Id: 0
              Master_UUID:
         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: 200529 10:13:34
 Last_SQL_Error_Timestamp:
           Master_SSL_Crl:
       Master_SSL_Crlpath:
       Retrieved_Gtid_Set:
        Executed_Gtid_Set: 00c755a6-7a07-11ea-8701-b8599f2ef058:33-222,           
Auto_Position: 0
     Replicate_Rewrite_DB:
             Channel_Name:
       Master_TLS_Version:

6.7、問題七：#

假設這種場景：假設主從現在的資料是一緻的，然後你在從庫所在的機器上本地登陸，然後手動删除一條，再從主庫寫入資料，那從庫還能同步成功嗎？

答：從庫依然會同步成功，但是其實這時候已經算是事故了，主從資料不一緻，萬一業務打來一條sql剛好使用你删的資料，那就會報錯。

如果覺得對你有幫助歡迎關注我，後面還會分享通過gtid搭建主從mysql以及其他相關的知識點

作者： 賜我白日夢

出處：

https://www.cnblogs.com/ZhuChangwu/p/12990062.html
sql 關系型資料庫 mysql 資料庫 RDS MySQL技術運維使用 RDS MySQL引擎表索引 vsftpd pam mysql建構 使用yum mysql mysql資料關系
上一篇: 服務阿裡 9 個APP|揭秘新奧創更新的品質演變背景新奧創的更新測試的影響結束語招招招招人啦!
下一篇: Unity 離線建造系統

繼續閱讀