SQLAlchemy use unicode

engine = create_engine('mysql://root:password@localhost/test?charset=utf8&use_unicode=0')

References :
sqlalchemy中文问题解决方案 - 江湖 - 51CTO技术博客

MYSQL UPDATE with WHERE SELECT subquery

update foo
set bar = bar - 1
where baz in
(
  select baz from
  (
    select baz
    from foo
    where fooID = '1'
  ) as arbitraryTableName
)

References : 

MYSQL update with WHERE SELECT subquery error - Stack Overflow

MySQL SHOW PROCESSLIST too long

$ echo "show full processlist;" | mysql -uroot -ppassword | less

MySQL SELECT WHERE REGEXP

mysql> SELECT * FROM `users` WHERE address REGEXP '台北市|新北市';

References :
sql - MySQL LIKE IN()? - Stack Overflow

MySQL show all grouped results

select t.Letter, t.Value
from MyTable t
inner join (
    select Letter, sum(Value) as ValueSum
    from MyTable
    group by Letter
) ts on t.Letter = ts.Letter
order by ts.ValueSum desc, t.Letter, t.Value desc

References :
sorting - MySQL show all grouped results and sort - Stack Overflow

MySQL Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes

因 MySQL 有單筆 SQL Query 長度的限制，在匯入檔案時，一次 INSERT 資料很大會錯誤，需調整單筆 SQL Query 長度

查詢目前 max_allowed_packet 設定值
mysql> show variables like 'max_allowed_packet';

更改爲 256MB (Client 需重新連結才會生效)

mysql> set global max_allowed_packet = 1024 * 1024 * 256;




References :
MySQL Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes - Stack Overflow

MySQL 開啓檔案數

系統允許最大開啓檔案數

$ sysctl -a | grep fs.file-max
fs.file-max = 146925
$ cat /proc/sys/fs/file-max
146925

目前系統所有程式已開啓的檔案數

$ lsof | wc -l
18120

每個 shell 允許開啓檔案數

$ ulimit -n
1024

MySQL 最大允許開啓檔案數 (與 open_files_limit 變數設定 (default: 0) 或 ulimit 有關)

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 1024 |
+------------------+-------+

MySQL 開啓的檔案數

$ pgrep mysqld
1661
1988
$ ls /proc/1988/fdinfo/ | wc -l
248
$ lsof -a -d 1-999 -p 1988

MySQL 目前開啓的檔案數

mysql> show status like '%Open_files%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 221 |
+---------------+-------+

export MySQL database into separate files per table

#!/bin/bash

BACKUP_DIR=/tmp/db_backup                                                                                                                    
HOST='127.0.0.1'
USER='root'
PASSWORD='root'
DATABASE='mydb'

for T in `mysql -u $USER --password="$PASSWORD" -h $HOST -N -B -e "show tables from $DATABASE"`;
do
    echo "Backing up $T"
    mysqldump --skip-comments --compact -u $USER --password="$PASSWORD" -h $HOST $DATABASE $T > $BACKUP_DIR/$T.sql
done;

References :
Export MySQL Database into Separate Files per Table | JamesCoyle.net

MySQL trailing white spaces and new line

mysql> UPDATE FOO set FIELD2 = TRIM(Replace(Replace(Replace(FIELD2,'\t',''),'\n',''),'\r',''));

References :
MySQL - how to remove white space in a mysql field - Stack Overflow

MySQL [Errno 24] Too many open files 錯誤

References :
mysql too many open files | 丫忠的伺服器
Can't open file: (errno: 24) - 风生水起 - 博客园

configure: error: Unknown MySQL directory - unable to find mysql.h

# apt-get install libmysqlclient-dev

MySQL 建立分區

• 建立分區時可在分區加上 DATA DIRECTORY 和 INDEX DIRECTORY 選項，將資料存在不同的硬碟分區中

• 刪除分區時，分區內的資料也會被刪除

• hash 和 key 類型的分區不能 REORGANIZE

分區限制：

1. 分區欄位必須加入主鍵或唯一鍵
2. 分區需返回 int 類型的值作爲條件判斷
3. 一個表最多 1024 個分區

分區類型：

• range
• list
• hash
• key

// 新增分區

mysql> ALTER TABLE tbl PARTITION BY KEY(col1) PARTITIONS 5;

mysql> ALTER TABLE tbl partition by range(`day`) (
partition p_2012 values less than (20130000),
partition p_2013 values less than (20140000)
);

// 刪除分區 (刪除分區時，分區內的資料也會被刪除)

mysql> ALERT TABLE tbl DROP PARTITION p0;

//分區資料操作

mysql> ALTER TABLE tbl REBUILD PARTITION p0, p1;
mysql> ALTER TABLE tbl ANALYZE PARTITION p0, p1;
mysql> ALTER TABLE tbl OPTIMIZE PARTITION p0, p1;
mysql> ALTER TABLE tbl REPAIR PARTITION p0, p1;mysql> ALTER TABLE tbl CHECK PARTITION p0, p1;

References :
MySQL :: MySQL 5.5 Reference Manual :: 19.2 Partitioning Types
MySQL Partition | Jonathan Hui
mysql partition 分区功能使用详解-mysql教程-数据库-壹聚教程网

show MySQL table partition

// show partitions in database

SELECT TABLE_NAME, PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'db_name' ;

// show partition type

SHOW CREATE TABLE tbl_name;

// show table partition size

SELECT PARTITION_ORDINAL_POSITION, TABLE_ROWS, PARTITION_METHOD
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tbl_name';

References :
database - How to see table partition size in MySQL ( is it even possible? ) - Stack Overflow
mysql - Can i view tables which are under Partition in my sql - Stack Overflow

mysql auto set timestamp on create and update

ALTER TABLE mytable
ADD lastmodified TIMESTAMP 
    DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP;

References : 

php - How to get ID of the last updated row in MySQL? - Stack Overflow

mysql-proxy 0.8.1 測試

# apt-get install mysql-proxy

$ mysql-proxy -V

mysql-proxy 0.8.1
  chassis: mysql-proxy 0.8.1
  glib2: 2.30.2
  libevent: 2.0.21-stable
  LUA: Lua 5.1.4
    package.path: /usr/lib/mysql-proxy/lua/?.lua
    package.cpath: /usr/lib/mysql-proxy/lua/?.so
-- modules
  admin: 0.8.1
  proxy: 0.8.1

$ mkdir mysql-proxy

$ cd mysql-proxy

$ wget https://raw.githubusercontent.com/cwarden/mysql-proxy/master/examples/tutorial-query-time.lua

$ vi mysql-proxy.cnf

[mysql-proxy]                                                                                            
daemon = false
pid-file = /tmp/mysql-proxy.pid
log-file = /tmp/mysql-proxy.log
log-level = debug
admin-username = 1
admin-password = 1
admin-lua-script = /usr/share/mysql-proxy/admin.lua
proxy-address = 0.0.0.0:3307
proxy-backend-addresses = 192.168.24.204:3306
proxy-lua-script = /home/yan/mysql-proxy/tutorial-query-time.lua

$ chmod 660 mysql-proxy.cnf

$ mysql-proxy --defaults-file=mysql-proxy.cnf

References :
mysql-proxy命令参数（二） | 哈巴狗

MySQL import from csv

LOAD DATA LOCAL INFILE '/tmp/bar.csv' INTO TABLE `foo` CHARACTER SET UTF8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

* windows 檔案需用 '\r\n'


ERROR 1148 (42000): The used command is not allowed with this MySQL version

LOAD DATA INFILE '/tmp/bar.csv' INTO TABLE `foo` CHARACTER SET UTF8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1'

Solve :
Install MySQL 5.6





References :
mysqldump mysql 5.6 problem solved | markomedia Technologies

Deian testing install MySQL 5.6

1. Download MySQL
https://dev.mysql.com/downloads/mysql/

2. dpkg -i mysql-5.6.17-debian6.0-i686.deb

3. /opt/mysql/server-5.6/bin/mysql




References :
How to Install MySQL 5.6 on Ubuntu 13.10 x64 / Debian Linux

mysql timestamp to date

DATE_FORMAT(FROM_UNIXTIME(created), '%d/%m/%Y')
DATE(FROM_UNIXTIME(created))

xtradb mysql cluster

References :
安裝 Percona XtraDB Cluster - 張旭