MySQL – YIDAS Code

[Web] 資料過濾與驗證指南 – Data Validation & Filter (XSS, nl2br)

2018-12-132018-12-21 NickDatabase, JavaScript, jQuery, MySQL, PHP

Intro 防範XSS、SQL Injection以及驗證資料已經是基本開發流程，但要如何標準化的去做團隊開發就是一門學問。資料驗證顧名思義，就是在寫入Datebase之前處理Input資料驗證。以MVC架構來說，Validator最佳的實踐流程點是在Model/Entity上，也就是Model已定義好驗證規則，所以所有經過此Model寫入的資料都會是經過經過規則驗證通過的，提供絕佳的共用性與嚴謹度。 Yii 2 – Validating Input (Yii 2 透過Model定義Validator實踐開發) CodeIgniter/Model – Validation (CI擴充框架中，一樣透過Model定義整合Validator實踐開發) Filter前處理or後處理以XSS來說，到底要前處理還是後處理? 兩個都沒錯，這全看你後端/資料庫那個欄位到底是定義成Plain Text還是HTML Content。平均開發上，只要一開始寫資料庫沒特別定義，不外乎都是Plain Text，意即做後處理比較直覺(畢竟過往資料都寫進去了)。後處理缺點每次在前端(HTML)顯示時都須謹記後處理，漏一個沒處理就中了廣義XSS 前處理缺點因為資料預設就是HTML，所以應付前端不須處理；相反的在需要Plain Text(例如Excel Plain Text)時就需要在後處理反轉回來無法保障資料驗證，因為輸入過濾將影響驗證一致性，例如最大字數通過過濾的不確定性造成不精準遇到textarea給值時需要Decode回來 (歸類input，正規給值方式value或jQuery val()，用html不標準) 綜合以上，優缺點是一體兩面的，取決於需求。例如80/20法則，前端顯示開發較多、Plain Text顯示開發較少，那選前處理絕對較省成本，且較嚴謹(畢竟反轉與否不影響安全性)，所以依照專案與團隊自行評估即可。我個人對於Pattern來說會選擇後處理為主，因為儲存資料定義為原始輸入資料(Plain […]

[SQL] 單一連線多資料庫設計模式 – (Multiple Catalogs Databases)

2018-12-082018-12-08 NickDatabase, MySQL

Intro 一個專案會有遇到需要操作多個資料庫的情境，然而多資料庫分離的Type一般分兩類：同一Application分主資料庫和附屬資料庫，如分myapp與myapp_log兩個DB。異質系統，若非API串接，也是有可能直接SQL Connection跨過去連線異質資料庫。異質系統資料庫連線異質系統連線沒甚麼設計模式，基本上必定需要另外設定一個連線，以PHP Yii2 Framework連線異質MySQL為例： return [ ‘components’ => [ ‘db’ => [ ‘class’ => ‘yii\db\Connection’, ‘dsn’ => ‘mysql:host=localhost;dbname=myapp’, ‘username’ => ‘user’, ‘password’ => ‘pass’, ‘charset’ => ‘utf8’, ], ‘dbOtherApp’ => [ ‘class’ => ‘yii\db\Connection’, ‘dsn’ […]

[MySQL] Slow Query Log 設定 (寫進mysql table)

2018-09-032018-09-03 NickMySQL

Intro 快速設定MySQL Slow Query Log參數。參數設定方法可在my.cnf或是全域參數設定或修改： // 開啟Slow Query Log 預設off slow_query_log => on // Long query 時間門檻，預設10 long_query_time => 10 // 載體，預設FILE改寫進Table global log_output => TABLE

[MySQL] MySQL效能測試指南(連線|運行) – MySQL Benchmark

2018-04-192018-04-19 NickDatabase, MySQL

Intro MySQL Benchmark方法指南。效能測試取得PROFILE資料檢測Query： SET profiling=1;<query>;SHOW PROFILE; 從mysqlDB測測CPU: SELECT DISTINCT Transition_type_id FROM `time_zone_transition`; 連線測試 MySQL目前有個有趣的連線Latency問題，可參考：[MySQL] 如何佈署Database最佳連線路由 – phpMyAdmin建議走本機或內網這邊提供測試方法(透過mysql client)：預裝好的選mysqldatabase來測試： use mysql; 找最大的time_zone_transition來測： SELECT * FROM `time_zone_transition`; 結果範例最後的資訊帶有總體來回時間： 120208 rows in set (0.08 sec) 交叉測試便可知越遠的client connection時間越久。

[MySQL] InnoDB Transaction – 交易指南 (SELECT … FOR UPDATE)

2018-02-012018-02-04 NickMySQL

鎖資料列(可讀/不可讀) MySQL Document 14.5.2.4 Locking Reads 主要分為sharedLock與lockForUpdate: sharedLock locks only for write, lockForUpdate also prevents them from being selected Pessimistic Locking (SELECT … FOR UPDATE) 談到pessimistic concurrency control(Lock row for read)，實作上再Transaction內使用： SELECT … FOR UPDATE 則結果列將會被鎖定且不可讀，應用上可做不重複流水號等等。

[MySQL] 資料庫密碼修改處理 – MySQL Password Change

2017-12-012018-06-02 NickMySQL

修改密碼指令 MySQL 5.7.5 and earlier: SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘MyNewPass’); MySQL 5.7.6 and later: ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’; MariaDB設定使用者失效Bug MariaDB版本與PHPMyAdmin版本存在使用者密碼操作問題，如有遇到則以語法自行對照操作即可修復。 Ex. 5.5.46 SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘MyNewPass’); 另外如果設定完成但登入後又失效，可以確認mysql->users該用戶的plugin欄位是否有mysql_native_password值，將其刪除再設定密碼即可。重設密碼 – Root密碼遺失強制修改 How to Reset the Root […]

[MySQL] Master / Slave – MySQL Replica 指南

2017-11-172018-10-04 NickMySQL

引言 MySQL Replica 相關指令及指南安裝 MySQL官方文件：Chapter 16 Replication 安裝筆記可以參考：https://blog.longwin.com.tw/2008/03/mysql_replication_master_slave_set_2008/ Slave 狀態檢查狀態查詢SQL SHOW slave STATUS; 建議使用command直式較好查看：SHOW slave STATUS \G; 忽略錯誤 SET GLOBAL SQL_SLAVE_SKIP_COUNTER =1; 可一次略過大量SQL數，建議逐筆Error Skip才不會漏重新跟Master (錯誤後會停止) START SLAVE; 狀態識別 Key Description Master_Log_File IO Thread正在讀取Master的binlog Slave_IO_Running 是否在同步，正常為Yes Slave_SQL_Running 是否在同步，正常為Yes […]

[MySQL] 計算全部資料庫容量 – Count size for all databases

2017-01-242017-01-24 NickMySQL

使用情境總會要計算全部資料庫的總容量吧！竟然沒這SQL語法。解決方法下SQL語句直接從Data length計算在群組，以下為個別列出資料庫容量： SELECT table_schema AS “Database name”, SUM( data_length + index_length ) /1024 /1024 /1024 AS “Size (GB)” FROM information_schema.TABLES GROUP BY table_schema 要做加總的話，就Group SUM欄： GROUP BY “Size (GB)”

[MySQL] 如何佈署Database最佳連線路由 – phpMyAdmin建議走本機或內網

2017-01-052017-01-05 NickAWS, MySQL

這只是一篇架構佈署建議文，主要探討MySQL Client連線MySQL Server的最佳路由。原由 MySQL Client連線到外網MySQL Server，發現Latency頗高，如果Client是使用phpMyAdmin，可以體驗甚麼叫易怒的操作。路由範例與速度結果以下為連線路由測試結果台灣MySQL Client (WAN)-> 日本AWS-RDS //慢台灣SSH Client (WAN)-> 日本AWS-EC2 MySQL Client (LAN)-> 日本AWS-RDS //快台灣HTTP Client (WAN)-> 日本AWS-EC2 phpMyAdmin MySQL Client (LAN)-> 日本AWS-RDS //快總結 MySQL Client建議是在MySQL server的Local或內網，所以應該很少看到有Application會把連線Database佈署到外網的例子。 phpMyAdmin的佈署來說，就如同MySQL Client，放在MySQL server的Local或內網吧！ […]

[MySQL] Mysqldump 備份&還原資料庫 – 指令範例

2016-12-212018-10-04 NickMySQL

安裝 apt-get install mysql-client-* 版本自行查閱，以下為5.1版本範例 apt-get install mysql-client-5.1 指令範例-備份備份使用mysqldump 備份單一資料庫 mysqldump -h hostname -u root -p database_name > backup.sql; 備份資料庫中單一資料表 # mysqldump -u root -p database_name table_name > backup.sql; 備份資料庫中多張資料表 # mysqldump -u root -p database_name table1 table2 > […]