MySQL：同一查询中有两个移动平均线？(MySQL: Two moving averages in the same query?)

 @ T0xicCode的答案是正确的，但我以为我会扩大细节，因为实际上花了大约20个小时才能得到一个实际的解决方案。 
 
 如果您想在其自己的容器中运行Nginx，并将其用作反向代理来在相同的服务器实例上负载平衡多个应用程序，那么您需要遵循的步骤就是这样： 

 
 链接您的容器 
 
 当您docker run您的容器时，通常通过将Shell脚本输入到User Data ，您可以声明到任何其他正在运行的容器的链接。 这意味着您需要按顺序启动您的容器，只有后者的容器可以链接到前一个容器。 像这样： 
 
#!/bin/bash
sudo docker run -p 3000:3000 --name API mydockerhub/api
sudo docker run -p 3001:3001 --link API:API --name App mydockerhub/app
sudo docker run -p 80:80 -p 443:443 --link API:API --link App:App --name Nginx mydockerhub/nginx
 
 所以在这个例子中， API容器没有链接到任何其他容器，但App容器被链接到API ， Nginx链接到API和App 。 
 
 这样做的结果是更改env vars和/etc/hosts文件驻存在API和App容器中。 结果如下： 

 
 / etc / hosts文件 
 
 在Nginx容器中运行cat /etc/hosts将产生以下内容： 
 
172.17.0.5  0fd9a40ab5ec
127.0.0.1   localhost
::1 localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
172.17.0.3  App
172.17.0.2  API
 


 ENV Vars 
 
 在您的Nginx容器中运行env将产生以下内容： 
 
API_PORT=tcp://172.17.0.2:3000
API_PORT_3000_TCP_PROTO=tcp
API_PORT_3000_TCP_PORT=3000
API_PORT_3000_TCP_ADDR=172.17.0.2

APP_PORT=tcp://172.17.0.3:3001
APP_PORT_3001_TCP_PROTO=tcp
APP_PORT_3001_TCP_PORT=3001
APP_PORT_3001_TCP_ADDR=172.17.0.3
 
 我截断了许多实际的vars，但上面是代理流量到容器的关键值。 
 
 要获取一个shell运行在运行容器中的上述命令，请使用以下命令： 
 
 sudo docker exec -i -t Nginx bash 
 
 您现在可以看到/etc/hosts文件条目和env vars包含任何链接的容器的本地IP地址。 据我所知，这是所有的事情发生时，您运行容器与链接选项声明。 但您现在可以使用此信息在Nginx容器内配置nginx 。 
 


 配置Nginx 
 
 这是一个棘手的地方，有几个选择。 您可以选择将站点配置为指向docker创建的/etc/hosts文件中的条目，也可以使用ENV var并在nginx.conf和任何其他conf文件上运行字符串替换（我使用sed ）这可能在您的/etc/nginx/sites-enabled文件夹中，以插入IP值。 
 


 选项A：使用ENV Vars配置Nginx 
 
 
 
 这是我去的选项，因为我无法使/etc/hosts文件选项工作。 我会尽快尝试选项B，并更新此帖子与任何发现。 
 
 
 此选项与使用/etc/hosts文件选项的主要区别是如何编写Dockerfile以将shell脚本用作CMD参数， CMD参数反过来处理字符串替换，以将IP值从ENV复制到您的conf文件（ S）。 
 
 以下是我最终提供的一组配置文件： 
 
 Dockerfile 
 
FROM ubuntu:14.04
MAINTAINER Your Name <you@myapp.com>

RUN apt-get update && apt-get install -y nano htop git nginx

ADD nginx.conf /etc/nginx/nginx.conf
ADD api.myapp.conf /etc/nginx/sites-enabled/api.myapp.conf
ADD app.myapp.conf /etc/nginx/sites-enabled/app.myapp.conf
ADD Nginx-Startup.sh /etc/nginx/Nginx-Startup.sh

EXPOSE 80 443

CMD ["/bin/bash","/etc/nginx/Nginx-Startup.sh"]
 
 nginx.conf 
 
daemon off;
user www-data;
pid /var/run/nginx.pid;
worker_processes 1;


events {
    worker_connections 1024;
}


http {

    # Basic Settings

    sendfile on;
    tcp_nopush on;
    tcp_nodelay on;
    keepalive_timeout 33;
    types_hash_max_size 2048;

    server_tokens off;
    server_names_hash_bucket_size 64;

    include /etc/nginx/mime.types;
    default_type application/octet-stream;


    # Logging Settings
    access_log /var/log/nginx/access.log;
    error_log /var/log/nginx/error.log;


    # Gzip Settings

gzip on;
    gzip_vary on;
    gzip_proxied any;
    gzip_comp_level 3;
    gzip_buffers 16 8k;
    gzip_http_version 1.1;
    gzip_types text/plain text/xml text/css application/x-javascript application/json;
    gzip_disable "MSIE [1-6]\.(?!.*SV1)";

    # Virtual Host Configs  
    include /etc/nginx/sites-enabled/*;

    # Error Page Config
    #error_page 403 404 500 502 /srv/Splash;


}
 
 
 
 注意： daemon off;非常重要daemon off; 在您的nginx.conf文件中，确保您的容器在启动后不会立即退出。 
 
 
 api.myapp.conf 
 
upstream api_upstream{
    server APP_IP:3000;
}

server {
    listen 80;
    server_name api.myapp.com;
    return 301 https://api.myapp.com/$request_uri;
}

server {
    listen 443;
    server_name api.myapp.com;

    location / {
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection 'upgrade';
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_cache_bypass $http_upgrade;
        proxy_pass http://api_upstream;
    }

}
 
 Nginx-Startup.sh 
 
#!/bin/bash
sed -i 's/APP_IP/'"$API_PORT_3000_TCP_ADDR"'/g' /etc/nginx/sites-enabled/api.myapp.com
sed -i 's/APP_IP/'"$APP_PORT_3001_TCP_ADDR"'/g' /etc/nginx/sites-enabled/app.myapp.com

service nginx start
 
 我将把它留给你做关于nginx.conf和api.myapp.conf大部分内容的家庭作业。 
 
 Nginx-Startup.sh中的魔法发生在我们使用sed在我们写入api.myapp.conf和app.myapp.conf文件的upstream块的APP_IP占位符上进行字符串替换。 
 
 这个ask.ubuntu.com问题非常好地解释： 使用命令查找和替换文件中的文本 
 
 
 
 GOTCHA在OSX上， sed处理选项不同， -i标志具体。 在Ubuntu上， -i标志将处理替换“到位”; 它将打开文件，更改文本，然后“保存”相同的文件。 在OSX上， -i标志需要您希望生成的文件具有的文件扩展名。 如果您使用的文件没有扩展名，则必须输入“'作为-i标志的值。 
 
 
 GOTCHA要使用sed用于找到要替换的字符串的正则表达式中的ENV var，您需要在双引号内包装var。 所以正确的，尽管如此，这些语法如上所述。 
 
 
 因此， Nginx-Startup.sh已经启动了我们的容器并触发了Nginx-Startup.sh脚本来运行，它使用sed将值APP_IP更改为sed命令中提供的相应的ENV变量。 我们现在在我们的/etc/nginx/sites-enabled目录中有conf文件，其中有启动容器时停靠站设置的ENV var的IP地址。 在您的api.myapp.conf文件中，您将看到upstream块已更改为： 
 
upstream api_upstream{
    server 172.0.0.2:3000;
}
 
 你看到的IP地址可能有所不同，但是我注意到它通常是172.0.0.x 
 
 你现在应该有一切正确路由。 
 
 
 
 GOTCHA运行初始实例启动后，无法重新启动/重新运行任何容器。 Docker在启动时为每个容器提供一个新的IP，似乎没有重新使用它以前使用的任何一个。 所以api.myapp.com将首次获得172.0.0.2，但是下一次可以获得172.0.0.4。 但Nginx已经将第一个IP设置为其conf文件或其/etc/hosts文件，因此无法确定api.myapp.com的新IP。 对此的解决方案可能会使用etcd及其etcd服务，在我有限的理解中，对于注册到同一CoreOS群集中的所有机器，其行为就像一个共享ENV 。 这是下一个我要玩的玩具。 
 
 


 选项B：使用/etc/hosts文件条目 
 
 这应该是更快，更简单的方法，但我无法让它工作。 表面上你只需api.myapp.conf /etc/hosts条目的值输入到你的api.myapp.conf和app.myapp.conf文件中，但是我无法得到这个方法。 
 
 
 
 更新：有关如何使此方法工作的说明，请参阅@Wes Tod的答案 。 
 
 
 这是我在api.myapp.conf所做的尝试： 
 
upstream api_upstream{
    server API:3000;
}
 
 考虑到我的/etc/hosts文件中有一个条目，如下所示： 172.0.0.2 API我认为它只是拉入值，但似乎不是。 
 
 我的Elastic Load Balancer从AZ的所有采购都有几个附带的问题，所以这可能是我尝试这条路线的问题。 相反，我不得不学习如何处理在Linux中替换字符串，这样很有趣。 我会试试一下，看看它是怎么回事。 

@T0xicCode's answer is correct, but I thought I would expand on the details since it actually took me about 20 hours to finally get a working solution implemented.
 
If you're looking to run Nginx in its own container and use it as a reverse proxy to load balance multiple applications on the same server instance then the steps you need to follow are as such: 

 
Link Your Containers
 
When you docker run your containers, typically by inputting a shell script into User Data, you can declare links to any other running containers. This means that you need to start your containers up in order and only the latter containers can link to the former ones. Like so:
 
#!/bin/bash
sudo docker run -p 3000:3000 --name API mydockerhub/api
sudo docker run -p 3001:3001 --link API:API --name App mydockerhub/app
sudo docker run -p 80:80 -p 443:443 --link API:API --link App:App --name Nginx mydockerhub/nginx
 
So in this example, the API container isn't linked to any others, but the App container is linked to API and Nginx is linked to both API and App.
 
The result of this is changes to the env vars and the /etc/hosts files that reside within the API and App containers. The results look like so: 

 
/etc/hosts
 
Running cat /etc/hosts within your Nginx container will produce the following:
 
172.17.0.5  0fd9a40ab5ec
127.0.0.1   localhost
::1 localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
172.17.0.3  App
172.17.0.2  API
 


 ENV Vars
 
Running env within your Nginx container will produce the following: 
 
API_PORT=tcp://172.17.0.2:3000
API_PORT_3000_TCP_PROTO=tcp
API_PORT_3000_TCP_PORT=3000
API_PORT_3000_TCP_ADDR=172.17.0.2

APP_PORT=tcp://172.17.0.3:3001
APP_PORT_3001_TCP_PROTO=tcp
APP_PORT_3001_TCP_PORT=3001
APP_PORT_3001_TCP_ADDR=172.17.0.3
 
I've truncated many of the actual vars, but the above are the key values you need to proxy traffic to your containers.
 
To obtain a shell to run the above commands within a running container, use the following:
 
sudo docker exec -i -t Nginx bash
 
You can see that you now have both /etc/hosts file entries and env vars that contain the local IP address for any of the containers that were linked. So far as I can tell, this is all that happens when you run containers with link options declared. But you can now use this information to configure nginx within your Nginx container.
 


 Configuring Nginx
 
This is where it gets a little tricky, and there's a couple of options. You can choose to configure your sites to point to an entry in the /etc/hosts file that docker created, or you can utilize the ENV vars and run a string replacement (I used sed) on your nginx.conf and any other conf files that may be in your /etc/nginx/sites-enabled folder to insert the IP values.
 


 OPTION A: Configure Nginx Using ENV Vars
 
 
 
This is the option that I went with because I couldn't get the /etc/hosts file option to work. I'll be trying Option B soon enough and update this post with any findings.
 
 
The key difference between this option and using the /etc/hosts file option is how you write your Dockerfile to use a shell script as the CMD argument, which in turn handles the string replacement to copy the IP values from ENV to your conf file(s).
 
Here's the set of configuration files I ended up with:
 
Dockerfile
 
FROM ubuntu:14.04
MAINTAINER Your Name <you@myapp.com>

RUN apt-get update && apt-get install -y nano htop git nginx

ADD nginx.conf /etc/nginx/nginx.conf
ADD api.myapp.conf /etc/nginx/sites-enabled/api.myapp.conf
ADD app.myapp.conf /etc/nginx/sites-enabled/app.myapp.conf
ADD Nginx-Startup.sh /etc/nginx/Nginx-Startup.sh

EXPOSE 80 443

CMD ["/bin/bash","/etc/nginx/Nginx-Startup.sh"]
 
nginx.conf
 
daemon off;
user www-data;
pid /var/run/nginx.pid;
worker_processes 1;


events {
    worker_connections 1024;
}


http {

    # Basic Settings

    sendfile on;
    tcp_nopush on;
    tcp_nodelay on;
    keepalive_timeout 33;
    types_hash_max_size 2048;

    server_tokens off;
    server_names_hash_bucket_size 64;

    include /etc/nginx/mime.types;
    default_type application/octet-stream;


    # Logging Settings
    access_log /var/log/nginx/access.log;
    error_log /var/log/nginx/error.log;


    # Gzip Settings

gzip on;
    gzip_vary on;
    gzip_proxied any;
    gzip_comp_level 3;
    gzip_buffers 16 8k;
    gzip_http_version 1.1;
    gzip_types text/plain text/xml text/css application/x-javascript application/json;
    gzip_disable "MSIE [1-6]\.(?!.*SV1)";

    # Virtual Host Configs  
    include /etc/nginx/sites-enabled/*;

    # Error Page Config
    #error_page 403 404 500 502 /srv/Splash;


}
 
 
 
NOTE: It's important to include daemon off; in your nginx.conf file to ensure that your container doesn't exit immediately after launching.
 
 
api.myapp.conf
 
upstream api_upstream{
    server APP_IP:3000;
}

server {
    listen 80;
    server_name api.myapp.com;
    return 301 https://api.myapp.com/$request_uri;
}

server {
    listen 443;
    server_name api.myapp.com;

    location / {
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection 'upgrade';
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_cache_bypass $http_upgrade;
        proxy_pass http://api_upstream;
    }

}
 
Nginx-Startup.sh
 
#!/bin/bash
sed -i 's/APP_IP/'"$API_PORT_3000_TCP_ADDR"'/g' /etc/nginx/sites-enabled/api.myapp.com
sed -i 's/APP_IP/'"$APP_PORT_3001_TCP_ADDR"'/g' /etc/nginx/sites-enabled/app.myapp.com

service nginx start
 
I'll leave it up to you to do your homework about most of the contents of nginx.conf and api.myapp.conf.
 
The magic happens in Nginx-Startup.sh where we use sed to do string replacement on the APP_IP placeholder that we've written into the upstream block of our api.myapp.conf and app.myapp.conf files.
 
This ask.ubuntu.com question explains it very nicely: Find and replace text within a file using commands
 
 
 
GOTCHA On OSX, sed handles options differently, the -i flag specifically. On Ubuntu, the -i flag will handle the replacement 'in place'; it will open the file, change the text, and then 'save over' the same file. On OSX, the -i flag requires the file extension you'd like the resulting file to have. If you're working with a file that has no extension you must input '' as the value for the -i flag.
 
 
GOTCHA To use ENV vars within the regex that sed uses to find the string you want to replace you need to wrap the var within double-quotes. So the correct, albeit wonky-looking, syntax is as above.
 
 
So docker has launched our container and triggered the Nginx-Startup.sh script to run, which has used sed to change the value APP_IP to the corresponding ENV variable we provided in the sed command. We now have conf files within our /etc/nginx/sites-enabled directory that have the IP addresses from the ENV vars that docker set when starting up the container. Within your api.myapp.conf file you'll see the upstream block has changed to this:
 
upstream api_upstream{
    server 172.0.0.2:3000;
}
 
The IP address you see may be different, but I've noticed that it's usually 172.0.0.x.
 
You should now have everything routing appropriately.
 
 
 
GOTCHA You cannot restart/rerun any containers once you've run the initial instance launch. Docker provides each container with a new IP upon launch and does not seem to re-use any that its used before. So api.myapp.com will get 172.0.0.2 the first time, but then get 172.0.0.4 the next time. But Nginx will have already set the first IP into its conf files, or in its /etc/hosts file, so it won't be able to determine the new IP for api.myapp.com. The solution to this is likely to use CoreOS and its etcd service which, in my limited understanding, acts like a shared ENV for all machines registered into the same CoreOS cluster. This is the next toy I'm going to play with setting up.
 
 


 OPTION B: Use /etc/hosts File Entries
 
This should be the quicker, easier way of doing this, but I couldn't get it to work. Ostensibly you just input the value of the /etc/hosts entry into your api.myapp.conf and app.myapp.conf files, but I couldn't get this method to work.
 
 
 
UPDATE: See @Wes Tod's answer for instructions on how to make this method work.
 
 
Here's the attempt that I made in api.myapp.conf:
 
upstream api_upstream{
    server API:3000;
}
 
Considering that there's an entry in my /etc/hosts file like so: 172.0.0.2 API I figured it would just pull in the value, but it doesn't seem to be.
 
I also had a couple of ancillary issues with my Elastic Load Balancer sourcing from all AZ's so that may have been the issue when I tried this route. Instead I had to learn how to handle replacing strings in Linux, so that was fun. I'll give this a try in a while and see how it goes.