資料庫主從複製

Curry秀 發表於 2020-08-01

主從複製是MySQL中最重要的功能之一。主從複製是指一臺伺服器充當主資料庫伺服器,另一臺或多臺伺服器充當從資料庫伺服器,主伺服器中的資料自動複製到從伺服器之中。對於多級複製,資料庫伺服器即可充當主機,也可充當從機。MySQL主從複製的基礎是主伺服器對資料庫修改記錄二進位制日誌,從伺服器通過主伺服器的二進位制日誌自動執行更新。

主從複製的型別:

1.基於語句的複製

主伺服器上面執行的語句在從伺服器上面再執行一遍
存在的問題:時間上可能不完全同步造成偏差,執行語句的使用者也可能不是同一個使用者。

2.基於行的複製

把主伺服器上面改編後的內容直接複製過去,而不關心到底改變該內容是由哪條語句引發的
存在的問題:比如一個工資表中有一萬個使用者,我們把每個使用者的工資+1000,那麼基於行的複製則要複製一萬行的內容,由此造成的開銷比較大,而基於語句的複製僅僅一條語句就可以了。

3.混合型別的複製

MySQL預設使用基於語句的複製,當基於語句的複製會引發問題的時候就會使用基於行的複製,MySQL會自動進行選擇。

主從複製的原理:

主伺服器上面的任何修改都會儲存在二進位制日誌Binary log裡面
從伺服器上面啟動一個I/O thread,連線到主伺服器上面請求讀取二進位制日誌,然後把讀取到的二進位制日誌寫到本地的一個Realy log(中繼日誌)裡面。
從伺服器上面開啟一個SQL thread定時檢查Realy log,如果發現有更改立即把更改的內容在本機上面執行一遍。

主從複製的步驟:

主伺服器:ip:192.168.10.139  系統:Rhel7 資料庫Maria DB            
從伺服器:ip:192.168.10.129  系統:Centos6.5  資料庫:MySQL

主伺服器配置:

  • 修改主伺服器配置檔案 /etc/my.cnf  ,修改完後需要重啟mysql服務
1 [mysqld]
2 log-bin=mysql-bin         //啟用二進位制日誌 (必選)
3 server-id=139             //設定伺服器唯一ID,一般取IP最後一段(必選)
4 binlog-do-db=140          //指定對db_nameA記錄二進位制日誌 (可選) 
5 binlog-ignore-db=mysql    //指定不對db_namB記錄二進位制日誌(可選)
  • 為從伺服器新增mysql賬戶並配置許可權,在主伺服器上,必須為從伺服器建立一個用來連線主伺服器的使用者,並設定replication slave許可權
1 mysql>grant  replication  slave  on  *.*   to  [email protected]'192.168.10.129'   identified   by   '123';    
2  //建立backup使用者,密碼為123,並且配置好許可權
  • 重新整理許可權:flush   privileges;
  • 驗證是否開啟主從複製:
1 select * from user where user = 'backup'  \G;
  • 檢視主伺服器正在使用二進位制日誌狀態:
show master status;

從伺服器配置 :

  • 修改從伺服器配置檔案 /etc/my.cnf  ,修改完後需要重啟mysql服務:service  mysqld restart
1 [mysqld]
2 server-id=129              //必須伺服器唯一ID,一般取IP最後一段
  • 進入資料庫,設定主伺服器資訊:
1 mysql> change master to master_host='192.168.10.139',
2     -> master_user='backup',
3     -> master_password='123',
4     -> master_log_file='mysql-bin.000001',
5     -> master_log_pos=245;
  • 開啟從伺服器複製二進位制日誌,實現同步功能: slave  start ;
  • 驗證資料庫相關引數: show  slave  status  \G;

資料庫的備份和還原:

衡量備份還原的指標:

  • RPO:恢復點目標,恢復的程度
  • RIO:恢復時間目標,恢復花費的時間

備份方式:

  • 冷備份:拷貝資料庫目錄,需要先停機再備份,對於線上不間斷提供業務的不適用
  • 快照備份:lvm快照,mysql裝在lvm建立的分割槽,可以熱備份(線上備份),缺點在於所有的檔案,包括資料、日誌等需要存放在一個邏輯卷中,然後再對卷快照備份,只支援本地備份,生產環境用的也比較少
  • 邏輯備份: mysqldump工具,單執行緒備份,備份速度較慢;mydumper工具,mysqldump升級版,有限制條件

備份單個庫:

  • 備份:
    mysqldump  -uroot  -p   dbname1> 1.sql
  • 還原:
    mysql  -uroot  -p   dbname2 < 1.sql

 

備份多個庫:

  • 備份:
    mysqldump  -uroot  -p  --database  db1  db2 > 1.sql
  • 還原: 
    mysql  -uroot -p  < 1.sql

備份全部庫:

  • 備份:
    mysqldump  -uroot  -p  --all-databases >  1.sql
  • 還原:
    mysql  -uroot  -p  <  1.sql

備份單個表:

  • 備份:
    mysqldump  dbname1  tb1 >  1.sql
  • 還原: 
    mysql  -uroot  -p   dbname2 <  1.sql