mysql主從複製的腳本文件（mysql主從複製的腳本文件怎麼刪除）

本文目錄一覽：

• 1、關於mysql 主從複製的錯誤
• 2、如何在一台windows主機上實現MySQL的主從複製
• 3、安全最重要！MySQL配置主從複製，主主複製
• 4、MySQL 主從，5 分鐘帶你掌握
• 5、如何在一台windows主機上搭建mysql主從複製

關於mysql 主從複製的錯誤

項目上 MySQL 還原 SQL 備份經常會碰到一個錯誤如下，且通常出現在導入視圖、函數、存儲過程、事件等對象時，其根本原因就是因為導入時所用賬號並不具有SUPER 許可權，所以無法創建其他賬號的所屬對象。ERROR 1227 (42000) : Access denied; you need (at least one of) the SUPER privilege(s) for this operation常見場景：1. 還原 RDS 時經常出現，因為 RDS 不提供 SUPER 許可權；2. 由開發庫還原到項目現場，賬號許可權等有所不同。

處理方式：

1. 在原庫中批量修改對象所有者為導入賬號或修改 SQL SECURITY 為 Invoker；2. 使用 mysqldump 導出備份，然後將 SQL 文件中的對象所有者替換為導入賬號。

二、問題原因我們先來看下為啥會出現這個報錯，那就得說下 MySQL 中一個很特別的許可權控制機制，像視圖、函數、存儲過程、觸發器等這些數據對象會存在一個 DEFINER 和一個 SQL SECURITY 的屬性，如下所示：

–視圖定義CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW v_test

–函數定義CREATE DEFINER=`root`@`%` FUNCTION `f_test()` RETURNS varchar(100) SQL SECURITY DEFINER

–存儲過程定義CREATE DEFINER=`root`@`%` PROCEDURE `p_test`() SQL SECURITY DEFINER

–觸發器定義CREATE DEFINER=`root`@`%` trigger t_test

–事件定義CREATE DEFINER=`root`@`%` EVENT `e_test`

DEFINER：對象定義者，在創建對象時可以手動指定用戶，不指定的話默認為當前連接用戶；

SQL SECURITY：指明以誰的許可權來執行該對象，有兩個選項，一個為 DEFINER，一個為 INVOKER，默認情況下系統指定為 DEFINER；DEFINER：表示按定義者的許可權來執行； INVOKER：表示按調用者的許可權來執行。

如果導入賬號具有 SUPER 許可權，即使對象的所有者賬號不存在，也可以導入成功，但是在查詢對象時，如果對象的 SQL SECURITY 為 DEFINER，則會報賬號不存在的報錯。ERROR 1449 (HY000): The user specified as a definer (‘root’@’%’) does not exist

三、改寫內容上述這個 DEFINER 問題，個人想到最簡單的解決方式就是 mysqldump 導出時直接摘除掉相關屬性，但是 mysqldump 本身並不提供對應參數，所以比較蛋疼，無論是原庫走腳本變更或是備份後修改 SQL 文件都不是非常方便，尤其是觸發器的 DEFINER，只能先 DROP 再 CREATE 才可以變更。只能看下是否可以從 mysqldump 源碼中去掉 DEFINER 定義。本次 mysqldump 改寫主要有 2 個目的：1. 摘取備份中視圖、函數、存儲過程、觸發器等對象的 DEFINER 定義；2. 嘗試加上比較簡單的備份進度顯示（原生 mysqldump 的 verbose 參數不是非常清晰，想要實現 navicate 備份時的那種行數顯示）。

改寫好處：1. 可以避免還原時遇到 DEFINER 報錯相關問題；2. 根據輸出信息知道備份是否正常進行，防止備份中遇到元數據鎖無法獲取然後一直卡住的情況。

如何在一台windows主機上實現MySQL的主從複製

1、首先要在本地建立兩個mysql服務（參考這裡），指定不同的埠。我這裡一個主（3306），一個從（3307）。

2、然後修改主配置文件:

[mysqld]

server-id = 1

binlog-do-db=test #要同步的資料庫

#binlog-ignore-db=mysql #不同步的資料庫,如果指定了binlog-do-db這裡應該可以不用指定的

log-bin=mysql-bin #要生成的二進位日記文件名稱

修改從配置文件:

[mysqld]

server-id = 2

log-bin = mysql-bin

replicate-do-db=test

3、在主庫添加一個用戶 repl 並指定replication許可權

create user ‘repl’@’127.0.0.1’ identified by ‘asdf’;

GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’127.0.0.1’; — –這裡我指定資料庫（test.*）時報錯，而指定全庫（*.*）時會成功。

4、保持主從mysql的test資料庫初始狀態一致。

一般是先將所有的表加讀鎖，然後copy磁碟上的資料庫文件夾。我這裡直接停止服務，然後將數據文件拷貝過去。

5、在主資料庫裡面運行show master status;記下file和position欄位對應的參數。

mysql show master status;

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000001 | 107 | test | |

+——————+———-+————–+——————+

安全最重要！MySQL配置主從複製，主主複製

為了保障數據的安全與穩定性，我們常用資料庫的主從複製與主主複製來實現。主從複製為從機實時拷貝一份主機的數據，當主機有數據變化時，從機的數據會跟著變，當從機數據有變化時，主機數據不變；同樣地，主主複製就是，多個主機之間，只要有一個主機的數據變化了，其它主機數據也會跟著變化。

添加以下內容

如果你是使用我之前那種方式啟動的MySQL，那麼你只需要去你相關聯的宿主機的配置文件夾裡面去建立一個 my.cnf 然後寫入上面的類容就好了。

比如：我的啟動命令如下（不應該換行的，這裡為了方便查看，我給它分行了）

那麼我只需要在 /docker/mysql_master/conf 這個目錄下創建 my.cnf 文件就好了。

這個命令是需要在容器裡面執行的

docker重啟mysql會關閉容器，我們需要重啟容器。

確保在主伺服器上 skip_networking 選項處於 OFF 關閉狀態, 這是默認值。 如果是啟用的，則從站無法與主站通信，並且複製失敗。

我的命令如下

在從伺服器配置連接到主伺服器的相關信息 （在容器裡面的mysql執行）

上面代碼的xxxxx你需要換成你的IP，docker 查看容器 IP 的命令如下：

啟動的那個從伺服器的線程

測試的話，你可以在主伺服器裡面，創建一個資料庫，發現從伺服器裡面也有了，就成功了。

如果你還想要一個從伺服器，那麼你只需要按照上面配置從伺服器再配置一個就行了，新建的從伺服器，會自動保存主伺服器之前的數據。（測試結果） 如果你上面的主從複製搞定了，那麼這個主主複製就很簡單了。我們把上面的從伺服器也改成主伺服器

1）、修改上面的從伺服器的my.cnf文件，和主伺服器的一樣（注意這個server-id不能一樣）然後重啟伺服器 2）、在從伺服器裡面創建一個複製用戶創建命令一樣（這裡修改一下用戶名可以改為 repl2） 3）、在之前的主伺服器裡面運行下面這個代碼

上面主要是教你怎麼搭建一個MySQL集群，但是這裡面還有很多其它的問題。也是我在學習過程中思考的問題，可能有的小夥伴上來看到文章長篇大論的看不下去，只想去實現這樣一直集群功能，所以我就把問題寫在下面了。

1）、MySQL的replication和pxc MySQL的集群方案有replication和pxc兩種，上面是基於replication實現的。

replication: 非同步複製，速度快，無法保證數據的一致性。 pxc: 同步複製，速度慢，多個集群之間是事務提交的數據一致性強。

2）、MySQL的replication數據同步的原理 我們在配置的時候開啟了它的二進位日誌，每次操作資料庫的時候都會更新到這個日誌裡面去。主從通過同步這個日誌來保證數據的一致性。

3）、可否不同步全部的數據 可以配置，同步哪些資料庫，甚至是哪些表。

4）、怎麼關閉和開始同步

5）、我就我的理解畫出了，主從、主從從、主主、複製的圖。

往期推薦：

利用Docker僅花1分鐘時間安裝好MySQL服務

Linux下MySQL 5.7的離線與在線安裝（圖文）

Linux下安裝MySQL8.0（收藏！）

MySQL 主從，5 分鐘帶你掌握

MySQL 主從一直是面試常客，裡面的知識點雖然基礎，但是能回答全的同學不多。

比如樓哥之前面試小米，就被問到過主從複製的原理，以及主從延遲的解決方案，因為回答的非常不錯，給面試官留下非常好的印象。你之前面試，有遇到過哪些 MySQL 主從的問題呢？

所謂 MySQL 主從，就是建立兩個完全一樣的資料庫，一個是主庫，一個是從庫， 主庫對外提供讀寫的操作，從庫對外提供讀的操作 ，下面是一主一從模式：

對於資料庫單機部署，在 4 核 8G 的機器上運行 MySQL 5.7 時，大概可以支撐 500 的 TPS 和 10000 的 QPS， 當遇到一些活動時，查詢流量驟然，就需要進行主從分離。

大部分系統的訪問模型是讀多寫少，讀寫請求量的差距可能達到幾個數量級，所以我們可以通過一主多從的方式， 主庫只負責寫入和部分核心邏輯的查詢，多個從庫只負責查詢，提升查詢性能，降低主庫壓力。

MySQL 主從還能做到服務高可用，當主庫宕機時，從庫可以切成主庫，保證服務的高可用，然後主庫也可以做數據的容災備份。

整體場景總結如下：

MySQL 的主從複製是依賴於 binlog 的，也就是記錄 MySQL 上的所有變化並以二進位形式保存在磁碟上二進位日誌文件。

主從複製就是將 binlog 中的數據從主庫傳輸到從庫上，一般這個過程是非同步的，即主庫上的操作不會等待 binlog 同步的完成。

詳細流程如下：

當主庫和從庫數據同步時，突然中斷怎麼辦？因為主庫與從庫之間維持了一個長鏈接，主庫內部有一個線程，專門服務於從庫的這個長鏈接的。

對於下面的情況，假如主庫執行如下 SQL，其中 a 和 create_time 都是索引：

我們知道，數據選擇了 a 索引和選擇 create_time 索引，最後 limit 1 出來的數據一般是不一樣的。

所以就會存在這種情況：在 binlog = statement 格式時，主庫在執行這條 SQL 時，使用的是索引 a，而從庫在執行這條 SQL 時，使用了索引 create_time，最後主從數據不一致了。

那麼我們改如何解決呢？

可以把 binlog 格式修改為 row，row 格式的 binlog 日誌記錄的不是 SQL 原文，而是兩個 event:Table_map 和 Delete_rows。

Table_map event 說明要操作的表，Delete_rows event用於定義要刪除的行為，記錄刪除的具體行數。 row 格式的 binlog 記錄的就是要刪除的主鍵 ID 信息，因此不會出現主從不一致的問題。

但是如果 SQL 刪除 10 萬行數據，使用 row 格式就會很占空間的，10 萬條數據都在 binlog 裡面，寫 binlog 的時候也很耗 IO。但是 statement 格式的 binlog 可能會導致數據不一致。

設計 MySQL 的大叔想了一個折中的方案，mixed 格式的 binlog，其實就是 row 和 statement 格式混合使用， 當 MySQL 判斷可能數據不一致時，就用 row 格式，否則使用就用 statement 格式。

有時候我們遇到從資料庫中獲取不到信息的詭異問題時，會糾結於代碼中是否有一些邏輯會把之前寫入的內容刪除，但是你又會發現，過了一段時間再去查詢時又可以讀到數據了，這基本上就是主從延遲在作怪。

主從延遲，其實就是「從庫回放」 完成的時間，與 「主庫寫 binlog」 完成時間的差值， 會導致從庫查詢的數據，和主庫的不一致 。

談到 MySQL 資料庫主從同步延遲原理，得從 MySQL 的主從複製原理說起：

總結一下主從延遲的主要原因 ：主從延遲主要是出現在 「relay log 回放」 這一步，當主庫的 TPS 並發較高，產生的 DDL 數量超過從庫一個 SQL 線程所能承受的範圍，那麼延時就產生了，當然還有就是可能與從庫的大型 query 語句產生了鎖等待。

我們一般會把從庫落後的時間作為一個重點的資料庫指標做監控和報警，正常的時間是在毫秒級別，一旦落後的時間達到了秒級別就需要告警了。

解決該問題的方法，除了縮短主從延遲的時間，還有一些其它的方法，基本原理都是盡量不查詢從庫。

具體解決方案如下：

在實際應用場景中，對於一些非常核心的場景，比如庫存，支付訂單等，需要直接查詢從庫，其它非核心場景，就不要去查主庫了。

兩台機器 A 和 B，A 為主庫，負責讀寫，B 為從庫，負責讀數據。

如果 A 庫發生故障，B 庫成為主庫負責讀寫，修復故障後，A 成為從庫，主庫 B 同步數據到從庫 A。

一台主庫多台從庫，A 為主庫，負責讀寫，B、C、D為從庫，負責讀數據。

如果 A 庫發生故障，B 庫成為主庫負責讀寫，C、D負責讀，修復故障後，A 也成為從庫，主庫 B 同步數據到從庫 A。

如何在一台windows主機上搭建mysql主從複製

1、首先要在本地建立兩個mysql服務（參考這裡），指定不同的埠。這裡一個主（3306），一個從（3307）。

2、然後修改主配置文件:

[mysqld]

server-id = 1

binlog-do-db=test #要同步的資料庫

#binlog-ignore-db=mysql #不同步的資料庫,如果指定了binlog-do-db這裡應該可以不用指定的

log-bin=mysql-bin #要生成的二進位日記文件名稱

修改從配置文件:

[mysqld]

server-id = 2

log-bin = mysql-bin

replicate-do-db=test

3、在主庫添加一個用戶 repl 並指定replication許可權

create user ‘repl’@’127.0.0.1’ identified by ‘asdf’;

GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’127.0.0.1’; — –這裡指定資料庫（test.*）時報錯，而指定全庫（*.*）時會成功。

4、保持主從mysql的test資料庫初始狀態一致。

一般是先將所有的表加讀鎖，然後copy磁碟上的資料庫文件夾。這裡直接停止服務，然後將數據文件拷貝過去。

5、在主資料庫裡面運行show master status;記下file和position欄位對應的參數。

mysql show master status;

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000001 | 107 | test | |

+——————+———-+————–+——————+

1 row in set (0.00 sec)

6、在從庫設置它的master：

mysql change master to master_host=’127.0.0.1′,master_port=3306,master_user=’repl’,master_password=’asdf’,master_log_file=’mysql-bin.000001′,master_log_pos=107;

Query OK, 0 rows affected (0.19 sec)

這裡的master_log_file和master_log_pos對應剛才show master status記下的參數。

7、在從庫開啟從資料庫複製功能。

slave start;

mysql slave start;

Query OK, 0 rows affected (0.00 sec)

在從庫可以通過show slave status來查看一些參數。

8. 此時在主庫創建表或插入數據，在從庫就會很快也能看到了。

— 主庫

mysql create table tianyc_02(b int);

Query OK, 0 rows affected (0.16 sec)

mysql insert into tianyc_02 values(2013);

Query OK, 1 row affected (0.13 sec)

— 從庫

mysql show tables;

+—————-+

| Tables_in_test |

+—————-+

| tianyc_01 |

| tianyc_02 |

+—————-+

2 rows in set (0.00 sec)

mysql select * from tianyc_02;

+——+

| b |

+——+

| 2013 |

+——+

1 row in set (0.00 sec)

同理，可以搭建第二、第三個從節點。

備註：兩個服務的serve_id必須不同，否則在開啟複製功能時會提示錯誤

mysql slave start;

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

這裡設置的主節點id為1，從節點id為2。也可以按照ip或埠進行設置，好用來區分。

注意：

* 主從間的資料庫不是實時同步，就算網路連接正常，也存在瞬間，主從數據不一致。

* 如果主從的網路斷開，從會在網路正常後，批量同步。

* 如果對從進行修改數據，那麼很可能從在執行主的bin-log時出現錯誤而停止同步，這個是很危險的操作。所以一般情況下，非常小心的修改從上的數據。