省科协高可用负载均衡集群

Posted on |
Words count in article: 3.9k | Reading time ≈ 20

整个集群的架构如下图，服务器是exsi虚拟化。

一、Firewall

    在集群的配置过程中，我们的Firewall和SElinux是一直处于关闭的，感觉还是先配置上比较方便，说来惭愧 SElinux我不会（setenforce 0），在这里我就只打开Firewall了。

  1. 第一层Nginx监听的是80端口（也可以自定义），再将请求反向代理到web服务器的80端口（也可以自定义） ，所以他们都需要在Firewall上允许80端口的流量通过，即：
1
2
3
firewall-cmd --zone=public --add-port=80/tcp
firewall-cmd --zone=public --add-interface=ens160
firewall-cmd --reload
  1. nginx高可用实现依靠的keepalived是以VRRP为基础，keepalived官方文档给出的组播地址是224.0.0.18，所以第一层和第三层的nginx的服务器上都需要在防火墙上允许vrrp组播的通过，即：
1
2
firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 \ 
--in-interface ens160 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
  1. MySQL集群必然要开启3306端口，第三层的Nginx也是在监听3306端口，即：
1
2
firewall-cmd --zone=public --add-port=3306/tcp
firewall-cmd --zone=public --add-interface=ens160

建议先看第五步网络存储挂载。

二、nginx高可用集群

nginx version: nginx/1.14.0，keepalived-2.0.7

编译安装nginx

wget http://www.zlib.net/zlib-1.2.11.tar.gz
wget https://jaist.dl.sourceforge.net/project/pcre/pcre/8.41/pcre-8.41.tar.gz
wget https://www.openssl.org/source/openssl-1.0.2o.tar.gz
wget http://labs.frickle.com/files/ngx_cache_purge-2.3.tar.gz
wget http://nginx.org/download/nginx-1.14.0.tar.gz

tar xvf …… 解压缩

1
2
3
4
5
6
7
8
9
10
11
cd nginx-1.14.0.tar.gz

./configure  --add-module=../ngx_cache_purge-2.3
--prefix=/usr/local/nginx --with-http_ssl_module
--with-stream --with-pcre=../pcre-8.41
--with-zlib=../zlib-1.2.11
--with-openssl=../openssl-1.0.2o 

make&&make install 
ln -s /usr/local/nginx/sbin/nginx /usr/local/bin/ 
systemctl enable nginx

配置nginx.conf

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
#user  nobody;
worker_processes  1;

#error_log  logs/error.log;
#error_log  logs/error.log  notice;
#error_log  logs/error.log  info;

#pid        logs/nginx.pid;


events {
    worker_connections  1024;
}


http {
    include       mime.types;
    default_type  application/octet-stream;

    #log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
    #                  '$status $body_bytes_sent "$http_referer" '
    #                  '"$http_user_agent" "$http_x_forwarded_for"';

    #access_log  logs/access.log  main;

    sendfile        on;
    #tcp_nopush     on;

    #keepalive_timeout  0;
    keepalive_timeout  65;

    #gzip  on;
    upstream backend {
        server 192.168.255.50       weight=100;
        server 192.168.255.53       weight=80;
    }
    server {
        listen       80;
        server_name  localhost;

        #charset koi8-r;

        #access_log  logs/host.access.log  main;

        location / {
            root   html;
            index  index.html index.htm;
            proxy_pass http://backend;
        }

        #error_page  404              /404.html;

        # redirect server error pages to the static page /50x.html
        #
        error_page   500 502 503 504  /50x.html;
        location = /50x.html {
            root   html;
        }

        # proxy the PHP scripts to Apache listening on 127.0.0.1:80
        #
        #location ~ \.php$ {
        #    proxy_pass   http://127.0.0.1;
        #}

        # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
        #
        #location ~ \.php$ {
        #    root           html;
        #    fastcgi_pass   127.0.0.1:9000;
        #    fastcgi_index  index.php;
        #    fastcgi_param  SCRIPT_FILENAME  /scripts$fastcgi_script_name;
        #    include        fastcgi_params;
        #}

        # deny access to .htaccess files, if Apache's document root
        # concurs with nginx's one
        #
        #location ~ /\.ht {
        #    deny  all;
        #}
    }


    # another virtual host using mix of IP-, name-, and port-based configuration
    #
    #server {
    #    listen       8000;
    #    listen       somename:8080;
    #    server_name  somename  alias  another.alias;

    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}


    # HTTPS server
    #
    #server {
    #    listen       443 ssl;
    #    server_name  localhost;

    #    ssl_certificate      cert.pem;
    #    ssl_certificate_key  cert.key;

    #    ssl_session_cache    shared:SSL:1m;
    #    ssl_session_timeout  5m;

    #    ssl_ciphers  HIGH:!aNULL:!MD5;
    #    ssl_prefer_server_ciphers  on;

    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}

}

    nginx负载均衡功能实现依靠upstream模块，upstream模块应放于nginx.conf配置的http{}标签内，支持五种分配方式，轮询（默认）、weight、ip_hash三种原生方式，以及fair、url_hash两种第三方支持的方式。我用的是weight方式。

1
2
3
4
5
6
proxy_pass http://backend；
指向
upstream backend {
        server 192.168.255.50       weight=100;
        server 192.168.255.53       weight=80;
    }

    访问的是nginx服务器的地址即起到了反向代理的作用，代理web
    接下来安装keepalive实现nginx集群的高可用。

编译安装keepalived

1
2
3
4
cd keepalived-2.0.7
./configure --prefix=/data/keepalived
 make
 make install

    生成Makefile时有如下报错，需要另外安装libnl/libnl-3以支持IPv6，此处确实用不到，VRRP功能开启即可

配置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
38
39
! Configuration File for keepalived

global_defs {
   notification_email {
     root@localhost
   }
   notification_email_from keepalived@localhost
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id keepalived01
   vrrp_skip_check_adv_addr
   #vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
   vrrp_mcast_group4 224.0.0.18  #vrrp组播，默认为224.0.0.18
}

vrrp_script chk_nginx { 
   script "/etc/keepalived/nginx_check.sh" # 检查nginx状态的脚本 
   interval 2 
   weight 3 
   } 
vrrp_instance VI_1 {
    state MASTER            #MASTER为主，BACKUP为从
    interface ens160       #网卡，根据自己实际
    virtual_router_id 51    #主从一致，用来区分多个instance的VRRP组播
    priority 100            #优先级，BACKUP上为99
    advert_int 1            #健康查检时间间隔
    authentication {        #authentication 认证区域
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.18.74.100       #设置vip
    }
    track_script {
        chk_nginx
    }
}

    keepalived是集群管理中保证集群高可用的一个服务软件，其功能类似于heartbeat，用来防止单点故障。
以VRRP协议为实现基础，可以认为是实现路由器高可用的协议，即将N台提供相同功能的路由器组成一个路由器组，这个组里面有一个master和多个backup，master上面有一个对外提供服务的vip（该路由器所在局域网内其他机器的默认路由为该vip），master会发组播，当backup收不到vrrp包时就认为master宕掉了，这时就需要根据VRRP的优先级来选举一个backup当master。这样的话就可以保证路由器的高可用了。

1
2
3
4
5
6
7
8
9
10
11
nginx_check.sh如下：

#!/bin/bash
A=ps -C nginx --no-header |wc -l
if [ $A -eq 0 ];then
    /usr/local/nginx/sbin
    sleep 3
    if [ ps -C nginx --no-header |wc -l -eq 0 ];then
        systemctl stop keepalived
    fi
fi

配置完成后Ngx-Master：

Ngx-Backup：

宕掉Master的网卡，vip直接转移到了Backup上

重启网卡后，vip回归到Master

三、web 集群

    这一部分其实很简单，在安装操作系统的时候我选的”Basic web server”,为了能验证web能提交数据到MySQL，写了一个PHP页面。
在连接数据库的时候，数据库地址是vip：192.168.255.200

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
index.php
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en">
<head>
    <meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
    <title>产品添加-JD产品管理系统</title>
</head>
<body>
    <h3>JD产品管理</h3>
    <form action="deal.php" method="post" enctype="multipart/form-data">
        图书名称：<input type='text' name='title' />
        <hr />
        销售价格：<input type="text" name='price' />
        <hr />
        市场价格：<input type="text" name='market_price' />
        <hr />
        <input type="submit" name="submit" value="添加" />
        <input type="reset" name="reset" value="重置" />
    </form>
</body>
</html>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
deal.php
<?php
    //1、设置响应头信息
    header('Content-type:text/html; charset=utf-8');
    //2、submit安全判断
    if(isset($_POST['submit'])) {
        //3、接收数据
        $title = $_POST['title'];
        $price = $_POST['price'];
        $market_price = $_POST['market_price'];
        include 'coon.php';
        //9、定义SQL语句
        $sql = "insert into tb_goods values (null,'$title','$price','$market_price','$thumb')";
        //10、执行SQL语句
        $result = mysql_query($sql);
        if($result) {
            echo '添加成功';
        } else {
            echo '添加失败';
        }
    }
?>
1
2
3
4
5
6
7
8
9
coon.php
<?php
    //1、连接数据库,内容根据实际
    mysql_connect('192.168.255.200','web','123456'); 
    //2、选择数据库
    mysql_select_db('db_jd');
    //3、指定数据库的编码格式
    mysql_query('set names utf8');
?>

下面的代码用来测试从数据库读取内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
// mysql_connect("数据库地址","数据库账号","数据库密码","连接数据库");
$con = mysql_connect("192.168.255.200","web","123456","db_jd");
//测试是否连接数据库
if($con){
  echo "连接成功";
}else{
  echo "连接失败 ";
}
mysql_select_db("db_jd",$con);
$result1=mysql_query("SELECT * from tb_goods");
echo "<table><tr><td>id</td><td>title</td><td>price</td><td>market_price</td><td>thumb</td></tr>";
while($row=mysql_fetch_array($result1)){
echo "<tr>";
echo "<td>".$row['id']."</td>";
echo "<td>".$row['title']."</td>";
echo "<td>".$row['price']."</td>";
echo "<td>".$row['market_price']."</td>";
echo "<td>".$row['thumb']."</td>";
echo "</tr>";
}
mysql_close($con);
?>

如果两个web服务器中的页面不同，在提交表单的时候回显示无法找到。

最终效果为：

四、nginx高可用集群

nginx version: nginx/1.14.0，keepalived-2.0.7

    与第一层原理相同，只不过反向代理的数据库，监听端口是3306
    这里也会有一个Vip：192.168.255.200

配置Nginx

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
nginx.conf
#user nobody;
worker_processes 1;


#error_log logs/error.log;
#error_log logs/error.log notice;
#error_log logs/error.log info;
#pid    logs/nginx.pid;


events {
    worker_connections 1024;
}


stream {
        upstream mysql {
         
            server 192.168.255.52:3306 weight=5 max_fails=3 fail_timeout=30s;
        server 192.168.255.57:3306 weight=5 max_fails=3 fail_timeout=30s;
       }

    server {

        listen 3306;
        proxy_connect_timeout 1s;
        proxy_timeout 3s;
        proxy_pass mysql;
    }   
}

配置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
38
39
40
41
keepalived.conf
! Configuration File for keepalived

global_defs {
   notification_email {
     root@localhost
   }
   notification_email_from keepalived@localhost
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id nginx_mysql_s
   vrrp_skip_check_adv_addr
   #vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
    vrrp_mcast_group4 224.0.0.18
}

vrrp_script chk_nginx { 
   script "/etc/keepalived/nginx_check.sh" # 检查nginx状态的脚本 
   interval 2 
   weight 3 
} 

vrrp_instance VI_1 {
    state BACKUP
    interface ens160
    virtual_router_id 66
    priority 100 # Backup是90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.255.200
    }
    track_script {
        chk_nginx.sh
    }
}

vip转移不再演示。

五、mysql双机热备集群

Server version: 5.6.43-log MySQL Community Server

源码安装mysql，不再展开。

mysql1：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
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
binlog_format=mixed
relay-log=relay-bin
relay-log-index=slave-relay-bin.index 
auto-increment-increment=2
auto-increment-offset=1
log-slave-updates
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

mysql2: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
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2
log-bin=mysql-bin
binlog_format=mixed
relay-log=relay-bin
relay-log-index=slave-relay-bin.index
auto-increment-increment=2
auto-increment-offset=2
log-slave-updates
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

    web想要上传数据到数据库，首先需要使用web登录至数据库，然后MySQL中应当有已经创建好的数据库，库中要有表，用户权限等等。MySQL集群我们做的是双主，数据是同步的。

    先配置双主吧，然后只用建一遍库就可以了。双主模型其实就是互为主从，主从同步复制原理分成三步：master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events ），slave将master的binary log events拷贝到它的中继日志(relay log)，slave重做中继日志中的事件，将改变反映它自己的数据。

首先在两台mysql上创建用户张三 ，允许对方远程连接

1
2
grant all privileges on *.* to zhangsan@192.168.255.52(192.168.255.57) identified by '123456';
Query OK, 0 rows affected (0.12 sec)

先以MySQL1为主

MySQL1上（192.168.255.52 主）：

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |     1480 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)

MySQL2上（192.168.255.57 从）：

1
2
3
4
mysql> change master to master_host='192.168.255.52',master_port=3306,master_user='zhangsan',master_password='123456',m aster_log_file='mysql-bin.000008',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.30 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

再以MySQL2为主

MySQL2上（192.168.255.57 主）:

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |     1480 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.04 sec)

MySQL1上（192.168.255.52从）;

1
2
3
4
mysql> change master to master_host='192.168.255.57',master_port=3306,master_user='zhangsan',master_password='123456',m aster_log_file='mysql-bin.000006',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.41 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)

    查看状态， Slave_IO_State 为等待主机事件；MySQL1主机显示192.168.255.57(即MySQL2)为主，在MySQL2上查看则相反；Slave_IO_Running: Yes；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
Mysql 1 ：
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.255.57
                  Master_User: zhangsan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 1480
               Relay_Log_File: relay-bin.000003
                Relay_Log_Pos: 856
        Relay_Master_Log_File: mysql-bin.000006
             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: 1480
              Relay_Log_Space: 1023
              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: 2
                  Master_UUID: fb80dcb2-409a-11e9-8823-000c29a992e7
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.00 sec)

ERROR: 
No query specified
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
MySQL 2：
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.255.52
                  Master_User: zhangsan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 1480
               Relay_Log_File: relay-bin.000003
                Relay_Log_Pos: 1070
        Relay_Master_Log_File: mysql-bin.000008
             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: 1480
              Relay_Log_Space: 1237
              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: e6c1eb80-40db-11e9-89cb-00505687a987
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.03 sec)

ERROR: 
No query specified

创建数据库

    创建db-jd数据库，在库中创建tb_goods表，表中字段与PHP网页相对应，在mysql1中创建，mysql2中同步

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> create database db_jd;
Query OK, 1 row affected (0.00 sec)
mysql> use db_jd;
Database changed
mysql> create table tb_goods (id int(11) null, tltle VarChar(40), price Decimal(10), market_price Decimal(10));
Query OK, 0 rows affected (0.13 sec)

mysql> show tables;
+-----------------+
| Tables_in_db_jd |
+-----------------+
| tb_goods        |
+-----------------+
1 row in set (0.00 sec)

授权允许web登陆

在两台MySQL服务器上创建web用户，允许远程登陆，并赋予db_jd数据库的权限

1
2
3
4
5
6
7
8
mysql> create user web@192.168.255.50 identified by '123456';
Query OK, 0 rows affected (0.37 sec)
mysql> grant all privileges on db_jd.* to web@192.168.255.50;
Query OK, 0 rows affected (0.12 sec)
mysql> create user web@192.168.255.53 identified by '123456';
Query OK, 0 rows affected (0.37 sec)
mysql> grant all privileges on db_jd.* to web@192.168.255.53;
Query OK, 0 rows affected (0.12 sec)

关于为什么不直接将权限赋给VIP，以及给Nginx服务器赋权，我认为归根到底还是web服务器的Apache与MySQL服务器MySQL -server之间通过套接字进行进程间通信，无论是vip还是Nginx只不过是中间的一座连接桥而已，实质上还是Apache与MySQL-Server通信。

在web插入数据进行测试，如下是在web1界面添加数据

mysql1中可以看到插入的数据test

mysql2中同样有

六、网络链接NAS存储

    输入ip访问NAS服务器的web页面，然后将nas中已经存在的文件夹挂载到mysql服务器的数据目录下即可。
    使用mount挂载后，格式化了mysql数据目录下的文件，很郁闷，建议先挂载后再去安装mysql。

    在NAS上开启NFS协议（NetworkFileSystem）。自带数据备份操作很简单。

1
2
mount -t nfs -o nolock 172.18.74.39:/home/admin /var/lib/mysql  
#nolock -- 禁用文件锁 -t 指定文件系统类型
------ end ------
0%