MySQL資料庫的主從同步實作及應用

讀寫分離（read/write splitting）讓主資料庫處理事務性增、改、删操作（insert、update、delete），從資料庫處理select查詢操作，

在大規模高并發的系統中，讀寫分離是提高性能很重要的手段。

讀寫分離實作的基礎就是mysql的主從同步，是以對于主從同步的管理也就顯得非常重要。

（1）mysql主從同步的作用

1.資料分布

2.負載平衡(load balancing)

3.備份(copy)

4.高可用性(high availability)和容錯

（2）主從同步的詳細過程如下：

1. 主伺服器驗證連接配接。

2. 主伺服器為從伺服器開啟一個線程。

3. 從伺服器将主伺服器日志的偏移位告訴主伺服器。

4. 主伺服器檢查該值是否小于目前二進制日志偏移位。

5. 如果小于，則通知從伺服器來取資料。

6. 從伺服器持續從主伺服器取資料，直至取完，這時，從伺服器線程進入睡眠，主伺服器線程同時進入睡眠。

7. 當主伺服器有更新時，主伺服器線程被激活，并将二進制日志推送給從伺服器，并通知從伺服器線程進入工作狀态。

8. 從伺服器sql線程執行二進制日志，随後進入睡眠狀态。

簡單學習一個典型的mysql主從同步的搭建過程。

（1）主從同步環境介紹

作業系統環境：centos 64 bit

mysql版本：mysql 5.1

主伺服器的ip：192.168.106.1

從伺服器的ip：192.168.106.2

（2）在主伺服器上建立同步帳号

在設定權限的時候不要将密碼設定過于簡單：

1

2

<code>grant</code> <code>replication slave,file </code><code>on</code> <code>*.* </code><code>to</code> <code>'replication'</code><code>@</code><code>'192.168.106.%'</code> <code>identified </code><code>by</code> <code>'mysqlpassword'</code><code>;</code>

<code>flush </code><code>privileges</code><code>;</code>

　　

（3）從伺服器配置檔案的更改

注意：

1. server-id這一項需要認真檢查，一定不能和主伺服器沖突了，不然到時候會出現莫名其妙的問題，因為同步的時候會會根據server-id做判斷，如果server-id一樣就不進行同步了，不然可能會導緻死循環(主主同步或者環狀同步的時候)。

2.這裡要使用replicate-wild-ignore-table參數，而不是用replicate-do-db或者replicate-ignore-db來過濾需要同步的資料庫和不需要同步的資料庫。

幾個原因：

replicate-wild-ignore-table參數能同步所有跨資料庫的更新，比如replicate-do-db或者replicate-ignore-db不會同步類似

<code>use mysql;</code>

<code>update</code> <code>test.aaa </code><code>set</code> <code>amount=amount+10;</code>

replicate-wild-ignore-table=mysql.%在以後需要添加同步資料庫的時候能友善添加而不需要重新啟動從伺服器的資料庫，因為以後很可能需要同步其他的資料庫。

3.auto_increment_increment和auto_increment_offset參數，這兩個參數一般用在主主同步中，用來錯開自增值，防止鍵值沖突。

4.--slave-skip-errors參數，最好不要使用這些跳過錯誤的參數，當你使用這些參數時候，mysql會忽略那些錯誤，這樣會導緻你的主從伺服器資料不一緻。

（4）從主伺服器得到一個快照版本

如果你的是myisam或者既有myisam又有innodb的話就在主伺服器上使用如下指令導出伺服器的一個快照：

隻有innodb的話就是用如下指令：

這裡需要注意幾個參數的使用：

--single-transaction 這個參數隻對innodb适用。

--databases 後面跟除mysql以後的其他所有資料庫的庫名，我這裡隻有一個test庫。

--master-data 參數會記錄導出快照時候的mysql二進制日志位置，一會會用到。

（5）将快照版本還原到從伺服器上

mysqldump -uroot -p -h 192.168.106.2 test &lt; db.sql

将快照版本還原到從伺服器上以後，此時從伺服器上的資料和主伺服器的資料是一緻的。

（6）在從伺服器上使用change master從主伺服器上同步

使用grep指令查找到二進制日志的名稱以及位置

# grep -i "change master" db.sql

-- change master to master_log_file='mysql-bin.000006', master_log_pos=106;

生成change master語句，然後在從上執行

stop slave; 

change master to master_host='192.168.106.1',master_user='replication',master_password='123456',master_log_file='mysql-bin.000006', master_log_pos=106;

start slave;

（7）這樣就完成了主從同步的搭建，最後使用

show slave status;

檢視slave_io_running和slave_sql_running的狀态，如果都為yes，就大功告成了。

注意不要将同步的資訊寫入配置檔案中，不友善管理，尤其是有變動需要重新開機。

這裡介紹一些管理mysql主從同步的指令：

（1）停止mysql同步

3

<code>stop slave io_thread; #停止io程序</code>

<code>stop slave sql_thread; #停止sql程序</code>

<code>stop slave; #停止io和sql程序</code>

（2）啟動mysql同步

<code>start slave io_thread; #啟動io程序</code>

<code>start slave sql_thread; #啟動sql程序</code>

<code>start slave; #啟動io和sql程序</code>

（3）重置mysql同步

<code>reset slave;</code>

用于讓從屬伺服器忘記其在主伺服器的二進制日志中的複制位置, 它會删除master.info和relay-log.info檔案，以及所有的中繼日志，并啟動一個新的中繼日志,當你不需要主從的時候可以在從上執行這個操作。不然以後還會同步，可能會覆寫掉你的資料庫。

（4）檢視mysql同步狀态

<code>show slave status;</code>

這個指令主要檢視slave_io_running、slave_sql_running、seconds_behind_master、last_io_error、last_sql_error這些值來監控複制的狀态。

（5）臨時跳過mysql同步錯誤

經常會出現mysql主從同步遇到錯誤的時候，比如一個主鍵沖突等，

那麼就需要在確定那一行資料一緻的情況下臨時的跳過這個錯誤，使用指令：

<code>sql_slave_skip_counter = n，</code>

n是表示跳過後面的n個事件,比如我跳過一個事件的操作如下：

<code>stop slave;</code>

<code>set</code> <code>global</code> <code>sql_slave_skip_counter=1;</code>

<code>start slave;</code>

（6）從指定位置重新同步

有的時候主從同步有問題了以後，需要從log位置的下一個位置進行同步，相當于跳過那個錯誤，

這時候也可以使用change master指令來處理，隻要找到對應的log位置就可以,比如：

4

5

6

7

8

<code>change master </code><code>to</code> <code>master_host=</code><code>'192.168.106.1主庫位址'</code><code>,</code>

<code>master_user=</code><code>'replication'</code><code>,</code>

<code>master_password=</code><code>'mysqlpassword'</code><code>,</code>

<code>master_log_file=</code><code>'mysql-bin.000006'</code><code>,</code>

<code>master_log_pos=106;</code>

不要亂使用sql_slave_skip_counter指令。

這個指令跳過之後很可能會導緻你的主從資料不一緻，一定要先将指定的錯誤記錄下來，然後再去檢查資料是否一緻，尤其是核心的業務資料。

使用replicate-wild-ignore-table選項而不要使用replicate-do-db或者replicate-ignore-db。

原因已經在上面做了說明。

将主伺服器的日志模式調整成mixed。

每個表都加上主鍵，主鍵對資料庫的同步會有影響尤其是居于row複制模式。

（1）忽略錯誤後，繼續同步

該方法适用于主從庫資料相差不大，或者要求資料可以不完全統一的情況，資料要求不嚴格的情況 

解決：

#表示跳過一步錯誤，後面的數字可變  set global sql_slave_skip_counter =1;  start slave;  之後再用mysql&gt; show slave status\g 檢視：  slave_io_running: yes  slave_sql_running: yes  ok，現在主從同步狀态正常。

（2）重新做主從，完全同步

該方法适用于主從庫資料相差較大，或者要求資料完全統一的情況 

解決步驟如下：

1.先進入主庫，進行鎖表，防止資料寫入  使用指令：  mysql&gt; flush tables with read lock;  注意：該處是鎖定為隻讀狀态，語句不區分大小寫 2.進行資料備份  #把資料備份到mysql.bak.sql檔案  [root@server01 mysql]#mysqldump -uroot -p -hlocalhost &gt; mysql.bak.sql  這裡注意一點：資料庫備份一定要定期進行，可以用shell腳本或者python腳本，都比較友善，確定資料萬無一失。 3.檢視master 狀态 mysql&gt; show master status; +-------------------+----------+--------------+-------------------------------+  | file | position | binlog_do_db | binlog_ignore_db |  | mysqld-bin.000001 | 3260 | | mysql,test,information_schema |  1 row in set (0.00 sec) 4.把mysql備份檔案傳到從庫機器，進行資料恢複 #使用scp指令  [root@server01 mysql]# scp mysql.bak.sql [email protected]:/tmp/ 5.停止從庫的狀态  mysql&gt; stop slave; 6.然後到從庫執行mysql指令，導入資料備份  mysql&gt; source /tmp/mysql.bak.sql 7.設定從庫同步，注意該處的同步點，就是主庫show master status資訊裡的| file| position兩項  change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260; 8.重新開啟從同步  9.檢視同步狀态  mysql&gt; show slave status\g 檢視：  slave_sql_running: yes