首页 > 代码库 > 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经典集群应用架构