mysqld_multi+keepalived高可用实践

阿使得肌肤·

系统环境Centos6.3x64
拓扑如下

一、配置mysql（四台都安装）
1.安装mysql
#./lnmp.shinstallmysql
mysql安装目录为/home/soft/mysql
为mysql添加默认搜索路径
#exportPATH=$PATH:/home/soft/mysql/bin
#echo“exportPATH=$PATH:/home/soft/mysql/bin”>>/etc/profile
2.创建数据存放目录和日志存放目录,并修改目录权限
#mkdir-p/home/data/dbdata
#cd/home/data/dbdata/
#mkdir/data_3306data_3307data_3308data_3309logs
#chownmysql.mysql-R.
3.初始化数据库
#/home/soft/mysql/scripts/mysql_install_db--basedir=/home/soft/mysql--datadir=/home/data/dbdata/data_3306--user=mysql
#/home/soft/mysql/scripts/mysql_install_db--basedir=/home/soft/mysql--datadir=/home/data/dbdata/data_3307--user=mysql
#/home/soft/mysql/scripts/mysql_install_db--basedir=/home/soft/mysql--datadir=/home/data/dbdata/data_3308--user=mysql
#/home/soft/mysql/scripts/mysql_install_db--basedir=/home/soft/mysql--datadir=/home/data/dbdata/data_3309--user=mysql
初始化完成后，查看data_3306,data_3307,data_3308,data_3309目录下是否生成数据
4.编辑/etc/my.cnf配置文件
#vim/etc/my.cnf
[mysqld_multi]
mysqld=/home/soft/mysql/bin/mysqld_safe
mysqladmin=/home/soft/mysql/bin/mysqladmin


[mysqld1]
port=3306
basedir=/home/soft/mysql
datadir=/home/data/dbdata/data_3306
pid-file=/home/data/dbdata/data_3306/mysql_3306.pid
socket=/home/data/dbdata/data_3306/mysql_3306.sock
log_error=/home/data/dbdata/logs/mysql_3306_error.log
user=mysql


[mysqld2]
port=3307
basedir=/home/soft/mysql
datadir=/home/data/dbdata/data_3307
pid-file=/home/data/dbdata/data_3307/mysql_3307.pid
socket=/home/data/dbdata/data_3306/mysql_3307.sock
log_error=/home/data/dbdata/logs/mysql_3307_error.log
user=mysql




[mysqld3]
port=3308
basedir=/home/soft/mysql
datadir=/home/data/dbdata/data_3308
pid-file=/home/data/dbdata/data_3308/mysql_3308.pid
socket=/home/data/dbdata/data_3308/mysql_3308.sock
log_error=/home/data/dbdata/logs/mysql_3308_error.log
user=mysql


[mysqld4]
port=3309
basedir=/home/soft/mysql
datadir=/home/data/dbdata/data_3309
pid-file=/home/data/dbdata/data_3309/mysql_3309.pid
socket=/home/data/dbdata/data_3309/mysql_3309.sock
log_error=/home/data/dbdata/logs/mysql_3309_error.log
user=mysql
启动服务并查看
#mysqld_multistart1
#ps-ef|grepmysql

启动3307,3308,3309
#mysqld_multistart2,3,4
登陆查看
#mysql-uroot-h127.0.0.1-P3306
或者
#mysql-uroot-S/home/data/dbdata/data_3306/mysql_3306.sock

关闭服务,对应启动的数字
#mysqld_multistop1
6.配置mysql主主同步（现在配置master1为主，master2为从）
修改my.cnf主要设置个不一样的ID(mysql_master1)
#vim/etc/my.cnf


[mysqld1],[mysqld2],[mysqld3],[mysqld4]中加入内容如下
server-id=1
log-bin=binlog
replicate-ignore-db=test#这是指定不需要同步的数据库
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
重启服务使配置生效
#mysqld_mulitstop1-4
#mysqld_mulitstart1-4
登陆主库master1赋予master2权限账号
#mysql-uroot-h127.0.0.1-P3306#分别登陆3306,3307,3308,3309
grantreplicationslaveon*.*to'admin'@'192.168.240.83'identifiedby'Pwd.123.!@#';
为验证账号我们可以在master2的机器上用命令做如下测试
mysql-uadmin-p’Pwd.123.!@#’-h192.168.240.82

显示主库信息
showmasterstatus；(记录file、position,从库设置将会用到)

修改master2配置文件my.cnf
在下面字段添加
[mysqld1],[mysqld2],[mysqld3],[mysqld4],
server-id=2
在master2上设置同步
#mysql–uroot-h127.0.0.1–P3306
mysql>stopslave;
changemasterto
master_host='192.168.240.82',master_user='admin',master_password='Pwd.123.!@#',master_log_file='binlog.000001’,master_log_pos=120;
mysql>startslave;
查看从库状态
mysql>showslavestatus\G

说明已经于主库同步
测试，在master1上面创建一个库，看master2是否同步过来
如果能看到主库中建立的库那么表示成功同步了
这里才是主主复制的开始，我们之前可以实现主从复制也就是说，再把从做为主，主再做为从，就实现主主复制了
7.编辑master2，my.cnf配置文件
vim/etc/my.cnf
[mysqld1],[mysqld2],[mysqld3],[mysqld4]中加入内容如下
log-bin=binlog
replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
重启数据库使配置生效
#mysqld_multistop1-4
#mysqld_multistart1-4
登录master2数据库赋予master数据库权限账号
#mysql-uroot-h127.0.0.1-P3306#分别登陆3306,3307,3308,3309
grantreplicationslaveon*.*to'admin'@'192.168.240.82'identifiedby'Pwd.123.!@#';
master2上登录数据库查看file和position

在master1上登录数据库做如下配置
#mysql-uroot-h127.0.0.1-P3306#分别登陆3306,3307,3308,3309
mysql>stopslave;
changemasterto
master_host='192.168.240.83',master_user='admin',master_password='Pwd.123.!@#',master_log_file='binlog.000001’,master_log_pos=411;
mysql>startslave;
查看从库状态
mysql>showslavestatus\G

看到上两个进程为数据库与另一边的master已经建立连接
8.配置mysql_slave1
登录master1数据库赋予slave1数据库权限账号
#mysql-uroot-h127.0.0.1-P3306#分别登陆3306,3307,3308,3309
grantreplicationslaveon*.*to'admin'@'192.168.240.84'identifiedby'Pwd.123.!@#';
master1上登录数据库查看file和position

登陆slave1，修改my.cnf
#vim/etc/my.cnf
[mysqld1],[mysqld2],[mysqld3],[mysqld4]中加入内容如下
server-id=3
log-bin=binlog
replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
#mysqld_multistop1-4
#mysqld_multistart1-4
分别登陆mysql，3306,3307,3308,3309，操作如下：
#mysql-uroot-h127.0.0.1-P3306#分别登陆3306,3307,3308,3309
mysql>stopslave;
changemasterto
master_host='192.168.240.82',master_user='admin',master_password='Pwd.123.!@#',master_log_file='binlog.000002',master_log_pos=300;
mysql>startslave;
查看从库状态
mysql>showslavestatus\G

8.配置mysql_slave2
登录master2数据库赋予slave1数据库权限账号
#mysql-uroot-h127.0.0.1-P3306#f分别登陆3306,3307,3308,3309


grantreplicationslaveon*.*to'admin'@'192.168.240.85'identifiedby'Pwd.123.!@#';
master2上登录数据库查看file和position

登陆slave2，修改my.cnf
#vim/etc/my.cnf
[mysqld1],[mysqld2],[mysqld3],[mysqld4]中加入内容如下
server-id=4
log-bin=binlog
replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
#mysqld_multistop1-4
#mysqld_multistart1-4
分别登陆mysql，3306,3307,3308,3309，操作如下：
#mysql-uroot-h127.0.0.1-P3306#分别登陆3306,3307,3308,3309
mysql>stopslave;
changemasterto
master_host='192.168.240.83',master_user='admin',master_password='Pwd.123.!@#',master_log_file='binlog.000001',master_log_pos=702;
mysql>startslave;
查看从库状态
mysql>showslavestatus\G

二、安装与配置keepalived（每台都安装）
1.安装兼容包
#yum-yinstallopensslopenssl-develipvsadmpopt-devel
2.下载并安装keepalived
#wgethttp://www.keepalived.org/software/keepalived-1.2.7.tar.gz
#tarzxvfkeepalived-1.2.7.tar.gz
#cdkeepalived-1.2.7
#./configure&&make&&makeinstall
#cp/usr/local/etc/rc.d/init.d/keepalived/etc/rc.d/init.d/
#cp/usr/local/etc/sysconfig/keepalived/etc/sysconfig/
#mkdir/etc/keepalived
#cp/usr/local/sbin/keepalived/usr/sbin/
#cp/usr/local/etc/keepalived/keepalived.conf/etc/keepalived/
#chkconfig--addkeepalived
3.mysql_master1,mysql_master2keepalived配置
#vi/etc/keepalived/keepalived.conf
!ConfigurationFileforkeepalived


global_defs{
notification_email{
zhengxiaofeiccc@126.com
}
notification_email_fromAlexandre.Cassen@firewall.loc
smtp_serversmtp.126.com
smtp_connect_timeout30
router_idLVS_DEVEL
}


vrrp_instanceVI_1{
stateBACKUP
interfaceeth0
virtual_router_id54
priority100#master2改为90
advert_int1
nopreempt#不抢占ip，只在master1上设置
authentication{
auth_typePASS
auth_pass1111
}
virtual_ipaddress{
192.168.240.91
}
}
4.Mysql_slave1,mysql_slave2keepalived配置
#vi/etc/keepalived/keepalived.conf
!ConfigurationFileforkeepalived
global_defs{
notification_email{
zhengxiaofeiccc@126.com
}
notification_email_fromAlexandre.Cassen@firewall.loc
smtp_serversmtp.126.com
smtp_connect_timeout30
router_idLVS_DEVEL
}


vrrp_instanceVI_1{
stateBACKUP
interfaceeth0
virtual_router_id55
priority100#mysql_slave2改为90
advert_int1
nopreempt#不抢占ip，只在slave1上设置
authentication{
auth_typePASS
auth_pass1111
}
virtual_ipaddress{
192.168.240.92
}
}


5.启动keepalived，并查看
#serviceseepalivedstart

6.编写mysql_master监控脚本
#vimcheck_mysql_master.sh
#!/bin/sh


#############check_port################
netstat -antlp|grep :::3306 && mysql_port[0]=3306
netstat -antlp|grep :::3307 && mysql_port[1]=3307
netstat -antlp|grep :::3308 && mysql_port[2]=3308
netstat -antlp|grep :::3309 && mysql_port[3]=3309


############check_status###############
mysql -u root -h 127.0.0.1 -P 3306 -e "showdatabases" && mysql_status[0]=1
mysql -u root -h 127.0.0.1 -P 3307 -e "showdatabases" && mysql_status[1]=2
mysql -u root -h 127.0.0.1 -P 3308 -e "showdatabases" && mysql_status[2]=3
mysql -u root -h 127.0.0.1 -P 3309 -e "showdatabases" && mysql_status[3]=4


if [ ${#mysql_port

} -ne 4 -o ${#mysql_status

} -ne 4 ];
then
echo"service keepalived stop"
fi
加入计划任务，每隔一分钟执行一次
7.编写mysql_slave监控脚本
#vimcheck_mysql_slave.sh
#!/bin/sh


#############check_status####################
mysql -u root -h 127.0.0.1 -P 3306 -e "showdatabases" && mysql_status[0]=1
mysql -u root -h 127.0.0.1 -P 3307 -e "showdatabases" && mysql_status[1]=2
mysql -u root -h 127.0.0.1 -P 3308 -e "showdatabases" && mysql_status[2]=3
mysql -u root -h 127.0.0.1 -P 3309 -e "showdatabases" && mysql_status[3]=4


#############check_slave_status##############
mysql -u root -h 127.0.0.1 -P 3306 -e "show slaves tatus\G"|head-13|tail-2|grep No\
&& mysql_slave_status=1||mysql_slave_status=0


if [ ${#mysql_status

} -ne 4 -o $mysql_slave_status -eq 1 ];then
service keepalived stop
fi