Mysql主从高可用

Mysql主从高可用

服务器	角色
192.168.1.10	主
192.168.1.20	从
keepalived----vip

环境准备

Keepalived-2.0.13.tar.gz
mysql-5.5.56-linux-glibc2.5-x86_64.tar.gz 

依赖安装

yum -y install kernel-devel openssl-devel popt-devel
yum install -y libaio
注：此部署中的高可用采用源码部署

部署（主从服务器同步部署操作）

用户是否创建
  创建用户
  groupadd mysql
  useradd -r -g mysql mysql
1、解压安装
     tar -zxf mysql-5.5.56-linux-glibc2.5-x86_64.tar.gz
     mv mysql-5.5.56-linux-glibc2.5-x86_64/ mysql
     chown -R mysql:mysql mysql
     chmod 777 /root/
2、初始化数据库
     ./scripts/mysql_install_db --basedir=/root/mysql --datadir=/root/mysql/data/ --user=mysql
3、拷贝配置文件
     cp support-files/my-medium.cnf /etc/my.cnf
4、修改配置文件
     在port端口下添加一段参数
     log-error=/root/mysql/data/mysqld.error
5、拷贝启动程序
     cp support-files/mysql.server /etc/init.d/mysql
6、配置启动目录
     sed -i 's#/usr/local/mysql#/root/mysql#g' /etc/init.d/mysql
7、命令创建软连接
     ln -s /root/mysql/bin/* /usr/local/sbin
8、创建登录密码
    /etc/init.d/mysql start
    mysqladmin -u'root' password'123456'
9、如想修改密码，可登录数据库，修改密码
    set password=password("dataexa");
    grant all privileges on *.* to 'root'@'%' identified by 'dataexa';
    flush privileges;
10、创建新用户
    CREATE USER 'bigdata'@'%' IDENTIFIED BY '密码';
    grant all privileges on *.* to bigdata@'%' identified by '密码'
    flush privileges;

主从配置
1、主服务器my.cnf文件修改
例下图

1、从服务器my.cnf文件配置
例下图

2、重启主从节点的my.cnf文件

 /etc/init.d/mysql restart

3、从节点进入到数据库中指定主从关系

CHANGE MASTER TO MASTER_HOST='IP',MASTER_USER='主从复制账户',MASTER_PASSWORD='密码',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=107;

4、从服务器指定之后开启主从复制

 Start slave;
 show slave status\G;

查看状态是否都为YES-例下图

高可用部署

1、解压编译 
   tar zxf Keepalived-2.0.13.tar.gz
   cd keepalived-2.0.13
   ./configure
   make && make install
2、拷贝相关启动文件和配置文件
    cp keepalived/etc/keepalived/keepalived.conf /etc/keepalived/  
    cp keepalived/etc/init.d/keepalived /etc/rc.d/init.d/keepalived 
    cp keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived
    chkconfig --add keepalived
3、编辑conf文件
主服务配置：
vim /etc/keepalived/keepalived.conf
#! Configuration File for keepalived
global_defs {
router_id shbdp-net02-3     #------主机id
}
vrrp_instance VI_1 {
state BACKUP               
interface eth0             #-------网卡名称，可ip  addr查看
virtual_router_id 60
priority 100               #------主的比从大
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.22                   #--------注意修改为自己网段的vip
}
}

从服务器配置
vim /etc/keepalived/keepalived.conf
#! Configuration File for keepalived
global_defs {
router_id shbdp-net02-3
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 60
priority 80
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.22
}
}

4、主从启动keepalived服务（先主后从的顺序）
   service keepalived start
5、ip addr 查看主服务是否产生自定义设置的虚拟ip
6、产生虚拟ip之后，我们测试一下service keepalived stop停掉主节点的keepalived服务，并ip addr 查看虚拟ip是否到从服务器上。
7、测试成功之后，我们还原状态
①　停掉从keepalived服务 service keepalived stop 
②　启动主服务器keepalived服务 service keepalived start
③　主服务器查看虚拟ip回来了，再启动从keepalived服务 service keepalived start

8、添加检测mysql状态模块脚本
注：以下配置的ip以及端口根据实际情况进行更改
主服务器操作
Vim /etc/keepalived/keepalived.conf
virtual_server 192.168.1.22 3306 {     #虚拟IP+端口
delay_loop 6
lb_algo wrr
lb_kind DR
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 192.168.1.10 3306 {     #-----本机IP+端口
weight 1
notify_down /opt/mysql.sh         #------脚本路径
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
connect_port 3306                 #----端口
}
}
}

从服务器添加
vim /etc/keepalived/keepalived.conf
virtual_server 192.168.1.22 3306 {
delay_loop 6
lb_algo wrr
lb_kind DR
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 192.168.1.20 3306 {
weight 1
notify_down /opt/mysql.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
connect_port 3306
}
}
}

主从服务器的opt下的脚本如下
cd /opt/
vim mysql.sh
#!/bin/bash
pkill keepalived

Chmod +x mysql.sh

9、主从keepalived重启（先主后从顺序）
   service keepalived restart
10、测试主mysql宕机之后的主从切换
①　pkill mysql  杀掉主服务器的mysql 测试
②　杀掉之后 主服务器ip addr查看 生成的虚拟ip是否消失，可多ip addr 刷新几次
③　主服务器的虚拟ip消失之后，进入到从服务器进行ip addr查看虚拟ip是否漂移到从从服务器，多ip addr刷新几次
④　如没问题，至此测试成功