简单lvs+mysql读写分离

我积极解决

示意图

服务器                                                   IP
Apache---------------------------------192.168.11.2
分发器----------------------------------192.168.0.254   --------eth0:0  192.168.11.254
Mysql-master---------------------------192.168.11.1
Mysql-slave 1---------------------------192.168.0.1
Mysql-slave 2-------------------------- 192.168.0.2
配置过程
1、分发器配置
  1）安装ipvsadm
     #mount  /dev/cdrom  /mnt        挂载光盘
     #cd  /mnt/Cluster
     #rpm  - ivh  ipvsadm-....         安装ipvsadm
  2）配置ipvsadm
    #ipvsadm -A  -t 192.168.11.254:3306  -s  rr       增加服务
   -A 添加服务  -t 服务所使用的虚拟ip地址：mysql端口  -s  rr算法 (均分)  
    DNAT#ipvsadm -a -t 192.168.11.254:3306 -r 192.168.0.1 -m
    DNAT#ipvsadm -a -t 192.168.11.254:3306 -r 192.168.0.2 -m
    -a  添加real server  -r real server的ip -m (nat模式)
   3）查看一下设置
     DNAT#ipvsadm -ln
     IP Virtual Server version 1.2.1 (size=4096)
     Prot LocalAddress:Port Scheduler Flags
        -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
     TCP  192.168.11.254:3306 rr
        -> 192.168.0.1:3306            Masq    1      0          0         
        -> 192.168.0.2:3306            Masq    1      0          0  
   
   4）开启路由转发
     #echo 1 > /proc/sys/net/ipv4/ip_forward     
   
   5）确认路由转发是否开启
      sysctl -p
      net.ipv4.ip_forward = 1
2、mysql配置
  1）安装mysql
    #yum install mysql mysql-server -y      提前配置yum源
  2）master:
vim /etc/my.cnf
[mysqld]
    server_id = 1
    slave:
vim /etc/my.cnf
[mysqld]
    server_id = 2
    master:
vim /etc/my.cnf
[mysqld]
    server_id = 1
    log-bin=binlog
log-bin-index=binlog.index
    slave:
vim /etc/my.cnf
[mysqld]
server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin
relay_log_index=/var/lib/mysql/mysql-relay-bin.index
master && slave
service mysqld restart
master:
    mysql> GRANT replication slave ON *.* TO 'alex'@'%' identified by '123';
    Query OK, 0 rows affected (0.05 sec)
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    slave:
cd /var/lib/mysql
rm -fr *
service mysqld restart
    master
mysqldump -A -x > /tmp/full.sql
scp /tmp/full.sql root@192.168.0.1:/tmp/
    scp /tmp/full.sql root@192.168.0.2:/tmp/
    slave:
mysql < /tmp/full.sql
    master:
mysql> flush tables with read lock;
mysql> show master status;
    +---------------+----------+--------------+------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +---------------+----------+--------------+------------------+
    | binlog.000001 |      367|              |                  |
    mysql>unlock tables;

    slave:
mysql>change master to master_host='192.168.11.1',master_port=3306,                   master_user='alex',master_password='123', master_log_file='binlog.000001',master_log_pos=367;
    mysql> start slave;
    slave:
mysql> show slave status \G
Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
    测试写数据用户
    master:
    mysql> GRANT all privileges ON *.* TO 'tom'@'%' identified by '123';
    Query OK, 0 rows affected (0.05 sec)
    mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3、apache && PHP配置
   1）安装apache && PHP
     #yum  install  httpd*  php  php-mysql  -y
   2）修改写入数据脚本
      #vim  /var/www/html/mysql-c.php
       $con = mysql_connect(&quot;192.168.11.1&quot;,&quot;tom&quot;,&quot;123&quot;);
     #vim  /var/www/html/inster.php
       $con = mysql_connect(&quot;192.168.11.1&quot;,&quot;tom&quot;,&quot;123&quot;);
3）修改读出数据脚本
#vim  /var/www/html/mysql-r.php
   $con = mysql_connect(&quot;192.168.11.254&quot;,&quot;tom&quot;,&quot;123&quot;);
4、客户端测试
file:///C:/Users/heaven/AppData/Local/Temp/ksohtml/wps_clip_image-14967.png
可以通过人为service mysqld stop来观察读出数据的效果