標籤: MySQL

分類
好用軟體

好用方便的虛擬主機管理器 – CyberPanel

這個管理工具, 是類似 cPanel、Plesk 這類的 hosting 服務的管理工具, 若是搭配 OpenLiteSpeed 版本的 web service 是完全免費的, 十分佛心.

而搭配 LiteSpeed 版本的 web service 的 CyberPanel Ent. 版, 在 1 domain 下, 2GB Ram 下也是每個月 0元, 可以參考價格說明:

https://cyberpanel.net/cyberpanel-enterprise/

而 OpenLiteSpeed 與 LiteSpeed Ent 版本有什麼差異, 可以參考這裡:

https://www.litespeedtech.com/products/litespeed-web-server/editions

安裝支援的系統有 Centos 7.x, Centos 8.x, Ubuntu 18.04, Ubuntu 20.04, 另外需要的條件有: Python 2.7, 1024MB or above Ram, 10GB Disk Space. 可以參考安裝說明:

https://cyberpanel.net/docs/installing-cyberpanel/

安裝完成後, 應用服務包含了 php (多版本), web, mysql, mail, ftp, dns, ssl 等, 十分方便, 另外管理介面使用 8090 port , 若希望管理介面也能用 let’s encrypt ssl 可以參考這篇的設定方式:

https://cyberpanel.net/docs/2-cyberpanel-on-ssl/

簡單說明, 也就是建立一個 website, 域名為你想用來管理的域名, 然後再到管理介面左側選單的 SSL, Hostname SSL, 選擇你想用的 website 域名後, 再按下 Issue SSL 即可.

他的 email service web 介面使用了 rainloop 也十分方便, 另外也支援了 DKIM 功能, 一樣能透過 SSL/TLS 加密 email 與使用 smtp / pop3 / imap 等方式來存取.

這個工具十分方便, 有在管理多虛擬主機的朋友們可以參考.

分類
好用軟體

Google Data Studio篩選器中文欄位問題

這個問題很有趣, 在引用 mysql database connection 下的 datasource 時, 文字欄位要下篩選器, 來過濾資料, 發現中文字會篩選不出符合的結果, 英數字是沒問題的.

另外測了使用文字檔上傳的 csv 做 datasource 時, 則沒有這樣的問題.

於是查了一下資料, 有篇文章提到了這個問題與他的解法:

https://www.en.advertisercommunity.com/t5/Data-Studio/Filtering-with-utf-characters/td-p/1198985

居然是使用了 REGEXP_REPLACE 方式來新建立一個 computed field 進行使用, 是可行的, 不過想說用輕量一點的文字函數來試, 如 REPLACE, TRIM 居然都不行, 真是太奇妙了。

詳細的 function 可以參考:

https://support.google.com/datastudio/table/6379764?hl=en

[2020/2/25]
網友 Fion:
https://www.en.advertisercommunity.com/t5/Data-Studio/Filtering-with-utf-characters/td-p/1198985
這篇文章已經找不到了，請問用REGEXP_REPLACE 的方式來解決篩選器問題時，參數應該怎麼下？

[2020/3/2]
回覆:
Hi, 你好, 該連結的確已失效, 這裡簡單說明一下作法, 就是在 [資料來源]這裡, 新增一個欄位, 以原欄位為 name 為例, 新建一個 new_name 的欄位, 把公式設為: REGEXP_REPLACE(name, ‘A’, ‘A’) 即可, 在設定篩選器時, 就可以使用 new_name 這個欄位來應用, 即可順利進行篩選.

分類
好用軟體

免費好用的cloudaccess代管服務-WordPress與Joomla

在建立網站時, 常常需要一個環境可供測試與驗證, 但又不需要像是一個虛擬機這麼大的服務, 有個佛心的廠商, 提供了免費的代管, 可以透過以下推薦碼來取得:

https://ccp.cloudaccess.net/aff.php?aff=5493

這個網路服務提供商提供了免費代管 wordpress 與 joomla 兩大應用服務, 可以線上直接啟用, 免費的版本也可以有多個網站, 但每 30天需要 renew 一次, 若 30天內沒有 renew, 網站就會被關閉.

這是一個用來做為測試與驗證, 甚至短期服務的一個網站提供商, 免費版的後端就可以有 ftp 與 phpmyadmin 管理資料庫, 十分方便, 來看看後端吧:

這個是 ftp:
備份管理:

資料庫管理(phpMyAdmin):

真的是太方便, 太佛心了, 不過網址只能使用 xxx.cloudccess.host , 若要自訂, 也是需要付費的, 付費方案比較, 請參考:

https://www.cloudaccess.net#pricing

記得申請帳號使用以下推薦連結:

https://ccp.cloudaccess.net/aff.php?aff=5493

分類
Database

Mysql dump檔案分割(大文字檔案分割指令-csplit)

在要還原 mysql 備份檔時, 有時整個 database 的 backup file 檔案很大, 要找出其中某一個區塊很花時間, 也需要用一個能打開大檔的文字編輯器才能找到, 有沒有更方便的方法, 找出其中某個 table 呢? 可以使用好用的文字檔分割工具 csplit (linux 指令)

舉例來說, 我們要找一個 table t_table1 的 dump 檔案內容, 而備份的 mysql database 檔案為 mysqlbackup.sql 我們使用指令如下:

csplit -f output_file mysqlbackup.sql "/Table structure for table `t_table1`/" "/Table structure for table/"

這樣一共會輸出三個檔案: output_file00, output_file01, output_file02, 其中的 output_file01 就會是我們要的 t_table1 這個資料表的 dump 資料了. 當然, 前後的檔案就分別是切出 01 後的 00, 02 了.

所以工作原理就是將 Table structure for table `t_table1` 與 Table structure for table 兩個分割點來進行檔案切割. 所以若是希望把所有的 table 檔分出來, 則可以使用以下指令:

csplit -f output_file mysqlbackup.sql "/Table structure for table/" "{10}"

其中的 10 就是要重覆做幾次(包含前面做第一次, 後面再做10次共11次), 重點是若有 11 個 table, 應該就可以分出 11 個檔案, 其中的 00 沒有用, 而 01~11 就會是那 11個 table 的 dump file, 所以若是不知 table 數量時, 後面的那個參數, 就不能寫超過總數的次數, 否則會一個檔案也不輸出.

另外可以使用 {*} 的方式如下:

csplit -f output_file mysqlbackup.sql "/Table structure for table/" "{*}"

會直接盡可能地使用重覆到無法使用, 也就是 table 有多少就會做多少次, 下面 PS 有說明我在 FreeBSD 8.2 執行時有錯誤, 不過在 CentOS 是可以正常執行的.

使用 csplit 可以快速的切割超級大檔案, 把要找的資料整理出來, 很方便又實用.

繼續閱讀:
http://www.computerhope.com/unix/ucsplit.htm

PS. 雖然上面這篇介紹有使用 {*} 來重覆作業, 不過我實際執行時, 會發生:
csplit: *}: bad repetition count 的錯誤訊息, 環境是 FreeBSD 8.2, 不過在另一台 CentOS 7.1 是可以正常執行的, 所以若是不能使用 {*} 請參考上面說明.

分類
Database

MySQL資料表不存在的資料就新增-存在就忽略(或異動)的語法

在新增資料於資料表時, 有個需求, 是不存在的資料就新增, 存在的資料就不動作(或是要處理一些異動), 這樣的需求如何操作呢?

可以使用 INSERT IGNORE INTO [table] ….. 的語法,

若是要異動則使用

INSERT INTO [table]….. ON DUPLICATE KEY UPDATE col=expr …..

可以參考以下語法:
http://cain19811028.blogspot.tw/2015/01/mysql-insert-ignore-replace-on.html
https://mariadb.com/kb/en/mariadb/insert-on-duplicate-key-update/

分類
Database

Mysql的字串轉型問題

Mysql 在字串欄位比對上, 有個奇妙的狀況. 當然, 在正常一般狀況下不太容易發生.

某欄位 f1 為 varchar 或 char , 而在 where 條件下, 使用了

where f1=’sometext’

這樣沒有問題.

但若是使用了

where f1=0

會發生什麼事呢? 結果是全部成立.

根據 f1=0 來看, mysql 會先將 f1 欄位轉為數字, 而將字串轉為數字, 在 mysql 中, 會是 0 的結果, 導致會全部成立. 檢查以下語法:

SELECT cast( 'test string' AS SIGNED )

結果會是 0 , 也就解開了這個問題.

另外可以參考一下 cast 的語法:
http://dev.mysql.com/doc/refman/5.6/en/cast-functions.html

其 case ( data as type ) 中的 type, 可以為以下幾種:
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

供各位在不小心, 使用了等於零在判斷條件時, 發生問題的參考.

分類
Database

MySQL的on duplicate key update語法

很有意思的一個語法, 不過可要小心使用, 在 mysql 5.0 版起, 可以在 insert 時, 指定若重覆 unique key 時, 則使用 update 語法, 這個有點像是在新增資料時, 若不存在則新增, 存在則 update 的方式.

來個例子:

CREATE TABLE `t3` (
`sid` INT NOT NULL ,
`v1` INT NOT NULL ,
`v2` INT NOT NULL ,
`v3` INT NOT NULL ,
PRIMARY KEY ( `sid` )
) 
insert into t3 values (1,1,1,1)
insert into t3 values (2,2,2,2)
insert into t3 values (2,2,2,2) on duplicate key update v1=v1+1

這時候, 會有一筆, 1,1,1,1 和 2,3,2,2 的資料產生, 如此一來, 可以在重覆 unique key 時, 用來做為更新的條件, 由於 unique key 只會出現一次在對應 table 上, 所以可以用來做為更新的條件值, 而 primary key 也是 unique key, 所以發生在 primary key 時是一樣的狀況.

不過若是新增的資料, 發生多筆 unique key 重覆時(指不同欄位), 該 update 會只更新一筆, 這是在程式上要避免發生的, 以免有資料更新錯誤的問題. 詳情可以參考官方網站資料: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

這種有趣的語法, 其實用的機會不是太多, 不過多學習了解很有意思.

分類
Database

FreeBSD上mysql的效能追蹤

最近因為有台主機的 mysql cpu 異常變高許多, 所以要來進行查詢問題所在.

因為 mysql 沒有像 mssql 的 sql profiler 那麼方便的工具, 所以利用 mysql 本身的 log 來進行, 也就是 slow query log 這個功能, 步驟如下:

1. 先將 /etc/my.cnf 中的 [mysqld] 內多加入下面資料:

log-slow-queries = /var/log/slow-query.log #slow query記錄檔的位置
long_query_time = 2 #query執行超過2秒時才記錄

2. 重啟 mysql 服務, 指令如下:

/usr/local/etc/rc.d/mysql-server restart

3. 執行一陣子後, 就可以看看該 log 檔內的 query , 接下來就是針對這些 query 來調整效能

以上是在 FreeBSD 環境下的作法. (其他環境其實也類似)

其實執行時間長不一定是效率不好, 不過若是常常發生的查詢是需要長時間的, 就有改善的必要, 簡單地說, 就是若一個查詢需要 5秒, 但一天跑不到 10次, 那根本不用管他, 不過若一個查詢需要 0.02 秒, 但一天要用到數萬次, 即使從 0.02 改善到 0.015 就會有很明顯的效能改善, 所以要看發生的頻率及所花費執行的時間, 平衡來看.

另外, 若是該 log 沒有產出, 記得權限要給對, 因為是 mysql service account 去執行寫入的動作, 就算沒有任何 log 也會有 mysql 啟動的資訊, 不會沒有任何產出的 log.

參考資料:
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
http://blog.lansea-chu.com/index.php/archives/238
http://homeserver.com.tw/mysql/mysql%E7%9A%84%E6%9F%A5%E8%A9%A2%E6%99%82%E9%96%93log-slow-queries/
http://ezkuan.blogspot.com/2010/05/freebsd-mysql.html

分類
Database

MySQL的Log

在 MySQL 運行時, 若想要了解執行時期, 對資料庫下達指令的狀況, 可以利用 my.ini 中的參數來達成, 在 [mysqld] 中多加一行 log=路徑/檔名, 再重起服務即可.

這個目的對於在追蹤應用程式執行指令很有幫助, 不過若是繁忙系統下, 很可能會有大量的資料, 而難以閱讀, 建議使用在開發環境, 再執行對應的 application 記錄下來的 log 就會單純許多, 以方便追蹤.

在 MSSQL 中可以利用 SQL Profiler 來達成, 不過 MySQL 無法過濾那麼多資訊, 只能整個記錄下來. 記得這些操作對於系統效能都會有一定的衝擊, 開起來之後, 記得要關掉, 否則將會影響效能.

另外還有一個好用的參數, 就是一樣在 [mysqld] 中, 加一行 log_slow_queries=路徑/檔名, 這個將會讓系統記錄執行時間較長的 query, 也方便做效能調校及追蹤使用, 預設是執行時間超過 10秒的會被記錄下來, 參數是 long_query_time 可以參考:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_long_query_time

這兩種方式分別做完整記錄及長時查詢記錄, 對於想要了解資料庫執行時期的問題及效能調校, 都可以提供不少幫忙.

相關閱讀:
http://blog.wabow.com/archives/54

分類
Database

MySQL的Text欄位長度

MySQL 中, text 屬性的欄位, 用來存放文字的資料格式, 共區分為以下 4種, tinytext, text, mediumtext, longtext, 分別的長度大小為 2^8, 2^16, 2^24, 2^32 bytes 這麼大.

簡單地說, 分別就是 256bytes, 64kbytes, 16Mbytes, 4Gbytes, 在 MySQL 中的 text 有大小不同的限制呢, 所以一般若是只開 text 的話, 最大也只能存到 64kbytes 而已.

參考資料:
http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html
http://plog.longwin.com.tw/programming/2009/10/20/mysql-text-field-type-length-limit-2009