技術分享 | MySQL 主從複製中創建複製用戶的時機探討

2022-01-31 愛可生開源社區
背景

該問題來自某客戶，據描述，他們在部署 MySQL 主從複製時，有時候僅在主庫上創建複製用戶，有時候主從實例上都會去分別創建複製用戶，發現這兩種方式都可以成功建立複製。針對這一現象，進行了一輪驗證，來觀察採用不同方式創建複製用戶對主從複製的影響。

通常來說，用得較多的方式是在搭建主從複製前，先在主庫創建好複製用戶，然後做一個 Xtrabackup 物理全備，再拿到從庫上恢復並搭建主從。除此以外，還有哪些方式呢？分別對主從複製有哪些影響？一起來看一下。


驗證

MySQL 版本為 5.7.32，主庫：10.186.60.62，從庫：10.186.60.68。

為了演示方便，本次搭建主從複製時均採用 mysqldump 進行邏輯備份。

場景 1：僅在主庫創建複製用戶

1. 主庫做一個備份並拷貝到從庫

/usr/local/mysql5732/bin/mysqldump --single-transaction --master-data=2 -B zlm -S /tmp/mysql3332.sock -p > zlm.sql
scp zlm.sql root@10.186.60.68:~
2. 登陸從庫執行導入
mysql> source zlm.sql
由於沒有使用參數 --set-gtid-purged=off，導出的語句中會帶有 SET @@GLOBAL.GTID_PURGED='xxxx:1-xx' 並執行，導入前需要先在從庫上執行 reset master。
3. 主庫創建複製用戶
mysql> create user repl1 identified by 'repl1';
4. 從庫配置主從複製並啟動
mysql> change master to master_host='10.186.60.62',master_port=3332,master_user='repl1',master_password='repl1',master_auto_position=1;
mysql> start slave;
啟動複製後，報了 Error 1045 的錯誤，此處並不是密碼錯，而是沒有給複製用戶配置 replication slave 權限，在主庫上對 repl1 用戶執行賦權後（grant replication slave on *.* to repl1;），再啟動複製就正常了。
主從複製正常以後，也會在從庫上創建複製用戶 repl1。
從庫上並沒有創建過複製用戶 repl1，主從複製就正常搭建好了，為什麼呢？因為 change master to 語句中指定的 master_user 是主庫上的複製用戶，從庫通過這個用戶連接到主庫進行同步，當開啟複製線程後，主庫上創建複製用戶的語句會在從庫上進行回放，於是從庫上也會有這個複製用戶了。
結論 1搭建主從複製時，在從庫創建複製用戶不是必須的，僅在主庫創建即可，複製用戶會同步到從庫。場景 2：主從庫單獨創建複製用戶（create 語句）
1. 主庫做一個備份並拷貝到從庫（gtid_purged=xxxx:1-23）
2. 從庫執行導入
3. 主庫創建複製用戶並賦權
mysql> create user repl2 identified by 'repl2';
mysql> grant replication slave on *.* to repl2;
4. 從庫創建複製用戶
由於不想在從庫上產生由從庫 uuid 寫入的 binlog 事務，此處設置了 sql_log_bin=0，使事務不被記錄到 binlog 中，原因是在資料庫管理平臺對高可用集群進行管理時，通常是不允許從庫上有主庫不存在的 GTID 事務的。
5. 從庫配置主從複製並啟動
mysql> change master to master_host='10.186.60.62',master_port=3332,master_user='repl2',master_password='repl2',master_auto_position=1;
mysql> start slave;
由於從庫上已經創建了複製用戶，當回放到主庫的這個事務時會報 Error 1396 的錯誤。
可以用 create user 語句創建一個重複用戶來驗證。
解析主庫 binlog，啟動複製後執行的第一個事務就是這個 24 的創建用戶語句。
結論 2在從庫導入備份後並分別在主、從庫單獨創建複製用戶後，當從庫執行到創建用戶的事務時會導致複製中斷。場景 3：主從庫單獨創建複製用戶（grant 語句）
1. 主庫做一個備份並拷貝到從庫（gtid_purged=xxxx:1-28）
2. 從庫執行導入
3. 主庫創建複製用戶
mysql> grant replication slave on *.* repl3 identified by 'repl3';
4. 從庫創建複製用戶
5. 從庫配置主從複製並啟動
mysql> change master to master_host='10.186.60.62',master_port=3332,master_user='repl2',master_password='repl2',master_auto_position=1;
mysql> start slave;
這次啟動複製後並沒有報錯。為何用 grant 語句創建用戶就可以，用 create 語句就不行呢？
create 與 grant 語句都會產生事務並記錄到 binlog 中，但區別是 grant 語句是一個近似冪等的操作，而 create 語句不是。
解析主庫 binlog，29 和 30 都是重複執行 grant 的事務。
觀察 show slave stauts\G，從庫上也把 29，30 這兩個事務都回放掉了，重複執行它們並不影響主從複製。
但要注意的是，在 MySQL 8.0 中已經禁止通過 grant 這種語法來創建用戶了。
結論 3從庫導入備份並在主從庫分別使用 grant 語句創建用戶後，在從庫回放時不會導致複製中斷。
總結
1. 根據以上驗證結果得知，在搭建主從複製時，採用多種方式創建複製用戶都是可行的，但有些方式存在一些限制，如：在主、從實例上分別創建複製用戶。雖然執行 grant 語句創建用戶不會導致複製中斷，但其並不是標準的 MySQL 創建用戶語法，在 MySQL 8.0 中已被視為語法錯誤，因此不推薦採用這樣的方式來搭建主從。
2. 創建複製用戶的方式
Create 語句創建用戶時
1. 主庫創建完複製用戶後做備份，再配置主從
2. 備份後僅在主庫創建複製用戶，再配置主從（推薦）
3. 如果要在主、從庫分別創建複製用戶，應先設置 session 級別的 sql_log_bin=0，再配置主從
Grant 語句創建用戶時（MySQL 5.7 及以下版本）
1. 主庫先創建複製用戶後備份，再配置主從
2. 僅在主庫創建複製用戶，再配置主從（推薦）
3. 主、從庫分別創建複製用戶，再配置主從

相關焦點

  • 【乾貨】-MySQL主從複製(Master-Slave)實踐
    如果配置了多個從伺服器或者多個主伺服器又涉及到相應的負載均衡問題，關於負載均衡具體的技術細節還沒有研究過，今天就先簡單的實現一主一從的主從複製功能。Mysql主從複製的實現原理圖大致如下(來源網絡)：MySQL之間數據複製的基礎是二進位日誌文件（binary log file）。
  • Mysql(Mariadb)資料庫主從複製
    Mysql主從複製的實現原理圖大致如下：MySQL之間數據複製的基礎是以二進位日誌文件（binary log file）來實現的，一臺MySQL資料庫一旦啟用二進位日誌後，其作為master，它資料庫中所有操作都會以「事件」的方式記錄在二進位日誌中，其他資料庫作為slave通過一個I/O線程與主伺服器保持通信，並監控
  • 使用mysqlreplicate命令快速搭建 Mysql 主從複製
    MySQL的Replication（英文為複製）是一個多MySQL資料庫做主從同步的方案，特點是異步複製，廣泛用在各種對
  • MySQL基礎應用-主從複製實現
    本文描述了MySQL主從複製的兩種情形:1.新建MySQL主從複製集群。2.已經運行了一段時間的MySQL且有大量數據的時候新增從節點。網上很多文章參差不齊，本文提供了一定意義上的規範部署操作。文末提供了MySQL的一鍵部署腳本以及MySQL5.7的常用優化參數。
  • 基於 Docker 搭建 MySQL 主從複製
    本篇博文相對簡單，因為是初次使用Docker，MySQL的主從複製之前也在Centos環境下搭建過，但是也忘的也差不多了，因此本次嘗試在Docker中搭建。根據網上教程走還是踩了一些坑，不過所幸最終搭建成功，因此記錄下來，避免以後踩了重複的坑。
  • 【mycat系列一】基於 Docker 搭建 MySQL 主從複製的詳細教程
    前言：主從複製原理mysql-slave1:/etc/mysql/my.cnf別忘記，重啟slave1容器，使配置生效docker restart mysql-slave12、配置同步複製帳號下一步在Master資料庫創建數據同步用戶，授予用戶 slave REPLICATION SLAVE權限和REPLICATION CLIENT權限，用於在主從庫之間同步數據
  • MySQL主從複製一致性檢驗和同步修復
    - 跳過了複製事件 (SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N)- 使用臨時表(Temporary Tables)- 複製過濾(Replication Filters)- 使用含LIMIT且沒有order by的更新語句(update/delete with LIMIT clause without order
  • 主從資料庫複製+Springboot項目中配置主從資料庫讀寫分離
    1.2 主從複製的原理：master將資料庫的改變記錄在二進位日誌中，salve同步這些二進位日誌，並根據日誌執行操作。1.4 MS模式主從資料庫的配置：開始複製之前，如果主資料庫已有內容，應先將主資料庫當前的內容同步到從資料庫中。可以新建一個用戶專門用於複製，將複製權限Replication Slave賦給該用戶。
  • 【Mysql源碼分析】基於行的複製實現之「主從關係建立」
    -u root啟動master後，需要創建一個用戶，用於主從同步：mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'localhost' IDENTIFIED BY '123456';查看主服務狀態
  • 技術分享 | MySQL 8.0 常見問題——複製篇
    從伺服器與主伺服器的連接可以斷開，當重新連接主伺服器，從伺服器會追趕主伺服器上的更新。主從複製依賴於伺服器上面的二進位日誌,當從伺服器能夠從最後讀取事件的位置繼續讀取二進位日誌時複製才能工作。因此，必須保證主伺服器中尚未複製到從伺服器二進位日誌文件沒被刪除，才可以使斷開的從伺服器重新連接主伺服器繼續進行複製。
  • 高性能Mysql主從架構的複製原理及配置詳解
    當一個從伺服器連接主伺服器時，它通知主伺服器從伺服器在日誌中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新，然後封鎖並等待主伺服器通知新的更新。請注意當你進行複製時，所有對複製中的表的更新必須在主伺服器上進行。否則，你必須要小心，以避免用戶對主伺服器上的表進行的更新與對從伺服器上的表所進行的更新之間的衝突。
  • 使用克隆插件搭建主從複製與組複製拓撲
    中提供了用於監控克隆操作的一些性能事件採集器PS：在組複製拓撲中使用遠程克隆操作時，為便於與非組複製拓撲做區分，我們這裡也可以將"recipient"（即，接收方）稱為"joiner"（即，加入方，表示將要加入組複製拓撲的MySQL Server）CLONE LOCAL DATA DIRECTORY [=] 'clone_dir';
  • MySQL複製技術對比與容器化探究
    半同步複製是建立在基本的主從複製基礎上，利用插件完成半同步複製，傳統的主從複製，不管從庫是否正確獲取到二進位日誌，主庫不斷更新，半同步複製則當確認了從庫把二進位日誌寫入中繼日誌才會允許提交，如果從庫遲遲不返回ack,主庫會自動將半同步複製狀態取消，進入最基本的主從複製模式。
  • 使用 Ansible 管理 MySQL 複製
    筆者在這裡演示如果通過 Ansible 其中的一個模塊「mysql_replication」來管理 MySQL 的複製。首先在配置好 MySQL master 伺服器與 slave 伺服器的 MySQL 環境，使其滿足 MySQL 複製的配置需求，並且 MySQL 主從伺服器都要安裝 Python 的 MySQLdb 模塊，通過 Ansible 在主、從伺服器上安裝 MySQLdb。
  • MySQL 8.0 異步複製的三種方式
    1 row affected (0.00 sec)2.在主庫創建複製用戶mysql> create user 'repl'@'%' identified with mysql_native_password by 'wwwwww';Query OK, 0 rows affected (0.03 sec)mysql>
  • MySQL 主從複製模式太難部署,我用 Docker 五分鐘就搞定它!
    1概述master 伺服器將數據的改變記錄二進位 binlog 日誌，當 master 上的數據發生改變時，則將其改變寫入二進位日誌中；slave 伺服器會在一定時間間隔內對 master 二進位日誌進行探測其是否發生改變，如果發生改變，則開始一個 I/O Thread 請求 master 二進位事件同時主節點為每個 I/O 線程啟動一個dump線程，用於向其發送二進位事件，並保存至從節點本地的中繼日誌中
  • mysql主從同步
    說起這個mysql主從同步，也有另外一種說法就是讀寫分離，相信大傢伙多少都了解一點，可能都不是很深入的了解，接下來我將帶領大家去進一步了解這個，
  • MySQL主從同步延遲的原因及解決辦法
    (3) 用途和條件1)、mysql主從複製用途●實時災備，用於故障切換●讀寫分離，提供查詢服務●備份，避免影響業務2)、主從部署必要條件：●主庫開啟binlog日誌（設置log-bin參數）●主從server-id不同●從庫伺服器能連通主庫
  • 複製信息記錄表|全方位認識 mysql 系統庫
    》中，我們詳細介紹了mysql系統庫中的時區信息記錄表，本期我們將為大家帶來系列第七篇《複製信息記錄表|全方位認識 mysql 系統庫》，下面請跟隨我們一起開始 mysql 系統庫的系統學習之旅吧！mysql.slave_worker_info表中，在該表中，有多少個並行複製線程，就有多少行記錄（如果是多主複製，則每個複製通道都有slave_parallel_workers變量指定的記錄數）。
  • Redis 主從複製(Replication)
    為了保證服務的可用性，現代資料庫都提供了複製功能，同時在多個進程中維護一致的數據狀態。Redis 支持一主多從的複製架構，該功能被簡化成了一條 SLAVEOF 命令，下面通過條命令來解析 Redis 的主從複製機制。