MySQL 5.5 多實例主從複製非同步、半同步實踐（1）

05-15

1）獲取MySQL軟體包
2）創建MySQL多實例的數據文件目錄
3）創建MySQL多實例的配置文件
4）添加my.cnf配置文件
5）創建MySQL多實例的啟動文件
6）配置MySQL多實例的文件許可權
7）設置環境變數
8）初始化故障排錯集錦
9）MySQL多實例啟動故障排錯說明
10）配置及管理MySQL多實例資料庫

1.配置MySQL多實例資料庫開機自啟動
2.登錄MySQL測試

11）提高MySQL的安全
12）master 創建授權用戶
13）配置master
14）master重啟MySQL服務
15）配置slave
16）啟動複製並查看狀態
17）主從複製測試

六、Mysql 主從複製（半同步）

1、同步複製
2、半同步複製
3、非同步與半同步異同
4、具體配置

1） master

1.安裝插件
2.修改配置文件

2）slave:

1.安裝插件
2.修改配置文件
3.重新啟動服務

3）查看一下狀態

1.master
2.slave

4）測試一下

1.master

5）模擬一下故障

1.slave
2.master

6）查看一下狀態

1.master

七、Mysql 複製工具

1、percona-toolkit簡介
2、安裝percona-toolkit
3、簡單使用

1）常用工具

1.伺服器摘要
2.伺服器磁碟監測
3.mysql服務狀態摘要
4.慢查詢日誌分析統計
5.MySQL 慢查詢的相關參數解釋
6.主從狀態監測

一、Mysql 基礎知識

1、mysql日誌類型

1）二進位日誌

說明：默認開啟，精確的記錄了用戶對資料庫中的數據進行操作的命令和操作的數據對象。

1.二進位日誌文件作用

提供了增量備份的功能提供了數據基於時間點的恢復，這個恢復的時間點可以由用戶控制為mysql的複製架構提供基礎，將這主伺服器的二進位日誌複製到從伺服器上並執行同樣的操作，就可將數據進行同步

2.二進位日誌格式

基於語句 statement

基於SQL語句的複製(statement-based replication,SBR)

每一條會修改數據的sql都會記錄到master的binlog中，slave在的時候sql進程會解析成和原來master端相同的sql再執行。優點：在Statement模式下首先就是解決了row模式下的缺點，不需要記錄記錄每一行日誌的變化，減少了binlog日誌量，節省了I/O以及存儲資源，提高性能。因為它們只需要記錄在master上所執行的語句的細節以及執行語句時候的上下文信息。缺點：在Statement模式下，由於它記錄的執行語句，所以，為了讓這些語句在slave端也能正確執行，那麼它還必須記錄每條語句在執行的時候的一些相關信息，也就是上下文信息，以保證所有語句在slave端被執行的時候能夠得到和在master端執行時候的結果。另外，由於MySQL現在發展較快，很多的新功能不斷的加入，使MySQL的遇到了不小的挑戰，自然的時候涉及到越複雜的內容，bug也就越容易出現。在Statement中，目前已經發現不少情況會造成MySQL的出現問題，主要是修改數據的時候使用了某些特定的函數或者功能的時候會出現。

基於行 row

基於行的複製(row-based replication,RBR)

日誌中會記錄成每一行數據被修改成的形式，然後在slave端再對相同的數據進行修改，只記錄要修改的數據，只有value，不會有sql多表關聯的情況。優點：在row模式下，bin-log中可以不記錄執行的sql語句的上下文相關信息，僅僅需要記錄哪一條記錄被修改了，修改成什麼信樣了，所以row的日誌內容會非常清楚的記錄下每一行數據修改的細節，非常容易理解。而且不會出現在某些特定情況下的存儲過程和function，以及trigger的調用無法被正確問題。缺點：在row模式下，所有執行的語句當記錄到日誌中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日誌內容。

混合方式 mixed

混合模式複製(mixed-based replication,MBR)

實際上就是前兩種模式的結合。在mixed模式下，MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日誌形式，也就是在Statement和row之間選擇一種。Statement還是記錄執行的語句。而新版本的MySQL中對row模式也做了優化，並不是所有的修改都會以row模式來記錄，比如遇到表結構變更的時候就會以Statement模式來記錄，如果sql語句確實是update或者delete等修改數據的語句，那麼還是會記錄所有行的變更。

3.二進位日誌事件

position 基於位置datetime 基於時間

4.二進位日誌查看與刪除

mysql>show master status; 查看當前正在使用的二進位日誌 mysql>show binlog events in mysql-bin.000001; 查看二進位日誌記錄的事件[from position] mysql>flush logs; 二進位日誌滾動 mysql>show binary logs; 查看所有二進位日誌 mysql>purge binary logs to mysql-bin.000003; 刪除二進位日誌

5.查看二進位日誌的命令

mysqlbinlog 相關選項，

--start-position #開始位置 --stop-position #結束位置 --start-datetime yyyy-mm-dd hh:mm:ss; #開始時間 --stop-datetime ; #結束時間

6.配置MySQL的主配置文件

sql_log_bin = {ON|OFF} #用於控制二進位日誌信息是否記錄進日誌文件。默認為ON，表示啟用記錄功能。用戶可以在會話級別修改此變數的值，但其必須具有SUPER許可權binlog_cache_size = 32768 #默認值32768 Binlog Cache 用於在打開了二進位日誌（binlog）記錄功能的環境，是 MySQL 用來提高binlog的記錄效率而設計的一個用於短時間內臨時緩存binlog數據的內存區域。一般來說，如果我們的資料庫中沒有什麼大事務，寫入也不是特別頻繁，2MB～4MB是一個合適的選擇。但是如果我們的資料庫大事務較多，寫入量比較大，可與適當調高binlog_cache_size。同時，我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設置的binlog_cache_size是否足夠，是否有大量的binlog_cache由於內存大小不夠而使用臨時文件（binlog_cache_disk_use）來緩存了binlog_stmt_cache_size = 32768 #當非事務語句使用二進位日誌緩存，但是超出binlog_stmt_cache_size時，使用一個臨時文件來存放這些語句 log_bin = mysql-bin #指定binlog的位置，默認在數據目錄下binlog-format = {ROW|STATEMENT|MIXED} #指定二進位日誌的類型，默認為MIXED。如果設定了二進位日誌的格式，卻沒有啟用二進位日誌，則MySQL啟動時會產生警告日誌信息並記錄於錯誤日誌中。 sync_binlog = 10 #設定多久同步一次二進位日誌至磁碟文件中，0表示不同步，任何正數值都表示對二進位每多少次寫操作之後同步一次。當autocommit的值為1時，每條語句的執行都會引起二進位日誌同步，否則，每個事務的提交會引起二進位日誌同步 max_binlog_cache_size = {4096 .. 18446744073709547520} #二進定日誌緩存空間大小，5.5.9及以後的版本僅應用於事務緩存，其上限由max_binlog_stmt_cache_size決定。 max_binlog_stmt_cache_size = {4096 .. 18446744073709547520} #二進位日誌緩存空間大小，5.5.9及以後的版本僅應用於事務緩存 expire_log_days = {0..99} #設定二進位日誌的過期天數，超出此天數的二進位日誌文件將被自動刪除。默認為0，表示不啟用過期自動刪除功能。如果啟用此功能，自動刪除工作通常發生在MySQL啟動時或FLUSH日誌時

註：一般建議將binlog日誌與數據文件分開存放，不但可以提高mysql性能，還可以增加安全性！

2）事務日誌

說明：詳細的記錄了在什麼時間發生了什麼時候，在哪個時間對哪些數據進行了改變，能後實現事件的重放，一般只記錄對數據進行改變的操作，對於讀操作一般不進行記錄。

事物日誌為資料庫伺服器實現以下功能：

(1).將隨機IO轉換為順序IO，大大的提高了資料庫的性能，存儲的數據可能存在在磁碟的不同位置，降低了數據的讀取和操作性能。轉換為順序IO的原理為，先將數據存放在日誌文件中，然後由RDBSM的後台將日誌中的數據存放到磁碟上，這樣就保證了存儲的數據是連續的。

(2).為事件重放提供基礎，事務日誌詳細的記錄了時間發生的時間以及操作的數據對象，事務進程可以根據這些信息進行時間重放。

默認的事務日誌文件有兩個，位於數據目錄下以ibdata+number結尾的數字，我們可以對事務日誌的位置、文件大小、增長方式進行定義，定義的方法如下：

這裡以使用支持事務的Innodb存儲引擎為例，配置mysql的主配置文件：

innodb_data_home_dir = /mydata/data #InnoDB所有共享表空間數據文件的目錄路徑，默認在數據目錄下innodb_data_file_path = ibdata1:1024M #指定InnoDB的各個數據文件及其大小，文件多於一個時彼此間用分號隔開 innodb_data_file_path = ibdata2:50M:autoextend #定義數據大小的增長方式innodb_log_group_home_dir = /mydata/data #設定InnoDB重要日誌文件的存儲目錄。在預設使用InnoDB日誌相關的所有變數時，其默認會在數據目錄中創建兩個大小為5MB的名為ib_logfile0和ib_logfile1的日誌文件innodb_log_files_in_group = {2 .. 100} #設定日誌組中日誌文件的個數。InnoDB以循環的方式使用這些日誌文件。默認值為2innodb_log_file_size = {108576 .. 4294967295} #設定日誌組中每個日誌文件的大小，單位是位元組，默認值是5MB。較為明智的取值範圍是從1MB到緩存池體積的1/n，其中n表示日誌組中日誌文件的個數。日誌文件越大，在緩存池中需要執行的檢查點刷寫操作就越少，這意味著所需的I/O操作也就越少，然而這也會導致較慢的故障恢復速度innodb_log_buffer_size = {262144 .. 4294967295} #設定InnoDB用於輔助完成日誌文件寫操作的日誌緩衝區大小，單位是位元組，默認為8MB。較大的事務可以藉助於更大的日誌緩衝區來避免在事務完成之前將日誌緩衝區的數據寫入日誌文件，以減少I/O操作進而提升系統性能。因此，在有著較大事務的應用場景中，建議為此變數設定一個更大的值

3）錯誤日誌

說明：在對應的數據目錄中，以主機名+.err命名的文件，錯誤日誌記錄的信息類型：

記錄了伺服器運行中產生的錯誤信息記錄了服務在啟動和停止是所產生的信息在從伺服器上如果啟動了複製進程的時候，複製進程的信息也會被記錄記錄event錯誤日誌

配置mysql的主配置文件：

log_error = /mydata/data/mysql.test.com.err #指定錯誤日誌的位置，默認是在數據目錄下，這個位置mysql用戶必須有寫許可權log_warning = {0|1} #默認開啟，伺服器運行中的警告日誌也會記錄在錯誤日誌中

4）一般查詢日誌

說明：對除了慢查日誌中記錄的查詢信息都將記錄下來,這將對伺服器主機產生大量的壓力，所以對於繁忙的伺服器應該關閉這個日誌.

與查詢日誌相關的變數

log = {ON|OFF} #是否啟用查詢日誌，該指令在mysq5.6中已廢棄general_log = {ON|OFF} #啟動或關閉查詢日誌，默認是關閉的general_log_file = /mydata/data/mysql.log #指定查詢日誌的位置，默認在數據目錄下log_output = {TABLE|FILE|NONE} #指定存放查詢日誌的位置，可以放在文件中，也可以放在資料庫的表中，放在表中比放在文件中更容易查看

5）中繼日誌

說明：主要是在mysql伺服器的中從架構中的從伺服器上用到的，當從伺服器想要和主伺服器進行數據的同步時，從伺服器將主伺服器的二進位日誌文件拷貝到己的主機上放在中繼日誌中，然後調用SQL線程按照中繼日誌文件中的二進位日誌文件執行就可達到數據的同步

開啟的方法：（只在從伺服器上開啟）

配置mysql的主配置文件：

relay-log = file_name #指定中繼日誌的位置和名字，默認為host_name-relay-bin。也可以使用絕對路徑，以指定非數據目錄來存儲中繼日誌 relay-log-index = file_name #指定中繼日誌的名字的索引文件的位置和名字，默認為數據目錄中的host_name-relay-bin.indexrelay-log-info-file = file_name #設定中繼服務用於記錄中繼信息的文件，默認為數據目錄中的relay-log.inforelay_log_purge = {ON|OFF} #設定對不再需要的中繼日誌是否自動進行清理。默認值為ONrelay_log_space_limit = 0 #設定用於存儲所有中繼日誌文件的可用空間大小。默認為0，表示不限定。最大值取決於系統平台位數max_relay_log_size = {4096..1073741824} #設定從伺服器上中繼日誌的體積上限，到達此限度時其會自動進行中繼日誌

6）慢查詢日誌

說明：默認為關閉狀態，記錄下來查詢時間超過設定時長的查詢，這些查詢日誌將被慢查日誌記錄下來

配置mysql的主配置文件：

slow_query_log = {ON | OFF} #是否開啟慢慢查詢日誌，默認是關閉的 slow_query_log_file = /mydata/data/mysql-slow.log #慢查詢日誌的存放位置，默認在數據目錄下log_query_time = 10 #定義默認的時長，默認時長為10秒log_query_not_using_indexes = {ON|OFF} #設定是否將沒有使用索引的查詢操作記錄到慢查詢日誌 log_output = {TABLE|FILE|NONE} #定義一般查詢日誌和慢查詢日誌的保存方式，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的組合(用逗號隔開)，默認為FILE。如果組合中出現了NONE，那麼其它設定都將失效，同時，無論是否啟用日誌功能，也不會記錄任何相關的日誌信息

MySQL 5.5 多實例 主從複製非同步、半同步實踐（1）

目錄

一、Mysql 基礎知識

1、mysql日誌類型

1）二進位日誌

1.二進位日誌文件作用

2.二進位日誌格式

3.二進位日誌事件

4.二進位日誌查看與刪除

5.查看二進位日誌的命令

6.配置MySQL的主配置文件

2）事務日誌

3）錯誤日誌

4）一般查詢日誌

5）中繼日誌

6）慢查詢日誌

MySQL 5.5 多實例主從複製非同步、半同步實踐（1）