wiki:jazz/MySQL

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)
      
  • 查詢資料庫硬碟用量 - 參考來源
    SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.TABLES WHERE table_schema = "$your_database";
    
  • 查詢資料表硬碟用量 - 參考來源
    SELECT sum(char_length($your_column))/1024/1024 FROM $your_table
    
  • 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;
    

踩到的雷

Last modified 7 years ago Last modified on Feb 26, 2018, 2:27:20 PM