菜鸟挑战虚拟机linux7.9mysql双主热备份+keepalived过程记录

前言

菜鸟练手作，该踩的坑都踩了，留下记录以备后用。

版本：

mysql 8.0.24  https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.24-1.el7.x86_64.rpm-bundle.tar

其他版本可以在MySQL :: Download MySQL Community Server (Archived Versions)选择系统和版本后找到下载

bundle即为完整安装包，也可以找到下面的安装项逐个下载，其中资源名‘mysql-8.0.24-1.el8.x86_64.rpm-bundle.tar’内的el8代表支持的系统版本

keepalived 1.2.7  https://www.keepalived.org/software/keepalived-1.2.7.tar.gz

一、安装过程：

将mysql和keepalived安装包移入linux虚拟机，各自解压后以备使用，操作如下：

1.删除冲突前置

因linux系统可能自带mysql的分支mariaDB，与mysql冲突，所以要先查找是否有mariadb并删除

rpm -qa | grep mariadb

#返回查找结果

mariadb-XXX-XXX

#逐个删除

rpm -e --nodeps mariadb-XXX-XXX

rpm -qa | grep mariadb
#无返回结果说明已删除干净

2.安装mysql

进入mysql解压出的文件目录

[root@localhost ~]# cd /XXX/mysql-8.0.24-1.el7.x86_64.rpm-bundle/
#进入解压出的目录


[root@localhost mysql-8.0.24-1.el7.x86_64.rpm-bundle]# ls

mysql-community-client-8.0.24-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.24-1.el7.x86_64.rpm
mysql-community-common-8.0.24-1.el7.x86_64.rpm
mysql-community-devel-8.0.24-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.24-1.el7.x86_64.rpm
mysql-community-libs-8.0.24-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.24-1.el7.x86_64.rpm
mysql-community-server-8.0.24-1.el7.x86_64.rpm
mysql-community-test-8.0.24-1.el7.x86_64.rpm

#使用rpm工具按顺序安装

rpm -ivh mysql-community-common-8.0.24-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.24-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.24-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.24-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.24-1.el7.x86_64.rpm

#剩余的文件不必要安装

 3.安装keepalived

进入keepalived的解压文件夹

# cd /XXX/keepalived-1.2.7

[root@localhost keepalived-1.2.7]# ./configure --prefix=/usr/local/keepalived --sysconf=/etc

 安装如遇到问题可参考处理keepalived 安装教程（centOS 7.x）_centos7安装keepalived-CSDN博客

二、配置mysql

1.设置启动

设置mysql开机自启动并启动mysql

[root@localhost /]# systemctl enable mysqld  #设置自启动

[root@localhost /]# systemctl start mysqld  #启动mysql进程

 #查看进程状态
[root@localhost /]# systemctl status mysqld 
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 一 2023-11-06 23:10:03 CST; 26min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1666 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 2488 (mysqld)
   Status: "Server is operational"
    Tasks: 38
   CGroup: /system.slice/mysqld.service
           └─2488 /usr/sbin/mysqld

11月 06 23:09:45 localhost.localdomain systemd[1]: Starting MySQL Server...
11月 06 23:10:03 localhost.localdomain systemd[1]: Started MySQL Server.
#Active: active (running)证明启动成功

2.进入mysql

因为mysql初始会生成临时密码，需要用临时密码进入再修改密码使用

[root@localhost /]# grep 'temporary password' /var/log/mysqld.log

#查找mysql日志内的临时密码

2023-11-06T15:09:56.187693Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: uss!wk.fk6fN

# 密码为 uss!wk.fk6fN

[root@localhost /]# mysql -uroot -p
Enter password:          #弹出本条后可粘贴密码后enter进入mysql，此处无输入反馈

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.24

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.

 接下来修改root用户并赋权

# 修改root账号的密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '1';# 

# 创建slave账号以供同步使用
CREATE USER 'slave'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '1';
# 对USER账号授权
GRANT ALL ON *.* TO 'slave'@'%';
# 刷新权限
FLUSH PRIVILEGES;

为操作简单，设定了非常简单的密码，如密码策略不允许可能返回错误

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

如果需要设定简易密码就需要修改密码策略，可通过以下命令更改后再修改密码

#密码至少要包含的小写字母个数和大写字母个数
mysql>set global validate_password.mixed_case_count=0;

#密码至少要包含的数字个数。
mysql>set global validate_password.number_count=0; 

#密码至少要包含的特殊字符数
mysql>set global validate_password.special_char_count=0; 

#密码长度
mysql>set global validate_password.length=0; 

 3.更改数据库配置文件my.cnf

[root@localhost /]# vim /etc/my.cnf #打开mysql配置文件

...
datadir=/var/lib/mysql               
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#这四条为配置文件、错误日志和运行日志各自位置，可根据日志辨别错误类型

# 使用insert键切为插入模式，在后面增加配置参数
# 配置server_id 不可冲突，从机可更改为222
server_id=111
# MySQL的日志文件的名字，从机更改为mysql_slave
log-bin=mysql_master
# 作为从库时 更新操作是否写入日志 on：写入  其他数据库以此数据库做主库时才能进行同步
log-slave-updates=on

# MySQL系统库的数据不需要同步 我们这里写了3个  更加保险
# 同步数据时忽略一下数据库 但是必须在使用use db的情况下才会忽略；如果没有使用use db 比如create user  数据还是会同步的
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
# 使用通配符忽略MySQL系统库的表  这样在create user时也不会进行同步了
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%
# MySQL系统库的日志不计入binlog 这样更加保险了
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
                        
#配置完成后按ESC退出插入模式，使用 ：wq！ 命令保存并关闭my.cnf                 

 

#从机更改以下两条
server_id=222
log-bin=mysql_slave

4.配置mysql主从同步

mysql> show master status;
+---------------------+----------+--------------+-------------------------------------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+---------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql_master.000001 |      156 |              | information_schema,mysql,performance_schema,sys |                   |
+---------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

#log名为mysql_master.000001，position=156，接下来要在从机配置对主机的连接

mysql>CHANGE MASTER TO MASTER_HOST='192.168.10.111',MASTER_PORT=3306,MASTER_USER='repl_master',MASTER_PASSWORD='Lft@2021',MASTER_LOG_FILE='mysql_master.000001',MASTER_LOG_POS=156;

#以上填入主机的IP、数据库端口、数据库账户密码、log名和log位置

# 开启从库
START SLAVE;

#按照以上流程在主机上重复，建立主到从的连接，事实上形成互为主机的同步方式

#关闭防火墙后验证连接

[root@localhost ~]# systemctl restart mysqld

# 查看从库的状态
mysql> show slave status
    -> ;
+----------------------------------+----------------+-------------+-------------+---------------+--------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+-------------------------------------------------+--------------------+------------------------+-------------------------+---------------------------------------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Slave_IO_State                   | Master_Host    | Master_User | Master_Port | Connect_Retry | Master_Log_File    | Read_Master_Log_Pos | Relay_Log_File             | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB                             | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table                             | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |
+----------------------------------+----------------+-------------+-------------+---------------+--------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+-------------------------------------------------+--------------------+------------------------+-------------------------+---------------------------------------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Waiting for master to send event | 192.168.10.111 | slave       |        3306 |            60 | mysql_slave.000012 |                 156 | localhost-relay-bin.000055 |           375 | mysql_slave.000012    | Yes              | Yes               |                 | information_schema,mysql,performance_schema,sys |                    |                        |                         | information_schema.%,mysql.%,performance_schema.%,sys.% |          0 |            |            0 |                 156 |             758 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |              222 | 8364f2e1-7988-11ee-a025-000c29f91170 | mysql.slave_master_info |         0 |                NULL | Slave has read all relay log; waiting for more updates |              86400 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |                        |                     0 |                   |
+----------------------------------+----------------+-------------+-------------+---------------+--------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+-------------------------------------------------+--------------------+------------------------+-------------------------+---------------------------------------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
1 row in set, 1 warning (0.00 sec)

三、配置keepalived

1.keepalived作用

功能为在数台计算机间创建一个共享的虚拟IP，外部机器可通过虚拟IP访问这个集群中的当前作为虚拟IP载体的计算机，当该计算机触发了虚拟IP的转移规则后，虚拟IP就会更换载体，以保证外部访问不变。

思路为设定虚拟IP跳转规则为定时检测数据库对应端口健康状态，若数据库返回状态不正常，则触发跳转。

2.配置

cd /etc/keepalived/
#将原有keepalived.conf更名为keepalived.conf.bak作为备份
#创建新的配置文件

[root@localhost keepalived]# pwd
/etc/keepalived
[root@localhost keepalived]# vim keepalived.conf


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_DEVEL   
    vrrp_skip_check_adv_addr   
    #vrrp_strict   
    vrrp_garp_interval 0  
    vrrp_gna_interval 0
}
# 检查mysql服务是否存活的脚本
vrrp_script chk_mysql{   
    script "/usr/bin/killall -0 mysqld"
}
# vrrp配置虚IP
vrrp_instance VI_1 
{    
    # 状态：state MASTER  另外一台机器为BACKUP   
    state BACKUP
    nopreempt  
    # 绑定的网卡  
    interface ens33   
    # 虚拟路由id  两台机器需保持一致  
    virtual_router_id 51   
    # 优先级 MASTER的值要大于BACKUP   
    priority 100    
    advert_int 1    
    authentication {       
        auth_type PASS      
        auth_pass 1111   
    }   
    # 虚拟IP地址 两台keepalived需要一致   
    virtual_ipaddress {       
        192.168.10.120    
    }  
    # 检查脚本 vrrp_script的名字   
   # track_script {     
    #   chk_mysql   
   # }
}
virtual_server 192.168.10.120  3306 {
    delay_loop 20
    lb_algo wrr
    lb_kind DR
    persistence_timeout 10
    protocol TCP

    real_server 192.168.10.111  3306{
        weight 1
       notify_down /etc/keepalived/closekeepalived.sh
       TCP_CHECK {
            connect_timeout 1
            nb_get_retry 1
            delay_before_retry 1
            connect_port 3306
        }
    }
}

 3.编写脚本

[root@localhost keepalived]# vim closekeepalived.sh

sudo -S killall keepalived

四、验证

1.查看主机ip

#查看ip是否有虚拟IP确认生效

[root@localhost keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    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: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:a1:6b:43 brd ff:ff:ff:ff:ff:ff
    inet 192.168.10.111/24 brd 192.168.10.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.10.120/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::b10a:a019:9086:6c08/64 scope link tentative noprefixroute dadfailed 
       valid_lft forever preferred_lft forever
    inet6 fe80::5a7e:dc15:57c8:6b6b/64 scope link tentative noprefixroute dadfailed 
       valid_lft forever preferred_lft forever
    inet6 fe80::cb7:65a3:f0e0:17ad/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:67:72:bb brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc fq_codel master virbr0 state DOWN group default qlen 1000
    link/ether 52:54:00:67:72:bb brd ff:ff:ff:ff:ff:ff
[root@localhost keepalived]# 

2.验证跳转

在虚拟IP载体的主机暂停mysql服务，查看是否虚拟IP跳转至从机10.112

主机

[root@localhost ~]# systemctl stop mysqld

从机

[root@localhost ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    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: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:f9:11:70 brd ff:ff:ff:ff:ff:ff
    inet 192.168.10.112/24 brd 192.168.10.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.10.120/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::5a7e:dc15:57c8:6b6b/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:67:72:bb brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc fq_codel master virbr0 state DOWN group default qlen 1000
    link/ether 52:54:00:67:72:bb brd ff:ff:ff:ff:ff:ff

发现已跳转，搭建成功