mysql 主從伺服器配置

標籤：while   val   nts   process   命令列   oba   pack   ipaddress   always   

當遇到磁碟讀寫的事務鎖 導致的系統讀寫能力下降的問題，可以使用主從讀寫分離的模式進行最佳化。不過讀寫分離會導致資料讀取的延時性。

mysql的主從複製 主要依賴於資料庫的二級制檔案進行資料庫的同步功能。

參考連結：

http://blog.csdn.net/cutesource/article/details/5710645

http://blog.itpub.net/29870867/viewspace-1304862/

1.安裝mysql  我的master 用的是win10系統  mysql版本是5.6，虛擬機器 slave 系統為centos mysql 版本 5.5  （查看版本命令    mysql -V）

主從同步

2.配置master資料庫

在Master MySQL上建立一個使用者‘repl’，並允許其他Slave伺服器可以通過遠端存取Master，通過該使用者讀取二進位日誌，實現資料同步。

create user master_user;

//master_user使用者必須具有REPLICATION SLAVE許可權，除此之外沒有必要添加不必要的許可權，密碼為mysql。說明一下192.168.2.%，這個配置是指明repl使用者所在伺服器，這裡%是萬用字元，表示192.168.0.0-192.168.0.255的Server都可以以repl使用者登陸主伺服器。當然你也可以指定固定Ip。

GRANT REPLICATION SLAVE ON *.* TO ‘master_user‘@‘192.168.2.%‘ IDENTIFIED BY ‘mysql‘;

// 賦予許可權

grant all privileges on *.* to ‘master_user‘ @"192.168.2.%" identified by "mysql";

flush privileges;

 

找到MySQL安裝資料夾修改my.Ini檔案。mysql中有好幾種日誌方式，這不是今天的重點。我們只要啟動二進位日誌log-bin就ok。

在mysqld節點 添加

server-id=1
log-bin=master-bin
binlog-do-db=master_test// 是我們要同步的資料庫

重啟mysql

show master status;//  用來查看配置是否起效

 

由於開啟了binlog會導致磁碟的日誌寫滿，

清除10天的日誌

PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);

另外可以配置

expire_logs_days = 10

在運行時修改：
show binary logs;
show variables like ‘%log%‘;
set global expire_logs_days = 10;
清除之前可以採用相應的備份策略。

3.配置slave 資料庫

同樣找到mysqld節點 添加配置

server-id=2

重啟mysql服務

systemctl restart mysql

進入mysql命名模式

mysql -uroot -p123456

建立連結

CHANGE MASTER TO MASTER_HOST=‘192.168.2.220‘,MASTER_USER=‘master_user‘,MASTER_PASSWORD=‘mysql‘,MASTER_LOG_FILE=‘master-bin.000006‘,MASTER_LOG_POS=120;

開啟從伺服器

start slave;

檢查從伺服器狀態

show slave status\G;

 

發現報錯

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

show variables like ‘server_id‘;

發現從伺服器的seerver_id 也是1  則確認 my.conf 無效 ，手動修改 server_id

set global server_id=2;

show variables like ‘server_id‘;

並開啟 slave;

再次show slave status\G;

發現報錯

Got fatal error 1236 from master when reading data from binary log: ‘Slave can not handle replication events with the checksum that master is configured to log; the first event ‘master-bin.000006‘ at 120, the last event read from ‘.\master-bin.000006‘ at 120, the last byte read from ‘.\master-bin.000006‘ at 120.‘

這個錯誤一般出現在master5.6,slave在低版本的情況下。這是由於5.6使用了crc32做binlog的checksum。把master的設定從crc32改到none

修改master mysqld配置

binlog-checksum = none

重啟master主機

slave中重新啟動slave;

show slave status\G; 發現 Slave_IO_Running: Yes 標識 配置主從正確；

在master資料庫的 master_test 中建立一個表 發現 同步到了slave資料庫中~~~

通過以上，便可實現mysql的主從同步，接下來，開始讀寫分離的配置；

讀寫分離

架設資料庫代理

database proxy採用amoeba ，它的相關資訊可以查閱官方文檔

1.安裝amoeba

下載amoeba（1.2.0-GA）後解壓到本地（D:/amoeba），即完成安裝

2.配置amoeba

先配置proxy串連和與各後端mysql伺服器串連資訊，修改amoeba目錄中的conf/amoeba.xml

<server>
<!-- proxy server綁定的連接埠 -->
<property name="port">8066</property>

<!-- proxy server綁定的IP -->
<!--
<property name="ipAddress">127.0.0.1</property>
-->
<!-- proxy server net IO Read thread size -->
<property name="readThreadPoolSize">20</property>

<!-- proxy server client process thread size -->
<property name="clientSideThreadPoolSize">30</property>

<!-- mysql server data packet process thread size -->
<property name="serverSideThreadPoolSize">30</property>

<!-- socket Send and receive BufferSize(unit:K) -->
<property name="netBufferSize">128</property>

<!-- Enable/disable TCP_NODELAY (disable/enable Nagle‘s algorithm). -->
<property name="tcpNoDelay">true</property>

<!-- 對外驗證的使用者名稱 -->
<property name="user">root</property>

<!-- 對外驗證的密碼 -->

<property name="password">root</property>
</server>

以上是proxy提供給client的串連配置

 

<dbServer name="server1">

<!-- PoolableObjectFactory實作類別 -->
<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<property name="manager">defaultManager</property>

<!-- 真實mysql資料庫連接埠 -->
<property name="port">3306</property>

<!-- 真實mysql資料庫IP -->
<property name="ipAddress">192.168.2.220</property>
<property name="schema">master_test</property>

<!-- 用於登陸mysql的使用者名稱 -->
<property name="user">root</property>

<!-- 用於登陸mysql的密碼 -->

<property name="password">123456</property>

</factoryConfig>

<!-- ObjectPool實作類別 -->
<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
<property name="maxActive">200</property>
<property name="maxIdle">200</property>
<property name="minIdle">10</property>
<property name="minEvictableIdleTimeMillis">600000</property>
<property name="timeBetweenEvictionRunsMillis">600000</property>
<property name="testOnBorrow">true</property>
<property name="testWhileIdle">true</property>
</poolConfig>
</dbServer>

<dbServer name="server2">

<!-- PoolableObjectFactory實作類別 -->
<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<property name="manager">defaultManager</property>

<!-- 真實mysql資料庫連接埠 -->
<property name="port">3306</property>

<!-- 真實mysql資料庫IP -->
<property name="ipAddress">192.168.2.55</property>
<property name="schema">master_test</property>

<!-- 用於登陸mysql的使用者名稱 -->
<property name="user">master_user</property>

<!-- 用於登陸mysql的密碼 -->

<property name="password">mysql</property>

</factoryConfig>

<!-- ObjectPool實作類別 -->
<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
<property name="maxActive">200</property>
<property name="maxIdle">200</property>
<property name="minIdle">10</property>
<property name="minEvictableIdleTimeMillis">600000</property>
<property name="timeBetweenEvictionRunsMillis">600000</property>
<property name="testOnBorrow">true</property>
<property name="testWhileIdle">true</property>
</poolConfig>
</dbServer>

配置兩個server

以上是proxy與後端各mysql資料庫伺服器配置資訊

最後是讀寫分離配置

 

<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<property name="ruleConfig">${amoeba.home}/conf/rule.xml</property>
<property name="functionConfig">${amoeba.home}/conf/functionMap.xml</property>
<property name="ruleFunctionConfig">${amoeba.home}/conf/ruleFunctionMap.xml</property>
<property name="LRUMapSize">1500</property>
<property name="defaultPool">server1</property>

<property name="writePool">server1</property>
<property name="readPool">server2</property>

<!--
<property name="writePool">server1</property>
<property name="readPool">server1</property>
-->
<property name="needParse">true</property>
</queryRouter>

 

讀slave庫，寫master庫

到此配置完畢

3.啟動amoeba

命令列進入 /bin/amoeba.bat 執行

發生錯誤 錯誤: 必須設定環境變數“JAVA_HOME”，指向JDK的安裝路徑

由於 amoeba是基於java的，需要安裝java的運行庫

故而需要安裝 java jdk

http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

http://jingyan.baidu.com/article/870c6fc3257891b03fe4bef6.html

在上面的連結中下載，安裝

安裝並配置環境變數

完成後，再次 命令列進入 /bin/amoeba.bat 執行

log4j:WARN log4j config load completed from file:D:\amoeba\bin\..\conf\log4j.xml
log4j:WARN ip access config load completed from file:D:\amoeba\bin\../conf/access_list.conf
2017-03-23 15:39:15,381 INFO net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.

 

amoeba 資料庫代理成功搭建

開始測試讀寫分離

執行個體：

$mysql_server_name=‘192.168.2.220:8066‘; //改成自己的mysql資料庫伺服器

$mysql_username=‘root‘; //改成自己的mysql資料庫使用者名稱

$mysql_password=‘root‘; //改成自己的mysql資料庫密碼

$mysql_database=‘master_test‘; //改成自己的mysql資料庫名

$conn=mysql_connect($mysql_server_name,$mysql_username,$mysql_password) or die("error connecting") ; //串連資料庫

mysql_query("set names ‘utf8‘");

mysql_select_db($mysql_database); //開啟資料庫

$sql ="select * from master1 "; //SQL語句

$result = mysql_query($sql,$conn); //查詢
print_r($result);

 

查看mysql日誌 可以發現 讀操作 在slave主機的mysql日誌中有記錄，而在master主機中沒有日誌記錄，說明讀操作 只通過了slave資料庫

而寫操作，在master  中先有，而後 在slave中亦發生了寫庫操作

 

說明讀寫分離配置成功~~

 

mysql 主從伺服器配置