差異處

這裏顯示兩個版本的差異處。

連向這個比對檢視

兩邊的前次修訂版 前次修改
下次修改		 前次修改
tech:mysqltips [2020/08/20 12:41] – [2. 設定與查詢週期定時執行程序] jonathan_tsaitech:mysqltips [2022/08/03 15:24] (目前版本) – jonathan
行 1: 行 1:
-====== MySQL 相關技巧彙整 ======+====== MySQL/MariaDB 相關技巧彙整 ======
 ===== 1. 資料表效能優化 ===== ===== 1. 資料表效能優化 =====
   * 參考 - https://www.thegeekstuff.com/2016/04/mysql-optimize-table/   * 參考 - https://www.thegeekstuff.com/2016/04/mysql-optimize-table/
行 8: 行 8:
  where round(data_free/1024/1024) > 500   where round(data_free/1024/1024) > 500 
  order by data_free_mb;  order by data_free_mb;
-</code> 結果顯示類似以下訊息<xtermrtf>+</code> 結果顯示類似以下訊息<cli>
 +----------------+----------------+--------------+ +----------------+----------------+--------------+
 | table_name     | data_length_mb | data_free_mb | | table_name     | data_length_mb | data_free_mb |
行 17: 行 17:
 +----------------+----------------+--------------+ +----------------+----------------+--------------+
 3 rows in set (0.006 sec) 3 rows in set (0.006 sec)
-</xtermrtf>+</cli>
   - 針對呈現出來的 Table 進行優化 <code sql>   - 針對呈現出來的 Table 進行優化 <code sql>
 OPTIMIZE TABLE dwg_files, pdm_to_erp_log, user_use_log; OPTIMIZE TABLE dwg_files, pdm_to_erp_log, user_use_log;
-</code> 結果顯示如下訊息<xtermrtf>+</code> 結果顯示如下訊息<cli>
 +--------------------------+----------+----------+-------------------------------------------------------------------+ +--------------------------+----------+----------+-------------------------------------------------------------------+
 | Table                    | Op       | Msg_type | Msg_text                                                          | | Table                    | Op       | Msg_type | Msg_text                                                          |
行 32: 行 32:
 +--------------------------+----------+----------+-------------------------------------------------------------------+ +--------------------------+----------+----------+-------------------------------------------------------------------+
 6 rows in set (31.340 sec) 6 rows in set (31.340 sec)
-</xtermrtf>+</cli>
   - 查看 /var/lib/mysql/abc 內的資料檔在執行後會變小   - 查看 /var/lib/mysql/abc 內的資料檔在執行後會變小
  
行 39: 行 39:
   - 查看現有排程 <code sql>   - 查看現有排程 <code sql>
 show events; show events;
-</code> Exp. <xtermrtf>+</code> Exp. <cli>
 +-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+ +-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
 | Db        | Name                                                        | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends                | Status  | Originator | character_set_client | collation_connection | Database Collation | | Db        | Name                                                        | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends                | Status  | Originator | character_set_client | collation_connection | Database Collation |
行 46: 行 46:
 | abc| 刪除1個月以上的curl資料                                     | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2019-10-08 00:00:00 | 2030-10-31 00:00:00 | ENABLED |          1 | utf8mb4              | utf8mb4_unicode_ci   | utf8_general_ci    | | abc| 刪除1個月以上的curl資料                                     | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2019-10-08 00:00:00 | 2030-10-31 00:00:00 | ENABLED |          1 | utf8mb4              | utf8mb4_unicode_ci   | utf8_general_ci    |
 : :
-</xtermrtf>+</cli>
   - 顯示詳細排程內容 Exp. 刪除1個月以上的curl資料 <code sql>   - 顯示詳細排程內容 Exp. 刪除1個月以上的curl資料 <code sql>
 SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='刪除1個月以上的curl資料'; SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='刪除1個月以上的curl資料';
-</code> 顯示結果: <xtermrtf>+</code> 顯示結果: <cli>
 +---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ +---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+
 | EVENT_CATALOG | EVENT_SCHEMA | EVENT_NAME                       | DEFINER        | TIME_ZONE | EVENT_BODY | EVENT_DEFINITION                                                                    | EVENT_TYPE | EXECUTE_AT | INTERVAL_VALUE | INTERVAL_FIELD | SQL_MODE                                                                                  | STARTS              | ENDS                | STATUS  | ON_COMPLETION | CREATED             | LAST_ALTERED        | LAST_EXECUTED       | EVENT_COMMENT | ORIGINATOR | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION | | EVENT_CATALOG | EVENT_SCHEMA | EVENT_NAME                       | DEFINER        | TIME_ZONE | EVENT_BODY | EVENT_DEFINITION                                                                    | EVENT_TYPE | EXECUTE_AT | INTERVAL_VALUE | INTERVAL_FIELD | SQL_MODE                                                                                  | STARTS              | ENDS                | STATUS  | ON_COMPLETION | CREATED             | LAST_ALTERED        | LAST_EXECUTED       | EVENT_COMMENT | ORIGINATOR | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
行 56: 行 56:
 +---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ +---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+
 1 row in set (0.001 sec) 1 row in set (0.001 sec)
-</xtermrtf>+</cli>
   - 建立新的排程 Exp. 針對特定 Table 每周日中午 12:07 進行優化 <code sql>   - 建立新的排程 Exp. 針對特定 Table 每周日中午 12:07 進行優化 <code sql>
 CREATE EVENT 優化LOG型資料表 CREATE EVENT 優化LOG型資料表
行 64: 行 64:
 </code> 如果沒有錯誤訊息, 就可以透過 <code sql> </code> 如果沒有錯誤訊息, 就可以透過 <code sql>
 SHOW events; SHOW events;
-</code> 顯示出來如下:<xtermrtf>+</code> 顯示出來如下:<cli>
 +-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+ +-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
 | Db        | Name                                                        | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends                | Status  | Originator | character_set_client | collation_connection | Database Collation | | Db        | Name                                                        | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends                | Status  | Originator | character_set_client | collation_connection | Database Collation |
行 71: 行 71:
 | abc | 優化LOG型資料表                                             | root@localhost | SYSTEM    | RECURRING | NULL       | 7              | DAY            | 2020-08-23 12:07:00 | NULL                | ENABLED |          1 | utf8                 | utf8_general_ci      | utf8_general_ci    | | abc | 優化LOG型資料表                                             | root@localhost | SYSTEM    | RECURRING | NULL       | 7              | DAY            | 2020-08-23 12:07:00 | NULL                | ENABLED |          1 | utf8                 | utf8_general_ci      | utf8_general_ci    |
 : :
-</xtermrtf> +</cli>
  
 +===== 3. 出現異常訊息 - [ERROR] mysqld: Table './stats/stats_media' is marked as crashed and should be repaired  =====
 +  * 參考 - https://stackoverflow.com/questions/58386240/mysqld-exe-table-mysql-db-is-marked-as-crashed-and-should-be-repaired
 +  * 對 database : stats 進行修復 <cli>
 +mysqlcheck -r --databases stats --use-frm
 +</cli> 會在 log 內看到如下的訊息<cli>
 +:
 +:
 +220803 15:11:48 [ERROR] mysqld: Table './stats/stats_media' is marked as crashed and should be repaired
 +220803 15:13:10 [Note] Found 6676 of 0 rows when repairing './stats/stats_access'
 +220803 15:13:10 [Note] Found 813 of 0 rows when repairing './stats/stats_edits'
 +:
 +220803 15:13:11 [Note] Found 517 of 0 rows when repairing './stats/stats_searchwords'
 +220803 15:13:11 [Note] Found 1013 of 0 rows when repairing './stats/stats_session'
 +</cli>
  
 {{tag>mysql tips}} {{tag>mysql tips}}
  
  • tech/mysqltips.1597898475.txt.gz
  • 上一次變更: 2020/08/20 12:41
  • 由 jonathan_tsai