創建賬號和授權

-- 以 xx_user 為例
CREATE USER `xx_user`@`%` IDENTIFIED WITH caching_sha2_password BY 'your_password';-- 全局表授權 select, insert, update, delete
grant select, insert, update, delete on xx_table to xx_user;-- 業務范圍內的表授 ALL PRIVILEGES
SELECT CONCAT('GRANT ALL PRIVILEGES ON ',TABLE_SCHEMA,'.',TABLE_NAME,' TO `xx_user`@`%`;')
FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` = 'xxx_db' AND `TABLE_NAME` LIKE 'test_%' ;
FLUSH PRIVILEGES;-- 視圖授權 SELECT, SHOW
GRANT SELECT, SHOW VIEW ON `xxx_view` TO 'xx_user'@'%';

查詢沒有主鍵的表

SELECT t.table_name, t.table_type,n.table_schema,n.table_name,n.constraint_name FROM information_schema.tables t
LEFT JOIN information_schema.key_column_usage n
ON t.table_schema=n.table_schema AND t.table_name= n.table_name AND n.constraint_name = 'PRIMARY'
WHERE t.table_schema = 'xxx_db'
AND t.table_type = 'BASE TABLE'
AND n.table_name is NULL;

統計每個庫大小

SELECT TABLE_SCHEMA, SUM(DATA_LENGTH)/1024/1024 AS Data_MB, SUM(INDEX_LENGTH)/1024/1024 AS INDEX_MB
FROM information_schema.tables AS T1
WHERE T1.TABLE_SCHEMA like 'xxx_db%'
GROUP BY table_schema;

前十張大表

按行排序（TABLE_ROWS）
按表空間排序（TABLE_ROWS）

-- 按行
SELECT TABLE_SCHEMA AS database_name,TABLE_NAME AS table_name,TABLE_ROWS AS table_rows, ENGINE AS table_engine
,ROUND((DATA_LENGTH) /1024.0/1024,2) AS Data_MB ,ROUND((INDEX_LENGTH) /1024.0/1024,2) AS Index_MB
,ROUND((DATA_LENGTH + INDEX_LENGTH)/1024.0/1024,2) AS Total_MB,ROUND((DATA_FREE)/1024.0/1024,2) AS Free_MB
FROM information_schema.tables AS T1
WHERE T1.TABLE_SCHEMA like 'xxx_db%'
ORDER BY TABLE_ROWS
DESC LIMIT 10;-- 按表空間
SELECT TABLE_SCHEMA AS database_name,TABLE_NAME AS table_name,TABLE_ROWS AS table_rows, ENGINE AS table_engine
,ROUND((DATA_LENGTH) /1024.0/1024,2) AS Data_MB ,ROUND((INDEX_LENGTH) /1024.0/1024,2) AS Index_MB
,ROUND((DATA_LENGTH + INDEX_LENGTH)/1024.0/1024,2) AS Total_MB,ROUND((DATA_FREE)/1024.0/1024,2) AS Free_MB
FROM information_schema.tables AS T1
WHERE T1.TABLE_SCHEMA like 'xxx_db%'
ORDER BY Total_MB
DESC LIMIT 10;

清理日志表

-- 清中間件的日志表
TRUNCATE TABLE xxljob.xxl_job_log;
TRUNCATE TABLE nacos.his_config_info;-- 清業務庫的日志表
TRUNCATE TABLE xx_db.xx_table;

Prepared statement needs to be re-prepared

問題：當時參數 table_open_cache =16384, 但 table_definition_cache 還是默認值 2000，table_definition_cache 也需要同時調大，這里我設置與 table_open_cache 一樣大。

MySQL提示：1615: Prepared statement needs to be re-prepared

xtrabackup 問題

• 問題 LOCK INSTANCE FOR BACKUP
  xtrabackup 運行過程中會鎖mysql實例，如果特殊情況下中斷或故障時，就需要手動解鎖

--  sql 執行解鎖
UNLOCK INSTANCE;

• [Xtrabackup] could not find redo log file with LSN 813931920896 [ERROR] [MY-011825] [Xtrabackup] read_logfile() failed. [ERROR] [MY-011825] [Xtrabackup] log copying failed.

--  sql 執行刷盤
FLUSH LOGS;

• [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue.

https://www.modb.pro/db/631294  升級到 8.0.33 或 8.2.0(xtrabackup 同時升到 8.2.0)

• The input device is not a TTY

在 crontab 定時執行，腳本中 docker run -it … 要去掉 -it , 不然就會出現 The input device is not a TTY

鎖問題處理

mysql 鎖實戰分析

官網文檔

快速處理

# 查詢數據鎖
SELECT * FROM performance_schema.data_locks;# 事務ID查詢進程ID
SELECT trx.trx_id, trx.trx_mysql_thread_id AS PID, trx.trx_state, trx.trx_query
FROM information_schema.innodb_trx trx;# 釋放鎖，下面語句結果復制出來執行，把對應的 pid 給 kill 掉，釋放鎖
SELECT concat('KILL ', trx.trx_mysql_thread_id,';') as cmd FROM performance_schema.data_locks lck
INNER JOIN information_schema.innodb_trx trx ON trx.trx_id= lck.ENGINE_TRANSACTION_ID;

查詢事務等待和阻塞情況

# 查詢事務等待和阻塞情況
SELECTr.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;# 直接用 sys.innodb_lock_waits 查詢
SELECTwaiting_trx_id,waiting_pid,waiting_query,blocking_trx_id,blocking_pid,blocking_query
FROM sys.innodb_lock_waits;

eg.

waiting trx id	waiting thread	waiting query	blocking trx id	blocking thread	blocking query
A4	6	SELECT b FROM t FOR UPDATE	A3	5	SELECT SLEEP(100)
A5	7	SELECT c FROM t FOR UPDATE	A3	5	SELECT SLEEP(100)
A5	7	SELECT c FROM t FOR UPDATE	A4	6	SELECT b FROM t FOR UPDATE

在識別阻塞事務時，如果發出查詢的會話已閑置，則阻塞查詢會報告一個空值。在這種情況下，使用以下步驟確定阻塞查詢：

確定阻塞事務的進程列表 ID。在 sys.innodb_lock_waits 表中，阻塞事務的進程表 ID 就是 blocking_pid 值。使用 blocking_pid，查詢 MySQL 性能模式線程表，以確定阻塞事務的 THREAD_ID。例如，如果 blocking_pid 為 6，請執行以下查詢：

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;

使用 THREAD_ID 查詢性能模式 events_statements_current 表，以確定線程執行的最后一次查詢。例如，如果 THREAD_ID 是 28，請執行以下查詢：

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = 28;

如果線程執行的最后一次查詢信息不足以確定鎖被鎖定的原因，可以查詢性能模式events_statements_histor表，查看線程執行的最后 10 條語句。

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID = 28 ORDER BY EVENT_ID;

innodb_trx

查詢出事務 id 和 pid( processlist 中的 id)

SELECT trx.trx_id, trx.trx_mysql_thread_id AS PID, trx.trx_state, trx.trx_query FROM information_schema.innodb_trx trx;

trx_id	pid	trx_state	trix_query
292681221	590590	RUNNING
292677969	587984	RUNNING

processlist

根據 pid 查詢進程詳情

SELECT * FROM `performance_schema`.PROCESSLIST WHERE id in (587984,590590);

id	user	host	db	command	time	state	info	execute_engine
587984	xxx	172.8.8.1:50372	xxx	Sleep	6468			PRIMARY
590590	xxx	172.8.8.1:1885	xxx	Sleep	3095			PRIMARY

data_locks

官網文檔

MySQL的data_locks表是InnoDB存儲引擎用于記錄當前事務持有和請求的數據鎖信息的。這些數據鎖用于控制并發訪問，確保數據的一致性和完整性。在MySQL中，當一個事務訪問某個數據時，它可以對該數據加鎖，這樣其他事務就無法修改或讀取該數據，直到鎖被釋放。

InnoDB存儲引擎在做SELECT、INSERT、DELETE、UPDATE操作的時候，不會為表加上S鎖或者X鎖的，但是會使用到意向鎖這種表級別鎖。MyISAM引擎是不支持意向鎖的。

意向鎖又分為意向共享鎖（intention shared lock,IS）：事務有意向對表中的某些行加共享鎖（S鎖）；意向排他鎖（intention exclusive lock,IX）：事務有意向對表中的某些行加排他鎖（X鎖）。事務在給一個數據行加共享鎖前必須取得該表的IS鎖；事務在給一個數據行加排他鎖前必須取得該表的IX鎖。意向鎖的引入主要是為了在進行行級鎖或頁級鎖時，提供一種機制來表示事務可能會對表中的某些行或頁面進行鎖定操作的意向，從而提高并發控制的效率。

如果沒有意向寫鎖，mysql在加行鎖之前，需要循環掃描表，判斷表是否有行鎖。

有了意向寫鎖之后，mysql在加行寫鎖時，只要判斷表上沒有意向寫鎖，可以直接加行寫鎖，無需掃描。

查看數據鎖的情況，查詢出來的 THREAD_ID 后面都有關聯在使用

SELECT * FROM `performance_schema`.`data_locks` WHERE engine_transaction_id in (292681221,292677969);

ENGINE	ENGINE_LOCK_ID	ENGINE_TRANSACTION_ID	THREAD_ID	EVENT_ID	OBJECT_SCHEMA	OBJECT_NAME	PARTITION_NAME	SUBPARTITION_NAME	INDEX_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_MODE	LOCK_STATUS	LOCK_DATA
INNODB	139813616145968:41767:139811118757608	292677969	588348	96	xxx	task_apply				139811118757608	TABLE	IX	GRANTED
INNODB	139813616145968:32475:139811118757520	292677969	588348	90	xxx	task_todo				139811118757520	TABLE	IX	GRANTED
INNODB	139813616145968:31409:4:19:139811118754608	292677969	588348	90	xxx	task_todo			PRIMARY	139811118754608	RECORD	X,REC_NOT_GAP	GRANTED
INNODB	139813616145968:40701:5:6:139811118754952	292677969	588348	96	xxx	task_apply			PRIMARY	139811118754952	RECORD	X,REC_NOT_GAP	GRANTED
INNODB	139813616109608:32475:139811118485088	292681221	590954	85	xxx	task_todo				139811118485088	TABLE	IX	GRANTED

588348 獲取 task_todo，task_apply 的行寫鎖（X），自然也有意向排他鎖 IX，590954 只獲取了 task_todo 意向寫鎖，并沒有獲得行鎖，可能是修改的是同一記錄，但 588348 沒有釋放寫鎖。

data_lock_waits

官網文檔

select * FROM  performance_schema.data_lock_waits

metadata_locks

官網文檔

元數據鎖，簡稱MDL鎖，屬于表鎖范疇。MDL的作用是，保證讀寫的正確性。比如，如果一個查詢正在遍歷一個表中的數據，而執行期間另一個線程對這個表結構做變更，增加了一列，那么查詢線程拿到的結果跟表結構對不上，肯定是不行的。
因此，當對一個表做 增刪改查 操作的時候，加MDL讀鎖；當要對表做 結構變更 操作的時候，加 MDL寫鎖。

讀鎖之間不互斥，因此你可以有多個線程同時對一張表增刪改查。讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性，解決了DML和DDL操作之間的一致性問題。不需要顯式使用，在訪問一個表的時候會被自動加上。

	IS	IX	AUTO-INC	S	X
IS	√	√	√	√	X
IX	√	√	√	X	X
AUTO-INC	√	√	X	X	X
S	√	X	X	√	X
X	X	X	X	X	X

思考：我們在對表做Alter操作的時候，是否能立即執行？

答案是不一定，如果此時還有事務在進行增刪改查操作，Alter操作會阻塞，必須等待所有事務執行完畢才能執行。

需要注意的是，我們在對大表做DDL的時候，有可能會造成數據庫崩潰。所以要求我們盡量在業務不繁忙的時候執行DDL，或者是使用第三方工具，如 pt-online-schema-change 等來安全的執行表的DDL操作。

SELECT * from performance_schema.metadata_locks ml WHERE ml.owner_thread_id in (588348,590954);

OBJECT_TYPE	OBJECT_SCHEMA	OBJECT_NAME	COLUMN_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_DURATION	LOCK_STATUS	SOURCE	OWNER_THREAD_ID	OWNER_EVENT_ID
TABLE	xxx	task_todo		139811196356656	SHARED_READ	TRANSACTION	GRANTED	sql_parse.cc:6251	588348	85
TABLE	xxx	task_todo		139811197083072	SHARED_WRITE	TRANSACTION	GRANTED	sql_parse.cc:6251	588348	90
TABLE	xxx	task_apply		139811195232128	SHARED_READ	TRANSACTION	GRANTED	sql_parse.cc:6251	588348	92
TABLE	xxx	task_apply		139811208160848	SHARED_WRITE	TRANSACTION	GRANTED	sql_parse.cc:6251	588348	96
TABLE	xxx	task_todo		139811162016464	SHARED_READ	TRANSACTION	GRANTED	sql_parse.cc:6251	590954	80
TABLE	xxx	task_todo		139811117870400	SHARED_WRITE	TRANSACTION	GRANTED	sql_parse.cc:6251	590954	85

events_statements_current

SELECT * FROM `performance_schema`.`events_statements_current` WHERE `THREAD_ID` IN (588348,590954)

THREAD_ID	EVENT_ID	END_EVENT_ID	EVENT_NAME	SOURCE	TIMER_START	TIMER_END	TIMER_WAIT	LOCK_TIME	SQL_TEXT	DIGEST	DIGEST_TEXT	CURRENT_SCHEMA	OBJECT_TYPE	OBJECT_SCHEMA	OBJECT_NAME	OBJECT_INSTANCE_BEGIN	MYSQL_ERRNO	RETURNED_SQLSTATE	MESSAGE_TEXT	ERRORS	WARNINGS	ROWS_AFFECTED	ROWS_SENT	ROWS_EXAMINED	CREATED_TMP_DISK_TABLES	CREATED_TMP_TABLES	SELECT_FULL_JOIN	SELECT_FULL_RANGE_JOIN	SELECT_RANGE	SELECT_RANGE_CHECK	SELECT_SCAN	SORT_MERGE_PASSES	SORT_RANGE	SORT_ROWS	SORT_SCAN	NO_INDEX_USED	NO_GOOD_INDEX_USED	NESTING_EVENT_ID	NESTING_EVENT_TYPE	NESTING_EVENT_LEVEL	STATEMENT_ID	CPU_TIME	MAX_CONTROLLED_MEMORY	MAX_TOTAL_MEMORY	EXECUTION_ENGINE
588348	95	95	statement/com/Execute	init_net_server_extension.cc:102	128857880080938000	128857881191745000	1110807000	4000000				xxx					0	00000	Rows matched: 1 Changed: 1 Warnings: 0	0	0	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	85	TRANSACTION	0	6829505	0	908448	1066609	PRIMARY
590954	84	84	statement/com/Execute	init_net_server_extension.cc:102	132231422072543000	132281427865065000	50005792522000	50005436000000				xxx					1205	HY000	Lock wait timeout exceeded; try restarting transaction	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	80	TRANSACTION	0	6927386	0	329376	350460	PRIMARY

其他

select * from performance_schema.events_transactions_current where thread_id in (588348,590954);
select * from performance_schema.threads where thread_id in (588348,590954);SELECT * FROM performance_schema.events_statements_history WHERE THREAD_ID IN  (588348,590954);
select * from performance_schema.events_transactions_history where thread_id in (588348,590954) ORDER BY event_id;

手動備份單表

mysql集群不支持無主鍵表, 手動備份單表時務必按下述操作, 否則會導致集群無法啟動

# 錯誤的方式CREATE TABLE xx_table_bakxxx AS SELECT * FROM xx_table;# 這個方法不會備份主鍵索引注釋# 正確的方式CREATE TABLE xx_table_bakxxx LIKE xx_table;INSERT INTO xx_table_bakxxx SELECT * FROM xx_table;

清理耗時過長的sql

壓測環境的mysql數據已經做了一個清場的存儲過程, 如果環境變卡, 監控界面顯示cpu過高, 請再壓測前調用以便殺掉卡死的sql語句

# 執行SQL> CALL xxx.kill_overtime_procs(120);# 驗證SQL> SELECT * FROM information_schema.processlistWHERE command in ('Query', 'Execute')AND time > 0AND user <> 'system user';