mysql5.7+keepalived主主配置实战傻瓜教程

悠悠 2023-10-08 21:29 23阅读 0赞

### 概述： ###

![2c3d72e74345456ab960c6307cf8eb95.png][]

MySQL 5.7和Keepalived主主配置架构是一种高可用性架构，它可以确保在一个节点出现故障时，系统仍然能够正常运行。在这种架构中，有两个MySQL节点，每个节点都是主节点和从节点的组合，它们共享相同的数据。 Keepalived是一个开源的软件，可以在多个服务器之间实现负载均衡和故障转移。

在MySQL 5.7和Keepalived主主配置架构中，Keepalived用于监控MySQL节点的状态，并在一个节点出现故障时，将流量转移到另一个节点上。

这种架构的好处是：

1.高可用性：由于有两个节点，因此即使其中一个节点出现故障，系统仍然能够正常运行。

2.负载均衡：两个节点可以共享相同的数据，从而实现负载均衡，提高系统的性能。

3.故障转移：当一个节点出现故障时，Keepalived可以自动将流量转移到另一个节点上，从而保证系统的可用性。

4.数据一致性：由于两个节点共享相同的数据，因此在一个节点上进行的更改会立即反映在另一个节点上，从而保证数据的一致性。

### **一、mysql安装部分** ###

#### **1.1 创建/mysql文件目录** ####

pvcreate /dev/sdb
    
    vgcreate mysqlvg/dev/sdb
    
    lvcreate -l 100%FREE -n mysqllv mysqlvg
    
    mkfs.xfs /dev/mapper/mysqlvg-mysqllv
    
    mkdir /mysql
    
    mount /dev/mapper/rhel-mysql /mysql

#### **1.2 安装mysql依赖包** ####

rpm -qa|grep mariadb
    
    rpm -e  mariadb-libs     --nodeps
    
    yum -y install numactl libaio

#### **1.3 解压文件** ####

tar -xvf mysql-5.7.29-el7-x86_64.tar
    
    tar -zxvf mysql-5.7.29-el7-x86_64.tar.gz
    
    mv mysql-5.7.29-el7-x86_64 /mysql/mysqlbase

#### **1.4 创建应用目录和数据目录** ####

mkdir /mysql/data
    
    mkdir /mysql/logs

#### **1.5 创建用户，用户组** ####

groupadd mysql
    
    useradd -r -g mysql mysql
    
    chown -R mysql.mysql /mysql

#### **1.6 安装mysql** ####

cd /mysql/mysqlbase/bin
    
    ./mysqld --initialize --user=mysql --basedir=/mysql/mysqlbase/ --datadir=/mysql/data/
    
    cd /mysql/mysqlbase/support-files/
    
    cp mysql.server /etc/init.d/mysql
    
    mkdir -p /var/lib/mysql
    
    chown -R mysql.mysql /var/lib/mysql

#### **1.7 基本的单点的mysql配置文件** ####

vi /etc/my.cnf
    
    [client]
    
    socket=/var/lib/mysql/mysql.sock
    
    [mysql]
    
    socket=/var/lib/mysql/mysql.sock
    
    [mysqld]
    
    log_error=/mysql/data/err.log
    
    port = 3306
    
    socket=/var/lib/mysql/mysql.sock
    
    basedir=/mysql/mysqlbase
    
    datadir=/mysql/data

#### **1.8启停mysql** ####

/etc/init.d/mysql  start
    
    ln -s /mysql/mysqlbase/bin/mysql /usr/bin
    
    /etc/init.d/mysql  stop

#### **1.8 修改root密码** ####

1.先使用默认密码登录mysql
    
    mysql -uroot -paj!w#:utp7DI
    
    2.修改root密码
    
    设置本地root登录密码
    
    set password for root@localhost = password('root2019!');
    
    设置远程root登录密码
    
    set password for root@'%' = password('root2019!');
    
    flush privileges;

#### **1.9 其他mysql参考命令** ####

**1.创建数据库**

CREATE DATABASE IF NOT EXISTS tenxcloud_2_0 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

**2创建用户**

create user 'root0532'@'%' identified by 'root2019!';

注意：

此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。

**3用户分配权限**

--授予用户test通过外网IP对数据库“tenxcloud_2_0”的全部权限
    grant all privileges on *.* to root0532@localhost identified by 'Root2019!';
    grant all privileges on root0532.* to root0532@'%' identified by 'Root2019!';
    --刷新权限
    flush privileges;

**4.查看用户权限**

show grants for root0532;

**5.查询用户**

select user,host from mysql.user;

**6.删除用户**

drop user root0532@localhost;
    
    --若创建的用户允许任何电脑登陆，删除用户如下
    
    drop user root0532@'%';

**7.更改密码**

--方法1，密码实时更新；修改用户“test”的密码为“1122”
    
    set password for root0532 =password('1122');
    
    --方法2，需要刷新；修改用户“test”的密码为“1234”
    
    update  mysql.user set  password=password('1234')  where user='root0532'
    
    --授予用户“test”通过外网IP对于该数据库“testdb”中表的创建、修改、删除权限,以及表数据的增删查改权限
    
    grant create,alter,drop,select,insert,update,delete on testdb.* to root0532@'%';

### **二、主主复制配置**  ###

#### **2.1、准备配置文件** ####

节点1 /etc/my.cnf配置文件内容

[client]
    
    socket=/var/lib/mysql/mysql.sock
    
    [mysql]
    
    socket=/var/lib/mysql/mysql.sock
    
    [mysqld]
    
    log_error=/mysql/data/err.log
    
    port = 3306
    
    socket=/var/lib/mysql/mysql.sock
    
    basedir=/mysql/mysqlbase
    
    datadir=/mysql/data
    
    server-id = 1
    
    auto_increment_offset = 1
    
    auto_increment_increment = 2                                           
    
    log-bin = mysql-bin                                                    
    
    binlog-format=ROW
    
    gtid-mode=on
    
    enforce-gtid-consistency=true
    
    master-info-repository=TABLE
    
    relay-log-info-repository=TABLE
    
    sync-master-info=1
    
    slave-parallel-workers=0
    
    sync_binlog=0
    
    binlog-checksum=CRC32
    
    master-verify-checksum=1
    
    slave-sql-verify-checksum=1
    
    binlog-rows-query-log_events=1
    
    #expire_logs_days=5
    
    max_binlog_size=1024M

节点2 /etc/my.cnf配置文件内容

[client]
    
    socket=/var/lib/mysql/mysql.sock
    
    [mysql]
    
    socket=/var/lib/mysql/mysql.sock
    
    [mysqld]
    
    log_error=/mysql/data/err.log
    
    port = 3306
    
    socket=/var/lib/mysql/mysql.sock
    
    basedir=/mysql/mysqlbase
    
    datadir=/mysql/data
    
    server-id = 2
    
    auto_increment_offset = 2
    
    auto_increment_increment =2                                                                                                             
    
    log-bin = mysql-bin
    
    binlog-format=ROW
    
    gtid-mode=on
    
    enforce-gtid-consistency=true
    
    master-info-repository=TABLE
    
    relay-log-info-repository=TABLE
    
    sync-master-info=1
    
    slave-parallel-workers=0
    
    sync_binlog=0
    
    binlog-checksum=CRC32
    
    master-verify-checksum=1
    
    slave-sql-verify-checksum=1
    
    binlog-rows-query-log_events=1
    
    #expire_logs_days=5
    
    max_binlog_size=1024M

#### **2.2 添加主从同步账户** ####

节点1上：

mysql> grant replication slave on *.* to 'repl'@'10.50.2.58' identified by '123456';
    
    mysql> flush privileges;

节点2上：

mysql> grant replication slave on *.* to 'repl'@'10.50.2.57' identified by '123456';
    
    mysql> flush privileges;

#### **2.3 查看主库的状态** ####

节点1上：
    
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000003 |      120 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
    节点2上
    
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000003 |      437 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

#### **2.4 配置同步信息：** ####

注意点： 节点1要配置节点2上看到的master\_log\_file和master\_log\_pos信息。

节点1上：

mysql> change master to master_host='10.50.2.58',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=608;
    
    mysql> start slave;
    
    mysql> show slave status\G;

显示有如下状态则正常：

**Slave\_IO\_Running: Yes**

**Slave\_SQL\_Running: Yes**

节点2上：

\#本人是测试环境，可以保证没数据写入，否则需要的步骤是：先节点1锁表-->节点1备份数据-->节点1解锁表 -->节点2导入数据-->节点2设置主从-->查看主从

mysql> change master to master_host='10.50.2.57',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=608;
    
    start slave;
    
    mysql> show slave status\G;

#### **2.5 测试主从同步** ####

3.1 分别在节点1上和节点2上创建一个数据库测试同步效果

### **三、keepalived安装配置篇** ###

#### **3.1 软件安装并配置开机自启动** ####

yum install keepalived -y
    
    systemctl start keepalived
    
    systemctl enable  keepalived

#### **3.2 配置文件及监听脚本配置** ####

节点1 keepalived.conf文件内容

cat /etc/keepalived/keepalived.conf
    
    global_defs {
    
    }
    
    
    
    vrrp_script chk_port {
    
      script "/etc/keepalived/check_port.sh 3306"
    
      interval 2
    
    }
    
    vrrp_instance VI_1 {
    
    state MASTER
    
    interface ens192
    
    virtual_router_id 123
    
    priority 150
    
    advert_int 1
    
    authentication {
    
    auth_type PASS
    
    auth_pass proxy776
    
    }
    
    virtual_ipaddress {
    
           10.50.2.56/24 dev ens192 label ens192:vip
    
    }
    
    
    
    track_script {
    
        chk_port
    
    }
    
    
    
    }

节点2 keepalived.conf

global_defs {
    
    }
    
    vrrp_script chk_port {
    
      script "/etc/keepalived/check_port.sh 3306"
    
      interval 2
    
    }
    
    vrrp_instance VI_1 {
    
    state BACKUP
    
    interface ens192
    
    virtual_router_id 123
    
    priority 130
    
    advert_int 1
    
    authentication {
    
    auth_type PASS
    
    auth_pass proxy776
    
    }
    
    virtual_ipaddress {
    
           10.50.2.56/24 dev ens192 label ens192:vip
    
    }
    
    
    
    track_script {
    
        chk_port
    
    }
    
    
    
    }

检测mysql活动状态脚本check\_port.sh

cat /etc/keepalived/check\_port.sh

#!/bin/bash
    
    
    
    for p in $*;do
    
           </dev/tcp/127.0.0.1/$p
    
           if [ $? -ne 0 ]; then
    
                  systemctl stop keepalived.service
    
                  echo "`date +%Y-%m-%d_%H:%M:%S` [ $p ] connected failed !" >> /var/log/keepalived_check.log
    
                  exit 1
    
           fi
    
    done

#### **3.3 测试验证** ####

[2c3d72e74345456ab960c6307cf8eb95.png]: https://img-blog.csdnimg.cn/2c3d72e74345456ab960c6307cf8eb95.png