MySQL8.0.21+keepalived双机热备

zlong.w Lv2
  2023-09-26 15:12:01 2023-09-26 15:12:01 创建   2023-09-26 15:42:48 2023-09-26 15:42:48 更新      

  1. 环境准备
    虚拟机安装CentOS7，并且在官网下载MySQL8.0.21压缩文件，给两台服务器都安装MySQL
    配置双机主主同步，将两台MySQL都当成master,无论哪一个处理数据，另一个都会同步
    使用keepalived实现MySQL双主热备，无论谁宕机另一台服务可以马上顶上去。

  2. keepalived原理总结
    keepalived基于VRRP协议的软件实现，原生设计目的为了高可用 ipvs服务 ipvs，IP虚拟服务器（IP Virtual Server，简写为IPVS）。是运行在LVS下的提供负载均衡功能的一种技术。

    keepalived的功能：

    1. 基于vrrp协议完成地址流动
    2. 为vip地址所在的节点生成ipvs规则(在配置文件中预先定义)
    3. 为ipvs集群的各RS（后端的Real Server）做健康状态检测
    4. 基于脚本调用接口完成脚本中定义的功能，进而影响集群事务，以此支持nginx、haproxy等服务
    5. Keepalived高可用对之间是通过VRRP进行通信的，VRRP是通过竞选机制来确定主备节点的，主节点的优先级高于备节点，因此，工作时主节点会优先获得所有的资源，备节点处于等待状态，当主节点出现故障时，备节点就会接管主节点的资源，然后顶替主节点对外提供服务。
    6. 在Keepalived服务对之间，只有作为主节点的服务器会一直发送 VRRP广播包，告诉备节点它还活着，此时备节点不会抢占主节点资源，当主节点不可用时，即备节点监听不到主节点发送的广播包时，备节点就会启动相关服务接管主节点的资源，保证业务的连续性。接管速度最快可以小于1秒。

  3. 将MySQL上传至虚拟机中

  4. 创建基础文件加和用户组、用户

    1
    2
    3
    4
    > mkdir -p /mysql/{data,bin-log,slow-log,error-log,app,pid,socket,tmp} #创建文件夹
    > groupadd mysql #创建用户组
    > useradd -r -g mysql mysql  #创建用户，设置用户组为MySQL
    > chown -R mysql:mysql /mysql #将文件夹赋权给MySQL用户

  5. 解压MySQL压缩包,改名为app,移动至指定文件夹

    1
    2
    3
    4
    > tar -xvf mysql-8.0.21-linux-glibc2.12-x86_64.tar.xz #解压
    > mv mysql-8.0.21-linux-glibc2.12-x86_64 mysql #剪切改名
    > mv mysql /mysql/app #将文件夹剪切到设定好的目录下
    > cd /mysql/app/mysql && chown -R mysql:mysql ./ #进入mysql下并授权所有文件

  6. 创建my.cnf配置文件，并加入以下内容

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    [client]
    #服务端口号，默认3306
    port=3306 
    ##mysql的主机和客户机在同一host上的时候，使用unix domain socket做为通讯协议的载体
    socket=/mysql/socket/mysql.sock
    [mysqld]
    ##MySQL服务的唯一编号,每个MySQL服务ID需要唯一
    server-id=1 
    ##服务端口号，默认3306
    port=3306 
    ##查询模式,添加该行可支持分组查询时查询多列 (select c1,c2,c3,count(1) total from tables group by c1)
    sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
    ##mysql安装根目录
    basedir=/mysql/app/mysql 
    ##mysql的主机和客户机在同一host上的时候，使用unix domain socket做为通讯协议的载体
    socket=/mysql/socket/mysql.sock
    ##MySQL数据文件所在位置
    datadir=/mysql/data 
    ##记录的是当前MySQL进程的 pid
    pid-file=/mysql/pid/mysql.pid 
    ##设置临时目录
    tmpdir=/mysql/tmp
    ##用户
    user=mysql 
    ##允许访问的IP网段
    bind-address=0.0.0.0 
    ##skip-grant-tables 跳过密码登录
    ##skip-external-locking 主要用于MyISAM存储引擎,如果多台服务器连接一个数据库则建议注释下面内容
    ##skip_name_resolve = 1 只能用IP地址检查客户端的登录，不用主机名
    ##transaction_isolation = READ-COMMITTED 事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能）
    ##数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
    character-set-server = utf8mb4 
    ##数据库字符集对应一些排序等规则，注意要和character-set-server对应
    collation-server = utf8mb4_general_ci 
    ##设置client连接mysql时的字符集,防止乱码
    init_connect='SET NAMES utf8mb4' 
    ##是否对sql语句大小写敏感，1表示不敏感
    lower_case_table_names = 1 
    ##最大连接数
    max_connections = 400  
    ##最大错误连接数
    max_connect_errors = 1000 
    ##TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
    explicit_defaults_for_timestamp = true 
    ##SQL数据包发送的大小，如果有BLOB对象建议修改成1G
    max_allowed_packet = 1G  
    ##MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
    interactive_timeout = 1800 
    ##MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
    wait_timeout = 1800 
    ##内部内存临时表的最大值 ，设置成128M。
    ##比如大数据量的group by ,order by时可能用到临时表，
    ##超过了这个值将写入磁盘，系统IO压力增大
    #tmp_table_size = 134217728
    #max_heap_table_size = 134217728
    #
    ##禁用mysql的缓存查询结果集功能
    ##后期根据业务情况测试决定是否开启
    ##大部分情况下关闭下面两项
    #query_cache_size = 0
    #query_cache_type = 0
    #
    ##数据库错误日志文件
    log_error = /mysql/error_log/mysql_error.log
    ##慢查询sql日志设置
    slow_query_log = 1
    slow_query_log_file = /mysql/slow_log/mysql_slow_query.log
    ##检查未使用到索引的sql
    log_queries_not_using_indexes = 1
    ##针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数
    log_throttle_queries_not_using_indexes = 5
    ##作为从库时生效,从库复制中如何有慢sql也将被记录
    log_slow_slave_statements = 1
    ##慢查询执行的秒数，必须达到此值可被记录
    long_query_time = 8
    ##检索的行数必须达到此值才可被记为慢查询
    #min_examined_row_limit = 100
    ##开启 binlog ( 二进制 ) 日志，主要用来做增量备份跟主从同步
    log-bin=mysql-bin
    #忽略写入binlog日志的数据库
    binlog-ignore-db=mysql,information_schema
    #字段变化增量值
    auto-increment-increment=2
    #初始子段ID为1
    auto-increment-offset=1
    #忽略复制错误
    slave-skip-errors=all
    #binlog日志位置
    log_bin=/mysql/data/bin-log
    ##Mysql binlog 的日志格式，Statement、ROW 跟 Mixed（ 混合模式 ）
    binlog_format = mixed
    skip_ssl
    default-authentication-plugin=mysql_native_password
    ### 二进制日志缓冲大小，此参数是为每 Session 单独分配的，当一个线程开始一个事务时，Mysql 就会为此 Session 分配一个 binlog cache，当这个事务提交时，binlog cache 中的数据被写入 binlog 文件
    ### 通过 show status like 'binlog_cache%'; 来查看使用 binlog cache 的次数及使用磁盘的次数
    binlog_cache_size = 2M
    ### 这个参数对 Mysql 系统来说很重要，不仅影响到 binlog 对 Mysql 所带来的性能损耗，还影响到 Mysql 中数据的完整性。
    ### 值为 0 时代表事务提交后，Mysql 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘，而让 Filesystem 自行决定什么时候同步，或者 cache 满了之后才同步磁盘。
    ### 值为 n 时代表进行 n 次事务提交后，Mysql 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。
    ### 系统默认将此参数设置为 0 ，即不做任何强制性的磁盘刷新指令，性能最好，但是风险也最大。当系统崩溃时 binlog_cache 中的所有 binlog 信息都会丢失。
    ### 而设置为 1 时，是最安全但是性能损耗最大。当系统崩溃时，最多丢失 binlog_cache 中未完成的一个事务，对实际数据没有实质性的影响。
    sync_binlog = 0
    ## 保留 30 天的 binlog 日志，系统重启、执行 flush logs 或 binlog 日志文件大小达到上限时删除 binlog 日志
    #expire_logs_days = 30
    ##开启事件功能定时任务等
    event_scheduler=ON 
    default-time_zone='+8:00'
    ##innodb存储引擎默认缓冲池大小(推荐服务器内存的一半)
    innodb_buffer_pool_size=2G 
    innodb_buffer_pool_instances=4
    read_buffer_size=32M
    read_rnd_buffer_size=32M
    join_buffer_size=32M
    sort_buffer_size=64M
    ##读的IO线程数
    innodb_read_io_threads=16 
    ##写的IO线程数
    innodb_write_io_threads=16 
    table_open_cache=4000

  7. 将启动文件放入自启动目录

    1
    > cp /mysql/app/mysql/support-files/mysql.server /etc/init.d/mysqld

  8. 初始化MySQL

    1
    > bin/mysqld --initialize --user=mysql --console

  9. 去启动日志中找初始密码

    1
    > cat /mysql/error_log/mysql_error.log

  10. 启动MySQL并登录测试

    1
    2
    3
    4
    5
    6
    > systemctl daemon-reload   #需要先刷新daemon，不然会提示找不到MySQL服务
    > systemctl status mysqld   #查看服务状态
    > systemctl start mysqld    #启动服务
    > systemctl restart mysqld  #重启服务
    > systemctl stop mysqld     #停止服务
    > /mysql/app/mysql/bin/mysql -uroot -p密码   #登录

  11. 修改root密码并设置客户端访问MySQL服务的权限

    1
    2
    3
    4
    5
    6
    alter user 'root'@'localhost' identified by 'optms90582';  -- 修改密码
    use mysql; -- 切换数据库
    select * from user; -- 查看用户列表
    -- 设置访问权限为所有客户端主机均可访问,即更新root用户的host字段为'%'
    update user set host='%' where user='root'; 
    exit  --退出客户端 
    1
    2
    > systemctl restart mysqld  #重启MySQL服务
    > systemctl stop firewalld  #关闭防火墙

  12. 出现问题，登录时报错：Packages providing this file are:’mariadb’’mysql’

    1
    > ln -s /mysql/app/mysql/bin/mysql /usr/bin #将当前版本的MySQL软连接配置到/usr/bin下

  13. 出现问题，mysql -uroot -p报错：error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory

    1
    2
    3
    > find / -name 'libtinfo*' #全局查询该文件
    > ln -s /usr/lib64/libtinfo.so.6.2 /usr/lib64/libtinfo.so.5 #配置软连接，给6.2一个别名5

  14. 编辑mysql.cnf，最终的my.cnf见上文,修改完成后重启服务，注意两台服务的server-id必须是唯一的

  15. 查看Master的binlog日志和pos值的位置

    1
    2
    3
    4
    5
    6
     -- 查看master
    mysql> show master status;
        -- 例：显示结果： File=mysql-bin.000002  Position=120
    -- 查看Slave
    mysql> show master status;
        --例： File=mysql-bin.000006 Position=689

    16.两个服务增加同步用户

master: 192.168.206.135

slave: 192.168.206.136

配置同步用户时需要注意对方的bin-log和Position的数值，必须呼应上

1
2
3
4
5
6
7
8
9
10
11
12
13
-- master(192.168.206.135)执行：
sql> create user 'root'@'192.168.206.136' identified by 'optms90582';
sql> grant replication slave on *.* to 'root'@'192.168.206.136';
sql> change master to master_host='192.168.206.136',
    master_user='root',master_password='optms90582',
    master_log_file='bin-log.000001',master_log_pos=156;
--slave(192.168.206.136)执行：
sql> create user 'root'@'192.168.206.135' identified by 'optms90582';
sql> grant replication slave on *.* to 'root'@'192.168.206.135';
sql> change master to master_host='192.168.206.135',
    master_user='root',master_password='optms90582',
    master_log_file='mysql-bin.000008',master_log_pos=156;

17.主主同步配置完成，查看Slave_IO和Slave_SQL状态，都时YES的时候说明主主同步成功，可以创建数据库表进行测试

1
mysql> show slave status \G;

如果Slave_IO和Slave_SQL有一个不是YES的时候可能防火墙没关、两个服务IP不对应、Por值匹配不到、bin-log不一致，需要具体看看
18.两台服务器都需要安装keepalived，从网上下载keeplived-2.2.0源码压缩包，进行编译安装
19.解压压缩包，并将安装包放到创建好的目录下

1
2
3
> tar -zxvf keepalived-2.2.0.tar.gz
> mv keepalived-2.2.0 keepalived
> mv keepalived /mysql

20.进入主目录进行检查编译安装

1
2
3
> cd keepalived
> ./configure --prefix=/mysql/keepalived
> make & make install

21.修改配置

1
2
3
4
5
6
7
8
9
# 启动脚本变量引用文件
> cp /mysql/keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived
# 将keepalived 主程序加入到环境变量下
> cp /mysql/keepalived/sbin/keepalived /usr/sbin/keepalived
# 将启动脚本放到init.d目录下 Linux9的init.d目录在/etc/rc.d/init.d
> cp /mysql/keepalived/keepalived/etc/init.d/keepalived /etc/init.d/keepalived
# 将配置文件放到默认路径下
> mkdir /etc/keepalived
> cp /mysql/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf

22.启动

1
> service keepalived start

23.查看日志

1
> tail -f /var/log/message

24.检查时报错：OpenSSL is not properly installed on your system!!!,解决后重新执行检查

1
> yum -y install openssl openssl-devel

25.检查时报错：WARNING-this build will not support IPv6 please install libnl/libnl-3 dev libraries to support UPV6 with IPVS.解决后重新执行检查

1
> yum -y install libnl libnl-devel

26.在/etc/keepalived文件夹下新增shutdown.sh脚本，用于MySQL挂掉以后将当前keepalived服务停止(两个服务器都需要创建)

1
2
#!/bin/bash
killall keepalived

27.修改/etc/keepalived/keepalived.conf 根据实际情况修改配置文件生成虚拟IP(两个服务都需要修改)
master: 192.168.206.135

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
> vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_MASTER
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}
 
vrrp_instance VI_1 {
    state MASTER
    #interface为刚才查到的本机网卡名称
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
       #该ip为虚拟出来的vip地址
       192.168.206.130
    }
}
 
#配置virtual_server  ip为上面配置的虚拟vip地址  端口为mysql的端口
virtual_server 192.168.206.130 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP
    #real_server 该配置为实际物理机ip地址 以及实际物理机mysql端口
    real_server 192.168.206.135 3306 {
        #当该ip 端口连接异常时，执行该脚本
        notify_down /etc/keepalived/shutdow.sh
        TCP_CHECK {
            #实际物理机ip地址
            connect_ip 192.168.206.135
            connect_timeuot 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}

slave: 192.168.206.136

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
> vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_MASTER
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}
 
vrrp_instance VI_1 {
    state MASTER
    #interface为刚才查到的本机网卡名称
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
       #该ip为虚拟出来的vip地址
       192.168.206.130
    }
}
 
#配置virtual_server  ip为上面配置的虚拟vip地址  端口为mysql的端口
virtual_server 192.168.206.130 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP
    #real_server 该配置为实际物理机ip地址 以及实际物理机mysql端口
    real_server 192.168.206.136 3306 {
        #当该ip 端口连接异常时，执行该脚本
        notify_down /etc/keepalived/shutdow.sh
        TCP_CHECK {
            #实际物理机ip地址
            connect_ip 192.168.206.136
            connect_timeuot 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}

28.将keepalived加入开机自启，并启动

1
2
> systemctl enable keepalived
> systemctl start keepalived

29.使用MySQL客户端连接数据库，IP使用虚拟IP

30.创建函数报错

1
> set global log_bin_trust_function_creators=TRUE;
  • 标题: MySQL8.0.21+keepalived双机热备
  • 作者: zlong.w
  • 创建于 : 2023-09-26 15:12:01
  • 更新于 : 2023-09-26 15:42:48
  • 链接: https://zlonx.cn/2023/09/26/MySQL8-0-21-keepalived双机热备/
  • 版权声明: 本文章采用 CC BY-NC-SA 4.0 进行许可。
此页目录
MySQL8.0.21+keepalived双机热备