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处于关闭状态（如果没有，请开放相应的端口）
　　[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.%'>　　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 root@192.168.154.176:/root/
　　[root@master ~]#rsync -arz backmysql.sql root@192.168.154.156:/root/
　　4 配置从服务器
　　1）修改配置文件
　　[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:>
　　>
　　>　　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环境
　　[root@amoeba ~]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
　　[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.%'>　　2）编辑amoeba.xml配置文件
　　[root@amoeba ~]# 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配置文件
　　[root@amoeba ~]# 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
　　[root@amoeba ~]# amoeba start&
　　[root@amoeba ~]# lsof -i :8066    #Amoeba默认端口为8066

　　COMMAND  PID USER   FD   TYPE DEVICE>　　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> 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从服务器，分担数据库的压力。