MySQL：修訂版本之間的差異

2023年3月12日 (日) 14:20的修訂版本

設置

新舊版更迭

一、MySQL Improved extension(MySQLi)

本來 PHP 只提供 MySQL Extension 來連接 MySQL 資料庫，PHP 5.0.0 引入了 mysqli，也在 PHP 5.3 時將 MySQL 原生啟動器(Native Driver) 包含進 PHP，到了 PHP 5.5 棄用 mysql extension，PHP 7.0 就將 mysql extension 移除了，mysql extension 是提供函數 API 支持程序式開發，而 mysqli 是有函數 API 也有提供「物件導向」的介面支持「物件導向」程式開發

mysqli函式

二、mysql 庫 user資料表

MariaDB10 相較於 MariaDB5 與 MariaDB：

1. 三者的密碼 Hash 算法一樣，得出的字串一樣，資料表存的是 Hashed 之後的字串
2. MariaDB 無 authentication_string 欄，MariaDB5 與 MariaDB10 的 plugin,authentication_string 兩欄的規則如下：
   • plugin 欄為空字串，則 authentication_string 欄內容亦為空字串，Hashed 後的密碼放 Password 欄
   • plugin 欄值為「mysql_native_password」，則 Hashed 後的密碼放 authentication_string 欄， Password 欄可為空字串。

一、密碼複雜度與密碼過期策略

(一)密碼複雜度策略設定

非原生支援，須使用外掛：

validate_password的密碼強度相關引數：

1. validate_password_length：密碼的最小長度，預設值是 8 。
2. validate_password_policy：密碼策略，預設是 MEDIUM (1)，代表除了需符合密碼長度，還要至少有1個數字、小寫字母、大寫字母和特殊字元。若配置為 LOW (0) ，代表僅需符合密碼長度。若配置為 STRONG ，除了滿足 MEDIUM 策略，同時密碼不能存在字典檔案（dictionary file）中。
3. validate_password_dictionary_file：字典檔案中存在的密碼不得使用。
4. validate_password_mixed_case_count：密碼策略設為中或強時，密碼中至少同時擁有小寫字母和大寫字母的數量，預設是1最小是0；預設是至少擁有一個小寫和一個大寫字母。
5. validate_password_number_count：密碼策略設為中或強時，密碼中至少擁有的數字的個數，預設1最小是0。
6. validate_password_special_char_count：密碼策略設為中或強時，密碼中至少擁有的特殊字元的個數，預設1最小是0。

設定範例：

plugin-load = validate_password.so
validate_password_length = 10
validate_password_policy = 1
validate-password = FORCE_PLUS_PERMANENT

跨伺服器叫用 MySQL

被叫用端設定

1. 設定一個帳號：
   • HOST 為允許叫用的 ip ，不能用域名或 server 網址。所有 ip 登入，Host設置為 '%' 。
   • 設定帳號密碼，並以最保守的立場設定對諸資料表的權限。
   • flush privileges;
2. /etc/my.cnf中的相關設定：
   • [mysqld]內新增一行:skip-name-resolve，關閉 MySQL DNS 反向解析。但伺服器會把在本機登入的使用者自動解析為'root'@'127.0.0.1';而不是'root'@'localhost';，如果權限表不合就會出問題。
   • [mysqld]內新增一行:lower_case_table_names=1，使MySQL忽略資料庫表名大小寫
3. 打開 3306 port

戰國策租賃主機不允許外部叫用內部的 MySQL 。？但資料庫伺服器明明和網頁伺服器分開啊？

檢測被叫用的伺服器

1. 網路檢測：
   • ping主機可以；
   • telnet 主機3306端口不可以；
   • telnet 主機22端口可以；

   跟伺服器沒關係

2. 端口檢測：
   • netstat -ntpl |grep 3306 得到 「tcp 0 0 :::3306 :::* LISTEN -」
   • netstat -ntpl |grep 22 得到 「tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN -」

   22端口監聽所有地址，而3306只監聽本機地址(綁定了到了本地)，所以遠程無法訪問。修改my.cnf 中bind-address=0.0.0.0
   對於端口只允許本機訪問，有兩個相關設定，一個是防火牆擋3306，一個就是mysql配置綁定本機地址。

3. 防火牆檢測：
   • iptables --list查看；
   • 開啟防火牆3306端口：在 iptables 中加「-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT」，防火牆允許3306端口通過。
   • 或者直接關閉防火牆；
4. mysql配置文件檢查：
   • my.cnf的配置，bind-address=addr可以配置綁定ip地址。不配置或者IP配置為0.0.0.0，表示監聽所有客戶端連接。
     1. ps -aux | grep mysql 查看進程ID是3340
     2. ll /proc/3340 查看進程程序情況，找配置文件
   • 或以 which mysql 找程序路徑

叫用端設定

DB::connect("mysql://帳號:密碼@被叫用伺服器:3306/資料庫名");

資料庫設定為「嚴格模式」

「嚴格模式」(Strict Mode)下：

1. 欄位若沒有預設值則不能插入
2. BLOB/TEXT 欄不能設預設值

MariaDB 10 或 MySQL 5.7.8 版本裡預設了「sql_mode」幾項功能，可以用 adminer 登入，然後查伺服器的「變數」，其中「sql_mode」的值包含：

1. STRICT_TRANS_TABLES：不合法的值會導致整個 SQL 指令出錯中止
   • 不可對 not null 欄位插入 null 值
   • 不可對自動遞增欄位插入值
   • text 欄位不可以有預設值
2. ERROR_FOR_DIVISION_BY_ZERO：在 insert 或 update 時，若數據被零除，則產生錯誤而非警告。若未設此值，則數據被零除時 MySQL 返回 NULL
3. NO_AUTO_CREATE_USER：不自動創建用戶，必須先建立用戶才能授權
4. NO_ENGINE_SUBSTITUTION：若存儲引擎被禁用或未編譯，此項功能會直接拋出錯誤；若未設此值， create 用預設的存儲引擎替代， alter 不進行更改，並拋出一個 warning。
   -----更多的嚴格模式值-----
5. NO_ZER0_DATE：日期「0000-00-00」的值不合法
6. NO_ZERO_IN_DATE：日期「2010-01-00」的值不合法，因為日期不可為 0
7. ONLY_FULL_GROUP_BY：若 select 中的欄、HAVING 或者 ORDER BY 中的欄，沒有在 group by 中出現，則此 SQL 不合法

解決方法：

1. PHP 連結資料庫一成功，馬上送入「SET SESSION sql_mode=''」，在本次會期中取消嚴格模式
2. 或將語法寫成：「SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'」保留部分嚴格模式

資料來源：

1. DB_MySQL:SQL-Mode的設定
2. MySQL 5.7：聊聊sql_mode
3. sql-mode
4. Mysql ERROR 1067: Invalid default value for 'date' 解決