SELECT User, Host FROM mysql.user;

-- 授予用戶對test數(shù)據(jù)庫的SELECT權(quán)限
GRANT SELECT ON test.* TO 'user'@'localhost';

-- 授予用戶對所有數(shù)據(jù)庫的SELECT權(quán)限
GRANT SELECT ON *.* TO 'user'@'localhost';

-- 授予用戶對特定表的權(quán)限
GRANT SELECT, INSERT ON test.users TO 'user'@'localhost';

-- 授予用戶對特定列的權(quán)限
GRANT SELECT (name, email) ON test.users TO 'user'@'localhost';

-- 查看用戶權(quán)限
SHOW GRANTS FOR 'user'@'localhost';

-- 查看用戶權(quán)限的詳細信息
SELECT * FROM mysql.user WHERE User = 'user' AND Host = 'localhost';

-- 修改用戶密碼
ALTER USER 'user'@'localhost' IDENTIFIED BY 'new_password';

-- 重置密碼
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('new_password');

-- 刷新權(quán)限
FLUSH PRIVILEGES;

-- 刪除用戶權(quán)限
REVOKE SELECT ON test.* FROM 'user'@'localhost';

-- 刪除用戶
DROP USER 'user'@'localhost';

-- 停止MySQL服務(wù)
sudo systemctl stop mysql

-- 以跳過權(quán)限檢查的方式啟動
sudo mysqld_safe --skip-grant-tables &

-- 登錄MySQL
mysql -u root

-- 重置密碼
USE mysql;
UPDATE user SET authentication_string = PASSWORD('new_password') WHERE User = 'root';

-- 重啟MySQL服務(wù)
sudo systemctl restart mysql

SELECT SCHEMA_NAME AS `Database`
FROM information_schema.SCHEMATA;

SELECT TABLE_NAME AS `Table`
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';

SELECT 
    COLUMN_NAME AS `Column`,
    DATA_TYPE AS `Type`,
    IS_NULLABLE AS `Nullable`,
    COLUMN_KEY AS `Key`,
    COLUMN_DEFAULT AS `Default`,
    EXTRA AS `Extra`
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

SELECT 
    INDEX_NAME AS `Index`,
    COLUMN_NAME AS `Column`,
    SEQ_IN_INDEX AS `Seq`,
    NON_UNIQUE AS `NonUnique`
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

SELECT 
    CONSTRAINT_NAME AS `Constraint`,
    REFERENCED_TABLE_NAME AS `Referenced Table`,
    REFERENCED_COLUMN_NAME AS `Referenced Column`
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name'
AND REFERENCED_TABLE_NAME IS NOT NULL;

SELECT 
    TABLE_NAME AS `View`,
    VIEW_DEFINITION AS `Definition`
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'your_database_name';

-- 比較兩個數(shù)據(jù)庫的表結(jié)構(gòu)差異
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE AS source_type,
    (SELECT DATA_TYPE 
     FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = 'target_db' 
     AND TABLE_NAME = source.TABLE_NAME 
     AND COLUMN_NAME = source.COLUMN_NAME) AS target_type
FROM information_schema.COLUMNS source
WHERE TABLE_SCHEMA = 'source_db'
AND (SELECT DATA_TYPE 
     FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = 'target_db' 
     AND TABLE_NAME = source.TABLE_NAME 
     AND COLUMN_NAME = source.COLUMN_NAME) IS NULL
OR (SELECT DATA_TYPE 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = 'target_db' 
    AND TABLE_NAME = source.TABLE_NAME 
    AND COLUMN_NAME = source.COLUMN_NAME) <> source.DATA_TYPE;

SHOW VARIABLES LIKE 'performance_schema';
-- 默認值為 ON（MySQL 5.7+）

-- 開啟所有事件的監(jiān)控（謹慎使用，可能影響性能）
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';

-- 按需開啟特定監(jiān)控（推薦）
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_statements%'; -- 開啟SQL語句監(jiān)控

-- 監(jiān)控所有用戶的SQL語句
UPDATE performance_schema.setup_actors SET ENABLED = 'YES', HISTORY = 'YES' 
WHERE USER = '%';

SELECT 
    DIGEST_TEXT AS 'SQL語句',
    COUNT_STAR AS '執(zhí)行次數(shù)',
    SUM_TIMER_WAIT / 1e9 AS '總耗時(秒)',
    AVG_TIMER_WAIT / 1e9 AS '平均耗時(秒)'
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

SELECT 
    EVENT_ID,
    SQL_TEXT AS 'SQL語句',
    TIMER_WAIT / 1e9 AS '耗時(秒)',
    LOCK_TIME / 1e9 AS '鎖等待時間(秒)',
    ROWS_EXAMINED AS '掃描行數(shù)',
    ROWS_SENT AS '返回行數(shù)'
FROM performance_schema.events_statements_history
ORDER BY TIMER_WAIT DESC
LIMIT 10;

SELECT 
    EVENT_NAME AS '事件類型',
    COUNT_STAR AS '次數(shù)',
    SUM_TIMER_WAIT / 1e9 AS '總等待時間(秒)',
    AVG_TIMER_WAIT / 1e9 AS '平均等待時間(秒)'
FROM performance_schema.file_summary_by_event_name
WHERE EVENT_NAME LIKE '%read%'
ORDER BY SUM_TIMER_WAIT DESC;

SELECT 
    THREAD_ID,
    EVENT_ID,
    SQL_TEXT,
    TIMER_START,
    TIMER_WAIT / 1e9 AS '已運行時間(秒)'
FROM performance_schema.events_transactions_current
WHERE STATE = 'ACTIVE'
ORDER BY TIMER_WAIT DESC;

-- 查看執(zhí)行時間最長的10個查詢
SELECT * FROM sys.statement_analysis 
ORDER BY total_latency DESC 
LIMIT 10;

-- 查看操作最頻繁的表
SELECT * FROM sys.schema_table_statistics 
ORDER BY rows_fetched + rows_inserted + rows_updated + rows_deleted DESC 
LIMIT 10;

-- 查看當前鎖等待鏈
SELECT * FROM sys.innodb_lock_waits;

-- 按主機統(tǒng)計文件IO延遲
SELECT * FROM sys.host_summary_by_file_io 
ORDER BY io_latency DESC;

SELECT 
    DIGEST_TEXT AS 'SQL語句',
    COUNT_STAR AS '執(zhí)行次數(shù)',
    SUM_CREATED_TMP_DISK_TABLES AS '磁盤臨時表次數(shù)',
    SUM_CREATED_TMP_TABLES AS '臨時表總數(shù)'
FROM sys.statements_with_temp_tables
ORDER BY SUM_CREATED_TMP_TABLES DESC
LIMIT 10;

SELECT 
    ID, 
    USER, 
    HOST, 
    DB, 
    COMMAND, 
    TIME, 
    STATE, 
    INFO
FROM sys.processlist
ORDER BY TIME DESC;

SELECT 
    EVENT_NAME AS '內(nèi)存事件',
    COUNT_STAR AS '次數(shù)',
    SUM_NUMBER_OF_BYTES_USED / 1024 / 1024 AS '總內(nèi)存(MB)'
FROM sys.memory_global_by_current_bytes
ORDER BY SUM_NUMBER_OF_BYTES_USED DESC
LIMIT 10;

SELECT 
    TABLE_SCHEMA AS '數(shù)據(jù)庫',
    TABLE_NAME AS '表名',
    INDEX_NAME AS '索引名',
    SEQ_IN_INDEX AS '索引順序',
    COLUMN_NAME AS '列名',
    CARDINALITY AS '基數(shù)',
    NON_UNIQUE AS '是否唯一'
FROM sys.schema_index_statistics
ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME;