Keepalived+MySQL主从搭建

最新推荐文章于 2024-03-22 09:51:39 发布

洋洋子~

最新推荐文章于 2024-03-22 09:51:39 发布

阅读量1k

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_42550693/article/details/127238938

版权

Keepalived+MySQL主从搭建

实验环境

虚拟机两台操作系统版本：centos7.3

数据库版本：5.7.37

node1（主节点）：192.168.92.141

node2（从节点）：192.168.92.142

VIP：192.168.92.140

MySQL安装

软件包上传

分别在node1、node2使用scp工具上传mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz

注意：分别在node1 和node2 上执行

创建数据库用户和组

[root@node1 ~]# groupadd mysql

[root@node1 ~]# useradd mysql -g mysql

解压压缩包

[root@node1 ~]# tar xvfz mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/

重命名

[root@node1 ~]# mv /usr/local/mysql-5.7.37-linux-glibc2.12-x86_64 /usr/local/mysql

创建数据目录

[root@node1 ~]# mkdir -p /data/mysql/

创建日志目录

[root@node1 ~]# mkdir -p /data/log/

修改目录权限

[root@node1 ~]# chown mysql.mysql /data/

初始化MySQL数据库，记录数据库密码

[root@node1 ~]# cd /usr/local/mysql
[root@node1 mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
2022-10-09T06:43:27.426299Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-10-09T06:43:33.552521Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-10-09T06:43:34.450688Z 0 [Warning] InnoDB: Creating foreign key   constraint system tables.
2022-10-09T06:43:34.624646Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b25d3ec2-479d-11ed-a0c4-000c29c00593.
2022-10-09T06:43:34.649311Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-10-09T06:43:35.449087Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-10-09T06:43:35.449107Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-10-09T06:43:35.450086Z 0 [Warning] CA certificate ca.pem is self signed.
2022-10-09T06:43:36.274669Z 1 [Note] A temporary password is generated for root@localhost: L)0A5kZtfat!

修改MySQL配置文件

[root@node1 mysql]# vim /etc/my.cnf
[client]
port = 3306
[mysqld]
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/
socket=/tmp/mysql.sock
log-bin=/data/log/mysql-bin
log-error=/data/log/err.log
slow_query_log=on
slow_query_log_file =/data/log/slow.log
character-set-server = utf8mb4
init_connect='SET NAMES utf8mb4'
default_storage_engine = innodb
default_authentication_plugin=mysql_native_password
server-id=1   #注意server-id要不一致
default_authentication_plugin=mysql_native_password

修改环境变量

[root@node1 ~]# vim .bash_profile
添加 export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
[root@node1 ~]# source .bash_profile

更改启动文件

[root@node1 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@node1 ~]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/data/mysql

使用mysql用户启动数据库

[root@node1 log]# su - mysql
上一次登录：日 10月  9 15:24:26 CST 2022pts/0 上
[mysql@node1 ~]$ /etc/init.d/mysqld start
Starting MySQL. SUCCESS!

登录数据库修改初始root密码

[root@node1 log]# mysql -uroot -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37-log
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' identified by '12345';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

搭建mysql主从

修改node2的mysql配置文件

[root@node2 log]# vim /etc/my.cnf

添加relay_log=/data/log/mysql-relay

重启数据库

[mysql@node2 ~]$  /etc/init.d/mysqld stop
[mysql@node2 ~]$  /etc/init.d/mysqld start

在主库node1上创建用于同步的用户并赋权，密码为：repl

mysql> grant replication slave,replication client on *.*  to 'repl'@'%' identified by 'repl';
Query OK, 0 rows affected, 1 warning (0.01 sec)

查看node1的binlog和pos位置

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      853 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在node2上执行同步配置

mysql> change master to master_host='192.168.92.141',
    -> master_user='repl',
    -> master_port=3306,
    -> master_password='repl',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=853;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看从库状态，均为YES同步成功

mysql> show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.92.141
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1157
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 624
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1157
              Relay_Log_Space: 827
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: b25d3ec2-479d-11ed-a0c4-000c29c00593
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.01 sec)

安装keepalived

注意：在node1和 node2上分别执行

下载keepalived

[root@node1 log]# yum install keepalived

编写keepalived的配置文件

[root@node1 log]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
        HA_MYSQL
}

vrrp_script check_run_mysql
{
    script "/root/keepalived_shutdown.sh"   #检查mysql状态脚本
    interval 2    #每两秒检测一下
}

vrrp_instance VI_1 {
    state BACKUP   #都设置为BACKUP，设置非抢占模式，防止主库故障重起之后数据不同步，就进行VIP飘移
    interface ens32  #设置网卡
    virtual_router_id 51
    priority 100   # 优先级，node2要比100低
    advert_int 1
    nopreempt   #不主动抢占资源，非抢占模式
    authentication {
        auth_type PASS
        auth_pass 1111
    }

track_script {
    check_run_mysql    ##执行mysql监控服务脚本
}

    virtual_ipaddress {
        192.168.92.140    #VIP
    }
}

virtual_server 192.168.92.140 3306 {
    delay_loop 2   #每2秒检查一次
#    lb_algo rr
#    lb_kind NAT  LVS模式，如果不关闭，服务器不能通过VIP连接MySQL
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP
    real_server 192.168.92.141 3306 {   #IP为当前主机IP，3306为数据库端口
        weight 3
        notify_down /root/keepalived_shutdown.sh  #当mysql服务宕了时，执行杀死keepalieved脚本
          TCP_CHECK{
            connect_timeout 3  #连接超时时间
            nb_get_retry 3  #重连次数
            delay_before_retry 3  #重连间隔时间
            connect_port 3306  #检查的端口
        }
    }
}

编写/root/keepalived_shutdown.sh脚本

[root@node2 log]# vim  /root/keepalived_shutdown.sh

#!/bin/sh

check=$(/usr/local/mysql/bin/mysql -uroot -p12345 -e 'select 1' |sed -n '2p')

function error_query(){

        systemctl stop  keepalived

        echo "192.168.92.142  mysql  down, keepalived 切换"

}

 echo "$check"

 if [ "$check" != "1" ]

then

        error_query

fi

启动keepalived并设置开机自启

[root@node1 log]# systemctl start keepalived.service
[root@node1 log]# systemctl enable keepalived.service

查看VIP，当前VIP在主节点node1上

[root@node1 log]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:c0:05:93 brd ff:ff:ff:ff:ff:ff
    inet 192.168.92.141/24 brd 192.168.92.255 scope global ens32
       valid_lft forever preferred_lft forever
    inet 192.168.92.140/32 scope global ens32
       valid_lft forever preferred_lft forever
    inet6 fe80::7fc9:696b:7c6b:794d/64 scope link
       valid_lft forever preferred_lft forever

测试VIP切换情况

在slave1上通过VIP连接数据库，查看当前连接数据库为server_id为1是node1主机

[root@slave1 ~]# mysql -uroot -p12345 -h192.168.92.140
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

db01 [(none)]>show variables like 'server_id';
+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id     | 1     |

+---------------+-------+
1 row in set (0.09 sec)

在node1 停止mysql

[root@node1 ~]# pkill mysql

在slave1上查看当前数据库的server_id为2，从库提供服务

db01 [(none)]>show variables like 'server_id';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    28
Current database: *** NONE ***
+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id     | 2     |

+---------------+-------+

1 row in set (0.11 sec)

在node2上执行ip a 查看VIP已飘移

[root@node2 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
3: ens37: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:35:01:7a brd ff:ff:ff:ff:ff:ff
    inet 192.168.92.142/24 brd 192.168.92.255 scope global ens37
       valid_lft forever preferred_lft forever
    inet 192.168.92.140/32 scope global ens37
       valid_lft forever preferred_lft forever
    inet6 fe80::ba09:6c26:28e4:1289/64 scope link
       valid_lft forever preferred_lft forever