mysql+keepalived 主从高可用

一，mysql 安装 centos 7 离线安装 mysql 5.7.31

1，[主]：配置

vi /etc/my.cnf
内容如下：

[client]
port = 3306
socket = /usr/local/mysql/tmp/mysql.sock



[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir  = /usr/local/mysql/tmp
socket = /usr/local/mysql/tmp/mysql.sock
pid-file = /usr/local/mysql/log/mysql.pid
log_error = /usr/local/mysql/log/error.log
slow_query_log_file = /usr/local/mysql/log/slow.log
explicit_defaults_for_timestamp=true
lower_case_table_names=1

#表示是本机的序号为1,一般来讲就是master的意思
server_id=1
#服务器在关闭它之前在一个连接上等待行动的秒数。
wait_timeout=360000
#定期清理
expire_logs_days=5
#开启Binlog日志
log-bin=binlog
#开启Binlog日志的索引文件
log-bin-index=binlog.index
#表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
#当每进行1次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘
sync-binlog = 1

2，[从]：配置内容如下

[client]
port = 3306
socket = /usr/local/mysql/tmp/mysql.sock


[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir  = /usr/local/mysql/tmp
socket = /usr/local/mysql/tmp/mysql.sock
pid-file = /usr/local/mysql/log/mysql.pid
log_error = /usr/local/mysql/log/error.log
slow_query_log_file = /usr/local/mysql/log/slow.log
explicit_defaults_for_timestamp=true
lower_case_table_names=1

#是否自动清空不再需要中继日志时,0不启动
relay_log_purge=0
server_id =2
#服务器在关闭它之前在一个连接上等待行动的秒数。
wait_timeout=360000
#定期清理
expire_logs_days=5
#开启Binlog日志
log-bin=binlog
#开启Binlog日志的索引文件
log-bin-index=binlog.index
#表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
#当每进行1次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘
sync-binlog = 1

二，主从配置

1，[主]：创建主从用户与权限

CREATE USER 'sec'@'%' IDENTIFIED BY '123456';
grant file on *.* to 'sec'@'%';
grant replication slave on *.* to 'sec'@'%';
flush privileges;
show master status;

2，[从]：配置主的（注：master_log_file 与 master_log_pos 在主中执行 “show master status;” 获得）

change master to master_host='192.168.124.181', master_port=3306, master_user='sec', master_password='123456', master_log_file='binlog.000006',master_log_pos=1250;

3，[从]：查询从状态

show slave status \G 

4，[从]：slave 启停

stop slave;
start slave;

三，keepalived 安装配置

cd /home/packages/
tar -zxvf keepalived-2.0.10.tar.gz
cd keepalived-2.0.10
./configure --prefix=/usr/local/keepalived
make && make install
cp keepalived/etc/init.d/keepalived /etc/rc.d/init.d/
cd /usr/local/keepalived/
cp etc/sysconfig/keepalived /etc/sysconfig/
mkdir /etc/keepalived/
cp etc/keepalived/keepalived.conf /etc/keepalived/
cp sbin/keepalived /usr/sbin/
echo “/etc/init.d/keepalived start” >> /etc/rc.local
cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
vim /etc/keepalived/keepalived.conf

keepalived.conf

! Configuration File for keepalived
       
global_defs {
    router_id MASTER-HA
}
       
vrrp_script chk_mysql_port {
    script "/etc/keepalived/chk_mysql.sh"
    interval 2            
    weight -5                 
    fall 2                 
    rise 1               
}
       
vrrp_instance VI_1 {
    state MASTER   #MASTER，SLAVE
    interface ens33    
    mcast_src_ip 192.168.124.146
    virtual_router_id 51    
    priority 101          
    advert_int 1         
    authentication {   
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {    
        192.168.124.188
    }
      
    track_script {               
       chk_mysql_port             
    }
    virtual_server 192.168.124.188 3306 {
        delay_loop 6               
        lb_algo wrr             
        lb_kind DR                 
        #nat_mask 255.255.255.0
        persistence_timeout 50     
        protocol TCP              
        
        ## Real Server设置，3306就是连接端口
        real_server 192.168.124.181 3306 {
            weight 3  ##权重
            TCP_CHECK {
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
                connect_port 3306
            }
        }
        real_server 192.168.124.182 3306 {
            weight 3
            TCP_CHECK {
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
                connect_port 3306
            }
        }
    }
}

vi /etc/keepalived/chk_mysql.sh

#!/bin/bash
counter=$(netstat -na| grep "LISTEN"|grep "3306" | wc -l)
if ["${counter}" - eq 0]; then
  /etc/init.d/keepalived stop
fi

service keepalived start
service keepalived status

查询当前使用的数据库

mysql -h 192.168.124.188 -uroot -p
show variables like 'server_id';