MySQL 設定 Replication (Master - Slave)

MySQL 設定寫入 Master 後, 自動 Replication 到 Slave 去, 運作基本原理是:

  1. INSERT/UPDATE/DELETE 語法, 自動寫入 Master 的 binlog file.
  2. 由 GRANT REPLICATION 授權的帳號, 自動將 SQL 語法 repl 到 Slave 的 DB 執行.
  3. 因而完成 Replication 的動作.

下述詳細可見: MySQL 5.0 Reference Manual :: 15 Replication

設定 Replication 的操作 (Master)

  1. $ sudo vim /etc/mysql/my.cnf # 下面是 Debian Linux 的設定, 找到下面的設定, 新增/修改 成下面這樣子.

    #bind-address           = 127.0.0.1
    server-id               = 1
    log_bin                 = /var/log/mysql/mysql-bin.log

    # 若是 innodb, 且有用 transaction 的話, 需再加入下面兩行
    innodb_flush_log_at_trx_commit=1
    sync_binlog=1

  2. $ sudo /etc/init.d/mysql restart
  3. $ mysql -u root -p # 進入 mysql
  4. mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl_pass'; # 先假設 帳號 repl, 密碼 repl_pass, 此步驟是 設定 repl 的帳號/密碼, 格式: GRANT REPLICATION SLAVE ON *.* TO
    'repl_user'@'%.mydomain.com' IDENTIFIED BY 'repl_password'; (Replace
    <some_password>with a real password!)
  5. mysql> FLUSH TABLES WITH READ LOCK; # 先讓 DB 不要再寫資料進去
  6. mysql> SHOW MASTER STATUS; # 這邊資料都要記好, 等一下設定 Slave 要用

    +----------------------+------------+------------------+----------------------+
    | File                     | Position   | Binlog_Do_DB | Binlog_Ignore_DB  |
    +----------------------+------------+------------------+----------------------+
    | mysql-bin.000014  |      232   |                      |                          |
    +----------------------+------------+------------------+----------------------+

  7. mysql> quit # 不可以離開此 session, 需要另外開一個 session 來倒資料, 離開此 session 就不會 lock table 了.
  8. 倒資料, 可以由下述的方法倒, 此次採用步驟 1
    1. $ mysqldump -u root -p DB > dbdump.sql
    2. $ mysqldump --all-databases --lock-all-tables >dbdump.sql
    3. $ mysqldump --all-databases --master-data >dbdump.sql # --master-data: 會自動將CHANGE MASTER 的語法帶在裡面
  9. $ mysql -u root -p # 進入 mysql
  10. mysql> UNLOCK TABLES; # dump 完資料後, 進去 mysql 解除唯讀
  11. 再來就是將 dbdump.sql scp 到 Slave 去即可.
  12. Master 就到此為止.

設定 Replication 的操作 (Slave)

  1. $ sudo vim /etc/mysql/my.cnf

    server-id               = 2  # server-id 不能與其它機器相同
    log_bin                 = /var/log/mysql/mysql-bin.log

  2. $ mysql -u root -p # 進入 mysql
  3. mysql> create database DBNAME;
  4. mysql> use DBNAME; source dbdump.sql; 或 $ mysql -u root -p DBNAME < dbdump.sql
  5. mysql> CHANGE MASTER TO
    MASTER_HOST='MASTER_HOSTNAME',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_pass',
    MASTER_LOG_FILE='mysql-bin.000014',
    MASTER_LOG_POS=232; # 這邊就要用到之前 Master 抄下來的值.
  6. mysql> START SLAVE; # 這樣子就會開始 Replication 了, 會將 LOG_POS 之後新的資料開始 sync 回來.
  7. mysql> show master status; # 檢查一下設定
  8. mysql> show slave status; # 檢查一下設定, 看是不是有異常狀況.

測試

  1. 在 master: mysql> create database test2;
  2. 在 slave: mysql> show database; # 應該會看到 test2
  3. 在 master 上的任何操作應該都會馬上 replication 到 slave 去.

其它相關網頁

作者: Tsung

對新奇的事物都很有興趣, 喜歡簡單的東西, 過簡單的生活.

在〈MySQL 設定 Replication (Master - Slave)〉中有 33 則留言

  1. 我有一台主要機房(master/slave),其下有多個中繼點(master/slave)六個
    主要點要和中繼點可以做雙向replicate, 中繼點彼此不能做replicate..
    想請問你有設過一台master/slave中的master ip可以多個IP嗎

    回覆

  2. 有點小問題, 我不太懂您說的中繼點是指什麼.
    然後一台 master/slave 的 master ip 是多個? 指的是一台 Slave 有多台 Master?
    還是一台 Slave 跑多個 MySQL, 然後多個 MySQL 分別是多個 Master 的 Slave?

    回覆

  3. 我是MySQL的新手
    可以請問一下嗎?
    我可以做到A->B，也可以做到B->C
    那可否做到A->B->C

    MySQL的Replication 原理是這樣嗎?
    master新增一筆資料時把動作記錄到 master 的 log 裡
    然後 slave端 的 log 跟著 master 的 log 進行更新
    如果是這樣的話
    slave端的資料經過Replication 增加了資料 ( A->B )
    那 slave 端 的 master 的 log 會有反應嗎? ( B->C )
    或是要如何設定?

    謝謝大大

    回覆

  4. 還有兩個小問題
    我查看我MySQL的log相關參數
    mysql> show variables like '%binlog%';
    出現結果
    +-----------------------------------------+----------------------+
    | Variable_name | Value |
    +-----------------------------------------+----------------------+
    | binlog_cache_size | 32768 |
    | binlog_direct_non_transactional_updates | OFF |
    | binlog_format | MIXED |
    | binlog_stmt_cache_size | 32768 |
    | innodb_locks_unsafe_for_binlog | OFF |
    | max_binlog_cache_size | 18446744073709547520|
    | max_binlog_size | 5242880 |
    | max_binlog_stmt_cache_size | 18446744073709547520 |
    | sync_binlog | 0 |
    +-----------------------------------------+----------------------+

    其中binlog_cache_size ,binlog_stmt_cache_size是什麼意思?
    網路上寫的我還是不太清楚
    它的意思是這樣嗎
    在master端更新數筆資料 → 紀錄於master的log中 → 丟到master對slave的暫存裡(這個暫存是在master端還是slave端= =?) → 丟到slave的log中 → 同步資料
    這個過程不知道有理解錯誤嗎? 請大大不吝指教,感恩~
    然後binlog_cache_size 是"事務性資料"的暫存大小,每32K就丟一個封包這樣嗎?
    而binlog_stmt_cache_size就是啥"非事務性資料"的暫存大小,每32K也是丟封包?
    上述的"事務性||非事務性"我也有點霧煞煞~''~?
    還有innodb_locks_unsafe_for_binlog這有什麼功用...

    回覆

    1. 你問的問題不是很好解釋, 但是我直接跟你說怎麼運作, 你可能比較能理解.

      Master 會在自己的硬碟寫 Binlog, 那 binlog 就是 insert / update.. 有修改動作的行為命令, 然後 Slave 會去 sync binlog 到自己的硬碟, 然後紀錄自己 sync 到哪個 binlog 和哪個位址, 再來 Slave 會就執行 Binlog 內的指令, 達成同步.

      這樣子可以理解嗎? 🙂

    3. 那雙向同步的整體架構是什麼?
      就是語句更新所跑的每一個節點是什麼?

      是這樣嗎?
      master端新增資料，寫進master端的 log，然後slave端的relay_log 複製 master端的log更新，透過log_slave_updates參數把被更新的資料寫進slave的log裡，然後master的relay_log又把slave的log複製回來?

      -------------sync------- log_slave_update-------sync-----------------------
      master's log => slave's relay_log => slave's log => master's relay_log ?

      那 binlog_cache 又是什麼?(binlog_cache_size=32K)
      是master's log 的cache 把語句累積到32K在丟封包到slave端的cache嗎?

    4. 那雙向同步的整體架構是什麼?
      我不曉得你從哪邊看到這個, 但是, 跟你想的不一樣.
      你的想法會造成無窮迴圈, 會一直更新同一個 SQL 語法, 跑不完.
      想要雙向, 就不能夠下 log_slave_updates, 不然就會跑不完.
      但是你後面講得順序大概是對得, 只是要雙向同步, 只要互設 Slave 就可以了, 不要下剛剛說得參數.

      就是語句更新所跑的每一個節點是什麼?
      不懂你講得意思.

      binlog_cache_size 是給 transaction 用得, 跟你想的不一樣唷. 🙂

    5. 喔喔
      大大謝啦(感激~)
      現在我開啟slave一段時間會自動斷掉的問題改善了

      回歸那個問題
      那如果三向同步呢?
      A>B>C>A(環狀)
      那 log-slave-updates 這個參數一定得設定啊
      那會出現之前大大提出的雙向同步的問題嗎(無窮迴圈)?

    7. 那三台同步有可能實現嗎?
      就是A,B,C不管哪台新增資料
      另兩台也會同步

      如果不行的話
      那多台同步理論上也是不能達成的囉?

  5. 抱歉一直打擾大大 有個問題一直很困擾我
    我在檢查雙向同步時，有時會發現它的IO或SQL running會自動變成no
    不曉得是什麼原因，查了slave的ero檔，在16:23:57時不曉得為什麼會跳掉之後就斷了同步，在16:39:58發現之後重新啟動slave才同步回來，這算是網路的問題嗎?(兩邊的server有一段距離...)還是是他暫存不夠大(binlog_cache_size)，有沒有什麼方式可以阻止它自動斷同步的發生?

    master ip 172.17.1.1
    slave ip 172.16.1.1
    (我的資料庫會不斷丟資料進去，我想做的是即時備源)

    slave.ero
    16:08:01 [Note] Start binlog_dump to slave_server(1), pos(mysql-bin.000006, 107)
    16:23:57 [ERROR] Error reading packet from server: Lost connection to MySQL
    server during query ( server_errno=2013)
    16:23:57 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log
    'mysql-bin.000005' at position 4583302
    16:29:45 [ERROR] Error reading packet from server: Lost connection to MySQL
    server during query ( server_errno=2013)
    16:29:45 [Note] Slave I/O thread killed while reading event
    16:29:45 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000006', position
    811370
    16:39:58 [Note] Slave I/O thread: connected to master
    '[email protected]:3306',replication started in log 'mysql-bin.000006'
    at position 811370

    附上master的ero檔
    master.ero
    16:08:23 [Note] Slave I/O thread: connected to master
    '[email protected]:3306',replication started in log 'mysql-bin.000006'
    at position 107
    16:24:02 [ERROR] Error reading packet from server: Lost connection to MySQL
    server during query ( server_errno=2013)
    16:24:02 [Note] Slave I/O thread killed while reading event
    16:24:02 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000007', position
    4104913
    16:24:20 [Note] Start binlog_dump to slave_server(2), pos(mysql-bin.000005,
    4583302)
    16:40:21 [Note] Start binlog_dump to slave_server(2), pos(mysql-bin.000006,
    811370)
    16:40:44 [Note] Error reading relay log event: slave SQL thread was killed
    16:48:00 [Note] Slave SQL thread initialized, starting replication in log 'mysql-
    bin.000007' at position 4104913, relay log '.\CIMV22-relay-bin.000017'
    position: 253

    回覆

    1. Slave I/O thread: Failed reading log event, reconnecting to retry, log
      'mysql-bin.000005' at position 4583302
      你的 Binlog 有問題, 如果 MySQL 能自動修復算是很好的, 不然你可能就得要重新倒資料囉~ 🙂

      中斷一定是網路或者資料有問題, 但是這個我沒辦法幫你解決耶, 得要自己追這兩個問題是哪邊出問題.

    2. 有自動開啟同步的參數可以設嗎?(Orz)
      在mysqld多設這行有用嗎= =?(想說不是自動六十秒一次...所以就沒加了)
      master-connect-retry=10
      或是自動跳過錯誤的參數(不知道這樣會不會遺失掉那個錯誤的log檔所執行的語句?)

  6. 不好意思，因為是資料庫新手，對於資料庫不慎了解，所以想跟您請教一下關於replication的問題，再麻煩大大幫我解惑也，謝謝
    就是現在在工作上遇到一項課題
    需求是要將目前32位元的作業系統換成64位元的作業系統
    其系統環境為
    linux redhat 4.7 32位元
    mysql 4.1

    現在要把作業系統升級成64位元，然後要將資料庫轉移，我想問在轉移過程透過replication這方法，那原本舊有的資料會自動更新至slave主機嗎??

    另外我講一下我現在的想法，再麻煩大哥幫我看看是否有什麼不妥

    1. 設定replication機制 ( master主機為原本的32位元主機，slave為新的64位元主機)
    2. 將資料dump出來，import進slave主機( 這是我想要詢問的，這動作是只要在master
    主機下指令就會自動將原本資料傳至slave主機，還是需要手動自行dump跟import)
    3. 資料同步後，進行停機，將slave主機改設定，改成主要機器，並取消replication機制
    4. 原本master主機(32位元)功成身退，完成資料庫轉移。

    我想請問大哥，
    1.我這樣的想法正確嗎，是否有甚麼不妥的地方，或是還有比較好的做法?
    2.如果想法可行，那是否是照本篇的步驟實作，或是需要進行哪些步驟?
    3.因為只有討論到作業系統升級，沒討論到mysql是否升級，不過如果並不困難，是否
    將資料庫一併升級會比較好呢?

    因為對資料庫不慎了解，所以問題有點多，再麻煩大哥幫我解惑也，感激不盡。

    回覆

    1. 1. 設定replication機制 ( master主機為原本的32位元主機，slave為新的64位元主機) => ok
      2. 將資料dump出來，import進slave主機 => 需要手動自行dump跟import

      你都會需要做 data dump + import, 如果 import 時間不長, 建議你直接換掉就好, 不用在多增加 replication 的步驟, 反而增加複雜度~ 🙂

    2. 謝謝大哥解惑。

      因為資料量很大，大約200多g，如果dump出來就會花一段時間
      import進去也會花一段時間，
      另外怕dump時，一些binary資料會不會出問題
      另外就是想說看有沒有其他方法可以減少停機時間，因為網路上有人提到可以使用replication減少停機時間，所以就研究了一下這方法，還是大哥有其他方法可以減少停機時間，可以給小弟一點建議嗎。

      謝謝，感恩不盡

    3. 這個很難說, 要看檔案大小、資料筆數、資料型態等等.

      用 replication 減少停機時間, 也是可以啦, 但是也是無法避免需要停機的狀況就是了. 🙂

  7. 你好！
    我按照你的步骤来：在我机器上：
    mysql> grant replication slave on *.* to 'root'@'%' identified by '0929';
    Query OK, 0 rows affected (0.01 sec)

    mysql> SHOW MASTER STATUS;
    Empty set (0.00 sec)

    mysql> flush tables with read lock;
    Query OK, 0 rows affected (0.07 sec)

    mysql> SHOW MASTER STATUS;
    Empty set (0.01 sec)

    为什么会是Empty set呢？ 这种情况是哪里没有设置好吗？

    回覆

  8. slave中：
    Last_IO_Errno: 1236
    Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Misconfigured master - server id was not set'

    在master中：
    mysql> show variables like 'server_id';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | server_id | 1 |
    +---------------+-------+
    已经设置了是1的...怎么还说我没有设置呢？
    google了好久，也没有解决，请问这大概是哪里出了问题？
    谢谢！！

    回覆

  9. 請問 FLUSH TABLES WITH READ LOCK; 的問題
    經過我實測 LOCK 只會鎖到該 session 結束為止
    所以下了 quit 之後
    其他的東西還是可以寫入資料耶....
    文章裡面是不是寫錯了?

    回覆

  10. 我來分享一個更好的做法好了
    建議在 mysqldump 的時候同時使用 --master-data
    這樣在那個 sql 檔案裡面就會有
    SHOW MASTER STATUS; 的資訊
    並且，mysqldump 的時候 mysql 會自動 lock 住
    所以用這個就不用擔心資料不一致的問題了

    參考資料：http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_master-data

    回覆
  11. 自動引用通知: [MySQL] Master/Slave Replication 同步設定 | 70's Blog
  12. 自動引用通知: [MySQL] Master / Slave – MySQL Replica 指南 – YIDAS Code

發表迴響

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料。