資料庫主從複製

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  backup@'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

     

 

相關文章