案例環境：
Master：192.168.154.164
Slave01：192.168.154.176
Slave02：192.168.154.156
Amoeba：192.168.154.177
應用伺服器：192.168.154.155
叢集拓樸：
<a href="https://s2.51cto.com/wyfs02/M02/92/2C/wKiom1j8wFmx0fd7AABYwmJNZoQ627.jpg-wh_500x0-wm_3-wmp_4-s_1669391682.jpg" target="_blank"></a>
部署實施：
一 作業系統和應用程式安裝（略）
二 搭建MySQL主從複制環境
1 架構前端有硬體防火牆，是以所有伺服器的iptables處于關閉狀态（如果沒有，請開放相應的端口）
[root@master ~]#service iptables stop
[root@master ~]#chkconfig iptables off
2 配置時間伺服器
1）配置Master主伺服器為時間伺服器
[root@master ~]#yum -y install ntp
[root@master ~]#vim /etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
[root@master ~]#service ntpd start
[root@master ~]#chkconfig ntpd on
[root@master ~]#ntpq -pn #檢視同步的時間伺服器
remote refid st t when poll reach delay offset jitter
==============================================================================
127.127.1.0 .LOCL. 8 l 298m 64 0 0.000 0.000 0.000
+83.168.200.198 194.58.204.20 2 u 156 1024 107 306.289 -4.281 89.418
*5.79.108.34 130.133.1.10 2 u 50 1024 367 238.308 19.508 15.059
-173.255.215.209 127.67.113.92 2 u 48m 1024 234 197.001 35.630 47.187
+193.228.143.22 192.36.143.152 2 u 156 1024 107 283.486 -1.203 52.145
2）在從節點上配置時間同步
[root@slave01 ~]#yum -y install ntp
[root@slave02 ~]# ntpdate 192.168.154.164
23 Apr 23:12:09 ntpdate[15333]: adjust time server 192.168.154.164 offset 0.342955 sec
[root@slave01 ~]#crontab -e #每天淩晨1：00時間同步
00 1 * * * /usr/sbin/ntpdate 192.168.154.164
3 配置MySQL Master主伺服器
1）修改配置檔案
[root@master ~]# vim /etc/my.cnf
server_id=164
log_bin=master-bin
2）重新開機MySQL服務
[root@master ~]#service mysqld restart
3）為從伺服器建立授權帳戶
mysql> grant replication slave on *.* to 'myslave'@'192.168.154.%' identified by 'myslave';
mysql> select user,host from mysql.user; #驗證授權賬戶
4）鎖表，并檢視二進制日志的資訊
mysql> flush tables with read lock;
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| master-bin.000001 | 333 | | | |
5）如果資料庫有資料則導出資料并推送到從庫（全新設定則略過此步）
[root@master ~]#mysqldump -uroot -p --all-databases > /tmp/backmysql.sql
[root@master ~]#cd /tmp/
[root@master ~]#rsync -arz backmysql.sql [email protected]:/root/
[root@master ~]#rsync -arz backmysql.sql [email protected]:/root/
4 配置從伺服器
[root@slave01 ~]#vim /etc/my.cnf
server_id=176 #另一台為156，以IP位址最後一段命名
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
2）重新開機服務
[root@slave01 ~]#service mysqld restart
3）把主庫備份的資料導入到從庫，然後解鎖主庫
[root@slave01 ~]#mysql -uroot -p < backmysql.sql
mysql> show databases; #驗證是否和主庫一緻
#在主伺服器上，解鎖主庫
mysql> unlock tables;
4）在從庫上配置同步（如果之前有做過從庫則：stop slave;)
mysql> change master to master_host='192.168.154.164',master_user='myslave',master_password='myslave',master_log_file='master-bin.000001',master_log_pos=333;
mysql> start slave;
5）檢視Slave狀态，確定以下兩項為yes
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.154.164
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 499
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 450
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes #表示網絡正常
Slave_SQL_Running: Yes #表示結構正常
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 499
Relay_Log_Space: 621
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 #主從同步延遲時間
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 164
Master_UUID: d1fbd0f8-2775-11e7-9981-000c29f84ca6
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
5 驗證主從複制的效果
1）在Master伺服器建立資料庫
mysql> create database aaa;
2）在主、從伺服器上檢視資料庫是否保持一緻
三 搭建MySQL讀寫分離
1 在主機Amoeba上配置java環境
[root@amoeba ~]yum install -y java-1.6.0-openjdk
vim /etc/profile #配置環境變量
export JAVA_HOME=/usr/
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export AMOEBA_HOME=/usr/local/amoeba/
export PATH=$PATH:$AMOEBA_HOME/bin
[root@amoeba ~]# source /etc/profile
[root@amoeba bin]# java -version
java version "1.6.0_38"
OpenJDK Runtime Environment (IcedTea6 1.13.10) (rhel-1.13.10.4.el6-i386)
OpenJDK Client VM (build 23.25-b01, mixed mode)
2 安裝配置Amoeba軟體
[root@amoeba ~]# mkdir -pv /usr/local/amoeba
[root@amoeba ~]# tar -zxf /usr/local/src/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@amoeba ~]# chmod 755 /usr/local/amoeba
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop #顯示此内容說明安裝成功
3 配置Amoeba讀寫分離，從伺服器讀負載均衡
1）在Master、slave伺服器中開放權限給Amoeba通路
mysql> grant all on *.* to amoeba@'192.168.154.%' identified by 'amoeba';
2）編輯amoeba.xml配置檔案
[root@amoeba ~]# vim /usr/local/amoeba/conf/amoeba.xml
<property name="authenticator">
<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
<property name="user">amoeba</property>
<property name="password">123456</property>
<property name="filter">
<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<property name="ruleLoader">
<bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
<property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
<property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
</bean>
</property>
<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
<property name="LRUMapSize">1500</property>
<property name="defaultPool">master</property>
<property name="writePool">master</property>
<property name="readPool">slaves</property>
<property name="needParse">true</property>
</queryRouter>
3）編輯dbServers.xml配置檔案
[root@amoeba ~]# vim /usr/local/amoeba/conf/dbServers.xml
<!-- mysql user -->
<property name="user">amoeba</property>
<property name="password">amoeba</property>
<dbServer name="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.154.164</property>
</factoryConfig>
</dbServer>
<dbServer name="slave1" parent="abstractServer">
<property name="ipAddress">192.168.154.176</property>
<dbServer name="slave2" parent="abstractServer">
<property name="ipAddress">192.168.154.156</property>
<dbServer name="slaves" virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<property name="loadbalance">1</property>
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">slave1,slave2</property>
4）啟動Amoeba，預設端口為tcp:8066
[root@amoeba ~]# amoeba start&
[root@amoeba ~]# lsof -i :8066 #Amoeba預設端口為8066
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
java 3522 root 53u IPv6 21121 0t0 TCP *:8066 (LISTEN)
4 測試
1）在應用伺服器上通過Amoeba代理伺服器通路MySQL
[root@localhost local]# mysql -uamoeba -p123456 -P8066 -h 192.168.154.177
2）在Master伺服器上建立一張表，同步到從伺服器，然後停止Slave功能，再寫入其它語句。
mysql> create database testdb;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
5 rows in set (0.00 sec)
mysql> use testdb;
Database changed
mysql> create table zang (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.44 sec)
分别在兩台從伺服器上stop slave;
mysql> stop slave;
Query OK, 0 rows affected (0.05 sec)
然後在主伺服器上
mysql> insert into zang values('1','zhang','this_is_master');
Query OK, 1 row affected (0.02 sec)
3）從伺服器上同步了testdb庫和zang表，手動插入其它内容
slave1:
mysql> insert into zang values('2','zhang','this_is_slave1');
Query OK, 1 row affected (0.00 sec)
slave2:
mysql> insert into zang values('3','zhang','this_is_slave2');
4）測試讀操作：
在應用伺服器上第一次查詢的結果如下：
mysql> select * from testdb.zang;
+------+-------+----------------+
| id | name | address |
| 3 | zhang | this_is_slave2 |
1 row in set (0.00 sec)
第二次查詢結果如下：
| 2 | zhang | this_is_slave1 |
1 row in set (0.03 sec)
第三次查詢結果如下：
1 row in set (0.01 sec)
由以上結果可知Amoeba實作了從伺服器的SQL路由、負載均衡的功能
5）測試寫操作：
在應用伺服器上插入語句
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> insert into zang values('4','zhang','write_test');
在應用伺服器上查詢，看不到相關的語句
mysql> select * from zang;
在Master伺服器上查詢，可以看到相關語句
| 1 | zhang | this_is_master |
| 4 | zhang | write_test |
2 rows in set (0.00 sec)
由以上操作可知，寫操作在Master伺服器上
是以，通過上面的步驟，已經實作了MySQL主從複制和讀寫分離，所有寫操作都在Master主伺服器上，而讀操作則分攤給了Slave從伺服器，分擔資料庫的壓力。
本文轉自 zengwj1949 51CTO部落格，原文連結:http://blog.51cto.com/zengwj1949/1918983
![Java String.format方法的簡單使用[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)