mysql多實例及主從複製,搭建mysql主從複製

本文目錄一覽：

• 1、如何配置MySQL資料庫主從複製
• 2、Mysql主從複製方式以及可能出現的問題
• 3、安全最重要！MySQL配置主從複製，主主複製
• 4、請問：1，mysql主從複製是什麼概念，什麼場合下用，最好舉例說明；

如何配置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主從複製方式以及可能出現的問題

大致流程：主庫將變更寫binlog日誌，然後從庫連接到主庫之後，從庫有一個IO線程，將主庫的binlog日誌拷貝到自己本地，寫入一個中繼日誌 relay日誌中。接著從庫中有一個SQL線程會從中繼日誌讀取binlog，然後執行binlog日誌中的內容，也就是在自己本地再次執行一遍SQL，這樣就可以保證自己跟主庫的數據是一樣的。

如果主庫突然宕機，然後恰好數據還沒同步到從庫，那麼有些數據可能在從庫上是沒有的，這時候從庫成為了主庫，那麼有些數據可能就丟失了。

開啟半同步複製 semi-sync ，用來解決主庫數據丟失問題；

這個所謂半同步複製， semi-sync複製 ，指的就是主庫寫入binlog日誌之後，就會將強制此時立即將數據同步到從庫，從庫將日誌 寫入自己本地的relay log之後 ，接著會 返回一個ack 給主庫， 主庫接收到至少一個從庫的ack之後才會認為寫操作完成了。 如果 過程出現失敗 ，那麼 我們的客戶端就可以進行重試了 ；

主從延遲對於讀寫分離的涉及影響比較大

這裡有一個非常重要的一點，就是 從庫同步主庫數據的過程是串列化的 ，也就是說 主庫上並行的操作，在從庫上會串列執行 。所以這就是一個非常重要的點了，由於從庫從主庫拷貝日誌以及串列執行SQL的特點，在 高並發場景下，主庫大量的寫，那麼從庫的數據一個個的讀，那麼就會導致從庫同步一定會比主庫慢一些，是有延時的 。所以經常出現，剛寫入主庫的數據可能是讀不到的，要過幾十毫秒，甚至幾百毫秒才能讀取到。（主庫並發寫的量級越高，從庫積壓的同步數據越多，延遲越高）

我們可以用 show status 看看 Seconds_Behind_Master 參數，你可以看到從庫複製主庫的數據落後了幾ms，但是這個也不是完全準確，可以看 Seconds_Behind_Master的

對於解決主從延遲，解決方案可以從以下方面考慮

安全最重要！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（收藏！）

請問：1，mysql主從複製是什麼概念，什麼場合下用，最好舉例說明；

1 主從複製，是用來建立一個和主資料庫完全一樣的資料庫環境，稱為從資料庫；主資料庫一般是實時的業務資料庫，從資料庫的作用和使用場合一般有幾個：

一是作為後備資料庫，主資料庫伺服器故障後，可切換到從資料庫繼續工作；

二是可在從資料庫作備份、數據統計等工作，這樣不影響主資料庫的性能；

2 讀寫分離，是指讀與寫分別使用不同的資料庫，當然一般是在不同伺服器上的；在同一台伺服器上的讀寫環境，估計只是用來測試吧。

一般讀寫的資料庫環境配置為，一個寫入的資料庫，一個或多個讀的資料庫，各個資料庫分別位於不同的伺服器上，充分利用伺服器性能和資料庫性能；當然，其中會涉及到如何保證讀寫資料庫的數據一致，這個就可以利用主從複製技術來完成。

一般應用場合為：業務吞吐量很大，讀資料庫（可簡單理解為select語句的 比例和影響）的負載較大；

官方的mysql-proxy就是一個實現了讀寫分離、負載均衡等多個功能的軟體。