Tag: mysql

Posted on

mysqldump/pgdump 連線密碼的存放

寫資料庫備份腳本時，會避免把密碼放在腳本裡，免得密碼流出去。

這時候密碼要藏在那邊呢？

postgresql

postgresql 的 pg_dump 預設會去讀取 $HOME/.pgpass 檔案，所以可以把帳號跟密碼放在這邊，腳本裡使用 pg_dump 進行備份時就不需要額外用參數指定。

.pgpass 檔案的內容是

hostname:port:database:username:password
  • hostname: 資料庫主機
  • port: 資料庫主機的 port
  • database: 資料庫
  • username: 連線的使用者帳號
  • password: 連線的使用者密碼

使用的時候，就不需要指定密碼了

pg_dump -h <db_host> -U <db_user> -F c -f $dump_file <db_name>

mysql

一般備份 mysql 資料庫會使用 mysqldump，mysqldump 有個參數 --defaults-file=<config_file>

所以可以用這個參數來指定，先在 $HOME/.config 資料夾下新增 backup.my.cnf 檔案，內容如下

[mysqldump]
user=<db_user>
password=<db_pass>
  • <db_user>: 放連線的使用者帳號
  • <db_pass>: 放連線的使用者密碼

至於主機的部份，可以用 mysqldump 參數指定，例如

mysqldump --defaults-file=$HOME/.config/backup.my.cnf -h <db_host> <db_name>

參考資料

Posted on

MySQL 到底有沒有 case sensitive ?

公司的程式有人回報說，在搜尋電子郵件時，沒有區分大小寫 (case insensitive)，我心裡想，雖然電子郵件並沒有嚴格區分大小寫，但我應該沒有特別做這件事，照理說，是要有區分大小寫才是，於是我開始看程式碼去找是不是 Django 等框架/函式庫自動加上了。

不過我找了好一陣子都沒找到，看來應該不是程式的問題。既然不是程式問題，那就可能是資料庫問題了，我在開發環境的 SQLite 裡用 SELECT SQL 查詢，有區分大小寫；生產環境是用 MySQL，我改在 MySQL shell 用 SELECT SQL 來查詢。結果，真的是資料庫設定問題，上網搜尋，才確定真的是 MySQL 問題：

簡單的說，是 collation 問題，所以解決方法也算是簡單，一個方法是改變 collation，看是要資料庫整個改，還是個別 table/column 去改；一個方法則是改 column data type。

MySQL 手冊對 collation 的說明是這樣的 (連結)，只要後綴是 _cs 的或是 _bin 的，都是 case-sensitive 的。改 collation ，可以先使用 SHOW COLLATION WHERE COLLATION LIKE “％_cs” 來找，但 utf8 是沒有後綴名為 _cs 的 collation，只能使用 utf8_bin 。找到合適的 collation 後，要改指定 column data type ，可以使用 ALTER TABLE documents__document CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

以 Django 來說，當然最好是一開始在建立資料庫時，就指定好 charset 跟 collation，之後的作法就是自己寫 Migration 執行自訂的 SQL 來變更指定欄位的 data type 了。

Posted on

Django queryset 對日期時間欄位的額外設定

Django queryset 對日期時間的處理已經很完備了，可以透過使用 __year 或 __month 等方式來找到是某年或某月的紀錄。

這兩天碰到的狀況是，資料是 MySQL 時，日期時間的比對 (__year / __month) 失效了。仔細看過文件以後，才發現 MySQL 需要事先設定，使用 mysql_tzinfo_to_sql 載入時區表格才行。

This function performs time zone conversions directly in the database. As a consequence, your database must be able to interpret the value of tzinfo.tzname(None). This translates into the following requirements:

在終端機 (shell) 裡，輸入下列指令：

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

接著重新啟動 MySQL 伺服器即可。

mysql_tzinfo_to_sql 的用法不只一種，我選擇的是最簡單的用法。

Posted on

Heroku 小記

安裝：wget -qO- https://toolbelt.heroku.com/install-ubuntu.sh | sh

在現有的 web application project 目錄裡輸入 heroku create ，這會自動去遠端 (就 heroku) 開一個 app project，然後把 git repository 放到 git remote 裡。
git remote -v 裡會多出一個 heroku ，當你把 master branch push 到 heroku 時 (git push heroku master)，就會自動佈署到 heroku 上。
這裡要注意的是 heroku 只認定 master，只有推 master 上去時，才會真正的進行佈署，如果是其他 branch 名稱，heroku 不鳥你。

多台機器開發，我是指一下在 A 機器開發，一下又在 B 機器開發的情況。那這時候怎麼處理 project ？

第1種情況是，直接從 heroku 取得專案：heroku git:clone -a your_heroku_app_name the_directory

第2種情況是，你已經先從你的 git repository 取得專案，那麼這時候就先切換到專案目錄下，再用 heroku git:remote -a your_heroku_app_name 這個指令來自動在 git remote 裡增加一個 heroku 的 remote repository。

安裝資料庫的 Addon

MySQL
Heroku 本身不提供 MySQL，只有合作廠商所提供的 ClearDB，但實質上 ClearDB 就是 MySQL。
ClearDB 有提供4個方案，Ignite 是免費的，雖然免費，但是安裝前，還是要先填信用卡資訊。
安裝方法：heroku addons:create cleardb:ignite
安裝以後，連線字串會放在 CLEARDB_DATABASE_URL 這個環境變數裡，程式只要存取這個環境變數就可以取得連線字串，用 heroku config:get CLEARDB_DATABASE_URL 則可以看到連接字串。
參考這篇文，就可以用本地端的 MySQL Query Browser (MySQL GUI 工具) 連上遠端的 ClearDB。
簡單說，就是從連線字串拆解出 username/password/host ，然後套進去用就可以了。

PostgreSQL
Heroku 預設提供的 database，不過還是要安裝。
安裝方法：heroku addons:add heroku-postgresql:dev
連線字串一樣是在環境變數裡：HEROKU_POSTGRESQL_BRONZE_URL 。

Posted on

Ansible mysql 相關模組所需的套件

使用 mysql_user 跟 mysql_db 這兩個模組時，Target 方必須要安裝幾個套件才能運作，否則會有錯誤：

msg: the python mysqldb module is required

在 Ubuntu 裡要裝的是 python-mysqldb 、mysql-server  。python-mysqldb 是必要的，因為 python 的程式會需要用到這個模組；而 mysql-server 則要視你的 mysql server 所在機器而定，如果是在其他機器上，就可以不需要安裝。

Posted on

innobackupex

在噗浪上看到有人分享 innobackupex 這工具，就決定來試用看看。這指令其實是被包含在 xtrabackup 工具裡，使用 Perl 寫的，所以只要安裝 xtrabackup 就可以。

CentOS 5 上的安裝可以透過 yum，步驟可以參考 Percona 上的說明，首先用 rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm 安裝 Percona 的 repo 檔案，接著用 yum install percona-xtrabackup 就可以了。

我使用的是 MariaDB 5.5，試著要備份時，卻出現沒有指定 datadir 參數的問題。innobackupex 裡實際上使用的是 xtrabackup_55 (不同的資料庫需要用不同的指令，innobackupex 是自動判斷)，大致追蹤指令原始碼，發現它是讀取 my.cnf 來得知 datadir 的值； MariaDB 的 my.cnf 很簡潔，直接 include /etc/my.cnf.d 下的所有 cnf 檔案，再查看裡面各個 cnf，都非常的簡潔，看起來是都採用預設值，所以不需要額外設置。因此我在 /etc/my.cnf.d/server.cnf 的 [mysqld] 之後加上 datadir=/var/lib/mysql 以後，innobackupex 就可以順利運作了。

  • 備份：innobackupex –user=DB_USER –password=DB_PASSWORD /path/to/BACKUP-DIR
  • 回存：innobackupex –copy-back /path/to/BACKUP-DIR
Posted on

轉換超級大的檔案為 MySQL 可用的 .sql 檔

在大致看過原始檔案以後，可以知道原始檔案大致的格式是是這樣：

  • 每筆記錄以只有 ‘@’ 存在的行做分隔。
  • 每筆欄位，也就是每行以 @field: value 存在。
  • @content: 該行比較特別，有可能是多行，有可能是單行。

最簡單的想法就是逐行解析。一開始是先寫了一個小程式，確定一下有哪些欄位，結果這樣就執行了好久好久。所以為了實驗方便，後來改以 head -n 2000000 | tail -n 1000000 大致取了一小段來使用，來確定有多少欄位。為了以防萬一，有刻意改變數字，從尾段取部分、從中段取部分，確定三者得到的欄位都一致，就確定了 MySQL table 所需的欄位。這應該也算是統計吧，以採樣的方式來取得概略值。(之後稱這些部分的檔案為樣本檔)

接著用 grep ‘@content:’ | wc -l 算了一下樣本檔，想不到這樣就有上萬筆!! 看來逐行解析然後新增到 MySQL 伺服器裡是不太現實的，不管是新增或是導出 SQL 敘述檔案肯定都會花許多的時間。因此就改變策略，要把每筆記錄都輸出到檔案，然後再根據檔案來逐檔轉換。但是這樣也出了問題，Linux 檔案系統的 inode 就這樣被我用盡，即使 df 的結果顯示還有空間，但就是沒辦法再新增檔案了。只好再次改變策略，分割為每十萬筆記錄為一個檔案。

針對檔案的解析並不難，那想要直接輸出 SQL 敘述，使用字串參數的方式未免太麻煩。這裡藉助了 mosql 模組的幫忙，只要丟一個 dict 進去，可以很簡單的輸出 SQL INSERT 敘述。

接著為了要讓 CPU 能用到所有核心，使用了 python 的 multiprocessing 模組，先把上述分割好的檔案名稱存為 list，然後以 multiprocessing.Pool 的 map 方法去執行，這樣就大幅的縮短了處理時間。

針對大型的檔案處理，必須要分割為多個小檔案，才能善用運算資源，速度才會快。