一、主從復制原理

1.1 基本介紹

MySQL 內(nèi)建的復制功能是構(gòu)建大型，高性能應用程序的基礎(chǔ)。將 MySQL 的 數(shù)億分布到到多個系統(tǒng)上去，這種分步的機制，是通過將 MySQL 的某一臺主機的數(shù)據(jù)復制到其它主機( Slave )上，并重新執(zhí)行一遍來實現(xiàn)的。復制過程中一個服務器充當主服務器，而一個或多個其它服務器充當從服務器。主服務器將更新寫入本地二進制日志文件中，并維護文件的一個索引以跟蹤日志循環(huán)。這些日志可以記錄發(fā)送到從服務器的更新。當一個從服務器連接主服務器時，它通知主服務器從服務器在日志中讀取的最后一次成功更新的位置，從服務器接收從那時起發(fā)生的任何更新，然后等待主服務器通知新的更新。?

注意：當你配置主從復制后，所有對數(shù)據(jù)的更新必須在主服務器上進行。否則，你必須要小心，以避免用戶對主服務器上的表進行的更新與對從服務器上的表所進行的更新之間的沖突。

1.2 Mysql支持的復制類型

• 基于語句的復制: 在主服務器執(zhí)行SQL語句，在從服務器執(zhí)行同樣語句。MySQL默認采用基于語句的復制，效率較高。一旦發(fā)現(xiàn)沒法精確復制時, 會自動選基于行的復制。 ?

• 基于行的復制: 把改變的內(nèi)容復制過去，而不是把命令在從服務器上執(zhí)行一遍. 從mysql5.0開始支持?

• 混合類型的復制: 默認采用基于語句的復制，一旦發(fā)現(xiàn)基于語句的無法精確的復制時，就會采用基于行的復制。

1.3 主從復制的優(yōu)點

• 在從服務器可以執(zhí)行查詢工作(即我們常說的讀功能)，降低主服務器壓力;（主庫寫，從庫讀，降壓）

• 在從主服務器進行備份，避免備份期間影響主服務器服務;（確保數(shù)據(jù)安全）

• 當主服務器出現(xiàn)問題時，可以切換到從服務器。（提升性能）
  

1.3.1 主從復制解決的問題

• - ?數(shù)據(jù)分布 (Data distribution )?

• - ?負載平衡(load balancing)?

• - ?據(jù)備份(Backups) ，保證數(shù)據(jù)安全?

• - ?高可用性和容錯行(High availability and failover)?

• - ?實現(xiàn)讀寫分離，緩解數(shù)據(jù)庫壓力

1.4 主從復制常用實現(xiàn)形式

一主多從復制架構(gòu)

應用場景：

在主庫讀取請求壓力非常大的場景下，可以通過配置一主多從復制架構(gòu)實現(xiàn)讀寫分離，把大量對實時性要求不是特別高的讀請求通過負載均衡調(diào)度到多個從庫上，降低主庫的讀取壓力。在主庫出現(xiàn)異常宕機的情況下，可以把一個從庫切換為主庫繼續(xù)提供服務（主庫授權(quán)從庫遠程連接，讀取binlog日志并更新到本地數(shù)據(jù)庫的過程；主庫寫數(shù)據(jù)后，從庫會自動同步過來（從庫跟著主庫變））

????注意事項：

• 
  

  • 當 Slave 增加到一定數(shù)量時，Slave 對 Master 的負載以及網(wǎng)絡(luò)帶寬都會成為一個嚴重的問題。

  • 不同的 Slave 扮演不同的作用(例如使用不同的索引，或者不同的存儲引擎)

  • 用一個 Slave 作為備用 Master，只進行復制

  • 用一個遠程的 Slave，用于災難恢復。

多級復制架構(gòu)

應用場景：

一主多從的架構(gòu)能夠解決大部分讀請求壓力特別大的場景需求，但主庫的I/O壓力和網(wǎng)絡(luò)壓力會隨著從庫的增加而增長，而使用多級復制架構(gòu)就可以解決一主多從場景下，主庫額外的I/O和網(wǎng)絡(luò)壓力。 但要注意的是，多級復制場景下主庫的數(shù)據(jù)是經(jīng)歷兩次才到達讀取的從庫，期間的延時比一主多從復制場景下只經(jīng)歷一次復制的要大

?????注意事項：

• 
  

  • 可能存在延時較長的風險

  • 這種方案可以與第三方軟件結(jié)合使用，例如Slave+LVS+Keepalived 實現(xiàn)高可用。

雙主復制架構(gòu)

應用場景：

雙主/Dual Master架構(gòu)適用于寫壓力比較大的場景，或者DBA做維護需要主從切換的場景，通過雙主/Dual master架構(gòu)避免了重復搭建從庫的麻煩；（主從相互授權(quán)連接，讀取對方binlog日志并更新到本地數(shù)據(jù)庫的過程；只要對方數(shù)據(jù)改變，自己就跟著改變）

? ? ?注意事項：

• 
  

  • 最大問題就是更新沖突。

  • 可以采用MySQL Cluster，以及將Cluster和Replication結(jié)合起來，可以建立強大的高性能的數(shù)據(jù)庫平臺

    
    

1.5 主從復制工作原理

主庫開啟binlog功能并授權(quán)從庫連接主庫，從庫通過change master得到主庫的相關(guān)同步信息,然后連接主庫進行驗證，主庫IO線程根據(jù)從庫slave線程的請求，從master.info開始記錄的位置點向下開始取信息，同時把取到的位置點和最新的位置與binlog信息一同發(fā)給從庫IO線程，從庫將相關(guān)的sql語句存放在relay-log里面，最終從庫的sql線程將relay-log里的sql語句應用到從庫上，至此整個同步過程完成，之后將是無限重復上述過程

需要理解

• 從庫會生成兩個線程,一個I/O線程,一個SQL線程;

• I/O線程會去請求主庫的binlog,并將得到的binlog寫到本地的relay-log(中繼日志)文件中;

• 主庫會生成一個log dump線程,用來給從庫I/O線程傳binlog;

• SQL線程,會讀取relay log文件中的日志,并解析成sql語句逐一執(zhí)行;

注意幾點問題：

• master將操作語句記錄到binlog日志中，然后授予slave遠程連接的權(quán)限（master要開啟binlog二進制日志功能；通常為了數(shù)據(jù)安全考慮，slave也開啟binlog）;?

• slave開啟兩個線程：IO線程和SQL線程。其中：IO線程負責讀取master的binlog內(nèi)容到中繼日志relay log里；SQL線程負責從relay log日志里讀出binlog內(nèi)容，并更新到slave的數(shù)據(jù)庫里，這樣就能保證slave數(shù)據(jù)和master數(shù)據(jù)保持一致了;?

• mysql復制至少需要兩個Mysql的服務，當然Mysql服務可以分布在不同的服務器上，也可以在一臺服務器上啟動多個服務;?

• mysql復制最好確保master和slave服務器上的Mysql版本相同（如果不能滿足版本一致，那么要保證master主節(jié)點的版本低于slave從節(jié)點的版本）;?

• master和slave兩節(jié)點間時間需同步;

大致可以簡單分為以下三步：

• 第一步：master在每個事務更新數(shù)據(jù)完成之前，將該操作記錄串行地寫入到binlog文件中。?

• 第二步：salve開啟一個I/O Thread，該線程在master打開一個普通連接，主要工作是binlog dump process。如果讀取的進度已經(jīng)跟上了master，就進入睡眠狀態(tài)并等待master產(chǎn)生新的事件。I/O線程最終的目的是將這些事件寫入到中繼日志中。?

• 第三步：SQL Thread會讀取中繼日志，并順序執(zhí)行該日志中的SQL事件，從而與主數(shù)據(jù)庫中的數(shù)據(jù)保持一致。

細節(jié)說明：

• ?- ?Mysql復制過程的第一部分就是master記錄二進制日志。在每個事務更新數(shù)據(jù)完成之前，master在二日志記錄這些改變。MySQL將事務串行的寫入二進制日志，即使事務中的 ?語句都是交叉執(zhí)行的。在事件寫入二進制日志完成后，master通知存儲引擎提交事務; - ?第二部分就是slave將master的binary log拷貝到它自己的中繼日志。首先，slave開始一個工作線程(I/O線程)。I/O線程在master上打開一個普通的連接，然后開始binlog dump process。Binlog dump process從master的二進制日志中讀取事件，如果已經(jīng)跟上master，它會睡眠并等待master產(chǎn)生新的事件。I/O線程將這些事件寫入中繼日志;?

• - ?SQL slave thread（SQL從線程）處理該過程的最后一步。SQL線程從中繼日志讀取事件，并重放其中的事件而更新slave的數(shù)據(jù)，使其與master中的數(shù)據(jù)一致。只要該線程與 ?I/O線程保持一致，中繼日志通常會位于OS的緩存中，所以中繼日志的開銷很小;?

• - ?此外，在master中也有一個工作線程：和其它MySQL的連接一樣，slave在master中打開一個連接也會使得master開始一個線程。復制過程有一個很重要的限制, 即復制在slave上是串行化的，也就是說master上的并行更新操作不能在slave上并行操作。

二、mysql主從配置實戰(zhàn)

2.1 配置簡易實現(xiàn)步驟

主服務器：?

????- 開啟二進制日志 功能

????- 配置唯一的server-id?

????- 獲得master二進制日志文件名及位置?

????- 創(chuàng)建一個用于slave和master通信的用戶賬號

從服務器：?

????- 配置唯一的server-id?

????- 使用master分配的用戶賬號讀取master二進制日志?

????- 啟用slave服務

2.2 配置注意事項

• 
  

  • 每個 Slave 只能有一個 Master；

  • 每個 Slave 只能有一個唯一的服務器ID；

  • 每個 Master 可以有很多 Slave;

  • 如果你設(shè)置了 log_slave_updates，Slave 可以是其他 Slave 的 Master，從而擴散 Master 的更新

  • MySQL 不支持多主服務器復制—即一個 Slave 可以有多個 Master，但是，通過一些簡單的組合，我們卻可以建立靈活而強大的復制體系結(jié)構(gòu)。

? -? 復制時，主庫有一個I/O線程，從庫有兩個線程，I/O和SQL線程
? -? 實現(xiàn)主從復制的必要條件是主庫要開啟記錄binlog功能
? -? binlog文件只記錄對數(shù)據(jù)庫有更改的SQL語句（來自主庫內(nèi)容的變更），不記錄任何查詢（select，show）語句

2.3 主從環(huán)境介紹

數(shù)據(jù)庫角色	IP	應用和系統(tǒng)	有無數(shù)據(jù)
Master數(shù)據(jù)庫	192.168.2.221	Centos7.3+mariadb5.5.56	有
Slave數(shù)據(jù)庫	192.168.2.222	Centos7.3+mariadb5.5.56	無

我這里mariadb使用yum直接安裝了

主服務器操作：

①使用yum安裝mariadb-server軟件包，關(guān)閉防火墻和selinux

[root@master?/]#?systemctl?stop?pfirewalld? [root@master?/]#?setenforce??0 [root@master?/]#?yum?install?-y?mariadb-server

②修改mariadb配置文件，開啟二進制日志功能，配置一個server-id并啟動mariadb服務

[root@master?/]#?vim?/etc/my.cnf -----------------------------修改添加以下內(nèi)容------------------------- [mysqld] .....省略 log-bin=bin-log????????????????????##開啟二進制日志功能 server-id=11???????????????????????##設(shè)置server-id所有主從服務器要唯一 innodb-file-per-table=ON skip-name-resolve=ON [root@master?/]#?systemctl?restart?mariadb

③創(chuàng)建slave通信用戶，創(chuàng)建ceshi數(shù)據(jù)庫并將mysql庫的數(shù)據(jù)備份后導入到ceshi數(shù)據(jù)庫

[root@master?/]#?mysql MariaDB?[(none)]>?grant?replication?slave?on?*.*?to?'repl'@'192.168.2.222'?identified?by?'replication'; Query?OK,?0?rows?affected?(0.00?sec)MariaDB?[(none)]>?flush?privileges; Query?OK,?0?rows?affected?(0.00?sec)MariaDB?[(none)]>?create?database?ceshi; Query?OK,?1?row?affected?(0.00?sec) [root@master?/]#?mysqldump?-uroot?-p?mysql?>?/mysql.sql [root@master?/]#?cat?/mysql.sql?|?mysql?-uroot?-p?ceshi

④登錄myslq數(shù)據(jù)庫，查看master的binlog日志文件名和pos-id

[root@master?/]#?mysql MariaDB?[ceshi]>?show?master?status; +----------------+----------+--------------+------------------+ |?File???????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?| +----------------+----------+--------------+------------------+ |?bin-log.000003?|???514412?|??????????????|??????????????????| +----------------+----------+--------------+------------------+ 1?row?in?set?(0.00?sec)

從服務器操作：

①使用yum安裝mariadb-server軟件包，關(guān)閉防火墻和selinux

[root@slave?/]#?systemctl?stop?pfirewalld? [root@slave?/]#?setenforce??0 [root@slave?/]#?yum?install?-y?mariadb-server

②修改mariadb配置文件，開啟中繼日志功能，配置一個server-id并啟動mariadb服務

[root@slave?/]#?vim?/etc/my.cnf -----------------------------修改添加以下內(nèi)容------------------------- [mysqld] .....省略 relay-log=relay-log????????????????????##開啟中繼日志功能 server-id=22????????????????????????????##設(shè)置slave的serverid innodb-file-per-table=ON skip-name-resolve=ON [root@slave?/]#?systemctl?restart?mariadb

③創(chuàng)建ceshi數(shù)據(jù)庫，將ceshi數(shù)據(jù)庫的全量備份文件復制到本地并導入到ceshi數(shù)據(jù)庫

[root@slave?/]#?mysql MariaDB?[(none)]>?create?database?ceshi; Query?OK,?1?row?affected?(0.00?sec) [root@slave?/]#?scp?192.168.2.221:/mysql.sql?/ [root@slave?/]#?cat?mysql.sql?|?mysql?-uroot?-p?ceshi

④配置master的讀取binlog信息配置，啟動slave進程并查看slave狀態(tài)

MariaDB?[ceshi]>?change?master?to->?master_host='192.168.2.221',->?master_user='repl',->?master_password='replication',->?master_log_file='bin-log.000003',->?master_log_pos=514412; Query?OK,?0?rows?affected?(0.32?sec)MariaDB?[ceshi]>?start?slave; Query?OK,?0?rows?affected?(0.01?sec)MariaDB?[ceshi]>?show?slave?status\G ***************************?1.?row?***************************Slave_IO_State:?Waiting?for?master?to?send?eventMaster_Host:?192.168.2.221Master_User:?replMaster_Port:?3306Connect_Retry:?60Master_Log_File:?bin-log.000003Read_Master_Log_Pos:?514412Relay_Log_File:?relay-log.000002Relay_Log_Pos:?527Relay_Master_Log_File:?bin-log.000003Slave_IO_Running:?Yes????????????????????????????##此處兩個yes代表slave配置同步成功Slave_SQL_Running:?Yes????????????????????????????##此處兩個yes代表slave配置同步成功Replicate_Do_DB:?Replicate_Ignore_DB:?Replicate_Do_Table:?Replicate_Ignore_Table:?Replicate_Wild_Do_Table:?Replicate_Wild_Ignore_Table:?Last_Errno:?0Last_Error:?Skip_Counter:?0Exec_Master_Log_Pos:?514412Relay_Log_Space:?815Until_Condition:?NoneUntil_Log_File:?Until_Log_Pos:?0Master_SSL_Allowed:?NoMaster_SSL_CA_File:?Master_SSL_CA_Path:?Master_SSL_Cert:?Master_SSL_Cipher:?Master_SSL_Key:?Seconds_Behind_Master:?0 Master_SSL_Verify_Server_Cert:?NoLast_IO_Errno:?0Last_IO_Error:?Last_SQL_Errno:?0Last_SQL_Error:?Replicate_Ignore_Server_Ids:?Master_Server_Id:?11 1?row?in?set?(0.00?sec)

選項說明：

• master_host：Master 服務器IP

• master_user：Master 服務器授權(quán)用戶，也就是 Master 前面創(chuàng)建的那個用戶

• master_password：Master 服務器授權(quán)用戶對應的密碼

• master_log_file：Master binlog 文件名

• master_log_pos：Master binlog 文件中的 Postion 值

⑤測試，在master數(shù)據(jù)庫中執(zhí)行sql語句操作，觀察slave是否同步，如果同步則說明配置成功

注意事項：

• 主庫和從庫的數(shù)據(jù)庫名必須相同；

• 主庫和從庫的復制可以精確到表，但是在需要更改主庫或從庫的數(shù)據(jù)結(jié)構(gòu)時需要立刻重啟slave；

• 不能在mysql配置文件里直接寫入master的配置信息，需要用change master命令來完成；

• 指定replicate_do_db必須在my.cnf里配置，不能用change master命令來完成；

• 如果不及時清理，日積月累二進制日志文件可能會把磁盤空間占滿，可以在配置文件里加上expire_logs_days=7，只保留最近7天的日志，建議當slave不再使用時，通過reset slave來取消relaylog；

• 寫一個監(jiān)控腳本，用來監(jiān)控 Slave 中的兩個"yes"，如果只有一個"yes"或者零個，就表明主從有問題。

2.3 主從復制常用的幾個配置選項

binlog_do_db="***,***,***";??????#數(shù)據(jù)庫白名單列表,二進制日志記錄的數(shù)據(jù)庫（多數(shù)據(jù)庫用逗號隔開或重復設(shè)置多行）,即需要同步的庫.不在內(nèi)的不同步。（不添加這行表示同步所有） binlog_ingore_db="***,***,***";??#數(shù)據(jù)庫黑名單列表,?二進制日志中忽略的數(shù)據(jù)庫?（多數(shù)據(jù)庫用逗號隔開或重復設(shè)置多行）,即不需要同步,要過濾掉的庫.從服務器上配置： replicate_do_db?="webdb";?????????#復制庫的白名單.?設(shè)定需要復制的數(shù)據(jù)庫（多數(shù)據(jù)庫使用逗號隔開或重復設(shè)置多行） replicate_ingore_db?="mysql";?????#復制庫的黑名單.?設(shè)定需要忽略的復制數(shù)據(jù)庫?（多數(shù)據(jù)庫使用逗號隔開或重復設(shè)置多行） replicate_do_table="webdb.user";??#復制表的白名單.?設(shè)定需要復制的表（多數(shù)據(jù)庫使用逗號隔開或重復設(shè)置多行） relicate_ingore_table="webdb.uw";?#復制表的黑名單.?設(shè)定需要忽略的復制的表（多數(shù)據(jù)庫使用逗號隔開或重復設(shè)置多行） expire_logs_days=14???????????????#設(shè)置log保存的時長 log-slave-updates?????????????????##可以成鏈式復制（主--->?從（主）--->從）replicate-wild-do-table???????????#同replication-do-table功能一樣，但是可以通配符.?更高級別的應用，通配符,應用到哪一類表的。 replicate-wild-ignore-table???????#同replication-ignore-table功能一樣，但是可以加通配符. replicate-wild-do-table=webdb.%??????#只復制webdb庫下的所有表 replicate-wild-ignore-table=mysql.%??#忽略mysql庫下的所有表

注意：這些參數(shù)默認是沒有配置的，沒有手動配置，那默認是同步所有的庫

轉(zhuǎn)載于:https://blog.51cto.com/13777759/2403839

總結(jié)

以上是生活随笔為你收集整理的Mysql主从复制详解和实战的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。