MySQL主從複制配置方法

字號+ 編輯: 种花家 修訂: 小红帽 來源: CSDN 2023-09-12 我要說兩句(1)

實際所謂的MySQL負載均衡沒有那麽複雜神秘, 簡簡單單的幾個小竅門, 會用了, 就可以達到目標。爲了更好的發揮MySQL開源精神, 方便廣大學習者學會使用MySQL主從架構方法, 在這裡提供一篇可用的標準文档《高性能MySQL》的複制篇。

筆者2017年參與過北京一家小型p2p公司面試, 那面試官看起來是個外行, 因爲MySQL主從模式, 其實筆者早已經不怎麽用, 線上環境推薦redis和mongodb或者polardb了, 聽到筆者的答語, 面試官劈頭蓋臉訓斥筆者說: 我面過的小孩都很勤奮, 人家在自己搭的虛擬機裡都弄過Mysql主從, 你這種人都嬾得在虛擬機裡學著怎麽搭Mysql主從, 你讓我怎麽用你? 筆者在測試用的雲主機上專門研究過這個流程,但可惜現在很多商用數據庫還是很香的, 哪有時間折騰那些坑呢。當年優信搞出的高可用主從架構, 趟了那麽多毫無意義的坑, 線上玩崩的時候互相甩鍋, 還沒折騰夠嗎? 如果你喜歡自行研究,你可以依照這篇文章的記述搞一搞, 擴充一下知識面, 至少能應付得了那些知識面比較教條型的面試官。如果你是CTO或者創業者, 在真正投入商業線上使用, 且鍾情於Mysql這類數據庫的話, 建議你選用國産的TiDB, 或者阿裡的PolarDB耍起來。自行組建數據庫讀寫集群的話,建議嘗試PostgreSQL。

接下來進入正題。

複制配置

有兩台MySQL數據庫服務器Master和slave,Master爲主服務器,slave爲從服務器,初始狀態時,Master和slave中的數據信息相同,當Master中的數據發生變化時,slave也跟著發生相應的變化,使得master和slave的數據信息同步,達到備份的目的。

要點:

負責在主、從服務器傳輸各種修改動作的媒介是主服務器的二進制變更日志,這個日志記載著需要傳輸給從服務器的各種修改動作。因此,主服務器必須激活二進制日志功能。從服務器必須具備足以讓它連接主服務器並請求主服務器把二進制變更日志傳輸給它的權限。

環境:

Master和slave的MySQL數據庫版本同爲5.0.18

操作系統:unbuntu 11.10

IP地址:10.100.0.100

1、創建複制帳號

1、在Master的數據庫中建立一個備份帳戶:每個slave使用標準的MySQL用戶名和密碼連接master。進行複制操作的用戶會授予REPLICATION SLAVE權限。用戶名的密碼都會存儲在文本文档master.info中

命令如下:

mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* 
TO backup@’10.100.0.200’ 
IDENTIFIED BY ‘1234’;

建立一個帳戶backup,並且只能允許從10.100.0.200這個地址上來登陸,密碼是1234。

如果因爲mysql版本新舊密碼算法不同,可以設置:

set password for 'backup'@'10.100.0.200'=old_password('1234')

2、拷貝數據

(假如是你完全新安裝mysql主從服務器,這個一步就不需要。因爲新安裝的master和slave有相同的數據)

關停Master服務器,將Master中的數據拷貝到B服務器中,使得Master和slave中的數據同步,並且確保在全部設置操作結束前,禁止在Master和slave服務器中進行寫操作,使得兩數據庫中的數據一定要相同!

3、配置master

接下來對master進行配置,包括打開二進制日志,指定唯一的servr ID。例如,在配置文档加入如下值:

server-id=1
log-bin=mysql-bin
server-id:爲主服務器A的ID值
log-bin:二進制變更日值

重啓master,運行SHOW MASTER STATUS,輸出如下:

4、配置slave

Slave的配置與master類似,你同樣需要重啓slave的MySQL。如下:

log_bin       = mysql-bin
server_id      = 2
relay_log      = mysql-relay-bin
log_slave_updates = 1
read_only      = 1

server_id是必須的,而且唯一。slave沒有必要開啓二進制日志,但是在一些情況下,必須設置,例如,如果slave爲其它slave的master,必須設置bin_log。在這裡,我們開啓了二進制日志,而且顯示的命名(默認名稱爲hostname,但是,如果hostname改變則會出現問題)。

relay_log配置中繼日志,log_slave_updates表示slave將複制事件寫進自己的二進制日志(後面會看到它的用處)。

有些人開啓了slave的二進制日志,卻沒有設置log_slave_updates,然後查看slave的數據是否改變,這是一種錯誤的配置。所以,盡量使用read_only,它防止改變數據(除了特殊的線程)。但是,read_only並是很實用,特別是那些需要在slave上創建表的應用。

5、啓動slave

接下來就是讓slave連接master,並開始重做master二進制日志中的事件。你不應該用配置文档進行該操作,而應該使用CHANGE MASTER TO語句,該語句可以完全取代對配置文档的修改,而且它可以爲slave指定不同的master,而不需要停止服務器。如下:

mysql> CHANGE MASTER TO MASTER_HOST='server1',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='p4ssword',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=0;

MASTER_LOG_POS的值爲0,因爲它是日志的開始位置。

你可以用SHOW SLAVE STATUS語句查看slave的設置是否正確:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: server1
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 4
             Relay_Log_File: mysql-relay-bin.000001
              Relay_Log_Pos: 4
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: No
          Slave_SQL_Running: No
                             ...omitted...
      Seconds_Behind_Master: NULL

Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No

表明slave還沒有開始複制過程。日志的位置爲4而不是0,這是因爲0只是日志文档的開始位置,並不是日志位置。實際上,MySQL知道的第一個事件的位置是4。

爲了開始複制,你可以運行:

mysql> START SLAVE;

運行SHOW SLAVE STATUS查看輸出結果:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: server1
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 164
             Relay_Log_File: mysql-relay-bin.000001
              Relay_Log_Pos: 164
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
                             ...omitted...
      Seconds_Behind_Master: 0

在這裡主要是看:

Slave_IO_Running=Yes
Slave_SQL_Running=Yes

slave的I/O和SQL線程都已經開始運行,而且Seconds_Behind_Master不再是NULL。日志的位置增加了,意味著一些事件被獲取並執行了。如果你在master上進行修改,你可以在slave上看到各種日志文档的位置的變化,同樣,你也可以看到數據庫中數據的變化。

你可查看master和slave上線程的狀態。在master上,你可以看到slave的I/O線程創建的連接:

在master上輸入show processlist\G;

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost:2096
     db: test
Command: Query
   Time: 0
 State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 2
   User: repl
   Host: localhost:2144
     db: NULL
Command: Binlog Dump
   Time: 1838
 State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
2 rows in set (0.00 sec)

行2爲處理slave的I/O線程的連接。

在slave服務器上運行該語句:

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 2291
 State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1852
 State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 3. row ***************************
     Id: 5
   User: root
   Host: localhost:2152
     db: test
Command: Query
   Time: 0
 State: NULL
   Info: show processlist
3 rows in set (0.00 sec)

行1爲I/O線程狀態,行2爲SQL線程狀態。

閲完此文,您的感想如何?
  • 有用

    2

  • 沒用

    1

  • 開心

    1

  • 憤怒

    1

  • 可憐

    1

1.如文章侵犯了您的版權,請發郵件通知本站,該文章將在24小時内刪除;
2.本站標注原創的文章,轉發時煩請注明來源;
3.交流群: 2702237 13835667

相關課文
  • [InnoDB] Failed to set NUMA memory policy of buffer pool page frames

  • 讓Mysql查詢後直接返回json字符串的方法

  • postgresql的pg_dump備份簡單方法

  • 使用sql語句在MySQL庫中去掉字段左邊、右邊指定字符串

我要說說
網上賓友點評
1 樓 IP 61.149.***.101 的嘉賓 说道 : 很久前
流量那么大都涉及到主从了干脆分库或者上mongodb吧。非常的影响开发体验。看着这些配置心好累。