Database / MariaDB / MySQL / SQL

在 MySQL 指令列模式下不輸入帳號密碼直接登入伺服器

在指令列模式下使用 mysql 指令登入伺服器時，正常情況下需要輸入帳號密碼進行登入，在安全考量上是很合情合理的事情；但是如果頻繁的進行相關作業（例如使用 mysqldump 個別匯出不同資料庫時），每一次都要輸入帳號密碼，會是一件令人困擾的事情。雖然在複製貼上的方法的情況下參數後面接上帳號與密碼就可以只輸入一次，但這種方式會讓帳號密碼曝光在 process monitor；在這種情況下，我們可以直接在家目錄建立一個 .my.cnf 檔進行一次性設定，之後無須輸入帳號密碼就可以登入伺服器。

[mysql]
user = calos
password = 123456

[mysqldump]
user = calos
password = 123456

其中，[mysql] 這一節的設定是用在使用 mysql 指令，而 [mysqldump] 則是在使用 mysqldump 時使用的。更多的設定可以參考 MySQL 官方說明文件 。

如果是將設定檔置於多人共用伺服器時，基於安全性考量，建議在作業完成後移除設定檔，或是使用 GnuPG 這類加密工具將設定檔進行加密，需要使用時再進行解密。

References:

Leave a comment
  / / /
Database / Debian / Development / Editor / Linux / MariaDB / MySQL / Server / SQL / Ubuntu / Visual Studio Code / Windows / WSL

WSL Ubuntu 透過 APT 安裝軟體時出現 IPC connect call failed 與 NO_PUBKEY

安裝 MariaDB 時，官方貼心的提供了一個 Repository Configuration Tool，可以選好自己的作業系統與想要安裝的發行版後產生相對應的指令碼，直接複製貼上就可以透過套件管理員進行安裝。 這兩天在摸索 WSL (Windows Subsystem Linux)，使用的發行版是 Ubuntu，而今天在 WLS 內安裝 MariaDB 10.4 時卻出現一些錯誤：

sudo apt-get install software-properties-common
Reading package lists... Done
Building dependency tree
Reading state information... Done
software-properties-common is already the newest version (0.96.24.32.9).
The following packages were automatically installed and are no longer required:
  gyp javascript-common libhttp-parser2.7.1 libjs-async libjs-inherits libjs-jquery libjs-node-uuid libjs-underscore libuv1-dev nodejs-doc
Use 'sudo apt autoremove' to remove them.
0 upgraded, 0 newly installed, 0 to remove and 21 not upgraded.

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
Executing: /tmp/apt-key-gpghome.jVRLtzEDLC/gpg.1.sh --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
gpg: connecting dirmngr at '/tmp/apt-key-gpghome.jVRLtzEDLC/S.dirmngr' failed: IPC connect call failed
gpg: keyserver receive failed: No dirmngr

sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.4/ubuntu bionic main'
Hit:1 http://free.nchc.org.tw/ubuntu bionic InRelease
Hit:2 http://free.nchc.org.tw/ubuntu bionic-updates InRelease
Get:3 http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.4/ubuntu bionic InRelease [3901 B]
Hit:4 https://deb.nodesource.com/node_10.x bionic InRelease
Hit:5 http://security.ubuntu.com/ubuntu bionic-security InRelease
Err:3 http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.4/ubuntu bionic InRelease
  The following signatures couldn't be verified because the public key is not available: NO_PUBKEY F1656F24C74CD1D8
Reading package lists... Done
W: GPG error: http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.4/ubuntu bionic InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY F1656F24C74CD1D8
E: The repository 'http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.4/ubuntu bionic InRelease' is not signed.
N: Updating from such a repository can't be done securely, and is therefore disabled by default.
N: See apt-secure(8) manpage for repository creation and user configuration details.

有兩個關鍵字：

  1. IPC connect call failed (Line 12)
  2. NO_PUBKEY (Line 25)

找了一下，發現是 WSL 自己的問題，跟 Ubuntu 沒有太大的關係；開發者 Ghost 表示是 WSL 下的 Ubuntu Bionic 第一版的問題，改用 curl 去把 key 抓下來後透過 sudo apt-key add 就可以解決：

It’s Bugging Since The First Bionics in wsl. But the temporary to pass this problems is using apt-key add instead adv options through wget or curl : curl -sL http://keyserver.ubuntu.com/pks/lookup?op=get&search=0xA6A19B38D3D831EF | sudo apt-key add

但是在 issue closed 後隔沒幾天有其他人表示說問題依然存在，並沒有被解決，最後發現只是各個軟體的 key 不一樣而已，開發者 foxliu 提醒依照安裝軟體的不同，要自己更換 key，以 MariaDB 來說，官方提供的 key 為 0xF1656F24C74CD1D8, 所以把指令改成這樣就好：

curl -sL http://keyserver.ubuntu.com/pks/lookup?op=get&search=0xF1656F24C74CD1D8 | sudo apt-key add

最後重新執行一次 sudo apt update，就可以把套件資訊抓回來了。

sudo apt update
Hit:1 http://free.nchc.org.tw/ubuntu bionic InRelease
Hit:2 http://free.nchc.org.tw/ubuntu bionic-updates InRelease
Hit:3 https://deb.nodesource.com/node_10.x bionic InRelease
Get:4 http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.4/ubuntu bionic InRelease [3901 B]
Hit:5 http://security.ubuntu.com/ubuntu bionic-security InRelease
Get:6 http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.4/ubuntu bionic/main arm64 Packages [7787 B]
Get:7 http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.4/ubuntu bionic/main amd64 Packages [8008 B]
Get:8 http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.4/ubuntu bionic/main ppc64el Packages [7793 B]
Fetched 27.5 kB in 4s (6549 B/s)
Reading package lists... Done
Building dependency tree
Reading state information... Done
24 packages can be upgraded. Run 'apt list --upgradable' to see them.

檢查一下套件資訊：

apt show mariadb-server
Package: mariadb-server
Version: 1:10.4.6+maria~bionic
Priority: optional
Section: database
Source: mariadb-10.4
Maintainer: MariaDB Developers <[email protected]>
Installed-Size: 10.2 kB
Depends: mariadb-server-10.4 (>= 1:10.4.6+maria~bionic)
Homepage: http://mariadb.org/
Download-Size: 3180 B
APT-Sources: http://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.4/ubuntu bionic/main amd64 Packages
Description: MariaDB database server (metapackage depending on the latest version)
 This is an empty package that depends on the current best version of
 mariadb-server (currently mariadb-server-10.4), as determined by the MariaDB
 maintainers. Install this package if in doubt about which MariaDB
 version you need. That will install the version recommended by the
 package maintainers.
 .
 MariaDB is a fast, stable and true multi-user, multi-threaded SQL database
 server. SQL (Structured Query Language) is the most popular database query
 language in the world. The main goals of MariaDB are speed, robustness and
 ease of use.

N: There are 2 additional records. Please use the '-a' switch to see them.

如果安裝其他套件時出現類似的錯誤，把 key 替換應該都能解決。

Reference: Ubuntu 18.04 gpg dirmngr IPC connect call failed #3286

Leave a comment
  / / / / / / / / /
Database / MariaDB / MySQL / SQL

[MariaDB/MySQL] 建立連線帳號與指派權限

建立帳號，其中「myaccount」是帳號，「mypassword」是密碼，主機為 localhost。

CREATE USER 'myaccount'@'localhost' IDENTIFIED BY 'mypassword';

 

權限設定的指令結構如下（MariaDB 10.2）：

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user  [IDENTIFIED [BY [PASSWORD] 'password']
        |{VIA|WITH} plugin_name 
           [{USING|AS} 'plugin_option']]
        [, user [IDENTIFIED [BY [PASSWORD] 'password']
        |{VIA|WITH} plugin_name] 
           [{USING|AS} 'plugin_option']]
    user_options...

GRANT PROXY ON user_specification
    TO user_specification [, user_specification] ...
    [WITH GRANT OPTION]

user_options:
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH with_option [with_option] ...]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
  | MAX_STATEMENT_TIME time

tls_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

Read more “[MariaDB/MySQL] 建立連線帳號與指派權限”

Leave a comment
  / / /
Database / MariaDB / MySQL / SQL

[MariaDB/MySQL] 在 Update 子查詢中使用 Limit 限制影響範圍

一般情況下，我們可以很簡單的利用 Limit 語法在進行資料異動中限制影響範圍。

UPDATE my_table SET my_col = 'data' LIMIT 2;

但是涉及使用子查詢的情境時，這招便不管用了。

例如今天資料長這樣：

+--------+--------+
| number | my_col |
+--------+--------+
|      1 | a      |
|      1 | a      |
|      2 | b      |
|      2 | b      |
|      3 | c      |
|      3 | c      |
+--------+--------+

我想要更新前面兩種資料，也就是要下 Limit 2，但是 number 一樣的話就要一起更新，所以 number 為 1 和 2 的資料應該都要 update，所以預期應該要有 4 筆記錄被更新。第一直覺是利用 子查詢應該可以做到：

UPDATE my_table SET my_col = 'data' WHERE number IN (
    SELECT DISTINCT number FROM my_table LIMIT 2
);

但是如果在 MariaDB/MySQL 5.5 或之後的版本執行時應該會得到 [Err] 1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'。

那這樣要怎麼辦呢？工具是死的，人是活的，StackOverflow 就有人想到辦法繞過去了。我們把 Limit 語法放到第三層子查詢（子查詢中的子查詢）就好了！

UPDATE my_table SET my_col = 'data' WHERE number IN (
    SELECT number FROM (
        SELECT DISTINCT number FROM my_table LIMIT 2
    ) as sub_query_2
);

Reference: MySQL – UPDATE query with LIMIT – Stack Overflow

Leave a comment
  / / / / / /
Database / MariaDB / MySQL / SQL

[MariaDB/MySQL] ERROR 1118 (42000): Row size too large (> 8126)

今天在匯入資料庫時出現錯誤，導致匯入中斷

ERROR 1118 (42000) at line 437: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

造成的主因是設定檔中啟用了 innodb_strict_mode。

在 MariaDB 10.2.2，或是 MySQL 5.7.7 以上的版本預設會開啟，這裡使用的伺服器是 MariaDB 10.2.8，所以在匯入的 SQL 檔單中有單行超過 8126 個字元的話，在 innodb_strict_mode 開啟的情況下會觸發錯誤，並中斷執行。

一般情況下建議是開啟的，因為相較之下 run-time error 會更頭痛，不過這裡只是單純要匯入資料，所以暫時先關閉後再開啟。

編輯 /etc/mysql/my.cnf，並將 innodb_strict_mode 設為 0，重新啟動服務後即可。

References:

Leave a comment
  / / /