（四）lvs负载均衡详解--mysql+keepalived配置，yum下载mysql详解、mysql集群高可用

​​​​​​（一）lvs负载均衡详解--lvs-DR模式配置_W1124824402的博客-CSDN博客

​​​​​​（二）lvs负载均衡详解--lvs-NAT模式配置_W1124824402的博客-CSDN博客

（三）lvs负载均衡详解--lvs-DR+keepalived配置_W1124824402的博客-CSDN博客

                                                      Keepalived+mysql 自动切换

一、mysql 互为主从 有双主就可以做测验从节点根据需求自行配置，正常集群应该是双主双从 

二、安装keepalived

三、keepalived主备配置文件

四、mysql状态检测脚本/root/bin/keepalived_check_mysql.sh

五、测试及诊断

Yum安装mysql

mysql的官方网站：www.mysql.com

 

 

 

 

wget https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm

安装mysql的yum仓库

rpm -ivh mysql80-community-release-el7-5.noarch.rpm

yum -y install yum-utils    #安装yum工具包

配置yum源 vim /etc/yum.repos.d/mysql-community.repo   #修改如下

1表示开启，0表示关闭

安装数据库

yum install -y mysql-community-server

systemctl start mysqld

过滤mysql的初始密码进行修改

grep password /var/log/mysqld.log

mysqladmin -u root -p'旧密码' password '新密码'

或者进入数据库修改密码

alter user 'root'@'localhost' identified by '新密码';     修改密码命令

基于Docker配置mysql互为主从_W1124824402的博客-CSDN博客

mysql 配置互为主从同步 详情看上方链接不是docker的只看配置文件即可

生成秘钥   ssh-keygen   （一路回车默认即可）

ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.11.194

把公钥发送给另一台服务器  测试一下是否可用免密登录

ssh root@192.168.11.194

然后在 master1中

vim /etc/my.cnf  中添加  

[mysqld]

server-id=1

log-bin=mysql-bin

skip-name-resolve

log-slave-updates

lower_case_table_names=1

interactive_timeout=7200

wait_timeout=86400

max_connections=4000

back_log=100

expire_logs_days=3

max_binlog_size=100M

character_set_server=utf8

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

在master2中添加

vim /etc/my.cnf

[mysqld]

server-id=2

log-bin=mysql-bin

skip-name-resolve

log-slave-updates

lower_case_table_names=1

interactive_timeout=7200

wait_timeout=86400

max_connections=4000

back_log=100

expire_logs_days=3

max_binlog_size=100M

character_set_server=utf8

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

进入数据库操作创建用户命令用来同步信息用的

grant all on *.* to 'slave'@'%' identified by '设置密码';

刷新配置

flush privileges;

重启一下mysql  

systemctl restart mysqld

进入数据库查看binlog日志的信息           show master status;

关注file和position的值

下面进行主主同步配置change操作前要先stop slave;

然后 \e进入编辑  master1节点操作

chnge master to master_host='192.168.11.194',

master_port=3306,

master_user='slave',                                                    

master_password='Zhguo@123',

master_log_file='mysql-bin.000001',                             

master_log_pos=154,                                                     

master_connect_retry=60;

start slave;  两个节点都要启动slave

然后 \e进入编辑  master2节点操作

chnge master to master_host='192.168.11.193',

master_port=3306,

master_user='slave',                                                    

master_password='Zhguo@123',

master_log_file='mysql-bin.000001',                             

master_log_pos=154,                                                     

master_connect_retry=60;

start slave;  两个节点都要启动slave

show slave status \G; 查看配置是否成功

 

如果同步效果不正常，进行以下操作: 在2台机器数据保持一致的情况 下。 从节点: stop slave; #停止同步 reset slave; #清除主节点信息 主节点: reset master; #清除bin1og日志 

同步成功后可以进行测试 创建一个库使用另一个节点查看

Create database dada；

下面安装keepalived---两台机器都操作

yum -y install keepalived

keepalived 主节点配置文件  参数解释看前面的博客

192.168.11.193 master配置

mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak

vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

   router_id master

}

vrrp_script check_run {

   script "/etc/keepalived/keepalived_chech_mysql.sh"   检测脚本路径

   interval 5

}

vrrp_instance VI_1 {

    state MASTER

    interface ens33

    virtual_router_id 89

    priority 100

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

        192.168.11.166/24

    }

    track_script {

        check_run         与上方检测脚本模块名一样

    }

}

keepalived 主节点配置文件

slave 192.168.11.194 配置

mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak

vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

   router_id backup

}

vrrp_script check_run {

   script "/etc/keepalived/keepalived_chech_mysql.sh"

   interval 5

}

vrrp_instance VI_1 {

    state BACKUP

    nopreempt

    interface ens33

    virtual_router_id 89

    priority 50

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

        192.168.11.166/24

    }

    track_script {

        check_run

    }

}

 mysql状态检测脚本/etc/keepalived/keepalived_chech_mysql.sh（两台MySQL同样的脚本）

vim /etc/keepalived/keepalived_chech_mysql.sh

#!/bin/bash

/usr/bin/mysql -uroot -p”自己设置的密码” -e "show status" &>/dev/null

if [ $? -ne 0 ] ;then

systemctl stop keepalived

fi

chmod +x /etc/keepalived/keepalived_chech_mysql.sh 记得给执行权限

两太服务器都启动keepalived

systemctl restart keepalived

在任意一台机器作为客户端。在测试的时候记得检查mysql用户的可不可以远程登录。

 mysql -uslave -p -h 192.168.11.166    -h是指定ip

然后停掉master节点的mysql在登录看一下 如果登录成功则配置完成、