SQL 兩點經緯度求距離

參考資料 Fastest Way to Find Distance Between Two Lat/Long Points裡的 Binary Worrier 解法。

使用 MariaDB 10，測試的 DB table 為 locations

CREATE TABLE `locations` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `lat` double NOT NULL COMMENT '緯度',
  `lng` double NOT NULL COMMENT '經度'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `locations` (`id`, `name`, `lat`, `lng`) VALUES
(1, '南勢角捷運站', 24.990508728072932, 121.509155455015),
(2, '景安站', 24.993901994056493, 121.50479966768725),
(3, '台大門口', 25.016772139171792, 121.53351504607843);

ALTER TABLE `locations`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `locations`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

-- 測試用的 SQL
-- 以南勢角捷運站為參考位置，求 3 公里以內的景點
SELECT *, 
       ( 6371 * ACOS( COS( RADIANS(24.990508728072932) ) 
              * COS( RADIANS( `lat` ) ) 
              * COS( RADIANS( `lng` ) - RADIANS(121.509155455015) ) 
              + SIN( RADIANS(24.990508728072932) ) 
              * SIN( RADIANS( `lat` ) ) ) ) AS `distance` 
from `locations` 
HAVING `distance` < 3 OR `distance` IS NULL
ORDER BY `distance`;

Nginx 作為均衡負載伺服器 (HTTP load balancer)

這裡直接改 /etc/nginx/sites-enabled/default 的設定，其中 upstream 用來定義工作的群組。

upstream myapp1 {
    ip_hash;
    server 10.140.0.2:3000 weight=1;
    server 10.140.0.3:3000 weight=2;
}

server {
    listen 80;

    location / {
        proxy_pass http://myapp1;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    }
}

ip_hash 是決定使用 ip 雜湊運算，讓用戶端的連線固定連到某一台，這樣有利於 session 或 cookie 的運作。

weight 的設定為權重，預設為 1。目前的設定，在沒有 ip_hash 的設定時，3 requests 會有 1 個給 10.140.0.2，另外 2 個給 10.140.0.2。有 ip_hash 的設定時，則是以用戶來區分。

Nginx 設定多台 web server

Nginx 和 Apache 一樣都可以設定多台 virtual hosts，服務多個站台。在此以 shinder.cc 和 www.shinder.cc 為例說明。系統為 Debian 10，預先安裝 Nginx，PHP 和 certbot 的方式請參考之前的 在 Debian 上安裝 NginX, PHP-FPM 環境和 Certbot。 以下有三個設定檔，皆位於 /etc/nginx/sites-enabled，其中 default-http 為處理 http 使其轉向到 https，另外兩個則是分別使用不同的後端技術，做為 web server 的服務功能。

# default-http
server {
    if ($host = www.shinder.cc) {
        return 301 https://$host$request_uri;
    } # managed by Certbot

    if ($host = shinder.cc) {
        return 301 https://$host$request_uri;
    } # managed by Certbot

    listen 80 ;
    listen [::]:80 ;
    server_name www.shinder.cc shinder.cc;
    return 404; # managed by Certbot
}

# shinder.cc 使用 NodeJS
server {
    server_name shinder.cc;

    listen 443 ssl; # managed by Certbot
    ssl_certificate /etc/letsencrypt/live/shinder.cc/fullchain.pem; # managed by Certbot
    ssl_certificate_key /etc/letsencrypt/live/shinder.cc/privkey.pem; # managed by Certbot
    include /etc/letsencrypt/options-ssl-nginx.conf; # managed by Certbot
    ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem; # managed by Certbot

    location / {
        proxy_pass http://127.0.0.1:3000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

    }
}

# www.shinder.cc 使用 PHP
server {
    server_name www.shinder.cc;

    listen 443 ssl; # managed by Certbot
    ssl_certificate /etc/letsencrypt/live/shinder.cc/fullchain.pem; # managed by Certbot
    ssl_certificate_key /etc/letsencrypt/live/shinder.cc/privkey.pem; # managed by Certbot
    include /etc/letsencrypt/options-ssl-nginx.conf; # managed by Certbot
    ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem; # managed by Certbot

    root /var/www/html;
    index index.php index.html index.htm index.nginx-debian.html;

    location / {
        try_files $uri $uri/ =404;
    }
    location ~ \.php$ {
        include snippets/fastcgi-php.conf;
        fastcgi_pass unix:/var/run/php/php7.3-fpm.sock;
    }
}

Debian 10 安裝 MySQL 8

參考資料： https://computingforgeeks.com/how-to-install-mysql-8-0-on-debian

先更新 repo 資料，並安裝 wget

sudo apt update
sudo apt -y  install wget

到 https://repo.mysql.com 查看官方 apt repo 資料，例如 mysql-apt-config_0.8.19-1_all.deb，下載並安裝。

wget https://repo.mysql.com/mysql-apt-config_0.8.19-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.19-1_all.deb

選擇預設設定即可，選「OK」並按 Enter。接著直接安裝，安裝過程要設定 root 密碼，還有選擇密碼加密的方式，建議使用舊的方式，和 5.X 相容的模式會比較方便：

sudo apt update
sudo apt -y install mysql-server

確認安裝的版本，即可登入：

apt policy mysql-server
mysql -uroot -p

查看伺服器狀態、關閉、啟動：

sudo systemctl status mysql
sudo systemctl stop   mysql
sudo systemctl start  mysql

GCP 全專案「安全殼層金鑰」設定

GCP 的全專案「安全殼層金鑰」(ssh public key) 設定，位於「Compute Engine」>「中繼資料(metadata)」>「安全殼層金鑰」，點擊「編輯」用以新增或修改，通常將 ~/.ssh/id_rsa.pub 內的資料複製進去即可。該帳號就可以有全專案裡，所有 VM instances 的 sudo 權限。

通常 ssh 用來連線至 VM，所以 ssh public key 設定放在 Compute Engine 的 Metadata 也算是合理的設計。

若要設定特定 VM 的 ssh key，可以到「Compute Engine」>「VM 執行個體」，選擇 VM 後在上方選單點選「編輯」，以進入編輯模式。在「SSH 金鑰」區塊，新增金鑰即可。

GCP 費用估算器

https://cloud.google.com/products/calculator

GCP Cloud SQL 公開 IP 連線

在此 Cloud SQL 以 MySQL 8 為例。在建立 SQL 執行個體時，連線設定必須勾選「公開 IP」，建立後會配發一個公開的 IP。 在「已授權網路」設定家用電腦或辦公室電腦對外的 IP，為了安全性通常會設定單 IP 通行 x.x.x.x/32。設定好之後，請記得按「儲存」按鈕儲存。

選定執行個體後，在 Cloud SQL 左側選單的「使用者」會有 MySQL 預設的管理帳號 root。從外部連入的用戶為 root@%，並沒有所有授權，例如變更其他用戶的密碼就沒有；而 root@localhost 才有所有授權。

如果你工作的電腦有 MySQL terminal client，可以使用下式連入：

/Applications/XAMPP/bin/mysql -h x.x.x.x -u root -p

如果工作的電腦有 PHP 的執行環境 (MySQL8 需要 PHP7 以上)，可以使用 phpMyAdmin 或 Adminer 工具。

# 在存放 adminer.php 的工作目錄啟動 PHP7 內建的測試 web server
php -S localhost:8888

使用 phpMyAdmin 管理資料滿方便的。然而比較建議使用 Adminer，可以在建立某個 DB 後，再建立只能存取該 DB 的用戶。以下是 Adminer 產生的 SQL 語法，也可以透過 MySQL client 去執行。

-- 建立用戶 shinder3 並設定密碼
CREATE USER 'shinder3'@'%' IDENTIFIED BY 'shinder3_password';
-- 建立後密碼不能以 root@% 去變更, 只能登入該帳號變更
-- SET PASSWORD = 'shinder33';

-- 只設定用戶可以存取 test 資料庫
GRANT CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE, ALTER ROUTINE, EXECUTE ON `test`.* TO 'shinder3'@'%';

以 Adminer 建立的用戶，也可以在 Cloud SQL 左側選單的「使用者」列表中看到，但權限範圍不同。直接透過 GCP console 的「SQL > 使用者」建立的用戶有管理所有資料庫的權限，透過 Adminer 或 MySQL client 的則只有單一 DB 的管理權限。