MySQL 运维命令 100 条

1SELECT VERSION();

1SELECT
2    @@hostname          AS hostname,
3    @@port              AS port,
4    @@server_uuid       AS server_uuid,
5    @@server_id         AS server_id,
6    @@version           AS version;

1SELECT
2    NOW() AS current_time,
3    NOW() - INTERVAL VARIABLE_VALUE SECOND AS startup_time,
4    VARIABLE_VALUE AS uptime_seconds
5FROM performance_schema.global_status
6WHERE VARIABLE_NAME = 'Uptime';

1SELECT NOW(), CURRENT_TIMESTAMP, UTC_TIMESTAMP();

1SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone;

1SELECT @@global.sql_mode, @@session.sql_mode;

1SELECT
2    @@character_set_server,
3    @@character_set_database,
4    @@character_set_connection,
5    @@character_set_client,
6    @@character_set_results;

1SELECT
2    @@collation_server,
3    @@collation_database,
4    @@collation_connection;

1SELECT @@datadir;

1SELECT
2    @@log_error,
3    @@pid_file,
4    @@socket,
5    @@tmpdir,
6    @@secure_file_priv;

1SHOW DATABASES;

1SELECT DATABASE();

1SHOW CREATE DATABASE db_name;

1SELECT
2    table_schema,
3    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
4FROM information_schema.tables
5GROUP BY table_schema
6ORDER BY size_gb DESC;

1SHOW FULL TABLES FROM db_name;

1DESC db_name.table_name;

1SHOW COLUMNS FROM db_name.table_name;

1SHOW CREATE TABLE db_name.table_name\G

1SHOW TABLE STATUS FROM db_name LIKE 'table_name'\G

1SELECT
2    table_schema,
3    table_name,
4    table_rows,
5    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
6    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
7    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
8FROM information_schema.tables
9WHERE table_schema = 'db_name'
10ORDER BY data_length + index_length DESC;

1SELECT
2    table_schema,
3    table_name,
4    engine
5FROM information_schema.tables
6WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
7  AND engine <> 'InnoDB';

1SHOW PROCESSLIST;

1SHOW FULL PROCESSLIST;

1SELECT
2    id,
3    user,
4    host,
5    db,
6    command,
7    time,
8    state,
9    info
10FROM information_schema.processlist
11ORDER BY time DESC;

1SHOW GLOBAL STATUS LIKE 'Threads_connected';

1SHOW GLOBAL STATUS LIKE 'Threads_running';

1SHOW VARIABLES LIKE 'max_connections';

1SHOW GLOBAL STATUS LIKE 'Max_used_connections';

1SELECT
2    VARIABLE_VALUE AS current_connections,
3    @@max_connections AS max_connections,
4    ROUND(VARIABLE_VALUE / @@max_connections * 100, 2) AS usage_percent
5FROM performance_schema.global_status
6WHERE VARIABLE_NAME = 'Threads_connected';

1SELECT
2    user,
3    COUNT(*) AS connection_count
4FROM information_schema.processlist
5GROUP BY user
6ORDER BY connection_count DESC;

1SELECT
2    SUBSTRING_INDEX(host, ':', 1) AS client_ip,
3    COUNT(*) AS connection_count
4FROM information_schema.processlist
5GROUP BY SUBSTRING_INDEX(host, ':', 1)
6ORDER BY connection_count DESC;

1SELECT
2    id,
3    user,
4    host,
5    db,
6    command,
7    time,
8    state,
9    info
10FROM information_schema.processlist
11WHERE command <> 'Sleep'
12  AND time > 60
13ORDER BY time DESC;

1SELECT
2    id,
3    user,
4    host,
5    db,
6    time
7FROM information_schema.processlist
8WHERE command = 'Sleep'
9  AND time > 600
10ORDER BY time DESC;

1KILL CONNECTION 12345;

1KILL QUERY 12345;

1SELECT CONCAT('KILL CONNECTION ', id, ';')
2FROM information_schema.processlist
3WHERE command = 'Sleep'
4  AND time > 3600
5  AND user NOT IN ('system user', 'event_scheduler');

1SELECT *
2FROM information_schema.innodb_trx\G

1SELECT
2    trx_id,
3    trx_mysql_thread_id,
4    trx_state,
5    trx_started,
6    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_seconds,
7    trx_rows_locked,
8    trx_rows_modified,
9    trx_query
10FROM information_schema.innodb_trx
11ORDER BY trx_started;

1SELECT
2    trx_id,
3    trx_mysql_thread_id,
4    trx_started,
5    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_seconds,
6    trx_rows_locked,
7    trx_rows_modified,
8    trx_query
9FROM information_schema.innodb_trx
10WHERE trx_started < NOW() - INTERVAL 60 SECOND
11ORDER BY trx_started;

1SELECT *
2FROM performance_schema.data_lock_waits;

1SELECT *
2FROM performance_schema.data_locks;

1SELECT *
2FROM sys.innodb_lock_waits\G

1SELECT *
2FROM performance_schema.metadata_locks
3WHERE LOCK_STATUS = 'PENDING';

1SELECT
2    ml.object_schema,
3    ml.object_name,
4    ml.lock_type,
5    ml.lock_duration,
6    ml.lock_status,
7    t.processlist_id,
8    t.processlist_user,
9    t.processlist_host,
10    t.processlist_time,
11    t.processlist_info
12FROM performance_schema.metadata_locks ml
13JOIN performance_schema.threads t
14  ON ml.owner_thread_id = t.thread_id
15WHERE ml.lock_status = 'PENDING';

1SELECT
2    trx.trx_id,
3    trx.trx_started,
4    trx.trx_mysql_thread_id,
5    p.user,
6    p.host,
7    p.db,
8    p.command,
9    p.time,
10    p.state,
11    p.info
12FROM information_schema.innodb_trx trx
13LEFT JOIN information_schema.processlist p
14  ON trx.trx_mysql_thread_id = p.id
15ORDER BY trx.trx_started;

1SHOW ENGINE INNODB STATUS\G

1SHOW ENGINE INNODB STATUS\G

1LATEST DETECTED DEADLOCK

1SET GLOBAL innodb_print_all_deadlocks = ON;

1SELECT
2    @@global.transaction_isolation,
3    @@session.transaction_isolation;

1SELECT @@global.autocommit, @@session.autocommit;

1COMMIT;

1ROLLBACK;

1EXPLAIN
2SELECT *
3FROM db_name.table_name
4WHERE id = 100;

1EXPLAIN FORMAT=JSON
2SELECT *
3FROM db_name.table_name
4WHERE id = 100;

1EXPLAIN ANALYZE
2SELECT *
3FROM db_name.table_name
4WHERE id = 100;

1SET optimizer_trace = 'enabled=on';
2
3SELECT *
4FROM db_name.table_name
5WHERE id = 100;
6
7SELECT trace
8FROM information_schema.optimizer_trace\G

1SELECT
2    processlist_id,
3    processlist_user,
4    processlist_host,
5    processlist_db,
6    processlist_time,
7    processlist_state,
8    processlist_info
9FROM performance_schema.threads
10WHERE type = 'FOREGROUND'
11  AND processlist_command <> 'Sleep'
12ORDER BY processlist_time DESC;

1SELECT
2    digest_text,
3    count_star,
4    ROUND(sum_timer_wait / 1000000000000, 2) AS total_seconds,
5    ROUND(avg_timer_wait / 1000000000000, 6) AS avg_seconds,
6    sum_rows_examined,
7    sum_rows_sent
8FROM performance_schema.events_statements_summary_by_digest
9WHERE digest_text IS NOT NULL
10ORDER BY sum_timer_wait DESC
11LIMIT 20;

1SELECT
2    digest_text,
3    count_star,
4    ROUND(avg_timer_wait / 1000000000000, 6) AS avg_seconds,
5    ROUND(max_timer_wait / 1000000000000, 6) AS max_seconds,
6    sum_rows_examined,
7    sum_rows_sent
8FROM performance_schema.events_statements_summary_by_digest
9WHERE count_star >= 10
10ORDER BY avg_timer_wait DESC
11LIMIT 20;

1SELECT
2    digest_text,
3    count_star,
4    sum_rows_examined,
5    sum_rows_sent,
6    ROUND(sum_rows_examined / NULLIF(sum_rows_sent, 0), 2) AS examine_send_ratio
7FROM performance_schema.events_statements_summary_by_digest
8ORDER BY sum_rows_examined DESC
9LIMIT 20;

1SELECT
2    digest_text,
3    count_star,
4    sum_no_index_used,
5    sum_no_good_index_used,
6    sum_rows_examined
7FROM performance_schema.events_statements_summary_by_digest
8WHERE sum_no_index_used > 0
9   OR sum_no_good_index_used > 0
10ORDER BY sum_no_index_used DESC
11LIMIT 20;

1SELECT
2    digest_text,
3    count_star,
4    sum_created_tmp_tables,
5    sum_created_tmp_disk_tables
6FROM performance_schema.events_statements_summary_by_digest
7WHERE sum_created_tmp_tables > 0
8ORDER BY sum_created_tmp_disk_tables DESC
9LIMIT 20;

1SELECT
2    digest_text,
3    count_star,
4    sum_sort_rows,
5    sum_sort_scan,
6    sum_sort_range,
7    sum_sort_merge_passes
8FROM performance_schema.events_statements_summary_by_digest
9WHERE sum_sort_rows > 0
10ORDER BY sum_sort_rows DESC
11LIMIT 20;

1SELECT *
2FROM sys.schema_tables_with_full_table_scans
3ORDER BY rows_full_scanned DESC
4LIMIT 20;

1SELECT *
2FROM sys.statement_analysis
3ORDER BY total_latency DESC
4LIMIT 20;

1SELECT
2    event_id,
3    sql_text,
4    rows_examined,
5    rows_sent,
6    timer_wait
7FROM performance_schema.events_statements_history
8WHERE thread_id = PS_CURRENT_THREAD_ID()
9ORDER BY event_id DESC;

1TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

1SHOW INDEX FROM db_name.table_name;

1SELECT
2    index_name,
3    non_unique,
4    seq_in_index,
5    column_name,
6    cardinality,
7    nullable
8FROM information_schema.statistics
9WHERE table_schema = 'db_name'
10  AND table_name = 'table_name'
11ORDER BY index_name, seq_in_index;

1SELECT
2    t.table_schema,
3    t.table_name
4FROM information_schema.tables t
5LEFT JOIN information_schema.table_constraints c
6  ON t.table_schema = c.table_schema
7 AND t.table_name = c.table_name
8 AND c.constraint_type = 'PRIMARY KEY'
9WHERE t.table_type = 'BASE TABLE'
10  AND t.engine = 'InnoDB'
11  AND t.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
12  AND c.constraint_name IS NULL;

1SELECT *
2FROM sys.schema_redundant_indexes;

1SELECT *
2FROM sys.schema_unused_indexes;

1SELECT *
2FROM sys.schema_index_statistics
3WHERE table_schema = 'db_name'
4  AND table_name = 'table_name'
5ORDER BY rows_selected DESC;

1ANALYZE TABLE db_name.table_name;

1CHECK TABLE db_name.table_name;

1OPTIMIZE TABLE db_name.table_name;

1SELECT
2    table_schema,
3    table_name,
4    engine,
5    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
6    ROUND(data_free / 1024 / 1024, 2) AS data_free_mb
7FROM information_schema.tables
8WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
9  AND data_free > 0
10ORDER BY data_free DESC;

1SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

1SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

1SHOW GLOBAL STATUS
2WHERE Variable_name IN (
3    'Innodb_buffer_pool_read_requests',
4    'Innodb_buffer_pool_reads'
5);

1SELECT
2    ROUND(
3        (
4            1 -
5            reads.variable_value /
6            NULLIF(requests.variable_value, 0)
7        ) * 100,
8        4
9    ) AS buffer_pool_hit_percent
10FROM performance_schema.global_status reads
11JOIN performance_schema.global_status requests
12WHERE reads.variable_name = 'Innodb_buffer_pool_reads'
13  AND requests.variable_name = 'Innodb_buffer_pool_read_requests';

1SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

1SHOW GLOBAL STATUS
2WHERE Variable_name IN (
3    'Innodb_buffer_pool_pages_total',
4    'Innodb_buffer_pool_pages_free',
5    'Innodb_buffer_pool_pages_data',
6    'Innodb_buffer_pool_pages_dirty'
7);

1SHOW VARIABLES
2WHERE Variable_name IN (
3    'innodb_redo_log_capacity',
4    'innodb_log_buffer_size',
5    'innodb_flush_log_at_trx_commit',
6    'sync_binlog'
7);

1SHOW GLOBAL STATUS
2WHERE Variable_name IN (
3    'Innodb_data_reads',
4    'Innodb_data_writes',
5    'Innodb_data_read',
6    'Innodb_data_written',
7    'Innodb_os_log_written'
8);

1SHOW GLOBAL STATUS
2WHERE Variable_name IN (
3    'Innodb_rows_read',
4    'Innodb_rows_inserted',
5    'Innodb_rows_updated',
6    'Innodb_rows_deleted'
7);

1SHOW GLOBAL STATUS
2WHERE Variable_name IN (
3    'Created_tmp_tables',
4    'Created_tmp_disk_tables',
5    'Created_tmp_files'
6);

1SHOW VARIABLES
2WHERE Variable_name IN (
3    'slow_query_log',
4    'slow_query_log_file',
5    'long_query_time',
6    'log_queries_not_using_indexes',
7    'min_examined_row_limit'
8);

1SET GLOBAL slow_query_log = ON;

1SET GLOBAL long_query_time = 1;

1SHOW VARIABLES
2WHERE Variable_name IN (
3    'log_bin',
4    'log_bin_basename',
5    'binlog_format',
6    'binlog_row_image',
7    'binlog_expire_logs_seconds'
8);

1SHOW MASTER STATUS;

1SHOW BINARY LOG STATUS;

1SHOW BINARY LOGS;

1SHOW BINLOG EVENTS
2IN 'mysql-bin.000001'
3LIMIT 100;

1mysqlbinlog \
2  --base64-output=DECODE-ROWS \
3  -vv \
4  mysql-bin.000001

1mysqlbinlog \
2  --start-datetime='2026-07-16 10:00:00' \
3  --stop-datetime='2026-07-16 11:00:00' \
4  --base64-output=DECODE-ROWS \
5  -vv \
6  mysql-bin.000001

1SHOW SLAVE STATUS\G

1SHOW REPLICA STATUS\G

1Replica_IO_Running
2Replica_SQL_Running
3Seconds_Behind_Source
4Last_IO_Error
5Last_SQL_Error
6Retrieved_Gtid_Set
7Executed_Gtid_Set

1SELECT *
2FROM performance_schema.replication_connection_status\G

1SELECT *
2FROM performance_schema.replication_applier_status\G

1SELECT
2    channel_name,
3    worker_id,
4    thread_id,
5    service_state,
6    last_error_number,
7    last_error_message,
8    last_error_timestamp
9FROM performance_schema.replication_applier_status_by_worker;

1STOP REPLICA;

1START REPLICA;

1STOP REPLICA IO_THREAD;
2START REPLICA IO_THREAD;

1STOP REPLICA SQL_THREAD;
2START REPLICA SQL_THREAD;

1SELECT
2    @@global.gtid_executed,
3    @@global.gtid_purged,
4    @@global.enforce_gtid_consistency,
5    @@global.gtid_mode;

1mysqldump \
2  -h 127.0.0.1 \
3  -P 3306 \
4  -u backup_user \
5  -p \
6  --single-transaction \
7  --routines \
8  --events \
9  --triggers \
10  --set-gtid-purged=OFF \
11  --databases db_name \
12  > db_name_$(date +%F).sql

1mysql \
2  -h 127.0.0.1 \
3  -P 3306 \
4  -u root \
5  -p \
6  < db_name_2026-07-16.sql

1SELECT
2    user,
3    host,
4    plugin,
5    account_locked,
6    password_expired
7FROM mysql.user;

1SHOW GRANTS FOR 'app_user'@'%';

1CREATE USER 'app_user'@'10.%'
2IDENTIFIED BY 'StrongPassword';

1GRANT SELECT, INSERT, UPDATE, DELETE
2ON db_name.*
3TO 'app_user'@'10.%';

1REVOKE DELETE
2ON db_name.*
3FROM 'app_user'@'10.%';

1ALTER USER 'app_user'@'10.%'
2IDENTIFIED BY 'NewStrongPassword';

1ALTER USER 'app_user'@'10.%' ACCOUNT LOCK;

1ALTER USER 'app_user'@'10.%' ACCOUNT UNLOCK;

1DROP USER 'app_user'@'10.%';