mysql AB複制搭建以及常見故障排查

             mysql AB複制搭建以及常見故障排查

MySQL主從複制(Master-Slave)也叫AB複制，Mysql作為目前世界上使用最廣泛的免費資料庫，相信所有從事系統運維的工程師都一定接觸過。但在實際的生産環境中，由單台Mysql作為獨立的資料庫是完全不能滿足實際需求的，無論是在安全性，高可用性以及高并發等各個方面。是以，一般來說都是通過主從複制（Master-Slave）的方式來同步資料，再通過讀寫分離（MySQL-Proxy）來提升資料庫的并發負載能力這樣的方案來進行部署與實施的。

如下圖所示：

<a href="http://s3.51cto.com/wyfs02/M02/12/51/wKioL1MDErPyUeK1AAO22EHGl5I963.jpg" target="_blank"></a>

拓撲圖畫的不是很好，大家見諒哈；今天我們來學習下如何搭建mysql的AB複制，其實呢很簡單，大家不要被它的表面所迷惑。

首先呢我先給大家描述下環境：

環境描述：

mysql主伺服器：系統rhel5.5；192.168.1.10；mysql呢是我用本機yum源安裝的，資料庫裡面什麼東西都沒有。

mysql從伺服器：系統rhel5.5；192.168.1.11；這個機器的mysql跟上面的一樣。

好了，環境描述完畢，下面給大家講解下步驟。

一、在主伺服器上操作

1.修改/etc/my.cnf

添加log-bin=mysql-bin     //啟用二進制日志

server-id=1              //資料庫ID号，為1時表示為Master,其中master_id必須為1到231之間的一個正整數值,每個同步伺服器都必須設定一個唯一的編号，否則同步就不能正常運作了;

2.啟動mysql服務

/etc/init.d/mysqld start

3.然後登入進去mysql資料庫

mysql -u root

授權給從mysql資料庫伺服器192.168.1.11；

mysql&gt; GRANT REPLICATION SLAVE ON *.* to 'ha'@'192.168.1.11'identified by ‘password’;

<a href="http://s3.51cto.com/wyfs02/M02/12/51/wKioL1MDFZ6ws-iiAABfJYJZBDY128.jpg" target="_blank"></a>

查詢主資料庫狀态

<a href="http://s3.51cto.com/wyfs02/M02/12/51/wKiom1MDFgzQWWRrAACwwLtRwzA644.jpg" target="_blank"></a>

記錄下 FILE 及 Position 的值，在後面進行從伺服器操作的時候需要用到。

二、配置從伺服器

跟上面一樣也是先修改下/etc/my.cnf

4.執行同步sql語句然後啟動slave同步程序

<a href="http://s3.51cto.com/wyfs02/M01/12/51/wKioL1MDFoyQGXQeAADOVbTNxS0635.jpg" target="_blank"></a>

5.主從同步檢查

<a href="http://s3.51cto.com/wyfs02/M00/12/51/wKiom1MDFv7xzpWKAAJTgdF2njo445.jpg" target="_blank"></a>

其中Slave_IO_Running 與Slave_SQL_Running 的值都必須為YES，才表明狀态正常。

如果主伺服器已經存在應用資料，則在進行主從複制時，需要做以下處理：

(1)主資料庫進行鎖表操作，不讓資料再進行寫入動作

mysql&gt; FLUSH TABLES WITH READ LOCK;

(2)檢視主資料庫狀态

mysql&gt; show master status;

(3)記錄下 FILE 及 Position 的值。

将主伺服器的資料檔案（整個/opt/mysql/data目錄）複制到從伺服器，建議通過tar歸檔壓縮後再傳到從伺服器解壓。

(4)取消主資料庫鎖定

mysql&gt; UNLOCK TABLES

三、驗證主從AB複制效果

在主伺服器上建立一個庫

<a href="http://s3.51cto.com/wyfs02/M02/12/51/wKiom1MDF_ezEZDrAABZ6YfWqLE188.jpg" target="_blank"></a>

在從伺服器上檢視下

<a href="http://s3.51cto.com/wyfs02/M01/12/51/wKiom1MDGDvDmZO8AACB_FAadXs797.jpg" target="_blank"></a>

由此，整個MySQL主從複制的過程就完成了，接下來，我們進行MySQL讀寫分離的安裝與配置。

常見故障總結以及處理方法

1.可能是/usr/local/mysql/data/***.pid檔案沒有寫的權限

解決方法 ：給予權限，執行 “chown -R mysql:mysql /var/data” “chmod -R 755 /usr/local/mysql/data”  然後重新啟動mysqld！

2.可能程序裡已經存在mysql程序

解決方法：用指令“ps -ef|grep mysqld”檢視是否有mysqld程序，如果有使用“kill -9  程序号”殺死，然後重新啟動mysqld！

3.可能是第二次在機器上安裝mysql，有殘餘資料影響了服務的啟動。

解決方法：去mysql的資料目錄/data看看，如果存在mysql-bin.index，就趕快把它删除掉吧，它就是罪魁禍首了。本人就是使用第三條方法解決的 ！http://blog.rekfan.com/?p=186

4.mysql在啟動時沒有指定配置檔案時會使用/etc/my.cnf配置檔案，請打開這個檔案檢視在[mysqld]節下有沒有指定資料目錄(datadir)。 

解決方法：請在[mysqld]下設定這一行：datadir = /usr/local/mysql/data

5.skip-federated字段問題

解決方法：檢查一下/etc/my.cnf檔案中有沒有沒被注釋掉的skip-federated字段，如果有就立即注釋掉吧。 

###

主要是從伺服器會出現錯誤

由于一些錯誤操作或者中途改變了master，而導緻CHANGE MASTER指令後SLAVE服務無法啟動,系統報錯如下:

Could not initialize master info structure; more error messages can be found in the MySQL error log.

無法初始化master info結構;MySQL錯誤日志記錄了更詳細的錯誤資訊.

兩種解決方法:

第一種：

1.檢視MySQL錯誤日志,檢視原因.

如:同步的上一個Position是多少.

很多情況下無法啟動服務是由于mysql識别的同步始終停留在上一個Position上.

2.檢視master.info和relay-log.info

master.info 記錄MASTER相關資訊

mysql-bin.000030

391156558

192.168.1.1

user_rep

rep123

3306

60

relay-log.info 記錄目前同步日志資訊

235

3.停止myslq服務,删除master.info和relay-log.info

# service mysql stop

/data/datafile/ # rm master.info

/data/datafile/ # rm relay-log.info

4.啟動mysql服務

# service mysql start

5.重新CHANGE MASTER,重新啟動SLAVE服務.

問題應該就可以解決了.

第二種方法比較簡單，也常用

mysql&gt; slave stop;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql&gt; reset slave;

Query OK, 0 rows affected,(0.00 sec)

#############

主從mysql不同步首先在Master上用 

show processlist;   檢視下程序是否Sleep太多。發現很正常。

show master status; 也正常。

再跑到Slave上檢視

show slave status;

錯誤提示：

Error 'Duplicate entry '1' for key 1' on query. Default database: 'movivi1'. Query: 'INSERT INTO `v1vid0_user_samename` VALUES(null,1,'123','11','4545','123')'

Slave_SQL_Running 為 NO

Seconds_Behind_Master 為 (null)

可見是Slave不同步

解決： 

stop slave;

set global sql_slave_skip_counter =1 ;

start slave;

########

1.網絡的延遲

由于mysql主從複制是基于binlog的一種異步複制，通過網絡傳送binlog檔案，理所當然網絡延遲是主從不同步的絕大多數的原因，特别是跨機房的資料同步出現這種幾率非常的大，是以做讀寫分離，注意從業務層進行前期設計。

2.主從兩台機器的負載不一緻

由于mysql主從複制是主上面啟動1個io線程，而從上面啟動1個sql線程和1個io線程，當中任何一台機器的負載很高，忙不過來，導緻其中的任何一個線程出現資源不足，都将出現主從不一緻的情況。

3.max_allowed_packet設定不一緻

主上面設定的max_allowed_packet比從大，當一個大的sql語句，能在主上面執行完畢，從上面設定過小，無法執行，導緻的主從不一緻。

4.key自增鍵開始的鍵值跟自增步長設定不一緻引起的主從不一緻。

5.mysql異常當機情況下，如果未設定sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出現binlog或者relaylog檔案出現損壞，導緻主從不一緻。

6.mysql本身的bug引起的主從不同步。

7.版本不一緻，特别是高版本是主，低版本為從的情況下，主上面支援的功能，從上面不支援該功能。

以上是我遇到的一些主從不同步的情況。或許還有其他的一些不同步的情況，請說出你所遇到的主從不一緻的情況。

基于以上情況，先保證max_allowed_packet，自增鍵開始點和增長點設定一緻，再者犧牲部分性能在主上面sync開啟_binlog，對于采用innodb的庫，推薦配置下面的内容

1.innodb_flush_logs_at_trx_commit = 1

2.innodb-support_xa = 1 # Mysql 5.0 以上

3.innodb_safe_binlog      # Mysql 4.0

同時在從上面推薦加入下面兩個參數

1.skip_slave_start

2.read_only

重新CHANGE MASTER,重新啟動SLAVE服務.這樣就搞定了O(∩_∩)O

本文轉自Devin 51CTO部落格，原文連結:http://blog.51cto.com/devingeng/1360162