mysql數據庫主主複製,mysql主從複製保證數據一致

本文目錄一覽：

• 1、如何在一台windows主機上搭建mysql主從複製
• 2、MySQL的主從複製原理以及流程
• 3、如何配置MySQL數據庫主從複製
• 4、MySQL 主從，5 分鐘帶你掌握
• 5、安全最重要！MySQL配置主從複製，主主複製
• 6、mysql主從複製是什麼概念？什麼場合用？最好舉例說明。

如何在一台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

MySQL的主從複製原理以及流程

原理：master將數據庫的改變寫入二進制日誌，slave同步這些二進制日誌，並根據這些二進制日誌進行數據重演操作，實現數據異步同步。流程：1. slave端的IO線程發送請求給master端的binlog dump線程2. master端binlog dump線程獲取二進制日誌信息(文件名和位置信息)發送給slave端的IO線程3. salve端IO線程獲取到的內容依次寫到slave端relay log里，並把master端的bin-log文件名和位置記錄到master.info里4. salve端的SQL線程，檢測到relay log中內容更新，就會解析relay log里更新的內容，並執行這些操作，從而達到和master數據一致原理和流程就是這樣了，如果在工作中遇到了，希望幫到你，還有不明白的，可以在網上多搜一下這方面的知識，我這些知識都是在黑馬程序員社區看到的，你也可以去看看有沒有想了解的。

如何配置MySQL數據庫主從複製

MySQL支持單向、異步複製，複製過程中一個服務器充當主服務器，而一個或多個其它服務器充當從服務器。主服務器將更新寫入二進制日誌文件，並維 護日誌文件的一個索引以跟蹤日誌循環。當一個從服務器連接到主服務器時，它通知主服務器從服務器在日誌中讀取的最後一次成功更新的位置。從服務器接收從那 時起發生的任何更新，然後封鎖並等待主服務器通知下一次更新。

為什麼使用主從複製？

1、主服務器/從服務器設置增加了健壯性。主服務器出現問題時，你可以切換到從服務器作為備份。

2、通過在主服務器和從服務器之間切分處理客戶查詢的負荷，可以得到更好的客戶響應時間。但是不要同時在主從服務器上進行更新，這樣可能引起衝突。

3、使用複製的另一個好處是可以使用一個從服務器執行備份，而不會干擾主服務器。在備份過程中主服務器可以繼續處理更新。

MySQL使用3個線程來執行複製功能(其中1個在主服務器上，另兩個在從服務器上。當發出START SLAVE時，從服務器創建一個I/O線程，以連接主服務器並讓主服務器發送二進制日誌。主服務器創建一個線程將二進制日誌中的內容發送到從服務器。從服 務器I/O線程讀取主服務器Binlog Dump線程發送的內容並將該數據拷貝到從服務器數據目錄中的本地文件中，即中繼日誌。第3個線程是SQL線程，從服務器使用此線程讀取中繼日誌並執行日 志中包含的更新。SHOW PROCESSLIST語句可以查詢在主服務器上和從服務器上發生的關於複製的信息。

默認中繼日誌使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是從服務器主機名，nnnnnn是序 列號。用連續序列號來創建連續中繼日誌文件，從000001開始。從服務器跟蹤中繼日誌索引文件來識別目前正使用的中繼日誌。默認中繼日誌索引文件名為 host_name-relay-bin.index。在默認情況，這些文件在從服務器的數據目錄中被創建。中繼日誌與二進制日誌的格式相同，並且可以用 mysqlbinlog讀取。當SQL線程執行完中繼日誌中的所有事件後，中繼日誌將會被自動刪除。

從服務器在數據目錄中另外創建兩個狀態文件–master.info和relay-log.info。狀態文件保存在硬盤上，從服務器關閉時不會丟失。下次從服務器啟動時，讀取這些文件以確定它已經從主服務器讀取了多少二進制日誌，以及處理自己的中繼日誌的程度。

設置主從複製：

1、確保在主服務器和從服務器上安裝的MySQL版本相同，並且最好是MySQL的最新穩定版本。

2、在主服務器上為複製設置一個連接賬戶。該賬戶必須授予REPLICATION SLAVE權限。如果賬戶僅用於複製(推薦這樣做)，則不需要再授予任何其它權限。

mysql GRANT REPLICATION SLAVE ON *.*

– TO ‘replication’@’%.yourdomain.com’ IDENTIFIED BY ‘slavepass’;

3、執行FLUSH TABLES WITH READ LOCK語句清空所有表和塊寫入語句：

mysql FLUSH TABLES WITH READ LOCK；

保持mysql客戶端程序不要退出。開啟另一個終端對主服務器數據目錄做快照。

shell cd /usr/local/mysql/

shell tar -cvf /tmp/mysql-snapshot.tar ./data

如果從服務器的用戶賬戶與主服務器的不同，你可能不想複製mysql數據庫。在這種情況下，應從歸檔中排除該數據庫。你也不需要在歸檔中包括任何日誌文件或者master.info或relay-log.info文件。

當FLUSH TABLES WITH READ LOCK所置讀鎖定有效時（即mysql客戶端程序不退出)，讀取主服務器上當前的二進制日誌名和偏移量值：

mysql SHOW MASTER STATUS;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.003 | 73 | test | manual,mysql |

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

File列顯示日誌名，而Position顯示偏移量。在該例子中，二進制日誌值為mysql-bin.003，偏移量為73。記錄該值。以後設置從服務器時需要使用這些值。它們表示複製坐標，從服務器應從該點開始從主服務器上進行新的更新。

如果主服務器運行時沒有啟用–logs-bin，SHOW MASTER STATUS顯示的日誌名和位置值為空。在這種情況下，當以後指定從服務器的日誌文件和位置時需要使用的值為空字符串(”)和4.

取得快照並記錄日誌名和偏移量後，回到前一中端重新啟用寫活動：

mysql UNLOCK TABLES；

4、確保主服務器主機上my.cnf文件的[mysqld]部分包括一個log-bin選項。該部分還應有一個server-id=Master_id選項，其中master_id必須為1到232–1之間的一個正整數值。例如：

[mysqld]

log-bin

server-id=1

如果沒有提供那些選項，應添加它們並重啟服務器。

5、停止從服務器上的mysqld服務並在其my.cnf文件中添加下面的行：

[mysqld]

server-id=2

slave_id值同Master_id值一樣，必須為1到232–1之間的一個正整數值。並且，從服務器的ID必須與主服務器的ID不相同。

6、將數據備據目錄中。確保對這些文件和目錄的權限正確。服務器 MySQL運行的用戶必須能夠讀寫文件，如同在主服務器上一樣。

Shell chown -R mysql:mysql /usr/local/mysql/data

7、啟動從服務器。在從服務器上執行下面的語句，用你的系統的實際值替換選項值：

mysql CHANGE MASTER TO

– MASTER_HOST=’master_host_name’,

– MASTER_USER=’replication_user_name’,

– MASTER_PASSWORD=’replication_password’,

– MASTER_LOG_FILE=’recorded_log_file_name’,

– MASTER_LOG_POS=recorded_log_position;

8、啟動從服務器線程：

mysql START SLAVE；

執行這些程序後，從服務器應連接主服務器，並補充自從快照以來發生的任何更新。

9、如果出現複製錯誤，從服務器的錯誤日誌（HOSTNAME.err）中也會出現錯誤消息。

10、從服務器複製時，會在其數據目錄中發現文件master.info和HOSTNAME-relay-log.info。從服務器使用這兩個文 件跟蹤已經處理了多少主服務器的二進制日誌。不要移除或編輯這些文件，除非你確切知你正在做什麼並完全理解其意義。即使這樣，最好是使用CHANGE MASTER TO語句。

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。

安全最重要！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主從複製是什麼概念？什麼場合用？最好舉例說明。

主從複製，是用來建立一個和主數據庫完全一樣的數據庫環境，稱為從數據庫；主數據庫一般是實時的業務數據庫

MySQL是一個關係型數據庫管理系統，由瑞典MySQL AB 公司開發，目前屬於 Oracle 旗下產品。MySQL 最流行的關係型數據庫管理系統，在 WEB 應用方面MySQL是最好的 RDBMS (Relational Database Management System，關係數據庫管理系統) 應用軟件之一。

MySQL是一種關聯數據庫管理系統，關聯數據庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度並提高了靈活性。

與其他的大型數據庫例如Oracle、DB2、SQL Server等相比，MySQL 自有它的不足之處，但是這絲毫也沒有減少它受歡迎的程度。

對於一般的個人使用者和中小型企業來說，MySQL提供的功能已經綽綽有餘，而且由於 MySQ L是開放源碼軟件，因此可以大大降低總體擁有成本。