# Keepalived+MySQL双主复制高可用配置

**Repository Path**: police-mini/keepalived_mysql

## Basic Information

- **Project Name**: Keepalived+MySQL双主复制高可用配置
- **Description**: MySQL双主复制 搭配keepalived 搭建高可用数据库
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 1
- **Created**: 2023-08-09
- **Last Updated**: 2023-08-09

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## MySQL 高可用搭建过程

### 安装docker、docker-compose
```shell script
yum install -y yum-utils
yum-config-manager --add-repo https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
yum install docker-ce docker-ce-cli containerd.io
systemctl start docker

curl -L https://github.com/docker/compose/releases/download/v2.14.0/docker-compose-linux-x86_64 -o /usr/local/bin/docker-compose
chmod +x /usr/local/bin/docker-compose

yum -y install psmisc # 安装killall命令
```


### 主一 . 搭建MySQL双主同步 - 在 master1 文件夹下
```shell script
docker-compose -f ./mysql.yaml up -d
```
进入MySQL内
```mysql
CREATE USER repl identified with mysql_native_password by 'repl123456';
GRANT REPLICATION SLAVE ON *.* to 'repl'@'%';
flush privileges;
-- 添加写锁，防止设置过程中，写入数据，导致binlog和pos发生变化
flush tables with read lock;
-- 查看当前MySQL的master_log_file和master_log_position的值
show master status ;
unlock tables ;
stop slave ;
change master to master_host='192.168.56.112',master_port=3306,master_user='repl',master_password='repl123456',master_log_file='mysql-bin.000003',master_log_pos=844;
start slave ;
show slave status;
```
> mysql 8开始已经更换默认的密码加密插件，所以给从库账号创建密码时，需要用之前默认的插件 mysql_native_password
> show slave status 中 Slave_IO_Running 和 Slave_SQL_Running 都为Yes即为成功



### 主二 . 搭建MySQL双主同步 - 在 master2 文件夹下
```shell script
docker-compose -f ./mysql.yaml up -d
```
进入MySQL内
```mysql
CREATE USER repl identified with mysql_native_password by 'repl123456';
GRANT REPLICATION SLAVE ON *.* to 'repl'@'%';
flush privileges;
-- 添加写锁，防止设置过程中，写入数据，导致binlog和pos发生变化
flush tables with read lock;
-- 查看当前MySQL的master_log_file和master_log_position的值
show master status ;
unlock tables ;
stop slave ;
change master to master_host='192.168.56.111',master_port=3306,master_user='repl',master_password='repl123456',master_log_file='mysql-bin.000004',master_log_pos=856;
start slave ;
show slave status;
```
> mysql 8开始已经更换默认的密码加密插件，所以给从库账号创建密码时，需要用之前默认的插件 mysql_native_password
> show slave status 中 Slave_IO_Running 和 Slave_SQL_Running 都为Yes即为成功


> 注意：这里是使用的docker容器部署的MySQL，存在两个服务器的容器无法互相访问的问题，导致设置同步失败。

### 安装keepalived
* 服务器1
```shell script
yum -y install keepalived
```
修改keepalived配置文件(/etc/keepalived/keepalived.conf)
```shell script
global_defs {
    router_id mysql_master
    script_user root
    enable_script_security
}

vrrp_script check_mysql {
   script "/etc/keepalived/chk_mysql.sh"
   interval 5
   weight -10
   fall 2
   rise 1
}

vrrp_instance mysql {
    state MASTER
    interface enp0s8
    virtual_router_id 51
    priority 150
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.56.233
    }

    track_script {
        chk_mysql
    }
}
```
/etc/keepalived/chk_mysql.sh
```shell script
#!/bin/bash
counter=$(netstat -na | grep "LISTEN" | grep "3306" | wc -l)
if [ ${counter} -eq 0 ]; then
    killall keepalived
fi
```
* 服务器2
```shell script
yum -y install keepalived
```
修改keepalived配置文件(/etc/keepalived/keepalived.conf)
```shell script
global_defs {
    router_id mysql_master
    script_user root
    enable_script_security
}

vrrp_script check_mysql {
   script "/etc/keepalived/chk_mysql.sh"
   interval 5
   weight -10
   fall 2
   rise 1
}

vrrp_instance mysql {
    state BACKUP
    interface enp0s8
    virtual_router_id 51
    priority 130
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.56.233
    }

    track_script {
        chk_mysql
    }
}
```
/etc/keepalived/chk_mysql.sh
```shell script
#!/bin/bash
counter=$(netstat -na | grep "LISTEN" | grep "3306" | wc -l)
if [ ${counter} -eq 0 ]; then
    killall keepalived
fi
```

[MySQL主从复制](./MySQL主从复制/MySQL8.0.31主从复制.md)