一點一點地慢慢吃掉吧

Remove partition without remove data

ALTER TABLE `TblName`  REMOVE PARTITIONING;

Optimize InnoDB table

ALTER TABLE table.name ENGINE='InnoDB';

Get tables with partition

SELECT table_schema, table_name FROM information_schema.`PARTITIONS` WHERE partition_name IS NOT NULL GROUP BY table_schema, table_name;

Mysqldump with query statement

mysqldump  -u$USER -p'$PWD' $db_name $tbl_name --where="added_time>='2018-05-01' and added_time <'2018-07-01'" --no-create-info > xxx.sql

Get lock

5.7

SELECT  TRX_L.`trx_mysql_thread_id` AS '進程編號'     , TRX_L.`trx_started` AS 'Start time'     , NOW() - TRX_L.`trx_started` AS '等待歷時(秒)'     , L_R.`lock_table` AS '表'  , TRX_L.`trx_id` AS '鎖定中的事務'  , L_R.`lock_mode` AS '鎖定模式'  , TRX_L.`trx_state` AS '鎖定狀態' FROM information_schema.innodb_lock_waits AS LW INNER JOIN (SELECT trx_id, trx_started, trx_mysql_thread_id, trx_state FROM information_schema.innodb_trx) AS TRX_L  ON LW.`blocking_trx_id` = TRX_L.`trx_id` INNER JOIN (SELECT lock_id, lock_table, lock_mode FROM information_schema.innodb_locks) AS L_R  ON LW.`requested_lock_id` = L_R.`lock_id`;

 

8.0

SELECT

    TRX_L.trx_mysql_thread_id AS '進程編號' ,

    TRX_L.trx_started AS 'Start time' ,

    NOW() - TRX_L.trx_started AS '等待歷時(秒)' ,

    L_R.lock_data AS '資料' ,

    TRX_L.trx_id AS '鎖定中的事務' ,

    L_R.lock_mode AS '鎖定模式' ,

    TRX_L.trx_state AS '鎖定狀態'

FROM

    performance_schema.data_lock_waits dlw

INNER JOIN (

    SELECT

        trx_id,

        trx_started,

        trx_mysql_thread_id,

        trx_state

    FROM

        information_schema.innodb_trx) AS TRX_L ON

    dlw.blocking_engine_transaction_id = TRX_L.trx_id

INNER JOIN (

    SELECT

        engine_lock_id,

        lock_data,

        lock_mode

    FROM

        performance_schema.data_locks) AS L_R ON

    dlw.requesting_engine_lock_id = L_R.engine_lock_id;