2021-10-29

SQL 兩點經緯度求距離

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`;

2021-10-16

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

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; } }

2021-10-03

Debian 10 安裝 MySQL 8

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

2021-10-02

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

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 連線

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 的管理權限。

2021-06-25

GCP VM執行個體掛載磁碟

GCP VM執行個體掛載磁碟

在建立「VM執行個體」時,可以建立可開機的磁碟,通常體積會落在 10G~50G 之間。

如果要存放大量的資料,會另外再開一般的磁碟(非開機用),而不會把 OS 和資料綁在同一個磁碟裡,以方便管理。

你可以先建立一個空白的磁碟,再掛到VM執行個體上;也可以在編輯VM執行個體時,直接建立一個新的磁碟。 以下是先建立磁碟再掛載的方式。

  1. 先建立VM執行個體(略)。

  2. 建立磁碟 點選「Compute Engine」>「儲存空間」>「磁碟」 點選「建立磁碟」,填入資料,假設名稱為「disk-2」

  3. 編輯VM執行個體

    • 點選「Compute Engine」>「虛擬機器」>「VM執行個體」
    • 點選個體名稱
    • 點選上方「編輯」按鈕
    • 點選「+ 連接現有的磁碟」
    • 選擇「disk-2」
    • 點按下方「儲存」按鈕

以上只設定磁碟個體跟隨的VM執行個體,接著還需要使用命令列掛載。接著使用 ssh 連線至 VM。參考官方說明 建立附加磁碟

# 查看磁碟 ls -al /dev/disk/by-id/ # 下行為查看到的磁碟,對應的是 /dev/sdb # lrwxrwxrwx 1 root root 9 Jun 25 09:52 google-disk-2 -> ../../sdb # 也可以使用 lsblk 查看 lsblk # 將磁碟格式化為 ext4 格式,使用最大的可得空間 sudo mkfs.ext4 -m 0 -E lazy_itable_init=0,lazy_journal_init=0,discard /dev/sdb # 建立掛載名稱(資料夾) my-disk2 sudo mkdir -p /mnt/disks/my-disk2 # 掛載 sudo mount -o discard,defaults /dev/sdb /mnt/disks/my-disk2 # 變更磁碟讀取權限 sudo chmod a+w /mnt/disks/my-disk2 # 存放文字檔做測試 echo "hi shinder" > /mnt/disks/my-disk2/test.txt

開機自動掛載

# 先備份 fstab 檔案 sudo cp /etc/fstab /etc/fstab.backup # 查看磁碟的識別碼 sudo blkid /dev/sdb # /dev/sdb: UUID="1db51edb-a3a5-4bac-a436-e6ea38012392" TYPE="ext4" # 在 fstab 內增加一行 UUID=1db51edb-a3a5-4bac-a436-e6ea38012392 /mnt/disks/my-disk2 ext4 discard,defaults,nofail 0 2 # 重開機看可不可以正常重掛 sudo reboot now

在 Debian 上安裝 NginX, PHP-FPM 環境和 Certbot

在 Debian 上安裝 NginX, PHP-FPM 環境和 Certbot

如果是雲端新開的主機先更新系統套件:

sudo apt update sudo apt upgrade

安裝 NginX

sudo apt install nginx

安裝 PHP_FPM

sudo apt show php-fpm # 可以先查看版本,debian 查到的是 7.3 版 # 同時安裝常用 php 套件 sudo apt install php7.3-fpm php7.3-mysql php7.3-curl php7.3-gd php7.3-json php7.3-mbstring php7.3-xml php7.3-xmlrpc php7.3-zip php7.3-bz2 php7.3-opcache php7.3-bcmath php7.3-iconv

修改 NginX 設定檔,以啟用 PHP 功能

# /etc/nginx/sites-enabled/default server { listen 80 default_server; listen [::]:80 default_server; root /var/www/html; index index.php index.html index.htm index.nginx-debian.html; server_name _; location / { try_files $uri $uri/ =404; } location ~ \.php$ { include snippets/fastcgi-php.conf; fastcgi_pass unix:/var/run/php/php7.3-fpm.sock; } }

重新啟動 NginX

sudo systemctl restart nginx

PHP 的設定檔主要為 /etc/php/7.3/fpm/php.ini 另一個可能要設定的地方為 /etc/php/7.3/fpm/pool.d/www.conf 可以參考 Debian 下安装 Nginx 和 PHP-FPM

# 1G 記憶體的配置 pm.max_children = 25 # 最大的行程數 pm.start_servers = 5 # 啟動時行程數 pm.min_spare_servers = 5 # 空閒時最小行程數 pm.max_spare_servers = 15 # 空閒時最大行程數

若有修改 PHP 的設定,記得重新啟動 PHP-FPM:

sudo systemctl restart php7.3-fpm

安裝 Certbot

在安裝 Cetbot 之前,請記得先設定好 domain 指定,和 NginX 設定檔裡的 server_name。可以參考這篇 在 AWS EC2 安裝 nginx

目前在 Debian 或 Ubuntu 已經不建議使用 apt 來安裝 Certbot,而是使用 snapd 來安裝。

先到 Certbot 官方首頁 選擇你的作業系統和網站伺服器的種類,接著會有說明指引你一步一步的安裝,相當方便。在這裡選 Debian 10 和 NginX。以下為安裝流程:

  1. Debian 預設沒有 snapd,先安裝 snapd
sudo apt install snapd
  1. 確認 snapd 是最新的版本
sudo snap install core; sudo snap refresh core
  1. 透過 snapd 安裝 Certbot
sudo snap install --classic certbot
  1. 設定命令檔連結
sudo ln -s /snap/bin/certbot /usr/bin/certbot
  1. 直接使用 certbot 去修改 nginx 的設定檔
sudo certbot --nginx # 以下為部份輸出結果 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Please read the Terms of Service at https://letsencrypt.org/documents/LE-SA-v1.2-November-15-2017.pdf. You must agree in order to register with the ACME server. Do you agree? - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - (Y)es/(N)o: Y - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Would you be willing, once your first certificate is successfully issued, to share your email address with the Electronic Frontier Foundation, a founding partner of the Let's Encrypt project and the non-profit organization that develops Certbot? We'd like to send you email about our work encrypting the web, EFF news, campaigns, and ways to support digital freedom. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - (Y)es/(N)o: Y Account registered. Which names would you like to activate HTTPS for? - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1: mysite.shinder.cc - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Select the appropriate numbers separated by commas and/or spaces, or leave input blank to select all options shown (Enter 'c' to cancel): 1 Requesting a certificate for mysite.shinder.cc Successfully received certificate. Certificate is saved at: /etc/letsencrypt/live/mysite.shinder.cc/fullchain.pem Key is saved at: /etc/letsencrypt/live/mysite.shinder.cc/privkey.pem This certificate expires on 2021-09-22. These files will be updated when the certificate renews. Certbot has set up a scheduled task to automatically renew this certificate in the background. Deploying certificate Successfully deployed certificate for mysite.shinder.cc to /etc/nginx/sites-enabled/mysite.shinder.cc.conf Congratulations! You have successfully enabled HTTPS on https://mysite.shinder.cc
  1. 測試認證更新功能是否正常
sudo certbot renew --dry-run

現在安裝 Certbot 真的方便太多了,很多設定官方工具都幫你搞定了。不過,要特別注意如果系統預先已經安裝 Certbot 的相關套件,要先全部移除,再安裝最新版的官方套件。

2021-02-27

Apple Silicon M1 的 homebrew

Apple Silicon M1 的 homebrew

在使用 Apple M1 核心晶片的 mac 使用 homebrew,記得要在前面下前綴指令:

arch -x86_64 brew

可以設定指令別名在設定檔裡,例如 ~/.zshrc

alias ibrew='arch -x86_64 /usr/local/bin/brew'

之後使用 ibrew 別名即可。

FB 留言