mysql主從複製與數據庫備份,mysql主從複製主備切換

本文目錄一覽：

• 1、如何備份Mysql數據庫
• 2、如何配置MySQL數據庫主從複製
• 3、Mysql主從複製方式以及可能出現的問題
• 4、mysql怎麼實現主從同步數據庫備份？
• 5、MySQL 主從，5 分鐘帶你掌握
• 6、安全最重要！MySQL配置主從複製，主主複製

如何備份Mysql數據庫

Mysql數據庫的常用備份方法是使用使用實用程序mysqldump, 其命令格式如下

　# mysqldump [options] database [tables]

其參數的含義為：

options：代表mysqldump的選項，通過mysqldump –help可以查到。

database: 代表將要備份的數據庫

tables: 代表將要備份的表，如果不指定任何錶，則備份整個數據庫。

使用 mysqldump進行備份非常簡單，如果要備份數據庫」 phpbb_db_backup 」,使用命令：

#mysqldump –u -p phpbb_db_backup /usr/backups/mysql/ phpbb_db_backup.2005.5.6

還可以使用gzip命令對備份文件進行壓縮：

#mysqldump phpbb_db_backup | gzip /usr/backups/mysql/ phpbb_db_backup.2005.5.6。gz

恢複數據使用命令：

#mysql –u -p phpbb_db_backup /usr/backups/mysql/phpbb_db_backup.2005

如何配置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怎麼實現主從同步數據庫備份？

1.主服務器：

#Master start

log-bin=”d:/log/mysql/mysql_log_bin”

server-id=1

　#Master end

2.從服務器：

#Slave start

log-bin=”D:/log/mysql2/log-bin.log”

relay_log=”D:/log/mysql2/relay-log-bin”

#從機id，區別於主機id

server-id=2

#主機ip，供從機連接主機用

#master-host=localhost

#主機端口

#master-port=3300

#剛才為從機複製主機數據新建的賬號

#master-user=slave

#剛才為從機複製主機數據新建的密碼

#master-password=654321

#重試間隔時間10秒

#master-connect-retry=10

#需要同步的數據庫

#replicate-do-db=test

#啟用從庫日誌，這樣可以進行鏈式複製

log-slave-updates

#從庫是否只讀，0表示可讀寫，1表示只讀

read-only=1

#只複製某個表

#replicate-do-table=tablename

#只複製某些表（可用匹配符）

#replicate-wild-do-table=tablename%

#只複製某個庫

#replicate-do-db=dbname

#不複製某個表

#replicate-ignore-table=tablename

#不複製某些表

#replicate-wild-ignore-table=tablename%

#不複製某個庫

#replicate-ignore-db=dbname

#Slave end

3.對從服務器制定主服務器使用CHANGE MASTER 語句

注意：1.一定要在主服務器上創建一個可以執行replication的用戶

2.該用戶名在從服務器上可遠程登錄到主服務器。

3.開啟MySQL的log-bin日誌功能

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（收藏！）