OneFamily,OneSchool,OneArmy

0%

在linux-aarach64下离线安装mysql8(互为主从、keeplived高可用)

本文作者 朱新培 发表于 分类于 ， Valine：
本文字数： 9.6k 阅读时长 ≈ 9 分钟

环境介绍

集群配置(互为主从|主备)

IP 配置 OS 集群标识
192.168.1.215 1C4T-4G centos-aarch64-7.6.1810 1 | MASTER-SLAVE
192.168.1.216 1C4T-4G centos-aarch64-7.6.1810 2 | SLAVE-MASTER

使用软件版本

软件 版本
OS centos-aarch64-7.6.1810
keepalived 1.3.5-16.el7.aarch64
MYSQL 8.0.21-arm
wi-mysql8-installer (内含MYSQL) 0.21-arm

[WI-MYSQL8-INSTALLER] 安装包下载

本教程中，使用了本地yum源作为离线安装，使用脚本自动修改初始密码及远程访问，自动配置主从模式，您只需根据脚本提示，最后做一下启动同步的命令，即可完成互为主备部署。

脚本安装

安装前确保 selinux 已经关闭

将脚本上传并解压至/wisoft目录

1
2
3
4
5
[root@mysql-master wisoft]# ll
total 923552
drwxr-xr-x. 4 root root        40 Sep 23 16:06 mysql
-rw-r--r--. 1 root root 945695368 Sep 18 12:29 wi-mysql8-installer-0.21-arm.tar.gz
-rwxr-xr-x. 1 root root      5072 Sep 18 12:27 wi-mysql8-install.sh

cd到/wisoft 并执行脚本 ./wi-mysql8-install.sh,选择一个IP作为主从通信的IP

1
2
3
4
5
[root@mysql-master wisoft]# ./wi-mysql8-install.sh
获取到本机IP, 请选择:
1) 192.168.1.215
#? 1
你选择了 192.168.1.215 作为本机安装的IP地址

接下来，脚本将自动安装，直至如下界面

1
2
3
4
5
6
7
安装MYSQL完成，正在还原yum源...
请输入mysql的数据目录,默认地址为[/wisoft/mysql/data],若目录不为空将自动清空:
路径 /wisoft/mysql/data 不存在，即将创建
请输入保证集群内唯一的mysql服务标识[1,2,3...] 1
1
请输入需要同步的数据库名称,多个用','分隔: test1,test2
test1,test2

mysql的数据目录 与实际挂载的数据盘有关，mysql默认安装在 /var/lib/mysql 这在实际运维中是不可取的。

test1,test2 在实际运维中是指你需要同步的数据库名称，这里仅演示用。

在操作完以上内容后脚本会自动启动mysql并获取到初始密码，如果你单机部署到此可以停止，退出。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
正在启动mysql服务。。。。
请等待10秒……
获取到初始密码为：K,zW,d&((4Q>
数据库安装完成，是否继续配置主从？
继续配置，输入[Y/N]y
正在修改密码及启用局域网访问...
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
wi-mysql-log-bin.000002 | 828 | test2,test1
CHANGE MASTER TO
MASTER_HOST='192.168.1.215',
MASTER_USER='wi_slave',
MASTER_PASSWORD='wisoft',
MASTER_LOG_FILE='wi-mysql-log-bin.000002',
MASTER_LOG_POS=828,
MASTER_SSL=1;
安装完毕，请将上述sql，在slave节点执行。

脚本将自动修改初始密码为 wisoft,并启用局域网访问

其中

1
2
3
4
5
6
7
CHANGE MASTER TO
MASTER_HOST='192.168.1.215',
MASTER_USER='wi_slave',
MASTER_PASSWORD='wisoft',
MASTER_LOG_FILE='wi-mysql-log-bin.000002',
MASTER_LOG_POS=828,
MASTER_SSL=1;

这段内容实际取自脚本生成的sql文件 /wisoft/master.sql,请将该语句在需要同步的mysql节点上，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@mysql-slave ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.215',
    -> MASTER_USER='wi_slave',
    -> MASTER_PASSWORD='wisoft',
    -> MASTER_LOG_FILE='wi-mysql-log-bin.000002',
    -> MASTER_LOG_POS=828,
    -> MASTER_SSL=1;
Query OK, 0 rows affected, 2 warnings (0.76 sec)

执行完成后，仍然在mysql终端内 执行命令 start slave 开启同步

1
2
mysql> start slave;
Query OK, 0 rows affected (0.06 sec)

执行 show slave status\G 查看同步的概况，主要看 Slave_IO_Running和Slave_SQL_Running是否yes

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
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.215
                  Master_User: wi_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: wi-mysql-log-bin.000002
          Read_Master_Log_Pos: 828
               Relay_Log_File: mysql-slave-relay-bin.000002
                Relay_Log_Pos: 331
        Relay_Master_Log_File: wi-mysql-log-bin.000002
             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: 828
              Relay_Log_Space: 546
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           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: fa81a126-fd73-11ea-afe5-6e153cfa2eaf
             Master_Info_File: mysql.slave_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:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.01 sec)

主从测试

接下来打开两边mysql客户端分别连上mysql本库

在主库服务器上,新建需要同步的数据库 create database test1;

在从库服务器上，查看数据库是否同步过来 show databases;

1
2
3
4
5
6
7
8
9
10
11
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
t rows in set (0.01 sec)

执行建表和插入语句查看从库是否能同步
建表 create table student1(id int(4) not null primary key);
插入 insert into student1 value(1);

1
2
3
4
5
6
7
8
mysql> select * from student1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

调换上述主从顺序，在从库中执行，看主库是否能够同步。

至此，互为主备部署已经完成

附安装过程视频：

高可用keepalived安装

cd到/wisoft 并执行脚本 ./wi-mysql8-install.sh ha,注意还是原来的脚本，只是后面增加ha参数

1
2
3
4
5
6
7
[root@mysql-master wisoft]# ./wi-mysql8-install.sh ha
获取到本机IP, 请选择:
1) 192.168.1.215
#? 1
你选择了 192.168.1.215 作为本机安装的IP地址
你将安装keepalived来实现mysql高可用，请输入虚IP的地址:192.168.1.222
192.168.1.222 eth0

如上，选择对应网卡后输入 虚IP地址，即可安装完成。

1
2
3
安装KEEPALIVED完成，正在配置...
Created symlink from /etc/systemd/system/multi-user.target.wants/keepalived.service to /usr/lib/systemd/system/keepalived.service.
keepalived启动成功,安装完成退出

高可用测试

首先查看 keepalived状态 systemctl status keepalived

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
[root@mysql-master wisoft]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2020-09-25 09:58:33 CST; 12s ago
  Process: 3552 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 3553 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─3553 /usr/sbin/keepalived -D
           ├─3554 /usr/sbin/keepalived -D
           └─3555 /usr/sbin/keepalived -D

Sep 25 09:58:38 mysql-master.wisoft.com.cn Keepalived_vrrp[3555]: Sending gratuitous AR...
Sep 25 09:58:38 mysql-master.wisoft.com.cn Keepalived_vrrp[3555]: Sending gratuitous AR...
Sep 25 09:58:38 mysql-master.wisoft.com.cn Keepalived_vrrp[3555]: Sending gratuitous AR...
Sep 25 09:58:38 mysql-master.wisoft.com.cn Keepalived_vrrp[3555]: Sending gratuitous AR...
Sep 25 09:58:43 mysql-master.wisoft.com.cn Keepalived_vrrp[3555]: Sending gratuitous AR...
Sep 25 09:58:43 mysql-master.wisoft.com.cn Keepalived_vrrp[3555]: VRRP_Instance(VI_1) S...
Sep 25 09:58:43 mysql-master.wisoft.com.cn Keepalived_vrrp[3555]: Sending gratuitous AR...
Sep 25 09:58:43 mysql-master.wisoft.com.cn Keepalived_vrrp[3555]: Sending gratuitous AR...
Sep 25 09:58:43 mysql-master.wisoft.com.cn Keepalived_vrrp[3555]: Sending gratuitous AR...
Sep 25 09:58:43 mysql-master.wisoft.com.cn Keepalived_vrrp[3555]: Sending gratuitous AR...
Hint: Some lines were ellipsized, use -l to show in full.
[root@mysql-master wisoft]# 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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 6e:15:3c:fa:2e:af brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.215/24 brd 192.168.1.255 scope global noprefixroute eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.222/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::b7c4:d716:c155:6dfe/64 scope link noprefixroute
       valid_lft forever preferred_lft forever

可以看到 虚IP 192.168.1.222 飘在哪台机器上。同时也可以通过 ip a 查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@mysql-master wisoft]# 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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 6e:15:3c:fa:2e:af brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.215/24 brd 192.168.1.255 scope global noprefixroute eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.222/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::b7c4:d716:c155:6dfe/64 scope link noprefixroute
       valid_lft forever preferred_lft forever

现在，通过 systemctl stop mysqld 将当前节点的mysql停止，模拟mysql宕机。

再次查看两台服务器 ip a

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[root@mysql-slave wisoft]# 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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 82:06:24:3b:b4:d5 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.216/24 brd 192.168.1.255 scope global noprefixroute eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.222/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::b7c4:d716:c155:6dfe/64 scope link tentative noprefixroute dadfailed
       valid_lft forever preferred_lft forever
    inet6 fe80::a4c4:4935:83dc:4a0a/64 scope link noprefixroute
       valid_lft forever preferred_lft forever

虚IP正常漂移到192.168.1.216上。

通过navicat观察，是否正常使用

keepalived安装测试过程视频