mysql5.6.13通用二進位制格式安裝並使用amoeba實現對mysql5.6資料庫讀寫分離

鄭子明發表於2016-01-27

proxy 192.168.8.39

master 192.168.8.40
slave 192.168.8.20

一、安裝mysql-5.6.13伺服器

安裝包：
mysql-5.6.13-linux-glibc2.5-x86_64.tar.gz

新增mysql資料庫使用者

# groupadd -r mysql
# useradd -g mysql -r -s /sbin/nologin mysql

# tar xf mysql-5.6.13-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
# cd /usr/local
# ln -sv mysql-5.6.13-linux-glibc2.5-x86_64 mysql
# cd mysql
改變mysql安裝檔案的陣列和屬主
# chown -R root.mysql ./*

建立資料庫和二進位制檔案目錄並賦權

# mkdir /data/mydata -pv
# chown -R mysql.mysql /data/mydata

主伺服器
# mkdir /data/binlogs
# chown -R mysql.mysql /data/binlogs

從伺服器
# mkdir /data/relaylogs
# chown -R mysql.mysql /data/relaylogs

執行安裝

# scripts/mysql_install_db --datadir=/data/mydata --user=mysql

將服務指令碼拷貝到啟動項
# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
# chkconfig --add mysqld
驗證
# chkconfig --list mysqld

將mysql5.5的配置檔案拷貝到該主機中

scp /etc/my.cnf 192.168.8.40:/etc/

編輯my.cnf檔案
# vim /etc/my.cnf

加入
innodb_file_per_table = ON
datadir = /data/mydata
log-bin=/data/binlogs/master-bin
binlog_format=row
server-id = 200

將mysql命令加入到環境變數中
# vim /etc/profile.d/mysql.sh

export PATH=/usr/local/mysql/bin:$PATH

修改主機名

192.168.8.40 master.oadb.com master
192.168.8.20 slave.oadb.com slave

# hostname master.oadb.com
# scp /etc/hosts 192.168.8.20:/etc/

二、簡單主從模式配置步驟(必須要mysql5.6)

1、配置主從節點的服務配置檔案

1.1、配置master節點：
[mysqld]
binlog-format=ROW
log-bin=master-bin
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=4
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=100
report-port=3306
port=3306
log-bin=/data/binlogs/master-bin
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=master.oadb.com

1.2、配置slave節點：
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=4
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=11
report-port=3306
port=3306
log-bin=/data/binlogs/master-bin
relay-log=/data/relaylogs/relay-bin
datadir=/data/mydata
socket=/tmp/mysql.sock
report-host=slave.oadb.com

2、建立複製使用者(主伺服器執行)

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';

mysql> FLUSH PRIVILEGES;

========================================================================================

示例說明：
mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@172.16.100.7 IDENTIFIED BY 'replpass';

172.16.100.7是從節點伺服器；如果想一次性授權更多的節點，可以自行根據需要修改；

3、為備節點提供初始資料集

鎖定主表，備份主節點上的資料，將其還原至從節點；如果沒有啟用GTID，在備份時需要在master上使用show master status命令檢視二進位制日誌檔名稱及事件位置，以便後面啟動slave節點時使用。

4、啟動從節點的複製執行緒

如果啟用了GTID功能，則使用如下命令：
mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.40',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_AUTO_POSITION=1;

mysql> 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).

意思是說主伺服器和從伺服器的server-id重複

通過檢視my.cnf檔案發現 server-id 沒有重複
在資料庫上檢視引數，確實有重複，重啟主伺服器，通過引數檢視沒有問題，再次show slave status正常
mysql> show global variables like '%server%';

沒啟用GTID，需要使用如下命令：
slave> CHANGE MASTER TO MASTER_HOST='172.16.100.6',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_LOG_FILE='master-bin.000003',
-> MASTER_LOG_POS=1174;

5.驗證：通過在主服務上建立庫、表檢視從伺服器是否能夠獲取響應資料

三、安裝amaoeba代理伺服器

1.安裝jdk1.6.0.31(軟體包jdk-6u31-linux-x64-rpm.bin)

# cp jdk-6u31-linux-x64-rpm.bin /usr/local/
# cd /usr/local
# chmod +x jdk-6u31-linux-x64-rpm.bin
# ./jdk-6u31-linux-x64-rpm.bin

加入到環境變數

# vi /etc/profile.d/java.sh
加入
export JAVA_HOME=/usr/java/latest
export PATH=$JAVA_HOME/bin:$PATH

驗證jdk安裝是否成功
# java -version

2.安裝amoeba(軟體包amoeba-mysql-binary-2.2.0.tar.gz)

# mkdir /usr/local/amoeba-mysql-2.2.0
# tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba-mysql-2.2.0
建立連結
# ln -sv amoeba-mysql-2.2.0 amoeba

主服務上新增專門的amoeba root使用者
mysql> GRANT ALL ON *.* TO 'root'@'192.168.%.%' IDENTIFIED BY 'root.pass';
mysql> FLUSH PRIVILEGES;

從伺服器可以看到使用者可以被讀取到
mysql> show grants for 'root'@'192.168.%.%';
+------------------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.%.% |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.%.%' IDENTIFIED BY PASSWORD '*79F802E6455F5621E94D5157B3A377363E660552' |
+------------------------------------------------------------------------------------------------------------------------+

新增到環境變數中
# vim /etc/profile.d/amoeba.sh

export AMOEBA_HOME=/usr/local/amoeba
export PATH=$AMOEBA_HOME/bin:$PATH

3.配置amoeba

amoeba.xml
監聽埠
<property name="port">3306</property>
繫結地址
<property name="ipAddress">0.0.0.0</property>

<property name="authenticator">
<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

<property name="user">root</property>

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

<property name="filter">
<bean class="com.meidusa.amoeba.server.IPAccessController">
<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
</bean>
</property>
</bean>
</property>

dbServers.xml
<property name="password">root.pass</property>

4.啟動amoeba服務(192.168.8.39)

# amoeba start

通過代理伺服器進行操作
# mysql -uroot -pamoebapass -h192.168.8.39

通過抓包觀察
master伺服器(8.40)
# tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host master