首页 > 代码库 > MySQL经典集群应用架构

MySQL经典集群应用架构

  • 注解:本实验思想来自于高俊峰
  • 下例为个人方法

技术分享

由上面架构总拓扑图可知:
dbm133是MySQL主与dbm134是MySQL主的备机

有Heartbeat+DRBD做的是MySQL高可用

dbs135、dbs136、dbs137三台为MySQL从服务器

主服务器是dbm133与dbm134两台的VIP地址

MySQL写的操作一般采用基于heartbeat+DRBD+MySQL搭建高可用集群的方案;通过heartbeat实现对MySQL主的状态进行监测,而DRBD实现dbm133与dbm134的数据同步

MySQL 读的操作普遍采用基于LVS+Keepalived搭建高可用扩展集群的方案;前段AS应用通过读VIP连接LVS,LVS有keepalived做成高可用模式,实现互备。

lvs1与lvs2为两台LVS服务器做的是keepalived高可用

最上面三台这里先不考虑,主要就是做读写分离的

写交给dbm133与dbm134两台的VIP地址

读交给lvs1与lvs2两台的VIP地址

技术分享

配置MySQL+HeartBeat+DRBD

操作主机:localhost1与localhost2

 

 

安装前准备配置:

这两台主机添加一块硬盘20G的SCSI接口

关闭防火墙:service iptables stop

关闭安全机制:setenforce 0

 

两台主机配置:

[root@localhost1 ~]# fdisk /dev/sdb
Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-2610, default 1): 
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610): +10G

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

[root@localhost1 ~]# partprobe /dev/sdb   #重新加载磁盘分区表、安装包为parted
[root@localhost1 ~]# hostname   
localhost1
[root@localhost1 ~]# vim /etc/hosts    #scp至所有主机
插入所有主机的主机名映射
192.168.8.10  localhost1
192.168.8.20  localhost2
192.168.8.30  localhost3
192.168.8.40  localhost4
192.168.8.50  localhost5
192.168.8.60  localhost6
192.168.8.70  localhost7

HeartBeat安装

 

[root@localhost1 ~]#  ls
cluster-glue-1.0.5-6.el6.x86_64.rpm
cluster-glue-libs-1.0.5-6.el6.x86_64.rpm
heartbeat-3.0.4-2.el6.x86_64.rpm
heartbeat-libs-3.0.4-2.el6.x86_64.rpm
lib64ltdl7-2.2.6-6.1mdv2009.1.x86_64.rpm
perl-TimeDate-1.16-13.el6.noarch.rpm
PyXML-0.8.4-19.el6.x86_64.rpm
resource-agents-3.9.5-24.el6_7.1.x86_64.rpm
[root@localhost1 ~]# rpm -ivh PyXML-0.8.4-19.el6.x86_64.rpm
[root@localhost1 ~]# rpm -ivh cluster-glue-libs-1.0.5-6.el6.x86_64.rpm
[root@localhost1 ~]# rpm -ivh perl-TimeDate-1.16-13.el6.noarch.rpm
[root@localhost1 ~]# rpm -ivh cluster-glue-1.0.5-6.el6.x86_64.rpm
[root@localhost1 ~]# rm -rf *.rpm
[root@localhost1 ~]# yum -y install resource-agents   #需配置网络yum源
[root@localhost1 ~]# ls | grep rpm     #上传HeartBeat软件包
heartbeat-3.0.4-2.el6.x86_64.rpm
heartbeat-libs-3.0.4-2.el6.x86_64.rpm
[root@localhost1 ~]# rpm -ivh heartbeat-*
[root@localhost1 wang]# rpm -q  kernel-headers
kernel-headers-2.6.32-696.el6.x86_64
[root@localhost1 wang]# rpm -q flex
flex-2.5.35-9.el6.x86_64
[root@localhost1 wang]# rpm -ivh /mnt/Packages/kernel-devel-2.6.32-431.el6.x86_64.rpm

DRBD安装配置

 

主从都要安装配置

下载地址: wget http://oss.linbit.com/drbd/8.4/drbd-8.4.3.tar.gz

[root@localhost1 ~]# tar xf drbd-8.4.3.tar.gz 
[root@localhost1 ~]# cd drbd-8.4.3
[root@localhost1 drbd-8.4.3]# ./configure --prefix=/usr/local/drbd --with-km --with-heartbeat
[root@localhost1 drbd-8.4.3]# make KDIR=/usr/src/kernels/2.6.32-696.1.1.el6.x86_64/
                                             #内核处都建议tab键补全
[root@localhost1 drbd-8.4.3]# make
[root@localhost1 drbd-8.4.3]# make install
[root@localhost1 drbd-8.4.3]# mkdir -p /usr/local/drbd/var/run/drbd
[root@localhost1 drbd-8.4.3]# cp /usr/local/drbd/etc/rc.d/init.d/drbd /etc/init.d/
[root@localhost1 drbd-8.4.3]# chkconfig --add drbd
[root@localhost1 drbd-8.4.3]# cd drbd
[root@localhost1 drbd]# make clean
rm -rf .tmp_versions Module.markers Module.symvers modules.order
rm -f *.[oas] *.ko .*.cmd .*.d .*.tmp *.mod.c .*.flags .depend .kernel*
rm -f compat/*.[oas] compat/.*.cmd 
[root@localhost1 drbd]# make KDIR=/usr/src/kernels/2.6.32-696.1.1.el6.x86_64/
[root@localhost1 drbd]# cp drbd.ko /lib/modules/2.6.32-431.el6.x86_64/kernel/lib/
[root@localhost1 drbd]# depmod
[root@localhost1 drbd]# cp -R /usr/local/drbd/etc/ha.d/resource.d/* /etc/ha.d/resource.d/
[root@localhost1 drbd]# cd /usr/local/drbd/etc/drbd.d/
[root@localhost1 drbd.d]# cat /usr/local/drbd/etc/drbd.conf
# You can find an example in  /usr/share/doc/drbd.../drbd.conf.example

include "drbd.d/global_common.conf";
include "drbd.d/*.res"; 
[root@localhost1 drbd.d]# pwd
/usr/local/drbd/etc/drbd.d
[root@localhost1 drbd.d]# cp global_common.conf{,-$(date +%F)}
[root@localhost1 drbd.d]# vim global_common.conf
global {
        usage-count yes;       #是否对使用信息作统计,默认为yes
}
common {
        startup {
                wfc-timeout 120;        #等待连接超时时间
                degr-wfc-timeout 120;
        }
        disk {
                on-io-error detach;           #当IO出现错误时执行的动作
        }
        net {
                protocol C;      #复制模式为第三种
        }
}
[root@localhost1 drbd.d]# vim r0.res
resource r0 { 
        on localhost1 {
                device /dev/drbd0;
                disk /dev/sdb1;
                address 192.168.8.10:7788;
                meta-disk internal;
        }
        on localhost2 {
                device /dev/drbd0;
                disk /dev/sdb1;
                address 192.168.8.20:7788;
                meta-disk internal;
        }
}
[root@localhost1 drbd.d]# 
scp global_common.conf r0.res 192.168.8.20:/usr/local/drbd/etc/drbd.d/

 

 创建元数据

两个节点上操作(两台主机上操作)

[root@localhost1 drbd.d]# modprobe drbd
WARNING: Deprecated config file /etc/modprobe.conf, all config files belong into /etc/modprobe.d/.    #这个警告没关系、可以忽略
[root@localhost1 drbd.d]# lsmod | grep drbd
drbd                  325658  0 
libcrc32c               1246  1 drbd
[root@localhost1 drbd.d]# dd if=/dev/zero bs=1M count=1 of=/dev/sdb1 
记录了1+0 的读入
记录了1+0 的写出
1048576字节(1.0 MB)已复制,0.0126091 秒,83.2 MB/秒
[root@localhost1 drbd.d]# drbdadm create-md r0   #输出以下信息
  --==  Thank you for participating in the global usage survey  ==--
The server‘s response is:

you are the 57300th user to install this version
Writing meta data...
initializing activity log
NOT initializing bitmap
New drbd meta data block successfully created.
Success             #到此如果没停止可以按Ctrl+C结束
注意: 当执行命令”drbdadm create-md r0 ”时,出现以下错误信息。 Device size would be truncated, which would corrupt data and result in 9 / 13 ‘access beyond end of device‘ errors. You need to either * use external meta data (recommended) * shrink that filesystem first * zero out the device (destroy the filesystem) Operation refused. Command ‘drbdmeta 0 v08 /dev/xvdb internal create-md‘ terminated with exit code 40 drbdadm create-md r0: exited with code 40 解决办法:初始化磁盘文件格式, dd if=/dev/zero bs=1M count=1 of=/dev/sdb1; sync

启动DRBD

(主从节点都要执行)

[root@localhost1 drbd.d]# /etc/init.d/drbd start
Starting DRBD resources: [
     create res: r0
   prepare disk: r0
    adjust disk: r0
     adjust net: r0
]
..........
[root@localhost1 drbd.d]# netstat -anpt | grep 7788
tcp        0      0 192.168.8.10:4173           192.168.8.20:7788           ESTABLISHED -                   
tcp        0      0 192.168.8.10:7788           192.168.8.20:48372          ESTABLISHED –

手动验证主从切换

初始化网络磁盘(主节点上执行)

[root@localhost1 ~]# drbdadm -- --overwrite-data-of-peer primary r0
[root@localhost1 ~]# cat /proc/drbd     #查看动态(watch –n 1 cat /proc/drbd)
version: 8.4.3 (api:1/proto:86-101)
GIT-hash: 89a294209144b68adb3ee85a73221f964d3ee515 build by root@localhost1, 2017-05-08 00:39:02
 0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r-----
    ns:1657856 nr:0 dw:0 dr:1658520 al:0 bm:101 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:8832196
	[==>.................] sync‘ed: 15.9% (8624/10244)M
	finish: 0:01:41 speed: 87,252 (87,252) K/sec

主节点上操作:

[root@localhost1 ~]# mkfs -t ext4 /dev/drbd0   格式化

[root@localhost1 ~]# mkdir /mysqldata        #创建挂载点

[root@localhost1 ~]# mount /dev/drbd0 /mysqldata/     #挂载

[root@localhost1 ~]# echo wangshushu > /mysqldata/name   #建立测试文件

[root@localhost1 ~]# umount /dev/drbd0      #卸载

[root@localhost1 ~]# drbdadm secondary r0     #主降级为次

[root@localhost1 ~]# mkfs -t ext4 /dev/drbd0   格式化
[root@localhost1 ~]# mkdir /mysqldata        #创建挂载点
[root@localhost1 ~]# mount /dev/drbd0 /mysqldata/     #挂载
[root@localhost1 ~]# echo wangshushu > /mysqldata/name   #建立测试文件
[root@localhost1 ~]# umount /dev/drbd0      #卸载
[root@localhost1 ~]# drbdadm secondary r0     #主降级为次

次节点上操作:

[root@localhost2 ~]# drbdadm primary r0    #次升级为主
[root@localhost2 ~]# mkdir /mysqldata      #创建挂载点
[root@localhost2 ~]# mount /dev/drbd0 /mysqldata/    #挂载
[root@localhost2 ~]# cat /mysqldata/name      #查看测试文件
wangshushu

还原操作:将localhost2降级为次localhost1升级为主

Localhost2上操作:

[root@localhost2 ~]# umount /dev/drbd0 
[root@localhost2 ~]# drbdadm secondary r0

Localhost1上操作:

[root@localhost1 ~]# drbdadm primary r0
[root@localhost1 ~]# mount /dev/drbd0 /mysqldata/

安装MySQL

两台机器操作

[root@localhost1 ~]# yum -y install mysql mysql-server
[root@localhost1 ~]# vim /etc/my.cnf
datadir=/mysqldata/mysql   #将第二行路径修改为/mysqldata/mysql
[root@localhost1 ~]# ll / | grep mysqldata
drwxr-xr-x    3 root root  4096 5月   8 01:57 mysqldata
[root@localhost1 ~]# chown -R mysql:mysql /mysqldata    #修改用户与组为MySQL
[root@localhost1 ~]# chkconfig mysqld on

注意:此时我们修改了数据目录和其属主和权限,有时会因为此操作导致数据库无法启动,

解决方法:

一,查看你的 selinux 是否处于打开状态,将其关闭。

二,/etc/apparmor.d/usr.sbin.mysqld 文件中,有两行内容规定了 mysql 使用数据文件的路径

权限,改掉即可,重启/etc/init.d/apparmor restart。

  

进行数据库测试

[root@localhost1 ~]# service mysqld start
[root@localhost1 ~]# mysql
mysql> create database wang;
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye

[root@localhost1 ~]# service mysqld stop      #localhost1上操作
[root@localhost1 ~]# umount /dev/drbd0       #localhost1上操作
[root@localhost1 ~]# drbdadm secondary r0      #localhost1上操作

还原操作:将localhost2降级为次localhost1升级为主

Localhost2上操作:

[root@localhost2 ~]# service mysqld stop
[root@localhost2 ~]# umount /dev/drbd0 
[root@localhost2 ~]# drbdadm secondary r0

Localhost1上操作:

[root@localhost1 ~]# drbdadm primary r0
[root@localhost1 ~]# mount /dev/drbd0 /mysqldata/
[root@localhost2 ~]# service mysqld start

 

配置HeartBeat

 

配置ha.cf文件(主从大体一致)

 

[root@localhost1 ~]# cd /usr/share/doc/heartbeat-3.0.4/
[root@localhost1 heartbeat-3.0.4]# cp ha.cf authkeys haresources /etc/ha.d/
[root@localhost1 heartbeat-3.0.4]# cd /etc/ha.d/
[root@localhost1 ha.d]# vim ha.cf
29 logfile /var/log/ha-log
34 logfacility local0
48 keepalive 2 				// 多长时间检测一次
56 deadtime 10 				// 连续多长时间联系不上后认为对方挂掉(秒)
61 warntime 5 				// 连续多长时间联系不上开始警告提示
71 initdead 100 				// 主要是给重启后预留的一段忽略时间
76 udpport 694 				//UDP  端口
121 ucast eth0 192.168.8.20	      // 填写对方 IP (主从的差异点)
157 auto_failback  on 				// 节点修复后是否切换回来
211 node  localhost1 					// 节点名称
212 node  localhost2 					// 节点名称

配置haresources文件(主从一致)

[root@localhost1 ha.d]# vim haresources
localhost1 IPaddr::192.168.8.100/24/eth0:0 drbddisk::r0    #注意:这两行为一行
Filesystem::/dev/drbd0::/mysqldata::ext4 mysqld       #主从不改变,都是这一行
[root@localhost1 ha.d]# ln -s /etc/init.d/mysqld /etc/ha.d/resource.d/mysqld

配置authkeys文件(主从一致)

[root@localhost1 ha.d]# vim authkeys    #将以下两行注释“#”去掉
23 auth 1
24 1 crc
[root@localhost1 ha.d]# chmod 600 authkeys

HA高可用验证

主从节点都启动HeartBeat

[root@localhost1 ~]# /etc/init.d/heartbeat start
Starting High-Availability services: INFO:  Resource is stopped
Done.

[root@localhost1 ~]# ip a     #等待大约30秒
    inet 192.168.8.10/24 brd 192.168.8.255 scope global eth0
    inet 192.168.8.100/24 brd 192.168.8.255 scope global secondary eth0:0
验证:先停掉 localhost1  上的 heartbeat  服务,查看 VIP  是否能转移
此时 localhost2 上 的 mysql  服务是关闭的
[root@localhost2 ~]# netstat -anpt | grep mysql   #localhost2上查看MySQL服务已关
[root@localhost2 ~]#

[root@localhost1 ~]# /etc/init.d/heartbeat stop   #主节点上操作
Stopping High-Availability services: Done.

[root@localhost2 ~]# ip a             #次节点查看
    inet 192.168.8.20/24 brd 192.168.8.255 scope global eth0
    inet 192.168.8.100/24 brd 192.168.8.255 scope global secondary eth0:0
[root@localhost2 ~]# netstat -anpt | grep mysql
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      6456/mysqld

  

此时还不具备停掉 mysql 后 后 VIP  漂移的功能,需要添加脚本实现,当发现 mysql  服务出现挂掉,就停掉 heartbeat  服务,实现 VIP  转移(双方都要在后台执行)

  

[root@localhost1 ~]# vim chk_mysql.sh
#!/bin/bash
while true
do
mysql="/etc/init.d/mysqld"
mysqlpid=$(ps -C mysqld --no-header | wc -l)
if [ $mysqlpid -eq 0 ];then
$mysql start
sleep 3 
mysqlpid=$(ps -C mysqld --no-header | wc -l)
if [ $mysqlpid -eq 0 ];then
/etc/init.d/heartbeat stop
echo "heartbeat stopped,please check your mysql !" | tee -a
/var/log/messages
fi
fi
done

[root@localhost1 ~]# echo "bash chk_mysql.sh &" >> /etc/rc.local
[root@localhost1 ~]# bash chk_mysql.sh &    #后台启动

此时MySQL的HeartBeat+DRBD已经完成

 

 

配置MySQL从服务器

在主服务器上建立NTP时间同步服务器

在localhost1上建立NTP服务器其他主机均与localhost1进行同步,生产环境中建议用网络中的NTP服务器

[root@localhost1 ~]# yum -y install ntp
[root@localhost1 ~]# date -s 2060-06-06
2060年 06月 06日 星期日 00:00:00 CST
[root@localhost1 ~]# date -s 06:06
2060年 06月 06日 星期日 06:06:00 CST
[root@localhost1 ~]# vim /etc/ntp.conf    #添加下面两行
server 127.127.1.0
fudge 127.127.1.0 stratum 8
[root@localhost1 ~]# service ntpd start
正在启动 ntpd:                                            [确定]

其他机器全部向localhost1进行时间同步

其他服务器需安装ntpdate

然后使用命令:/usr/sbin/ntpdate 192.168.8.10

                            

配置MySQL master服务器     #两台主服务器配置一样

在/etc/my.cnf 中修改或者增加如下内容:

 

[mysqld]
server-id=10
log-bin=mysql-binlog
log-slave-updates=true

重启MySQL

注意:重启MySQL时只需要把HeartBeat重启MySQL就会跟着重启

[root@localhost1 ~]# /etc/init.d/heartbeat stop
[root@localhost1 ~]# /etc/init.d/heartbeat start

注:此时如果报错可以重新安装rpm –ivh …..rpm  --force  强制  

如果重启MySQL时报错,MySQL启动不了

可以执行以下命令

mysql_install_db --user=mysql --ldata=http://www.mamicode.com/mysqldata/mysql

注:ldata后跟MySQL的位置路径

 

 

Localhost1配置:

创建Replication用户

mysql> grant replication slave on *.* to ‘myslave‘@‘192.168.8.%‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)    #用户myslave密码123456

mysql> flush privileges;            #刷新授权表
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;      #获得Master DB的相关信息
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000001 |      336 |              |                  |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

如果之前主数据库里有数据

建议用mysqldump进行备份

然后还原至其他从服务器里面

保证在做主从复制时数据一样

 

Localhost3、localhost4、localhost5三台从服务器安装MySQL数据库

[root@localhost3 ~]# yum -y install mysql mysql-server
[root@localhost3 ~]# vim /etc/my.cnf   插入以下三行
[mysqld]
server-id=20          #三台从服务器ID为20、30、40 注意修改依次设置server-id号
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

三台从服务器进入数据库

在 Slave服务器授权,启动从库,进行主从库数据同步

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.8.100‘,MASTER_USER=‘myslave‘,MASTER_PASSWORD=‘123456‘,MASTER_LOG_FILE=‘mysql-binlog.000001‘,MASTER_LOG_POS=336;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.8.100
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000001
          Read_Master_Log_Pos: 336
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 254
        Relay_Master_Log_File: mysql-binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
				·····························
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:

测试主从同步:

在主服务器上创建一个数据库

查看其他从服务器里是否复制成功

 

 

配置LVS-DR+Keepalived高可用

LVS 现在已成为Linux内核的一部分,以ip_vs模块的形式加载

 

两台LVS都操作:

确保软件包正常安装  

[root@localhost6 ~]# rpm -ivh /mnt/Packages/ipvsadm-1.26-2.el6.x86_64.rpm
[root@localhost6 ~]# modprobe ip_vs
WARNING: Deprecated config file /etc/modprobe.conf, all config files belong into /etc/modprobe.d/.
[root@localhost6 ~]# lsmod | grep ip_vs
ip_vs                 125220  0 
ipv6                  317340  263 ip_vs
libcrc32c               1246  1 ip_vs


[root@localhost6 ~]# cat /proc/net/ip_vs
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port Forward Weight ActiveConn InActConn

[root@localhost6 ~]# yum -y install keepalived
[root@localhost6 ~]# cd /etc/keepalived/
[root@localhost6 keepalived]# cp keepalived.conf{,-$(date +%F)}
[root@localhost6 keepalived]# vim keepalived.conf
! Configuration File for keepalived
global_defs {
   notification_email {
        goodmoodwjl@163.com
   }
   notification_email_from goodmoodwjl@qq.com
   smtp_server mail163.com
   smtp_connect_timeout 30
   router_id LVS_DEVEL_1       #另一台此处改为LVS_DEVEL_2
}

vrrp_instance VI_1 {
    state MASTER               #另一台改为BACKUP
    interface eth0
    virtual_router_id 51
    priority 100          #另一台改为50或低于100都可以
    advert_int 1
    authentication {
        auth_type PASS      
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.8.200          #VIP地址
    }
}
两台LVS启动Keepalived
[root@localhost6 ~]# /etc/init.d/keepalived start
[root@localhost6 ~]# ip a
    inet 192.168.8.60/24 brd 192.168.8.255 scope global eth0
    inet 192.168.8.200/32 scope global eth0
两台都操作
[root@localhost6 ~]# ipvsadm -A -t 192.168.8.200:3306 -s rr    #算法为rr
[root@localhost6 ~]# ipvsadm -a -t 192.168.8.200:3306 -r 192.168.8.30:3306 -g -w 1  #-g为DR
[root@localhost6 ~]# ipvsadm -a -t 192.168.8.200:3306 -r 192.168.8.40:3306 -g -w 1 #-w为权重
[root@localhost6 ~]# ipvsadm -a -t 192.168.8.200:3306 -r 192.168.8.50:3306 -g -w 1
[root@localhost6 ~]# ipvsadm -L -n 
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.8.200:3306 rr
  -> 192.168.8.30:3306            Route   1      0          0         
  -> 192.168.8.40:3306            Route   1      0          0         
  -> 192.168.8.50:3306            Route   1      0          0    

三台从服务器Realserver 上配置

不需要安装任何 IPVS 软件,只需要设置在开机后

自动执行以下脚本:

在 RealServer1 (localhost3)上的设置

[root@localhost3 ~]# vim /opt/lvs-dr
#!/bin/bash
VIP="192.168.8.200"    #VIP地址
/sbin/ifconfig eth0 192.168.8.30/24 up      #其他三台仅把这个IP地址换成自己的就OK
/sbin/ifconfig lo:0 $VIP broadcast $VIP netmask 255.255.255.255  up
/sbin/route add -host $VIP dev lo:0
echo "1" > /proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" > /proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" > /proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" > /proc/sys/net/ipv4/conf/all/arp_announce

[root@localhost3 ~]# chmod u+x /opt/lvs-dr 
[root@localhost3 ~]# echo "/opt/lvs-dr" >> /etc/rc.local 
[root@localhost3 ~]# /opt/lvs-dr 
[root@localhost3 ~]# service mysqld restart

[root@localhost3 ~]# mysqladmin -uroot password 123456    #给数据库设置密码
[root@localhost4 ~]# mysqladmin -uroot password 123456
[root@localhost5 ~]# mysqladmin -uroot password 123456

今后进入数据库命令:	
[root@localhost3 ~]# mysql -uroot -p123456

进入三台从MySQL(realserver)服务器

分别创建各自的一个数据库

Localhost3操作:
mysql> create database host3;
mysql> GRANT all ON *.* TO ‘wang‘@‘192.168.8.%‘ IDENTIFIED BY ‘666666‘;
mysql> FLUSH PRIVILEGES;
Localhost4操作:
mysql> create database host4;
mysql> GRANT all ON *.* TO ‘wang‘@‘192.168.8.%‘ IDENTIFIED BY ‘666666‘;
mysql> FLUSH PRIVILEGES;
Localhost4操作:
mysql> create database host5;
mysql> GRANT all ON *.* TO ‘wang‘@‘192.168.8.%‘ IDENTIFIED BY ‘666666‘;
mysql> FLUSH PRIVILEGES;

测试

用localhost7主机进行测试首先得安装MySQL客户端(yum -y install mysql)

[root@localhost7 ~]# mysql -u wang -h 192.168.8.200 -p666666
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| host4              |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> quit
Bye

[root@localhost7 ~]# mysql -u wang -h 192.168.8.200 -p666666
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| host3              |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> quit
Bye

[root@localhost7 ~]# mysql -u wang -h 192.168.8.200 -p666666
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| host5              |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> quit
Bye

在主LVS上进行查看:

[root@localhost6 ~]# ipvsadm -Lnc
IPVS connection entries
pro expire state       source             virtual            destination
TCP 00:22  FIN_WAIT    192.168.8.70:28945 192.168.8.200:3306 192.168.8.40:3306
TCP 00:31  FIN_WAIT    192.168.8.70:28946 192.168.8.200:3306 192.168.8.30:3306
TCP 00:10  FIN_WAIT    192.168.8.70:28944 192.168.8.200:3306 192.168.8.50:3306

在主MySQL上授权用户
[root@localhost1 ~]# mysqladmin -uroot password 123456
[root@localhost1 ~]# mysqlad -uroot -p123456
mysql> GRANT all ON *.* TO ‘wang‘@‘192.168.8.%‘ IDENTIFIED BY ‘666666‘;
mysql> FLUSH PRIVILEGES;
mysql> quit
Bye

测试:

登录主数据库进行写操作:VIP :100

(生产环境中授权用户权限一定要给最小,本实验都是给所有权限)

[root@localhost7 ~]# mysql -u wang -h 192.168.8.100 -p666666
mysql> create database benet_test;
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye

 

登录从数据库进行读操作:VIP :200

[root@localhost7 ~]# mysql -u wang -h 192.168.8.200 -p666666

  技术分享

技术分享

技术分享

技术分享

实验到此结束:

将读的VIP与写的VIP交于程序员开发基于源码读写分离软件

进而应用 如果公司不具备开发能力 可以使用 其他第三方或中间件

MySQL经典集群应用架构