天天看點
傳回

MySQL複制功能

mysql複制功能

MySQL 内建的複制功能是建構大型，高性能應用程式的基礎。将 MySQL 的 資料分布到到多個系統上去，這種分布的機制，是通過将 MySQL 的某一台主機的資料複制到其它主機( Slave )上，并重新執行一遍來實作的。複制過程中一個伺服器充當主伺服器，而一個或多個其它伺服器充當從伺服器。主伺服器将更新寫入二進制日志，并維護檔案的一個索引以跟蹤日志循環。這些日志可以記錄發送到從伺服器的更新。當一個從伺服器連接配接主伺服器時，它通知主伺服器從伺服器在日志中讀取的最後一次成功更新的位置，從伺服器接收從那時起發生的任何更新，然後封鎖等等主伺服器通知新的更新。請注意當你進行複制時，所有對複制中的表的更新必須在主伺服器上進行。否則，你必須要小心，以避免使用者對主伺服器上的表進行的更新與對伺服器上的表所進行的更新之間的沖突

複制問題解決了什麼問題：

  • 實作了不同伺服器上的資料分布
  • 利用二進制日志增量進行
  • 不需要太多的帶寬
  • 但是使用基于行的複制在進行大批量的更改時會對帶寬帶來一定的壓力，特别是跨IDC環境下進行複制應該分批進行。
  • 實作在不同伺服器上的資料分布
  • 實作在資料讀取的負載均衡，需要其他元件配合完成比如利用DNS輪詢的方式把程式的讀連接配接到不同的備份資料庫，使用LVS，haproxy這樣的代理方式。
  • 非共享架構，同樣的資料分布在多台伺服器上，增強了資料的安全性
  • 利用備庫的備份來減少主庫的複雜，複制并不能代替備份
  • 友善的進行資料庫高可用架構的部署，避免MySQL單點失敗，實作資料庫高可用和故障切換
  • 實作資料庫線上更新

MySQL二進制日志

MySQL有很多種日志，按照記錄日志的MySQL元件可以分為MySQL服務層日志和MySQL存儲引擎層日志，比較重要的是MySQL二進制日志，如下：

  • 錯誤日志――MySQL服務啟動和關閉過程中的資訊以及其它錯誤和警告資訊。預設在資料目錄下。
  • 一般查詢日志――用于記錄select查詢語句的日志。general_log、general_log_file 預設關閉，建議關閉。
  • 慢查詢日志――log-slow-queries記錄所有超過long_query_time時間的SQL語句。
  • 二進制日志――記錄任何引起資料變化的操作，用于備份和還原。預設存放在資料目錄中，在重新整理和服務重新開機時會滾動二進制日志。
  • 中繼日志――從主伺服器的二進制檔案中複制的事件，并儲存為二進制檔案，格式和二進制日志一樣。
  • 事務日志――保證事務的一緻性。
    MySQL複制功能

主伺服器的所有變更點都記錄在二進制日志裡面。二進制日志裡記錄了與更新相關的SQL文，執行查詢的日期時間等中繼資料也記錄在内。事務送出的同時以二進制的形式進行記錄(sync_binlog=1)。

mysqlbinlog 指令可以檢視日志内容。指定啟動選項進行輸出二進制日志。

--log-bin[=file_name]

MySQL二進制日志格式

基于段的格式：

binlog_format=STATEMENT
  • 優點：日志記錄量相對較小，節約磁盤以及網絡I/O
  • 缺點：必須要記錄上下文資訊，保證語句自愛從伺服器上執行結果和主伺服器上相同，但是比如特定的函數UUID()，user()這樣的非确定性函數還是無法複制，可能造成MySQL複制的主從伺服器資料不一緻。 
mysql> show variables like 'binlog_format'; #檢視二進制日志格式，下邊顯示使用row
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)


mysql> set session binlog_format=statement;  #設定成statement格式

mysql> show variables like 'log_bin'; #查詢二進制日志是否開啟，顯示關閉
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+

開啟二進制日志

打開mysql的配置檔案添加log_bin配置

[mysqld]
log_bin = mysql_bin#位置自定義,如/var/lib/mysql/sql_log/mysql_bin

重新整理bin_logs;

mysql>flush logs; #重新整理bin_logs
mysql>flush binary logs;
+---------------------+-------------+
| Log_name            | File_size   |
+---------------------+-------------+
| mysql-bin.000001    | 201         |
+---------------------+-------------+
| mysql-bin.000002    | 154         |
+---------------------+-------------+
#各種資料庫操作，statement日志會記錄自行的SQL
>create xx
>select xxx
>update xx
>delete xx

檢視二進制日志

$ cd /var/lib/mysql/sql_log/
$ ls 
mysql-bin.000001  mysql-bin.000002  mysql-bin.index  mysql-error.log

$ mysqlbinlog -vv mysql-bin.000002  #檢視日志内容，可以清楚的看到執行的SQL語句
基于行的日志格式：

binlog_format=ROW

ROW格式可以避免MySQL複制中出現的主從不一緻問題，MySQL5.7預設采用的日志格式

基于行的日志會分别記錄每一行的資料修改

優點

  • 使MySQL主從複制更加安全
  • 對每一行資料的修改比基于段的複制高效
  • 如果誤操作修改了資料庫中的資料，同時又麼有備份可以恢複時，我們就可以通過分析二進制日志，對日志中的記錄的資料修改操作做反向處理的方式達到恢複資料的目的。

缺點

  • 記錄日志量較大

binlog_row_image

MySQL5.6為row格式增加一個

binlog_row_image

參數，這個參數可以控制row格式記錄日志的方式

binlog_row_image=[FULL|MINIMAL|NOBLOB]
FULL：資料修改時，會記錄一行資料的所有的列的内容，不論列是否被修改過
MINIMAL：隻會記錄修改的列的變化
NOBLOB：不記錄BLOB類型的變化
混合日志格式：

binlog_format = MIXED

特點：

  • 根據SQL語句由系統決定在基于段和基于行的日志格式中進行選擇
  • 資料量的大小由所執行的SQL語句決定

如何選擇二進制日志的格式

建議：

binlog_format=mixed

或

binlog_format=row

，使用row格式的話建議設定參數為

binlog_row_image=minimal

MySQL二進制日志格式對複制的影響

根據二進制的資料格式，複制可以分為：

基于SQL語句的複制（SBR）

二進制日志格式使用statement格式

優點：

  • 生成的日志量少，節約網絡傳輸I/O
  • 并不強制要求主從資料庫的表定義完全相同
  • 相比于基于行的複制方式更加靈活

缺點：

  • 對于非确定性時間，無法保證主從複制資料的一緻性
  • 對于存儲過程，觸發器，自定義函數進行的修改也可能造成資料不一緻
  • 相比基于行的複制方式在從執行時間上需要更多的執行時間
基于行的複制（RBR）

二進制日志格式使用的是基于行的日志格式

優點：

  • 可以應用于任何SQL的複制包括非确定函數，存儲過程等
  • 可以減少資料庫鎖的使用

缺點：

  • 要求主從資料庫的表結構相同，否則可能會中斷複制
  • 無法在從上單獨的執行觸發器
混合模式

根據實際内容在以上兩者間切換

MySQL複制工作方式

MySQL複制功能
  • 1、主伺服器将變更寫入二進制日志
  • 2、從伺服器讀取主伺服器的二進制日志變更并寫入到relay_log中
    • 基于日志點的複制
    • 基于GTID的複制
  • 3、在從上重放relay_log中的日志
    • 基于SQL段的日志使在從庫上重新執行記錄的SQL完成的
    • 基于行的日志則使在從庫上直接應用對資料庫行的修改完成的

基于日志點的複制

如果主從伺服器上的MySQL都是最新安裝的，為了實作主從複制需要進行如下步驟

  • 1、在主DB伺服器上建立複制的賬号并授權 
mysql>create user 'repl'@'IP段' identified by 'PassWord';
mysql>grant replication slave on *.* to 'repl'@'IP段';
  • 2、配置主資料庫伺服器
binlog_format二進制格式要設定，前面内容講解到

my.cnf檔案配置

[mysqld]
#啟用二進制日志(可自定義存放位置,需要先建立并賦予權限)
log_bin = /var/lib/mysql/sql_log/mysql_bin
# mkdir /var/lib/mysql/sql_log
# chown -R mysql:mysql /var/lib/mysql/sql_log/

#指定主伺服器的服務ID,整個服務叢集中式唯一的(自定義,叢集中不重複即可)
server_id = 100

  • 3、配置從資料庫伺服器

    my.cnf檔案配置

#啟用二進制日志(可自定義存放位置,需要先建立并賦予權限)
log_bin = /var/lib/mysql/sql_log/mysql_bin
# mkdir /var/lib/mysql/sql_log
# chown -R mysql:mysql /var/lib/mysql/sql_log/

#指定從伺服器的服務ID,整個服務叢集中式唯一的(自定義,叢集中不重複即可)
server_id = 101

#啟用中繼日志(可自定義存放位置)
relay_log = /var/lib/mysql/sql_log/mysql_relay_bin

#可選，建議從伺服器隻讀
read_only = on

#可選(如果需要将從伺服器作為其他伺服器的主伺服器時必須設定，否則不設定)
log_slave_updates = on
  • 4、初始化從伺服器資料，導出主庫中的資料到從庫
如果是新伺服器 或 資料都是一緻的跳過此步驟

兩種導出方式：

mysqldump --master-data -single-transaction#會對表造成大量阻塞
xtrabackup --slave-info#對全部都是innodb的表最好的選擇,不會阻塞
  • 5、在主伺服器檢視日志 
mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
             File: mysql_bin.000007
         Position: 5856
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
  • 6、在從伺服器啟動複制鍊路 
mysql>CHANGE MASTER TO
	->MASTER_HOST='master_host_ip',#主DB伺服器IP
	->MASTER_USER='repl',#主DB複制使用者
	->MASTER_PASSWORD='PassWord',#主DB複制使用者密碼
	->MASTER_LOG_FILE='mysql_bin.000007',#在主DB檢視日志檔案
	->MASTER_LOG_POS=5856;#在主DB檢視節點

mysql>show slave status \G;#檢視複制鍊路是否啟動
mysql>start slave;#啟動複制鍊路
mysql>show processlist; #可以看到從伺服器上建立的程序

  • 7、在從伺服器檢查複制鍊路狀态

    檢查以下兩個參數是否為Yes：

  • Slave_IO_Running
  • Slave_SQL_Running 
mysql>show slave status \G;
*************************** 1. row ***************************
             Slave_IO_Running: Yes					#YES表示成功
            Slave_SQL_Running: Yes					#YES表示成功
                Last_IO_Errno: 0					#IO錯誤代碼
                Last_IO_Error:						#IO錯誤提示
               Last_SQL_Errno: 0					#SQL錯誤代碼
               Last_SQL_Error:						#SQL錯誤提示

  • 8、在主伺服器檢視正在運作的程序

    可以看到Command:Binlog Dump

mysql> SHOW PROCESSLIST \G;
*************************** 2. row ***************************
     Id: 143
   User: repl
   Host: 192.168.10.1:55402
     db: NULL
Command: Binlog Dump
   Time: 9527
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 3. row ***************************

基于日志點的複制的

優點：

  • 是MySQL最早的複制技術，Bug相對較少
  • 對SQL查詢沒有任何權限
  • 故障處理比較容易

缺點：

  • 故障轉移時重新擷取新的住的日志點資訊比較困難

基于GTID的複制

MySQL5.6 時支援的模式

基于GTID的複制與基于日志的的複制有很大的差別

MySQL複制功能
MySQL複制功能

什麼是GTID

即全局事務ID，其保證為每一個在主上送出的事務在複制叢集中可以生成一個唯一的ID

GTID=source_id:transaction_id

使用了GTID後不可使用如下操作
  • create table …select
  • 在事務中使用create temporary table 建立臨時表使用關聯更新事務表和事非事務表

操作步驟

  • 1、在主DB伺服器上建立服務制賬号 
mysql>create user 'repl' @ 'IP段' identified by 'PassWord';
mysql>grant replication slave on *.* to 'repl' @ 'IP段';

  • 2、配置主資料庫伺服器

    my.cnf

log_bin = /usr/local/mysql/log/mysql-bin
server_id = 100
gtid_mode = on  #啟動gtid
enforce-gtid-consiste #強制gtid一緻性，保證事務安全
log-slave-updates = on # mysql5.7可以不用這個參數
  • 3、配置從資料庫伺服器 
server_id = 101
relay_log = /usr/local/mysql/log/relay_log
gtid_mode = on
enforce-gtid-consistency
log-slave-updates = on # mysql5.7可以不用這個參數
read_only = on  #建議
master_info_repository = TABLE #建議
realy_log_info_repository = TABLE #建議
  • 4、初始化從伺服器資料 
mysqldump --single-transaction --master-data --triggers --routines --all-database -uroot -p >> all2.sql;
 #會對表進行加鎖
xtrabackup --slave-info 

#scp all2.sql [email protected]:/root #拷貝備份檔案到從庫

# mysql -uroot -p < all2.sql #初始化從伺服器，導入後主從資料庫初始化是一緻的。
  • 5、啟動基于GTID的複制(記錄備份時最後的事務的GTID值) 
mysql>CHANGE MASTER TO MASTER_HOST = 'master_host_ip'
					MASTER_USER = 'repl'
					MASTER_PASSWORD = 'PassWord'
					MASTER_AUTO_POSITION = 1;

基于GTID複制的優缺點

優點：

  • 可以很友善的故障轉移
  • 從庫上不會丢失主庫上的任何修改

缺點：

  • 故障處理比較複雜
  • 對執行SQL有一定的限制

選擇複制模式要考慮的問題

  • 所使用的MySQL版本
  • 複制架構以及主從切換的方式
  • 所使用的高可用管理元件
  • 對應用的支援程度

MySQL複制拓撲

MySQL複制功能

下邊來了解一下MySQL常見的拓撲結構

1、一主多從的複制拓撲
MySQL複制功能

優點：

  • 配置簡單
  • 可以用多個從庫分擔讀負載

用途：

  • 為不同業務使用不同的從庫
  • 将一台從庫放到遠端的IDC，用于災備恢複
  • 分擔主庫的讀負載
2、主-主複制
MySQL複制功能

主備模式的主主複制模式：

有一台主伺服器對外提供服務，一台伺服器處于隻讀狀态，并且隻作為熱備使用。

在對外提供服務的主庫出現故障或者是計劃性的維護時才會進行切換，使原來的備庫成為主庫，而原來的主庫會成為新的備庫，并處理隻讀或者是下線狀态，維護完成後重新上線。

主備模式下的主-主複制的配置注意事項：

  • 確定兩台伺服器上的初始化資料相同
  • 確定兩台伺服器上已經啟動logbin并且有不同的server_id
  • 兩台伺服器上啟動log_slave_updates參數
  • 在初始化備庫上啟用read_only

主主模式的主主複制模式

兩個主同時對外提供服務，并不能分擔寫負載

主主模式下的主主複制缺點：

  • 産生資料沖突而造成複制鍊路中斷，耗費大量的時間排查問題
  • 容易造成資料丢失

如果一定要使用主主複制模式，應該注意一下事項：

  • 兩個主中所操作的表最好能夠分開
  • 使用下邊兩個參數控制自增ID的生成

auto_increment_increment = 2

auto_increment_offset = 1 | 2

3、擁有備庫的主-主複制拓撲
MySQL複制功能
4、級聯複制

可以避免分發主庫連接配接從庫過多而占用主庫過多的帶寬，使用分發主庫可以解決這個問題。

MySQL複制功能

MySQL複制性能優化

影響主從延遲的原因：

  • 主庫寫入二進制的日志時間
可以通過：控制主庫的事務大小，分割事務來解決
  • 二進制日志傳輸時間
可以通過：段使用MIXED日志格式或行設定set binlog_row_image=minimal來解決
  • 預設情況下隻有一個SQL線程，主上并發的修改在從上變成了串行
可以使用使用多線程複制（MySQL5.6）,在MySQL5.7中可以按照邏輯時鐘的方式來配置設定線程

MySQL5.7上使用多線程複制 

mysql>stop slave
mysql>set global slave_parallel_type = 'logical_clock'; #使用邏輯時鐘的方式複制
mysql>set global slave_parallel_workers=4; #設定多線程複制的數量
mysql>start slave;#啟動複制
mysql>show processlist; #檢視程序的數量
mysql>show variables like 'slave_parallel_type'; #檢視并發類型配置
mysql>show variables like 'slave_parallel_workers'; #檢視并發線程數量

MySQL複制常見問題處理

由于資料損壞或丢失所引起的主從複制錯誤

  • 主庫或從庫意外當機引起的錯誤，可以使用跳過二進制日志事件或者注入空事務的方式恢複中斷的複制鍊路再使用其他方法來對比主從伺服器上的資料。
  • 主庫上的二進制的日志損壞，隻能通過從庫中使用change master指令來重新指定
  • 備庫上的中繼日志的損壞
  • 在從庫上進行資料修改造成的主從複制錯誤，應該設定從庫時read_only
  • 不唯一的server_id或server_uuid（記錄在資料目錄中的auto.cnf檔案中）
  • max_allow_packet設定引起的主從複制錯誤

MySQL複制無法解決的問題

  • 分擔主資料庫的寫負載
  • 自動的故障轉移以及主從切換
  • 提供讀寫分離

參考：

https://www.cnblogs.com/happy4java/p/11206023.html

mysql 伺服器 二進制日志 資料庫
上一篇: 圖解Hbase之使用外置Zookeeper分布式叢集
下一篇: Hbase出現ServerNotRunningYetException的解決方案

繼續閱讀