MySQL之主从复制和读写分离（Amoeba)
案例环境：Master：192.168.154.164
Slave01：192.168.154.176
Slave02：192.168.154.156
Amoeba：192.168.154.177
应用服务器：192.168.154.155
集群拓朴：
部署实施：
一 操作系统和应用程序安装（略）
二 搭建MySQL主从复制环境
1 架构前端有硬件防火墙，因此所有服务器的iptables处于关闭状态（如果没有，请开放相应的端口）
#service iptables stop
#chkconfig iptables off
2 配置时间服务器
1）配置Master主服务器为时间服务器
#yum -y install ntp
#vim /etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
#service ntpd start
#chkconfig ntpd on
#ntpq -pn #查看同步的时间服务器
remote refid st t when poll reach delay offsetjitter
==============================================================================
127.127.1.0 .LOCL. 8 l 298m 64 0 0.000 0.000 0.000
+83.168.200.198194.58.204.20 2 u156 1024107306.289 -4.28189.418
*5.79.108.34 130.133.1.10 2 u 50 1024367238.308 19.50815.059
-173.255.215.209 127.67.113.92 2 u48m 1024234197.001 35.63047.187
+193.228.143.22192.36.143.152 2 u156 1024107283.486 -1.20352.145
2）在从节点上配置时间同步
#yum -y install ntp
# ntpdate 192.168.154.164
23 Apr 23:12:09 ntpdate: adjust time server 192.168.154.164 offset 0.342955 sec
#crontab -e #每天凌晨1：00时间同步
00 1 * * * /usr/sbin/ntpdate 192.168.154.164
3 配置MySQL Master主服务器
1）修改配置文件
# vim /etc/my.cnf
server_id=164
log_bin=master-bin
2）重启MySQL服务
#service mysqld restart
3）为从服务器建立授权帐户
mysql> grant replication slave on *.* to 'myslave'@'192.168.154.%'> 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）如果数据库有数据则导出数据并推送到从库（全新设置则略过此步）
#mysqldump -uroot -p --all-databases> /tmp/backmysql.sql
#cd /tmp/
#rsync -arz backmysql.sql root@192.168.154.176:/root/
#rsync -arz backmysql.sql root@192.168.154.156:/root/
4 配置从服务器
1）修改配置文件
#vim /etc/my.cnf
server_id=176 #另一台为156，以IP地址最后一段命名
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
2）重启服务
#service mysqld restart
3）把主库备份的数据导入到从库，然后解锁主库
#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:>
>
> 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
> 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> 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环境
yum install -y java-1.6.0-openjdk
vim /etc/profile #配置环境变量
export JAVA_HOME=/usr/
export PATH=$PATH:$JAVA_HOME/bin
export> export AMOEBA_HOME=/usr/local/amoeba/
export PATH=$PATH:$AMOEBA_HOME/bin
# source /etc/profile
# 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软件
# mkdir -pv /usr/local/amoeba
# tar -zxf /usr/local/src/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
# chmod 755 /usr/local/amoeba
# /usr/local/amoeba/bin/amoeba
amoeba start|stop #显示此内容说明安装成功
3 配置Amoeba读写分离，从服务器读负载均衡
1）在Master、slave服务器中开放权限给Amoeba访问
mysql> grant all on *.* to amoeba@'192.168.154.%'> 2）编辑amoeba.xml配置文件
# vim /usr/local/amoeba/conf/amoeba.xml
amoeba
123456
${amoeba.home}/conf/rule.xml
${amoeba.home}/conf/ruleFunctionMap.xml
${amoeba.home}/conf/functionMap.xml
1500
master
master
slaves
true
3）编辑dbServers.xml配置文件
# vim /usr/local/amoeba/conf/dbServers.xml
amoeba
amoeba
192.168.154.164
192.168.154.176
192.168.154.156
1
slave1,slave2
4）启动Amoeba，默认端口为tcp:8066
# amoeba start&
# lsof -i :8066 #Amoeba默认端口为8066
COMMANDPID USER FD TYPE DEVICE> java 3522 root 53uIPv621121 0t0TCP *:8066 (LISTEN)
4 测试
1）在应用服务器上通过Amoeba代理服务器访问MySQL
# 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> use testdb;
Database changed
mysql> insert into zang values('2','zhang','this_is_slave1');
Query OK, 1 row affected (0.00 sec)
slave2:
mysql> use testdb;
Database changed
mysql> insert into zang values('3','zhang','this_is_slave2');
Query OK, 1 row affected (0.03 sec)
4）测试读操作：
在应用服务器上第一次查询的结果如下：
mysql> select * from testdb.zang;
+------+-------+----------------+
|> +------+-------+----------------+
| 3 | zhang | this_is_slave2 |
+------+-------+----------------+
1 row in set (0.00 sec)
第二次查询结果如下：
mysql> select * from testdb.zang;
+------+-------+----------------+
|> +------+-------+----------------+
| 2 | zhang | this_is_slave1 |
+------+-------+----------------+
1 row in set (0.03 sec)
第三次查询结果如下：
mysql> select * from testdb.zang;
+------+-------+----------------+
|> +------+-------+----------------+
| 3 | zhang | this_is_slave2 |
+------+-------+----------------+
1 row in set (0.01 sec)
由以上结果可知Amoeba实现了从服务器的SQL路由、负载均衡的功能
5）测试写操作：
在应用服务器上插入语句
mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into zang values('4','zhang','write_test');
Query OK, 1 row affected (0.02 sec)
在应用服务器上查询，看不到相关的语句
mysql> select * from zang;
+------+-------+----------------+
|> +------+-------+----------------+
| 3 | zhang | this_is_slave2 |
+------+-------+----------------+
1 row in set (0.01 sec)
在Master服务器上查询，可以看到相关语句
mysql> select * from zang;
+------+-------+----------------+
|> +------+-------+----------------+
| 1 | zhang | this_is_master |
| 4 | zhang | write_test |
+------+-------+----------------+
2 rows in set (0.00 sec)
由以上操作可知，写操作在Master服务器上
因此，通过上面的步骤，已经实现了MySQL主从复制和读写分离，所有写操作都在Master主服务器上，而读操作则分摊给了Slave从服务器，分担数据库的压力。
页:
[1]