Node.js 筆記 - Loopback 2 與 MySQL 查詢緩慢的瓶頸排除 @ loopback-connector-mysql, connectionlimit

花了兩天幫同事追一個問題：為何有個服務重啟後，一直處於不穩甚至無法服務的狀態，但隨著時間是有機會變穩定的。

這算是一個 IoT 老服務了，當裝置上線時會跟服務保持連線(Websocket)，其中有一些認證跟服務會使用到 DB server。當運行好一段時間，沒什麼大問題，反應速度也很良好。但碰到服務發布時，都會有一段期間無法正常使用。

此現象主要是 IoT 裝置們當初設計了自動連上雲台的架構，每台裝置啟動後，依序連上雲服務。但是，當服務更新時，若採到斷線機制時，將導致大量的裝置重新連線，而連線過程若有要認證就會佔用到 DB query 資源。此時若有些回應慢，將導致有些裝置認為服務不正常而自行斷線，並且又進入 retry 機制，導致災難性的自家人打自家人 DDoS 世界奇觀。

追了好一陣子，鎖定了一套使用 node.js - loopback 2 框架的服務，並且追到 DB query 瓶頸。主要透過對 db 查詢結果做 cache ，可以大幅度改善狀態，去減少 db query。並且當下 db 並未忙碌到回應緩慢。包括用其他對應機器追蹤查詢 db server 效率，以及在雲台出問題的機器上，直接用 mysql command line 查詢，即可交叉驗證應當是 node.js - loopback 的狀態問題。

原本也有意先把套件都升一升，可惜要跳到 LoopBack 4 並不是簡單的事 Orz 最後，老覺得這件事還是不太正常，就用 concurrent limit loopback mysql 關鍵字逛逛，大部分都在討論連線異常，很少提到連線數的問題，很幸運最後找到 connectionlimit 關鍵字，並且在查一下 loopback-connector-mysql 得知預設是只用 10 這個數字！

github.com/loopbackio/loopback-connector-mysql/blob/master/lib/mysql.js#L121

  

if (isNaN(s.connectionLimit)) {

    s.connectionLimit = 10;

  }

因此，就只需要在 loopback 框架下的 datasources.json ，多定義 connectionLimit 即可，例如來個 100 ，並且在系統上追蹤連線數：

$ netstat -np --inet | grep "YourDBServerIP:3306" | wc -l

100

這樣就搞定啦！當時想說 loopback 2 太舊太難升，是不是弄個類似 mysql command line 查詢機制來頂著用 XD 還因此配合 server 環境用 php5 寫好小工具了：

% echo '{"db_host":"DB_IP","db_user":"DB_USER","db_pass":"password","db_database":"DB_NAME","db_query":"SELECT count(*) FROM device;"}'  | jq ''     

{

  "db_host": "DB_IP",

  "db_user": "DB_USER",

  "db_pass": "password",

  "db_database": "DB_NAME",

  "db_query": "SELECT count(*) FROM device;"

}

% echo '{"db_host":"DB_IP","db_user":"DB_USER","db_pass":"password","db_database":"DB_NAME","db_query":"SELECT count(*) FROM device;"}'  | php db_helper.php /tmp/db_helper.log N M | jq ''

{

  "input": [

    [

      {

        "count(*)": "1234567"

      }

    ]

  ],

  "log": [

    0.34358501434326,

    0.11107587814331,

    0.47047901153564

  ],

  "status": true

}

還設計了 db_helper.php 能不能支援最多跑 N 隻，每隻也來個 timeout M 秒，並透過 lock 機制來保護溝通等，想想這段旅行也是滿好玩的。先把尚未妥善測試的 php5 工具丟到 github 記一下。

[MySQL] 從 JSON 抽取資料建立虛擬欄位 @ MySQL 5.7

同事反映 query 很慢，除了改進 SQL 語法外，也小試身手，對 JSON 資料抽出來建立虛擬欄位跟索引，順便筆記一下。

CREATE TABLE `my_data` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

這邊 data column 是 text 型態，主因是這 DB server 從 5.6 升上來的 XD 且當初讓 data 有很多彈性，不一定是 json 格式。

但，要抽取成虛擬欄位時，建議要保持 data 是 json 格式，就先整理一下資料：

mysql> UPDATE my_data SET data = '{}' WHERE data IS NULL OR data = ''

假想 data 的數值為 {"keyword":"value"}，因此抽出 keyword 虛擬欄位來用：

mysql> ALTER TABLE my_data ADD keyword VARCHAR(64) AS (JSON_UNQUOTE(data->>"$. keyword"));
mysql> ALTER TABLE my_data ADD INDEX (keyword);

如此一來，可以改對 keyword 欄位查詢了，可以再加快一點，而 table 狀態更新為：

CREATE TABLE `my_data ` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `data` text,
  `keyword` varchar(64) GENERATED ALWAYS AS (json_unquote(json_unquote(json_extract(`data`,'$. keyword')))) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `keyword ` (`keyword `),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[macOS] 透過 MacPorts 安裝 MySQL Client

每次裝完都很感到疑惑，為何當下沒 mysql 指令可以用，然後又跑去 linux server 把玩 :p 其實是自己沒做設定罷了：

$ sudo port install mysql56
$ mysql
-bash: mysql: command not found
$ sudo port select mysql mysql56
$ mysql --version
mysql  Ver 14.14 Distrib 5.6.34, for osx10.11 (x86_64) using  EditLine wrapper

收工

[NodeJS] 批次處理 Website snapshot 並存進 MySQL DB @ Ubuntu Server 14.04

延續之前 [NodeJS] 使用 WebShot 進行網頁截圖、顯示正確的中文(CJK)等編碼 @ Ubuntu 14.04 Server 的部分，稍微改幾行 code 就支援批次處理啦

前置環境：

$ sudo apt-get install nodejs npm xfonts-wqy xfonts-kaname
$ sudo ln -s /usr/bin/nodejs  /usr/bin/node
$ mkdir -p job/images && cd job
$ npm install webshot 程式主體：

$ vim build.js

var output_dir = 'images';
var concurrent_limit = 10;
var running_task = 0;
var total_task = [
{ domain: 'tw.yahoo.com', url: 'https://tw.yahoo.com' } ,
{ domain: 'facebook.com', url: 'https://facebook.com' } ,
];

function build_website_snapshot() {
while(total_task.length > 0 && running_task < concurrent_limit) {
var item = total_task.shift();
var url = item.url;
var domain = item.domain;

// https://github.com/brenden/node-webshot
webshot(url, output_dir+'/'+domain+'.png', {
screenSize: {
width: 320,
height: 480,
},
shotSize: {
width: 320,
height: 320,
},
timeout: 20000,
renderDelay: 3000,
userAgent: 'Mozilla/5.0 (iPhone; U; CPU iPhone OS 3_2 like Mac OS X; en-us) AppleWebKit/531.21.20 (KHTML, like Gecko) Mobile/7B298g'

}, function(err) {
if(err)
console.log(err);
running_task--;
if (running_task == 0)
console.log('done');
if (total_task.length > 0)
build_website_snapshot();
});
running_task++;
}
}

$ node build.js

如此一來，就稍微搞定批次產出了。若要把產出的東西存進 MySQL DB server，那可以再這樣做：

$ npm install mysql

$ vim import.js

var fs = require('fs');
var path = require('path');
var mysql = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'dbuser',
  password : 'dbpassword',
  database : 'dbname',
});

var scan_source_dir = 'images';
var files = [];
var sql_values = [];
fs.readdirSync(scan_source_dir).filter(function(file){
        //console.log(file);
        if (fs.statSync(path.join(scan_source_dir, file)).isFile() && file.lastIndexOf('.png') == (file.length - 4)) {
                var domain = file.substring(0, file.length - 4);
                //files[domain] = fs.readFileSync(path.join(scan_source_dir, file), {encoding: 'binary'});
                files[domain] = fs.readFileSync(path.join(scan_source_dir, file));

                sql_values.push([domain, files[domain], Math.round(new Date().getTime()/1000), Math.round(new Date().getTime()/1000)]);
        }
});
// console.log (sql_values);
/*
CREATE TABLE `snapshot_table ` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `domain` varchar(64) NOT NULL DEFAULT '',
  `image` blob,
  `createtime` int(11) DEFAULT NULL,
  `updatetime` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
*/

var sql = "INSERT INTO snapshot_table (domain, image, createtime, updatetime) VALUES ? ON DUPLICATE KEY UPDATE image=VALUES(image), updatetime=VALUES(updatetime) ";
connection.query(sql, [sql_values], function(err) {
        console.log(err);
});
connection.end();

如此一來，就可以自動掃目錄下符合 *.png 的檔案，並將 binary data 紀錄至 db server 中。

Bash 筆記 - 使用 s3fs、mysqldump、find 透過 s3 定期備份 MySQL 資料庫資料

由於資料庫恰好有 timestamp 這個數值，除設計完 incremental backup 方式後（Bash 筆記 - 使用時間區間備份 MySQL 資料表），而備份好的資料又感到麻煩，所以就往 s3 丟吧！而 s3 的丟法就用 s3fs 來執行。

流程：

1. 執行 db incremental backup
2. 使用 s3fs 掛載 s3 空間
3. 確認 s3 是否已有資料，若沒有則複製過去

其中我把 db backup 的(tar.bz2)產物都有做一次 checksum (tar.bz2.md5)，因此判斷是否已在 s3 上時，可以判斷 checksum file 即可：

mkdir -p /data/fuse-tmp/my-s3-bucket
mkdir -p /data/s3/my-s3-bucket
touch /data/s3/my-s3-bucket.password
s3fs my-s3-bucket /data/s3/my-s3-bucket -d -o use_cache=/data/fuse-tmp -o passwd_file=/data/s3/my-s3-bucket.password

find /data/db/ -name "*.bz2" -exec bash -c "test -e {}.md5 && test ! -e /data/s3/my-s3-bucket/\`basename {}\`.md5 && echo 'upload {} , {}.md5 ... ' && time cp {} {}.md5 /data/s3/smy-s3-bucket/ && echo 'done' " \;

sync;sync;sync

fusermount -u /data/s3/my-s3-bucket

其中，/data/db 是擺放一堆 db.sql.tar.bz2 跟 db.sql.tar.bz2.md5 的位置，而我讓備份過程中最後產出 db.sql.tar.bz2.md5 檔，因此先判斷 /data/db/db.sql.tar.bz2.md5 是否產生也代表備份流程是否跑完，接著才檢查 /data/s3/my-s3-bucket/db.sql.tar.bz2.md5 資料來決定是否要上傳到 s3 。

最後，s3 還可以開啟版本控制，這樣可以避免不小心蓋掉檔案的問題 :) 達成完整可自動化的備份方案

[Linux] 透過 sed 將 MySQL Table 資料轉成 csv 格式 @ Ubuntu 14.04

印象中之前找資料時，有發現 MySQL Server 可以輸出 CSV 格式到本地端。但是，對於無法存取到 DB Server 的檔案系統的環境時(AWS RDS)，只好手動處理一下 :P

用法：

$ mysql -h rds-server -u root -p -e "SELECT field1, field2 FROM db_name.table_name" | grep "^Keyword" | sed 's/\t/","/g' | sed 's/^/"/g' | sed 's/$/"/g' > db-table-export.csv

其中 grep 那段只是為了mysql 輸出時，略過第一行 XD

AWS 筆記 - DB Instance XXXX contains MyISAM tables that have not been migrated to InnoDB. These tables can impact your ability to perform point-in-time restores. Consider converting these tables to InnoDB

先查看有哪些 table:

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES;
+----------------------+--------------------+
| TABLE_NAME           | ENGINE             |
+----------------------+--------------------+
...
| pma_bookmark         | MyISAM             |
| pma_column_info      | MyISAM             |
| pma_designer_coords  | MyISAM             |
| pma_history          | MyISAM             |
| pma_pdf_pages        | MyISAM             |
| pma_recent           | MyISAM             |
| pma_relation         | MyISAM             |
| pma_table_coords     | MyISAM             |
| pma_table_info       | MyISAM             |
| pma_table_uiprefs    | MyISAM             |
| pma_tracking         | MyISAM             |
| pma_userconfig       | MyISAM             |
...

結果發現是 PHPMyAdmin 的那些 tables，由於這些影響不大，就操刀吧！以 pma_bookmark 為例

mysql> ALTER TABLE  `phpmyadmin`.`pma_bookmark` ENGINE = INNODB;

收工。

[SQL] select n rows from each group @ MySQL 5.6

假設有一張 table 名為 log 長這樣:

{
id INTEGER,
level VARCHAR(16),
user VARCHAR(32)
}

mysql> SELECT * FROM log
1, "SA", "admin1"
2, "SA", "admin2"
3, "SA", "admin3"
4, "SA", "admin4"
5, "RD", "programmer1"
6, "RD", "programmer2"
7, "RD", "programmer3"
8, "RD", "programmer4"
9, "RD", "programmer1"
10, "FAE", "programmerA"
11, "FAE", "programmerB"
12, "FAE", "programmerC"

有沒有一招可以撈出，讓每個 Level 只顯示 3 筆資料？假想成果：

mysql> SELECT ... FROM log GROUP BY level
1, "SA", "admin1"
2, "SA", "admin2"
3, "SA", "admin3"
5, "RD", "programmer1"
6, "RD", "programmer2"
7, "RD", "programmer3"
10, "FAE", "programmerA"
11, "FAE", "programmerB"
12, "FAE", "programmerC"

土法煉鋼法，用 UNION ALL 來處理：

mysql> SELECT * FROM (SELECT * FROM log WHERE level = 'SA' LIMIT 3) AS t UNION ALL (SELECT * FROM log WHERE level = 'RD' LIMIT 3) UNION ALL (SELECT * FROM log WHERE level = 'FAE' LIMIT 3);

所幸，問了一下強者我同學，得到個關鍵字：GROUP_CONCAT , http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat

mysql> SELECT level, GROUP_CONCAT(user) FROM log GROUP BY level;
"SA", "admin1,admin2,admin3"
"RD", "programmer1, programmer2, programmer3"
"FAE", "programmerA, programmerB, programmerC"

如果想限制撈出的資料個數，要設定 group_concat_max_len：

mysql> SET group_concat_max_len = 2;
mysql> SELECT level, GROUP_CONCAT(user) FROM log GROUP BY level;
"SA", "admin1,admin2"
"RD", "programmer1, programmer2"
"FAE", "programmerA, programmerB"

雖然上述結果還不太適合再做 JOIN 來處理，但，已經算佛心了... XD

其他 Google 用的關鍵字："select top n rows from each group"，會看到一些 RANK() OVER(PARTITION BY level) ，但對 MySQL 應該不適用 XD 強者我同學說，若在 PostgreSQL 可以用：

postgrel> select level, array_aggr(user)[0:1] from table group by level;

看來該多給 PostgrelSQL 機會 XD (當初案子用到 GIS 相關 plugin 才有用它...)

此外，跟強者我同學閒聊時，發現去年的一些經驗還滿適合使用的，有些查詢很久的指令，可以考慮定期產生並儲存在另一張 tabel，降低一般 client 觸發複雜的 SQL Query，像是 JOIN, GROUP 等，這也是在大型服務中也常用到的方式。算是此次閒聊最大的心得，因為去年也有應用這個架構來處理服務，驗證自已的(偷懶)做法無誤 XDDD

[MySQL] 使用 FOREIGN KEY 筆記 @ MySQL 5.6

話說，2014年算是我最常用 SQL DB 的一年，在這之前我都是用 NOSQL 架構 Orz (扣除 SQLite 啦)。最近設計一些新服務的資料儲存，正在想如何使用 MySQL Relations 的特色，才發現之前都沒在用 FOREIGN KEY 啦 :P

簡短介紹 FOREIGN KEY 的功用：

當設計很多階層性的 table 時，如 user (上層), group (中層), data (下層)，其中 data 每一筆都有 user, group 資訊，而 group 裡每一筆都跟 user 有關。此時在 DELETE 事件發生時，如果有建立 Relations 時，可在上層資料刪除時，順便幫你把相關資料刪掉。此外，在新增下層資料時，也會幫你驗證資料的正確性，不會讓你隨意新增假資料。

對於要建立 FOREIGN KEY 的要件：

• 在上層(Parent)預計使用的參考欄位要有 index 屬性，如 Primary Key, Unique Key 或 Index 都行
• 在下層(Child)預計使用的欄位也要有 index 屬性
• 在上層跟下層對應的欄位型態要一樣，例如一樣為非負整數等

此外，則是可以設定 Action，例如上層刪除、更新資料時，下層是否也要一同更新，其選項：

• Restrict: 拒絕 parent event，也將造成 parent 動作失敗
• Cascade: 跟著 parent event 更新或是刪除
• Set null: 收到 parent event 時，將參考欄位設定成 null
• No action: 標準 SQL 語法且為預設選項，在 MySQL 環境上與 Restrict 等價

下次是操作時碰到的問題：

Q: Cannot add foreign key constraint
A: 仔細確認一下指定的欄位，其型態是不是一致的，或是對應的 Action 若為 Set Null 時，要確認資料欄位是否允許 Null

Q: Cannot add or update a child row: a foreign key constraint fails
A: 追蹤一下已存在的資料，是不是有不合理的地方，例如下層存在一筆資料，其對應上層的資料無法匹配。解法就是手動修正，或是乾脆清光資料來做也行。

整體上，建議有參考關係時，可以面對 Parent delete event 時，可以設定成 Cascade 處理，如此一來刪除資料就輕鬆許多，不必做多個處理。

[Linux] MySQL Server 5.6 更換 DB Location @ Ubuntu 14.04

在 AWS EC2 叫機器出來時，大多都會用 EBS 來擴充空間，對於 mysql server 預設裝在 /var/lib/mysql 時，就容易碰到空間不足的時候。解法：手動更換 mysql db location 吧 :P

順便把 EBS 處理過程也都列一列，假設已經新增並掛載 EBS 空間：

$ sudo lsblk
NAME    MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT
...
xvdb    202:16   0     4G  0 disk /mnt
xvdc    202:32   0   500G  0 disk

$ sudo file -s /dev/xvdc
$ sudo mkfs -t ext3 /dev/xvdc
$ sudo mkdir /data
$ sudo mount -t ext3 /dev/xvdc /data
$ sudo chmod 777 /da
$ sudo vim /etc/fstab
/dev/xvdc /data ext3 defaults,nofail 0 2

處理 MySQL Server:

$ sudo apt-get install mysql-server-5.6
$ sudo service mysql stop
$ sudo mv /var/lib/mysql /data/mysql
$ sudo ln -s /data/mysql /var/lib/mysql
$ sudo vim /etc/apparmor.d/usr.sbin.mysqld
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/log/mysql/ r,
  /var/log/mysql/* rw,

  /data/mysql r,
  /data/mysql** rwk,
  /data/mysql r,
  /data/mysql* rwk,
$ sudo service apparmor restart
$ sudo service mysql start

[MySQL] Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

些前在阿里雲、AWS RDS 架設 MySQL Master-Master Replication 出現此問題 Orz 將兩邊設定為各是對方的 Master 如此達到資料跨國同步。然而，卻發現在阿里雲作為 Replication Slave 角色的出現這個問題 Orz 由於在阿里雲的 DB Server 偏向 Cache 角色，確認後資料屬性，決定解決的方案是將阿里雲機器清空再重 AWS RDS 匯入，筆記一下解決步驟：

1. 兩邊都停下 slave 角色動作
• ALiYun-DB> STOP SLAVE
• AWS-RDB> CALL mysql.rds_stop_replication
2. 從 AWS RDS 匯出資料、並查看 MASTER_LOG_FILE 和 MASTER_LOG_POS 資訊
• 在 AWS RDS 標準做法是 Using Replication to Export Amazon RDS MySQL 5.6 Data，若 DB 資料更新沒很頻繁或是重要料更動低，可以直接考慮用 mysqldump
• AWS-RDB> SHOW MASTER STATUS \G
3. 匯入阿里雲 DB Server，並設定 Slave 角色資訊，啟動 Slave 工作
• $ mysql -h AliYunDB < AWSRDBExport.sql
• ALiYun-DB> CHANGE MASTER TO MASTER_LOG_FILE='aws-rdb-mysql-bin-changelog.#######', MASTER_LOG_POS=#####;
• ALiYun-DB> START SLAVE
• ALiYun-DB> SHOW SLAVE STATUS \G
4. 查看阿里雲 MASTER_LOG_FILE 和 MASTER_LOG_POS，更新 AWS RDS Slave 角色資訊，重新啟動 AWS RDS Slave 工作
• ALiYun-DB> SHOW MASTER STATUS \G
• MASTER_LOG_FILE
• MASTER_LOG_POS
• AWS-RDB> CALL mysql.rds_reset_external_master;
• AWS-RDB> CALL mysql.rds_set_external_master (
      'aliyun-db-server-ip',
      3306,
      'repl_account',
      'repl_password',
      'aliyun-db-MASTER_LOG_FILE',
      aliyun-db-MASTER_LOG_POS
  ,   0 )
• AWS-RDB > CALL mysql.rds_start_replication;
• AWS-RDS > CALL mysql.rds_set_configuration('binlog retention hours', 24*14);

[SQL] SELECT IN SELECT 以及 Pagination 的使用 @ MySQL 5.6

使用 SQL 語法時，有時會需要從另一張 Table 取出清單，接著對清單內的資料做為基準再進行一次資料的擷取，直觀的想法大概是 SELECT something FROM Table1 WHERE id IN (SELECT id FROM Table2 WHERE ... )。

可惜上述語法是不行的 XD 要改成 JOIN 的做法：

SELECT something FROM Table1, (SELECT id FROM Table2) AS list WHERE Table1.id = list.id;

接著，偶爾會需要 pagination 的需求，加個 LIMIT 的用法，這時候又會想要回報全部有幾筆資料（對於一些搜尋引擎的設計，有些是採用預估的方式），以便前端可以估算有幾筆資料。

最簡單的解法是再用一個 SQL Query 去問 Table2 的 id 資料，但想要更快一點，就來試試 MySQL User-Defined Variables 吧!

SELECT something, @n AS total FROM Table1, (SELECT id, CASE WHEN @n > 0 THEN @n := @n + 1 ELSE @n := 1 END AS n FROM Table2, (SELECT @n := 0) AS init) AS list WHERE Table1.id = list.id;

如此一來，結果都會有個 total 筆數跟著，雖然仍不夠好，但也不錯啦 XD  而搭配 LIMIT OFFSET,COUNT 時，total 的資訊是來自掃 Table2 的資料，所以也能正常顯示：

SELECT something, @n AS total FROM Table1, (SELECT id, CASE WHEN @n > 0 THEN @n := @n + 1 ELSE @n := 1 END AS n FROM Table2, (SELECT @n := 0) AS init) AS list WHERE Table1.id = list.id LIMIT 0,10;

[Linux] 找尋 PHP 檔案內，用到 mysql_* 函數的檔案清單 @ Ubuntu 14.04

三個月前用過又忘了 Orz  還是筆記一下：

$ find /path/target -name "*.php" -exec sh -c 'cnt=`grep -c "mysql_" {}` && test $cnt -gt 0 && echo {}' \;

AWS 筆記 - 透過 Script 半自動化處理 MySQL Replication Error

用了 AWS RDS 一陣子了，之前一直沒搞懂為何 call mysql.rds_skip_repl_error; 一次只能清一條 Error ，直到另一台 MySQL Slave Server 出錯，變成也要手動清除錯誤訊息時，才搞懂原理就是這樣，一次只能清一條。

所以，對於 MySQL Replication Error 不知不覺就生了兩種 script 來應付，一種是 RDS 當 MySQL Replication Slave 用的，另一個則是一般 MySQL Replication Slave Server 用的：

處理 AWS RDS MySQL Replication Slave Servers:

#!/bin/sh
a=0
while [ $a -lt 1000 ]
do
        check=`echo "SHOW SLAVE STATUS \G" | mysql -h YOUR_AWS_RDS_SERVER -u root -ppassword | grep "Last_SQL_Error:" | grep -c "Could not execute"`
        if [ $check -eq 0 ] ; then
                break
        fi
        echo "CALL mysql.rds_skip_repl_error;" | mysql -h YOUR_AWS_RDS_SERVER -u root -ppassword
        a=$(( $a + 1 ))
done

處理 MySQL Replication Slave Servers:

#!/bin/sh

a=0
while [ $a -lt 2000 ]
do
        check=`echo "SHOW SLAVE STATUS \G" | mysql -h YOUR_MYSQL_SLAVE_SERVER -u root -ppassword | grep "Last_SQL_Error:" | grep -c "Error "`
        if [ $check -eq 0 ] ; then
                break
        fi
        check=`echo "STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;" | mysql -h localhost -u root -ppassword`
        a=$(( $a + 1 ))
        sleep 3
done

以上是用在 MySQL Server 彼此算異質系統，只能先透過這招頂替了。

[SQL] 依據條件取出指定欄位 SELECT IF/ELSE, CASE WHEN/ELSE 用法 @ MySQL 5.6

假設有一張 Table 有兩個欄位：

mysql> SELECT * FROM mtable;
+----+----+
| f1 | f2 |
+----+----+
| 1  | 2  |
| 4  | 3  |
| 5  | 6  |
+----+----+

想要撈出 f1 跟 f2 之中，數值最大者:

mysql> SELECT f1 AS result FROM mtable WHERE f1 > f2;
+--------+
| result |
+--------+
| 4      |
+--------+

mysql> SELECT f2 AS result FROM mtable WHERE f1 < f2;
+--------+
| result |
+--------+
| 2      |
| 6      |
+--------+

這時候，可透過條件判斷，透過 CASE WHEN/ELSE 的用法，就可以不用分兩次撈了：

mysql> SELECT
CASE WHEN f1 > f2
THEN f1
ELSE f2
END AS result
FROM mtable;
+--------+
| result |
+--------+
| 2      |
| 4      |
| 6      |
+--------+

[SQL] 透過 INNER JOIN 更新 Table 新增的欄位數值 @ MySQL 5.6

對於一些當作收集 log 用途的 table tb_log ，隨著時間增加後，通常會再整理另一個 tb_status 的 table，快速查詢各個狀態，設計上就會定期批次從 tb_log 取出資料，存進 tb_status 中。

假設 tb_log 有 5 個欄位，一開始只覺得需要 2 個欄位的資訊，就把 tb_status 設定為 2 個欄位，然而過一陣子後，想多記錄一個欄位時，只好變動 tb_status ，但新增的欄位沒有舊資料，就變成要從 tb_log 取出來再存進 tb_status 了

碰到這種問題，有一個解法就是使用 INNER JOIN 來處理：

1. 先從 tb_status 找出欄位未有值的資料
2. 從 tb_log 組出 tb_status 所需的資料
3. 透過  Update 指令更新

情況敘述：

mysql> describe tb_log;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| f0     | int(11)      | NO   | PRI | NULL    | auto_increment |
| f1     | varchar(8)   | YES  |     | NULL    |                |
| f2     | varchar(8)   | YES  |     | NULL    |                |
| f3     | varchar(8)   | YES  |     | NULL    |                |
| f4     | varchar(8)   | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

mysql> describe tb_status;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| f1     | varchar(8)   | YES  | PRI | NULL    |                |
| f2     | varchar(8)   | YES  |     | NULL    |                |
| f3     | varchar(8)   | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

其中 tb_status.f3 則是新建出來，未有資料的。

第一步：先找出 tb_status.f3 是空的（新進資料會有 f3 數值，只有舊資料沒有）

mysql> SELECT f1 WHERE f3 IS NULL;

第二步：從 tb_log 組出 f3 資料，由於 tb_log 是流水帳，且 tb_status 本身也可以從 tb_log 查詢出來的，只需組出 tb_status 需要的欄位即可：

mysql> SELECT f1, f3 FROM tb_log GROUP BY f1;

第三步，把上述兩個資料 JOIN 起來：

SELECT tb1.f1, tb2.f3 FROM
( SELECT f1 WHERE f3 IS NULL ) AS tb1, (SELECT f1, f3 FROM tb_log GROUP BY f1) AS tb2
WHERE tb1.f1 = tb2.f2;

最後，追加更新 tb_status 的用法：

UPDATE tb_status AS tb4
INNER JOIN

(
SELECT tb1.f1, tb2.f3 FROM
( SELECT f1 WHERE f3 IS NULL ) AS tb1,
(SELECT f1, f3 FROM tb_log GROUP BY f1) AS tb2
WHERE tb1.f1 = tb2.f2
) AS tb3

ON tb4.f1 = tb3.f1

SET

tb4.f3 = tb3.f3;

[Linux] 簡易備份加密 MySQL / MySQLDump 資料 @ Ubuntu 14.04

採用 mysqldump 備份 MySQL 資料，可搭配 --where " timestamp < '2014-06-01' AND timestamp >= '2014-05-01' " 等月份備份方式；使用 md5sum 驗證；使用 tar 和 openssl des3 加密：

• $ mysqldump -u root -p myDatabase myTable --where " timestamp < '2014-06-01' AND timestamp >= '2014-05-01' " > mydatabase_mytable.2014-06.sql
• $ find * -name "*.sql" -exec sh -c 'test -e {}.md5sum || md5sum {} > {}.md5sum' \; 
• $ find * -name "*.sql" -exec sh -c 'tar -zcf - {} {}.md5sum | openssl des3 -salt -k MyPassword | dd of={}.tgz.des3' \;

連續動作：

• $ find * -name "*.sql" -exec sh -c 'test -e {}.md5sum || md5sum {} > {}.md5sum' \; && md5sum -c *.md5sum > /dev/null && find * -name "*.sql" -exec sh -c 'test -e {}.tgz.des3 || tar -zcf - {} {}.md5sum | openssl des3 -salt -k MyPassword | dd of={}.tgz.des3' \;

如此一來，即可透過 crontab 定期一直加密新增的 *.sql 檔案（大概要處理一下 md5sum 的指令驗證那塊），加密後就可以隨意丟一堆雲端儲存服務了吧

[Linux] PHPMyAdmin - Table 'phpmyadmin.pma_table_uiprefs' doesn't exist @ Ubuntu 14.04

主因是將某處 DB 搬遷後，統一用一個 PHPMyAdmin 管理多個 DB Server ，因此出現的問題。

解法：

$ locate create_tables.sql.
/usr/share/doc/phpmyadmin/examples/create_tables.sql.gz
$ cp /usr/share/doc/phpmyadmin/examples/create_tables.sql.gz /tmp
$ cd /tmp && gunzip /tmp/create_tables.sql.gz

$ mysql -h xxxx -u root -p < create_tables.sql
$ sudo vim /etc/phpmyadmin/config.inc.php
更新 'pma_bookmark' to 'pma__bookmark' 等一系列項目，如：
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';

[Linux] 從 Ubuntu 12.04 / MySQL 5.5 升級到 Ubuntu 14.04 / MySQL 5.6 過程 @ Ubuntu 12.04

由於某台機器接近閒置，原本單純想把 MySQL 5.5 升級到 MySQL 5.6 的，後來看一下 Ubuntu 14.04 裡頭有 MySQL 5.6 可以用，就把它升了。

連續過程：

$ sudo do-release-upgrade -d

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 14.04 LTS
Release:        14.04

Codename:       trusty

$ service mysql stop

$ sudo apt-get remove mysql-common mysql-server-5.5 mysql-server-core-5.5 mysql-client-5.5 mysql-client-core-5.5
$ sudo apt-get autoremove

$ sudo apt-get install mysql-server-5.6
$ mysql_upgrade -u root -p

更正規的 MySQL 更新方式是要先把 /var/lib/mysql 跟 /etc/mysql 備份起來才行動的，此處因為機器上的服務已經遷移（在其他處有備份），所以就這樣升了。

AWS - 使用 Amazon RDS - MySQL Replica 筆記

體驗 Amazon RDS 與 MySQL Replica 運作模式，有幾項心得：

• Amazon RDS 當 Slave 時，必須改用 Amazon 自定 func 來啟動，如 mysql.rds_set_external_master、mysql.rds_reset_external_master、mysql.rds_start_replication (START SLAVE)、mysql.rds_stop_replication (STOP SLAVE) 和 mysql.rds_skip_repl_error 等
• Amazon RDS 使用 UTC 時區，依照 MySQL Replication and Time Zones 文件所述，Master 跟 Slave 的時區須一致，因此，也調整 Master 情況，對於 Timestamp 的用法就要避免用 MySQL CURRENT_TIMESTAMP 等，建議避開透過 MySQL 管理時區，直接用程式設定 timestamp 數值。但這樣變動還滿大的。
• 定期用 mysql> SHOW SLAVE STATUS \G 觀看，儘量不要用 SELECT count(*) FROM table 來觀看，因為會需要關注的情況通常是資料量已經很大了，而 AWS RDS $0.10 per 1 million I/O requests，一直用 count(*) 在資料量大的情況也會很可觀的。Seconds_Behind_Master 數值，可能短期不會降下來，用0.5~1天的時間間隔去觀察是否有下降即可
• 機器要 reboot 前，記得要先用 mysql.rds_stop_replication (等同 STOP Slave) 指令，若 Seconds_Behind_Master 數值很大，通常要跑很久
  
  

  mysql> CALL mysql.rds_stop_replication;                                                                                          
  +---------------------------+
  | Message                   |
  +---------------------------+
  | Slave is down or disabled |
  +---------------------------+
  1 row in set (5 min 28.69 sec)
  

• 假設 RDS 專門當 MySQL Replica Slave 的話，記得 Master 可以透過 binlog-ignore-db 濾掉 mysql 等 databases，不然當 Master 更動 mysql 時（例如 apt-get upgrade 有 mysql 時也會有機會碰到）也會把這些 log 記錄起來，到時後 RDS 收到就會噴錯誤訊息，而 RDS 的解法就是不斷地用 mysql.rds_skip_repl_error 略過這些更動（可以搭配 SHOW SLAVE STATUS \G 查看 Last_SQL_Error 資訊，例如：
  
  

  mysql> SHOW SLAVE STATUS \G
  ...
  Last_SQL_Error: Error 'Access denied for user ''@'' to database 'mysql'' on query. Default database: 'mysql'. Query: 'ALTER TABLE db
     MODIFY Host char(60) NOT NULL default '',
     MODIFY Db char(64) NOT NULL default '',
     MODIFY User char(16) NOT NULL default '',
     ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin'
  
  mysql> CALL mysql.rds_skip_repl_error ;
  +-------------------------------------+
  | Message                             |
  +-------------------------------------+
  | Statement in error has been skipped |
  +-------------------------------------+
  1 row in set (0.05 sec)
  
  +-----------------------------------------------------------------------------------+
  | Message                                                                           |
  +-----------------------------------------------------------------------------------+
  | Slave has encountered a new error. Please use SHOW SLAVE STATUS to see the error. |
  +-----------------------------------------------------------------------------------+
  1 row in set (2.05 sec)
  
  Query OK, 0 rows affected (2.05 sec)
  
  ...
  
  mysql> CALL mysql.rds_skip_repl_error ;
  +---------------------------------------------------------+
  | Message                                                 |
  +---------------------------------------------------------+
  | Slave is running normally.  No errors detected to skip. |
  +---------------------------------------------------------+
  1 row in set (0.03 sec)
  
  

此外，對於 RDS 操作心得：

• 開一個單位出來至少 15 分鐘起跳，從 create 到 backup 約 15 分鐘，其中 backup 是開台例行流程，以 18GB的 storage size 來說，約 3 分鐘，從 backup 換到 modifying 還要 2分鐘。
• 從 snapshot restore 亦是如此，過程還必須重 load my.cnf 設定(因為預設不給挑 my.cnf)，過程像是要再 reboot 一下，從 applying 到 pending-reboot 約 4 分鐘，當按下手動 reboot 時，兩分鐘內狀態從 pending-reboot 變成 in-sync，但 DB Instance Status 還是在 rebooting，從 rebooting 轉成 available 約 2 分鐘
• 試過 db.t1.micro 跟 db.m1.small 做 MySQL Replica Slave 的角色，發現 DB server 資料增加量追不上？改用 db.m1.medium 了($0.115 per RDS Medium Instance hour )，穩穩地有看到 Seconds_Behind_Master 下降，但貴的不是開台的價錢，還可能是 I/O Requests 的價碼，資料要一直追，現況 12 小時約 0.5 million I/O requests ($0.10 per 1 million I/O requests)

所以從 snapshot 開一檯機器都可以用耗時約 27 分鐘，比我想像中慢了不少，測試起來真痛苦。

其他心得：

• 別忘了確認 Master 跟 Slave 的 character，此例我是設定為 utf8，RDS 預設也是 latin1
  
  

  mysql> show variables like '%char%';
  +--------------------------+-------------------------------------------+
  | Variable_name            | Value                                     |
  +--------------------------+-------------------------------------------+
  | character_set_client     | utf8                                      |
  | character_set_connection | utf8                                      |
  | character_set_database   | utf8                                      |
  | character_set_filesystem | binary                                    |
  | character_set_results    | utf8                                      |
  | character_set_server     | utf8                                      |
  | character_set_system     | utf8                                      |
  | character_sets_dir       | /rdsdbbin/mysql-5.5.33.R1/share/charsets/ |
  +--------------------------+-------------------------------------------+
  

• 如果未來打算把 RDS 當 Master 的話，可以留意 auto_increment_increment 跟 auto_increment_offset 的設定，可提供平順的轉換機制（可支援從 Read 變 Write）
  

  mysql> show variables like 'auto_inc%';
  +--------------------------+-------+
  | Variable_name            | Value |
  +--------------------------+-------+
  | auto_increment_increment | 10    |
  | auto_increment_offset    | 2     |
  +--------------------------+-------+

• 建一台 RDS 個人常做設定 Parameter Groups：
  

  max_allowed_packet = 67108864
  sync_binlog = 1
  innodb_flush_log_at_trx_commit = 1
  auto_increment_offset = m
  auto_increment_increment = n
  collation_connection = utf8_unicode_ci
  character_set_results = utf8
  character_set_server = utf8
  character_set_connection = utf8
  character_set_database = utf8
  character_set_client = utf8
  character_set_filesystem = binary
  

• 一般 MySQL Server Master my.cnf：
  

  [mysqld]
  # ...
  character-set-server=utf8
  collation-server=utf8_general_ci
  # ...
  default-time-zone       = '+00:00'
  server-id               = x 
  auto-increment-increment = y
  auto-increment-offset   = z 
  log_bin                 = /var/log/mysql/mysql-bin.log
  binlog-ignore-db = mysql
  binlog-ignore-db = performance_schema
  binlog-ignore-db = information_schema
  binlog-ignore-db = test
  innodb_flush_log_at_trx_commit = 1 
  sync_binlog = 1
  

• 簡易 Slave 設定對應表:
  • 一般 MySQL Slave:

    mysql> CHANGE MASTER TO 
    MASTER_HOST='YourDBServerIP', 
    MASTER_USER='repl_account', 
    MASTER_PASSWORD='repl_password', 
    MASTER_LOG_FILE='mysql-bin.######', 
    MASTER_LOG_POS=#######;
    
    mysql> START SLAVE;
    
    mysql> STOP SLAVE;
    
    mysql> RESET SLAVE;
    

  • RDS Slave:

    mysql> CALL mysql.rds_set_external_master (
    'YourDBServerIP'
    , 3306
    , 'repl_account'
    , 'repl_password'
    , 'mysql-bin.######'
    , #######
    , 0
    );
    
    mysql> CALL mysql.rds_start_replication;
    
    mysql> CALL mysql.rds_stop_replication;
    
    mysql> CALL mysql.rds_reset_external_master;
    
    

• 如果MySQL Replica Master 是 5.5.x 版，那依照 MySQL 文件在 RDS 開一台 5.6.x 當 MySQL Replica Slave 應該也行，文件出處：MySQL 5.6 Reference Manual :: 16 Replication :: 16.4 Replication Notes and Tips :: 16.4.3 Upgrading a Replication Setup When you upgrade servers that participate in a replication setup, the procedure for upgrading depends on the current server versions and the version to which you are upgrading.

This section applies to upgrading replication from older versions of MySQL to MySQL 5.6. A 4.0 server should be 4.0.3 or newer.

When you upgrade a master to 5.6 from an earlier MySQL release series, you should first ensure that all the slaves of this master are using the same 5.6.x release. If this is not the case, you should first upgrade the slaves. To upgrade each slave, shut it down, upgrade it to the appropriate 5.6.x version, restart it, and restart replication. Relay logs created by the slave after the upgrade are in 5.6 format.

...