= MySQL =

* 查詢同時連線數上限
{{{
mysql> SHOW VARIABLES where Variable_name like 'max_connect%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)
}}}
* 查詢目前的同時連線數
{{{

mysql> show status where `variable_name` = 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 151   |
+-------------------+-------+
1 row in set (0.00 sec)
}}}
* 查詢目前連線的 process
{{{
mysql> show processlist;
+----+-------+-----------+-------+---------+------+-------+------------------+
| Id | User  | Host      | db    | Command | Time | State | Info             |
+----+-------+-----------+-------+---------+------+-------+------------------+
|  1 | root  | localhost | NULL  | Query   |    0 | init  | show processlist |
|  5 | cacti | localhost | cacti | Sleep   |  292 |       | NULL             |
|  6 | cacti | localhost | cacti | Sleep   |  311 |       | NULL             |
|  7 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
|  8 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
| 10 | cacti | localhost | cacti | Sleep   |  306 |       | NULL             |
| 11 | cacti | localhost | cacti | Sleep   |  306 |       | NULL             |
| 12 | cacti | localhost | cacti | Sleep   |  306 |       | NULL             |
| 33 | cacti | localhost | cacti | Sleep   |    0 |       | NULL             |
| 34 | cacti | localhost | cacti | Sleep   |    1 |       | NULL             |
| 35 | cacti | localhost | cacti | Sleep   |   37 |       | NULL             |
| 36 | cacti | localhost | cacti | Sleep   |    4 |       | NULL             |
| 37 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
| 38 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
| 39 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
| 40 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
| 41 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
+----+-------+-----------+-------+---------+------+-------+------------------+
17 rows in set (0.00 sec)
}}}
* 查詢目前的連線等待時間 
  * [參考] https://stackoverflow.com/questions/23399111/safely-keeping-mysql-connections-alive
  * 預設值是 28800 seconds
{{{
mysql> SHOW VARIABLES where Variable_name like 'wait%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)
}}}
  * 可以 runtime 修改
{{{
mysql> set global wait_timeout=60;
mysql> set session wait_timeout=60;
mysql> SHOW VARIABLES where Variable_name like 'wait%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 60    |
+---------------+-------+
1 row in set (0.00 sec)
}}}

* 查詢資料庫硬碟用量 - [https://makandracards.com/makandra/39413-mysql-mariadb-show-disk-usage-of-tables-and-columns 參考來源]
{{{
SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.TABLES WHERE table_schema = "$your_database";
}}}
* 查詢資料表硬碟用量 - [https://makandracards.com/makandra/39413-mysql-mariadb-show-disk-usage-of-tables-and-columns 參考來源]
{{{
SELECT sum(char_length($your_column))/1024/1024 FROM $your_table
}}}
* [https://dev.mysql.com/doc/refman/5.7/en/rename-table.html RENAME TABLE Syntax]
* MySQL Table 換資料庫 - 從 `DB_A` 要搬到 `DB_B`
{{{
mysql> RENAME TABLE DB_A.old_table TO DB_B.new_table;
}}}
* MySQL Table 重新命名
{{{
mysql> RENAME TABLE old_table TO new_table;
}}}
* MySQL Table 互換名字
{{{
mysql> RENAME TABLE old_table TO tmp_table, new_table TO old_table, tmp_table TO new_table;
}}}
* 顯示 InnoDB 跟 MyISAM 各自儲存了多少資料量
{{{
mysql> SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;
}}}

== 踩到的雷 ==

* 用 InnoDB 會遇到 ibdata1 一直長大
  * 參考：[https://dba.stackexchange.com/questions/39125/ibdata1-grows-exponentially-when-innodb-file-per-table-is-configured ibdata1 grows exponentially when innodb_file_per_table is configured]
{{{
/var/lib/mysql$ sudo du -s $(pwd)/* | sort -n | tail -n 2
33232756    /var/lib/mysql/ibdata1     # 32G
49176852    /var/lib/mysql/dashboard   # 49G
}}}
* 用 InnoDB 必須下 OPTIMZE 才能完全釋出已經刪除 record 的硬碟空間，而且執行 OPTIMIZE TABLE 時，需要有足夠的硬碟空間（因為會產生一個新的資料表檔案，然後複製資料進去，再刪掉舊的資料表檔案）
  * 參考：https://blog.longwin.com.tw/2012/03/mysql-myisam-innodb-optimize-2012/