linux-keepalived-05 keepalived+HAProxy+Mysql（双主）部署

发表于 | 更新于 | 分类于 ， | 评论数： | 热度： ℃
linux-keepalived-05 keepalived+HAProxy+Mysql（双主）部署

linux-keepalived-05 keepalived+HAProxy+Mysql（双主）部署

环境准备

node1（HAProxy1+keepalived主）：192.168.141.53
node2（HAProxy2+keepalived从）：192.168.141.69
node3（Mysql1主）：192.168.141.12
node4（Mysql2主）：192.168.141.132
VIP：192.168.10.100

Mysql部署（双主）

1、部署数据库

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
node3:
[root@node3 ~]# vim node3.sh
[root@node3 ~]# cat node3.sh 
#!/bin/bash
Ip_addr="192.168.141.132" # 修改为对端的node地址
User_pwd="000000"
systemctl stop firewalld
setenforce 0
yum install mariadb-server -y
sed -i '/^\[mysqld\]$/a\binlog-ignore = information_schema' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\binlog-ignore = mysql' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\skip-name-resolve' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\auto-increment-increment = 1' /etc/my.cnf.d/server.cnf # 注意node4节点上必须不同
sed -i '/^\[mysqld\]$/a\log-bin = mysql-bin' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\auto_increment_offset = 1'  /etc/my.cnf.d/server.cnf # 注意node4节点上必须不同
sed -i '/^\[mysqld\]$/a\server-id = 1' /etc/my.cnf.d/server.cnf # 注意node4节点上必须不同
systemctl restart mariadb
mysql -uroot -e "grant replication slave on *.* to 'repuser'@'$Ip_addr' identified by '$User_pwd';"
[root@node3 ~]# chmod +x ./node3.sh 
[root@node3 ~]# ./node3.sh 


node4:
[root@node4 ~]# vim node4.sh
[root@node4 ~]# cat node4.sh 
#!/bin/bash
Ip_addr="192.168.141.12" # 修改为对端的node地址
User_pwd="000000"
systemctl stop firewalld
setenforce 0
yum install mariadb-server -y
sed -i '/^\[mysqld\]$/a\binlog-ignore = information_schema' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\binlog-ignore = mysql' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\skip-name-resolve' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\auto-increment-increment = 2' /etc/my.cnf.d/server.cnf # 注意node3节点上必须不同
sed -i '/^\[mysqld\]$/a\log-bin = mysql-bin' /etc/my.cnf.d/server.cnf
sed -i '/^\[mysqld\]$/a\auto_increment_offset = 2'  /etc/my.cnf.d/server.cnf # 注意node3节点上必须不同
sed -i '/^\[mysqld\]$/a\server-id = 2' /etc/my.cnf.d/server.cnf # 注意node3节点上必须不同
systemctl restart mariadb
mysql -uroot -e "grant replication slave on *.* to 'repuser'@'$Ip_addr' identified by '$User_pwd';"
[root@node4 ~]# chmod +x ./node4.sh 
[root@node4 ~]# ./node4.sh

2、初始化数据库，初始密码为空，直接回车即可。然后设置数据库root密码为123456，之后一路敲y回车即可。node3如下：

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
[root@node3 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@node3 ~]#

node4如下：

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
[root@node4 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@node4 ~]#
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
[root@node3 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000003 |     1642 |              | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 



查询node4节点master状态
[root@node4 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000003 |     1721 |              | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>



在node3节点执行连接命令：
MariaDB [(none)]> change master to master_host='192.168.141.132',master_port=3306,master_user='repuser',master_password='000000',master_log_file='mysql-bin.000003',master_log_pos=1642;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> use  mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> 





在node4节点执行连接命令：
MariaDB [(none)]> change master to master_host='192.168.141.12',master_port=3306,master_user='repuser',master_password='000000',master_log_file='mysql-bin.000003',master_log_pos=1721;
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]>

注意：master_user=’repuser’,master_password=’000000’设置的用户名密码，

master_log_file=’mysql-bin.000003’,master_log_pos=1721中的值对应的是上面的File和Position的值。

HAProxy部署

在node1和node2上执行以下脚本：

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
[root@node1 ~]# vim node1_haproxy.sh
[root@node1 ~]# cat node1_haproxy.sh 
#!/bin/bash
yum install haproxy -y
mv /etc/haproxy/haproxy.cfg{,.bak}
cat > /etc/haproxy/haproxy.cfg << EOF
global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    stats socket /var/lib/haproxy/stats
listen mysql_proxy
    bind    0.0.0.0:3306
    mode    tcp
    balance source
    server  mysqldb1    192.168.141.12:3306  weight  1   check
    server  mysqldb2    192.168.141.132:3306  weight  2   check
listen stats
    mode http
    bind    0.0.0.0:8080
    stats   enable
    stats   uri /dbs
    stats   realm   haproxy\    statistics
    stats   auth    admin:admin
EOF
systemctl start haproxy
[root@node1 ~]# chmod +x node1_haproxy.sh 
[root@node1 ~]# ./node1_haproxy.sh 


node2和node1脚本内容是一样的
[root@node2 ~]# vim node2_haproxy.sh
[root@node2 ~]# cat node2_haproxy.sh 
#!/bin/bash
yum install haproxy -y
mv /etc/haproxy/haproxy.cfg{,.bak}
cat > /etc/haproxy/haproxy.cfg << EOF
global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    stats socket /var/lib/haproxy/stats
listen mysql_proxy
    bind    0.0.0.0:3306
    mode    tcp
    balance source
    server  mysqldb1    192.168.141.12:3306  weight  1   check
    server  mysqldb2    192.168.141.132:3306  weight  2   check
listen stats
    mode http
    bind    0.0.0.0:8080
    stats   enable
    stats   uri /dbs
    stats   realm   haproxy\    statistics
    stats   auth    admin:admin
EOF
systemctl start haproxy
[root@node2 ~]# chmod +x ./node2_haproxy.sh 
[root@node2 ~]# ./node2_haproxy.sh

注意：bind 0.0.0.0:8080指的是8080端口

stats auth admin:admin表示haproxy的用户名密码均为admin

　　测试haproxy的可用性，分别打开node1和node2的ip即可。

Keepalived部署

这个脚本建立在node1上即可，node2不需要。

1
2
3
4
5
6
7
8
9
10
11
12
13
[root@node1 ~]# vim /usr/local/src/check_proxy_pid.sh
[root@node1 ~]# cat /usr/local/src/check_proxy_pid.sh
#!/bin/bash
A=`ps -C haproxy --no-header |wc -l`                     
    if [ $A == 0 ];then
        exit 1
    else
        exit 0
    fi
[root@node1 ~]# cd  /usr/local/src/
[root@node1 src]# chmod +x ./check_proxy_pid.sh 
[root@node1 src]# cd
[root@node1 ~]#
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
node1上执行脚本：
[root@node1 ~]# vim node1_keepalived.sh 
[root@node1 ~]# cat node1_keepalived.sh 
#!/bin/bash
yum install keepalived -y
mv /etc/keepalived/keepalived.conf{,.bak}
cat > /etc/keepalived/keepalived.conf << EOF
! Configuration File for keepalived
global_defs {
   router_id node1
}
vrrp_script chk_http_port {
    script "/usr/local/src/check_proxy_pid.sh"
    interval 1                   
    weight -20
}
vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 10
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        chk_http_port 
    }
    virtual_ipaddress {
    192.168.141.100
    }
}
EOF
systemctl start keepalived
systemctl stop firewalld
[root@node1 ~]# chmod +x node1_keepalived.sh 
[root@node1 ~]# ./node1_keepalived.sh 



node2上执行脚本：
[root@node2 ~]# vim node2_keepalived.sh
[root@node2 ~]# cat node2_keepalived.sh 
#!/bin/bash
yum install keepalived -y
mv /etc/keepalived/keepalived.conf{,.bak}
cat > /etc/keepalived/keepalived.conf << EOF
! Configuration File for keepalived
global_defs {
   router_id node2
}
vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 10
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
    192.168.141.100
    }
}
EOF
systemctl start keepalived
systemctl stop firewalld
[root@node2 ~]# chmod +x ./node2_keepalived.sh 
[root@node2 ~]# ./node2_keepalived.sh

测试

测试vip的可用性，访问http://192.168.141.100:8080/dbs

1VZBJH.png

用户名密码均为admin，如下可以看到vip是可用的。

1VZDWd.png

检测keepalived的vip是否可以正常漂移

首先记住要关闭node1和node2的防火墙！

1
2
[root@node1 ~]# systemctl stop firewalld
[root@node2 ~]# systemctl stop firewalld

node1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@node1 ~]# ip add
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 pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:8b:6f:8d brd ff:ff:ff:ff:ff:ff
    inet 192.168.141.53/24 brd 192.168.141.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.141.100/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::45c1:b728:e8e7:a1fa/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@node1 ~]#

node2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@node2 ~]# ip add
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 pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:56:58:3c brd ff:ff:ff:ff:ff:ff
    inet 192.168.141.69/24 brd 192.168.141.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::1edf:37bf:62b9:68e2/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@node2 ~]#

停止node1的Keepalived服务，观察VIP是否漂移，访问正常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@node1 ~]# systemctl stop keepalived
[root@node1 ~]# ip add
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 pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:8b:6f:8d brd ff:ff:ff:ff:ff:ff
    inet 192.168.141.53/24 brd 192.168.141.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::45c1:b728:e8e7:a1fa/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@node1 ~]#

可以看到vip现在已经转移到了node2上面

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@node2 ~]# ip add
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 pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:56:58:3c brd ff:ff:ff:ff:ff:ff
    inet 192.168.141.69/24 brd 192.168.141.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.141.100/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::1edf:37bf:62b9:68e2/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@node2 ~]#

测试漂移后的vip的可用性

1VZDWd.png

　　由于keepalived主设备的选举默认是抢占模式的，所以我们重启node1的keepalived之后，vip能够重新自node2上面漂移回到node1上面。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[root@node1 ~]# systemctl restart keepalived
[root@node1 ~]# ip add
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 pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:8b:6f:8d brd ff:ff:ff:ff:ff:ff
    inet 192.168.141.53/24 brd 192.168.141.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.141.100/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::45c1:b728:e8e7:a1fa/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@node1 ~]#

可以看到vip已经漂移回到node1上了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@node2 ~]# ip add
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 pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:56:58:3c brd ff:ff:ff:ff:ff:ff
    inet 192.168.141.69/24 brd 192.168.141.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::1edf:37bf:62b9:68e2/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@node2 ~]#

停止node1节点上的HAProxy服务，观察VIP是否漂移，vip是否还能正常访问

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
[root@node1 ~]# systemctl stop haproxy
[root@node1 ~]# ip add
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 pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:8b:6f:8d brd ff:ff:ff:ff:ff:ff
    inet 192.168.141.53/24 brd 192.168.141.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::45c1:b728:e8e7:a1fa/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@node1 ~]# 


[root@node2 ~]# ip add
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 pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:56:58:3c brd ff:ff:ff:ff:ff:ff
    inet 192.168.141.69/24 brd 192.168.141.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.141.100/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::1edf:37bf:62b9:68e2/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@node2 ~]#

可以看到vip正常漂移到了node2上面，而且vip仍然可以正常访问

1VZDWd.png

然后重启node1节点上的HAProxy服务，观察VIP是否漂移回来，vip是否还能正常访问

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
[root@node1 ~]# systemctl restart haproxy
[root@node1 ~]# ip add
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 pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:8b:6f:8d brd ff:ff:ff:ff:ff:ff
    inet 192.168.141.53/24 brd 192.168.141.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.141.100/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::45c1:b728:e8e7:a1fa/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@node1 ~]# 




[root@node2 ~]# ip add
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 pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:56:58:3c brd ff:ff:ff:ff:ff:ff
    inet 192.168.141.69/24 brd 192.168.141.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::1edf:37bf:62b9:68e2/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@node2 ~]#

可以看到vip漂移回到了node1上面，而且vip仍然可以正常访问。

1VZDWd.png

现在开始检测haproxy对后端两台mysql服务器的监控

1）关闭node3和node4的mariadb

1
2
[root@node3 ~]# systemctl stop mariadb
[root@node4 ~]# systemctl stop mariadb

2）查看监控，可以看到监控那两行均变为红色，意思是挂了

1VZ6yt.png

3）开启node3的mariadb服务

1
2
[root@node3 ~]# systemctl restart mariadb
[root@node3 ~]# systemctl enable mariadb

再看监控界面，可以看到mysqldb1那一行变成了绿色

1VZ2ef.png

同理，将node4的mariadb服务也开启一下

1
2
[root@node4 ~]# systemctl restart mariadb
[root@node4 ~]# systemctl enable mariadb

再看监控界面，可以看到mysqldb2那一行也变成了绿色

1VZRw8.png

因此，当node3或者node4的mariadb服务发生异常的时候，我们可以通过监控界面及时发现。

接下来进行mysql远程连接测试

1）先在360软件管家下载一个navicat

1VZoSs.png

2）可以看到它的有效期是14天，很鸡肋，这是百度经验上如何激活navicat的教程，亲测有效！

https://jingyan.baidu.com/article/495ba841b239e638b20ede4a.html

3）先在node3上的mysql里创建test用户

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
[root@node3 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create user test@localhost identified by '123456';
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> select Host,User,Password from user where User= 'test';
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | test | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
1 row in set (0.01 sec)

MariaDB [mysql]> 
MariaDB [mysql]> quit
Bye
[root@node3 ~]#

4）授权允许任意远程主机以test的身份连接node3的数据库

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

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> update mysql.user set host = '%' where user ='test' limit 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
Bye
[root@node3 ~]# systemctl restart mariadb.service 
#如果是阿里云服务器，这里会让比输入服务器的root密码进行验证，而且如果你不放行阿里云服务器的3306端口的话，是无法用navicat连接上服务器上面的数据库的。
[root@node3 ~]#

5）再使用远程主机连接192.168.141.12的3306端口，这里我们的windows对于node3就算是远程主机了。如下图，可以看到连接是成功的

1VZqmV.png

　　然后测试远程主机连接192.168.141.100的3306端口，可以发现被拒了，是不是很难受？分析一下，可以发现拒绝我们的并非是node3，而是当前vip所在主机也就是node1。

1VZvY4.png

　　我们之前已经关闭了node1的防火墙和selinux，可以通过vip正常访问haproxy监控页面，可以正常连接node3的数据库，然而通过远程连接vip的3306端口来连接后端数据库居然是被拒绝的，为什么？

　　因为，在这种服务体系架构中，一旦别人可以通过远程连接vip的3306端口来连接后端数据库，那还了得？那还有什么安全性可言呢？

　　例如现在咱们在node3后node4上面都搭建了一个wordpress博客，博客所用数据库就放在node3和node4上面。我们的node1和node2通过反向代理，让别人通过访问node1和node2来访问后端的node3和node4上面的wordpress博客，可以避免我们的后端数据库直接暴露在访问者的面前。现在我们实现了高可用，别人访问我们的wordpress博客，看到的只是vip：192.168.141.100，他们不会知道后端node3和node4的具体存在，这让我们得以保护好弄得3和node4上面的数据库，然后又不妨碍我们内部人员知道node3和node4这些后端具体服务器ip的人去连接数据库。

　　所以，远程主机连接192.168.141.100的3306端口被拒绝是正常的，这是我们对后端数据库的保护措施。

接下来，重新使用远程主机连接node3也就是192.168.141.12的3306端口，可以看到连接成功。

1Ve961.png

现在关闭node3上的mariadb服务，再次查看连接可以看到连接失败。

1
[root@node3 ~]# systemctl stop mariadb

1Veim6.png

现在重启node3上的mariadb服务，再次查看连接可以看到连接成功。

1
[root@node3 ~]# systemctl restart mariadb

1Ve961.png

　　还记得我们初始化数据库时设置的root密码吗？往上翻翻可以看到是123456。接下来我们使用root用户来连接一下node3的数据库，可以看到连接失败。

1VeEkD.png

现在我们要让root用户也可以连接node3的数据库。

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

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> update mysql.user set host = '%' where user ='root' limit 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
Bye
[root@node3 ~]# systemctl restart mariadb.service 
[root@node3 ~]#

再次使用root用户来连接一下node3的数据库，则可以看到连接成功。

欢迎打赏，谢谢
------ 本文结束------
0%