PolarDB MySQL 版运维命令 100 条

前言

PolarDB MySQL 版兼容 MySQL 协议和常用 SQL，但底层采用计算与存储分离架构。一个集群通常包含主节点、只读节点、集群地址和主地址，连接经过代理后还可能启用读写分离、会话一致性和事务拆分。

因此，排查 PolarDB 问题时既要掌握 MySQL 的会话、事务锁、执行计划和 Performance Schema，也要知道哪些能力属于云平台管理范围。节点扩缩容、主备切换、集群重启、参数模板、自动备份、时间点恢复、读写分离和 SQL 洞察等操作，通常需要在阿里云控制台、DAS 或 API 中完成，不能用普通 MySQL 命令替代。

下面整理了 PolarDB MySQL 版 DBA 常用的 100 条命令，覆盖连接、实例信息、对象、参数、会话、事务锁、SQL 性能、空间、索引、用户权限、导入导出和云平台检查等场景。

本文主要面向兼容 MySQL 8.0 的 PolarDB MySQL 集群。不同产品版本、企业版与标准版、集群规格和兼容内核之间可能存在差异。文中的地址、用户、数据库和对象名均为示例。

一、连接与实例信息

1. 使用 MySQL 客户端连接 PolarDB

Bash

1mysql -h pc-example.rwlb.rds.aliyuncs.com \
2  -P 3306 -u appuser -p

应根据业务需求选择集群地址、主地址或自定义地址。

2. 测试连接

SQL

1SELECT 1;

3. 查看数据库版本

SQL

1SELECT VERSION();

4. 查看基础实例信息

SQL

1SELECT
2    @@hostname AS hostname,
3    @@port AS port,
4    @@server_uuid AS server_uuid,
5    @@version AS version,
6    CONNECTION_ID() AS connection_id;

经过集群地址连接时，多次建立新连接可能被路由到不同节点。

5. 查看当前数据库和用户

SQL

1SELECT
2    DATABASE(),
3    USER(),
4    CURRENT_USER();

6. 查看当前节点是否只读

SQL

1SELECT
2    @@global.read_only AS read_only,
3    @@global.super_read_only AS super_read_only;

只读节点通常不能执行写操作。

7. 查看当前时间和时区

SQL

1SELECT
2    NOW() AS local_time,
3    UTC_TIMESTAMP() AS utc_time,
4    @@session.time_zone,
5    @@system_time_zone;

8. 查看数据库启动时间

SQL

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

9. 查看字符集

SQL

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

10. 查看 SQL 模式

SQL

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

二、数据库与表对象

11. 查看所有数据库

SQL

1SHOW DATABASES;

12. 创建数据库

SQL

1CREATE DATABASE appdb
2DEFAULT CHARACTER SET utf8mb4
3DEFAULT COLLATE utf8mb4_0900_ai_ci;

排序规则应根据当前兼容版本确认。

13. 查看数据库创建语句

SQL

1SHOW CREATE DATABASE appdb;

14. 查看当前数据库

SQL

1SELECT DATABASE();

15. 查看数据库中的表

SQL

1SHOW FULL TABLES FROM appdb;

16. 查看表结构

SQL

1DESC appdb.orders;

17. 查看完整建表语句

SQL

1SHOW CREATE TABLE appdb.orders\G

18. 查看表状态

SQL

1SHOW TABLE STATUS FROM appdb LIKE 'orders'\G

19. 查看字段定义

SQL

1SELECT
2    column_name,
3    column_type,
4    is_nullable,
5    column_key,
6    column_default,
7    extra
8FROM information_schema.columns
9WHERE table_schema = 'appdb'
10  AND table_name = 'orders'
11ORDER BY ordinal_position;

20. 查看表约束

SQL

1SELECT
2    constraint_name,
3    constraint_type
4FROM information_schema.table_constraints
5WHERE table_schema = 'appdb'
6  AND table_name = 'orders'
7ORDER BY constraint_type, constraint_name;

三、参数与状态

21. 查看指定参数

SQL

1SHOW VARIABLES LIKE 'max_connections';

22. 查看全部参数

SQL

1SHOW VARIABLES;

23. 查看全局和会话参数

SQL

1SELECT
2    @@global.wait_timeout AS global_wait_timeout,
3    @@session.wait_timeout AS session_wait_timeout,
4    @@global.max_connections AS max_connections;

24. 查看重要 InnoDB 参数

SQL

1SELECT
2    @@innodb_buffer_pool_size,
3    @@innodb_flush_log_at_trx_commit,
4    @@innodb_lock_wait_timeout,
5    @@transaction_isolation;

25. 查看参数来源

SQL

1SELECT
2    variable_name,
3    variable_value,
4    variable_source
5FROM performance_schema.variables_info
6WHERE variable_name IN (
7    'max_connections',
8    'wait_timeout',
9    'long_query_time'
10);

26. 修改当前会话参数

SQL

1SET SESSION wait_timeout = 1800;

27. 设置当前会话 SQL 超时

SQL

1SET SESSION max_execution_time = 30000;

单位为毫秒。

28. 查看状态变量

SQL

1SHOW GLOBAL STATUS;

29. 查看连接状态

SQL

1SHOW GLOBAL STATUS LIKE 'Threads%';

30. 查看临时表状态

SQL

1SHOW GLOBAL STATUS LIKE 'Created_tmp%';

磁盘临时表持续增加，通常需要检查排序、分组、连接和内存参数。

四、连接与会话

31. 查看当前连接数

SQL

1SELECT COUNT(*) AS connections
2FROM information_schema.processlist;

32. 查看完整会话

SQL

1SHOW FULL PROCESSLIST;

33. 查询会话明细

SQL

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;

34. 按用户统计连接

SQL

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

35. 按客户端统计连接

SQL

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

36. 查看长时间运行的 SQL

SQL

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

37. 查看空闲连接

SQL

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

38. 取消正在执行的 SQL

SQL

1KILL QUERY 12345;

39. 终止数据库连接

SQL

1KILL CONNECTION 12345;

终止连接会回滚未提交事务，执行前应核对连接 ID、用户和来源地址。

40. 查看连接使用率

SQL

1SELECT
2    current_connections,
3    max_connections,
4    ROUND(current_connections * 100 / max_connections, 2) AS usage_pct
5FROM (
6    SELECT
7        (SELECT COUNT(*) FROM information_schema.processlist) AS current_connections,
8        @@global.max_connections AS max_connections
9) t;

五、事务、锁与死锁

41. 查看正在运行的事务

SQL

1SELECT
2    trx_id,
3    trx_state,
4    trx_started,
5    trx_mysql_thread_id,
6    trx_rows_locked,
7    trx_rows_modified,
8    trx_query
9FROM information_schema.innodb_trx
10ORDER BY trx_started;

42. 查看长事务

SQL

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

43. 查看数据锁

SQL

1SELECT
2    engine_transaction_id,
3    thread_id,
4    object_schema,
5    object_name,
6    index_name,
7    lock_type,
8    lock_mode,
9    lock_status,
10    lock_data
11FROM performance_schema.data_locks;

44. 查看锁等待

SQL

1SELECT *
2FROM performance_schema.data_lock_waits;

45. 查看阻塞关系

SQL

1SELECT
2    r.trx_mysql_thread_id AS waiting_thread,
3    TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) AS waiting_seconds,
4    b.trx_mysql_thread_id AS blocking_thread,
5    r.trx_query AS waiting_query,
6    b.trx_query AS blocking_query
7FROM performance_schema.data_lock_waits w
8JOIN information_schema.innodb_trx r
9  ON r.trx_id = w.requesting_engine_transaction_id
10JOIN information_schema.innodb_trx b
11  ON b.trx_id = w.blocking_engine_transaction_id;

46. 查看元数据锁

SQL

1SELECT
2    object_type,
3    object_schema,
4    object_name,
5    lock_type,
6    lock_duration,
7    lock_status,
8    owner_thread_id
9FROM performance_schema.metadata_locks
10WHERE lock_status = 'PENDING';

47. 查看 InnoDB 状态

SQL

1SHOW ENGINE INNODB STATUS\G

重点关注最近死锁、事务、Buffer Pool 和 I/O。

48. 查看锁等待超时

SQL

1SELECT @@session.innodb_lock_wait_timeout;

49. 设置会话锁等待超时

SQL

1SET SESSION innodb_lock_wait_timeout = 30;

50. 查看事务隔离级别

SQL

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

六、SQL 性能与执行计划

51. 查看执行计划

SQL

1EXPLAIN
2SELECT *
3FROM appdb.orders
4WHERE customer_id = 1001;

52. 查看实际执行计划

SQL

1EXPLAIN ANALYZE
2SELECT *
3FROM appdb.orders
4WHERE customer_id = 1001;

EXPLAIN ANALYZE 会真正执行 SQL，不应随意用于修改类语句和高开销查询。

53. 查看 JSON 执行计划

SQL

1EXPLAIN FORMAT=JSON
2SELECT *
3FROM appdb.orders
4WHERE customer_id = 1001;

54. 查看 Digest SQL 统计

SQL

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

55. 查看平均耗时最高的 SQL

SQL

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

56. 查看扫描行数最高的 SQL

SQL

1SELECT
2    schema_name,
3    count_star,
4    sum_rows_examined,
5    sum_rows_sent,
6    digest_text
7FROM performance_schema.events_statements_summary_by_digest
8ORDER BY sum_rows_examined DESC
9LIMIT 20;

57. 查看 sys 慢 SQL 汇总

SQL

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

58. 查看全表扫描 SQL

SQL

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

59. 清空 Digest 统计

SQL

1TRUNCATE TABLE
2performance_schema.events_statements_summary_by_digest;

清空会影响趋势分析，应在明确采样窗口时执行。

60. 查看 Performance Schema 是否开启

SQL

1SELECT @@performance_schema;

七、空间与 InnoDB

61. 查看数据库大小

SQL

1SELECT
2    table_schema,
3    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
4FROM information_schema.tables
5WHERE table_schema NOT IN (
6    'information_schema',
7    'mysql',
8    'performance_schema',
9    'sys'
10)
11GROUP BY table_schema
12ORDER BY size_gb DESC;

62. 查看大表排行

SQL

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 = 'appdb'
10ORDER BY data_length + index_length DESC
11LIMIT 20;

63. 查看表碎片候选

SQL

1SELECT
2    table_schema,
3    table_name,
4    table_rows,
5    ROUND(data_free / 1024 / 1024, 2) AS data_free_mb,
6    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
7FROM information_schema.tables
8WHERE engine = 'InnoDB'
9  AND data_free > 0
10ORDER BY data_free DESC
11LIMIT 20;

data_free 不能直接等同于可回收空间，应结合表结构和存储实现判断。

64. 查看 Buffer Pool 状态

SQL

1SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

65. 查看 Buffer Pool 命中率

SQL

1SELECT
2    ROUND(
3        (1 - reads / NULLIF(read_requests, 0)) * 100,
4        4
5    ) AS buffer_pool_hit_pct
6FROM (
7    SELECT
8        MAX(CASE WHEN variable_name = 'Innodb_buffer_pool_reads'
9                 THEN variable_value END) AS reads,
10        MAX(CASE WHEN variable_name = 'Innodb_buffer_pool_read_requests'
11                 THEN variable_value END) AS read_requests
12    FROM performance_schema.global_status
13) s;

66. 查看脏页比例

SQL

1SELECT
2    ROUND(dirty * 100 / NULLIF(total_pages, 0), 2) AS dirty_page_pct
3FROM (
4    SELECT
5        MAX(CASE WHEN variable_name = 'Innodb_buffer_pool_pages_dirty'
6                 THEN variable_value END) AS dirty,
7        MAX(CASE WHEN variable_name = 'Innodb_buffer_pool_pages_total'
8                 THEN variable_value END) AS total_pages
9    FROM performance_schema.global_status
10) s;

67. 查看 Redo 日志等待

SQL

1SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';

68. 查看行操作统计

SQL

1SHOW GLOBAL STATUS LIKE 'Innodb_rows_%';

69. 查看打开表数量

SQL

1SHOW GLOBAL STATUS LIKE 'Open%tables';

70. 更新表统计信息

SQL

1ANALYZE TABLE appdb.orders;

八、索引与表结构

71. 查看表索引

SQL

1SHOW INDEX FROM appdb.orders;

72. 查看索引基数

SQL

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

73. 查看没有主键的表

SQL

1SELECT
2    t.table_schema,
3    t.table_name
4FROM information_schema.tables t
5LEFT JOIN information_schema.table_constraints c
6  ON c.table_schema = t.table_schema
7 AND c.table_name = t.table_name
8 AND c.constraint_type = 'PRIMARY KEY'
9WHERE t.table_schema = 'appdb'
10  AND t.table_type = 'BASE TABLE'
11  AND c.constraint_name IS NULL;

74. 查看未使用索引

SQL

1SELECT *
2FROM sys.schema_unused_indexes
3WHERE object_schema = 'appdb';

实例重启和统计清空后数据会重新累计，不能仅凭一次结果删除索引。

75. 查看重复索引

SQL

1SELECT *
2FROM sys.schema_redundant_indexes
3WHERE table_schema = 'appdb';

76. 创建索引

SQL

1CREATE INDEX idx_orders_customer
2ON appdb.orders(customer_id);

77. 创建联合索引

SQL

1CREATE INDEX idx_orders_customer_time
2ON appdb.orders(customer_id, order_time);

字段顺序应结合过滤、排序和选择性设计。

78. 删除索引

SQL

1DROP INDEX idx_orders_customer
2ON appdb.orders;

79. 增加字段

SQL

1ALTER TABLE appdb.orders
2ADD COLUMN remark VARCHAR(500) NULL;

80. 查看正在执行的 DDL

SQL

1SELECT
2    processlist_id,
3    processlist_user,
4    processlist_host,
5    processlist_time,
6    processlist_state,
7    processlist_info
8FROM performance_schema.threads
9WHERE processlist_command <> 'Sleep'
10  AND processlist_info REGEXP
11      '^(ALTER|CREATE|DROP|TRUNCATE|RENAME)';

九、用户与权限

81. 查看用户

SQL

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

82. 创建用户

SQL

1CREATE USER 'appuser'@'10.%'
2IDENTIFIED BY 'Replace_With_Strong_Password';

83. 修改用户密码

SQL

1ALTER USER 'appuser'@'10.%'
2IDENTIFIED BY 'Replace_With_New_Strong_Password';

84. 锁定和解锁用户

SQL

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

解锁：

SQL

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

85. 查看用户权限

SQL

1SHOW GRANTS FOR 'appuser'@'10.%';

86. 授予只读权限

SQL

1GRANT SELECT ON appdb.*
2TO 'report_user'@'10.%';

87. 授予读写权限

SQL

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

88. 回收权限

SQL

1REVOKE INSERT, UPDATE, DELETE
2ON appdb.*
3FROM 'appuser'@'10.%';

89. 查看角色

SQL

1SELECT *
2FROM mysql.role_edges;

90. 删除用户

SQL

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

删除前应确认应用已经停止使用该账号。

十、导入导出与云平台运维

91. 使用 mysqldump 导出数据库

Bash

1mysqldump -h pc-example.rwlb.rds.aliyuncs.com \
2  -P 3306 -u backup_user -p \
3  --single-transaction \
4  --routines --events --triggers \
5  appdb > appdb.sql

大规模迁移建议使用 DTS、DMS 或官方迁移方案。

92. 导出指定表

Bash

1mysqldump -h pc-example.rwlb.rds.aliyuncs.com \
2  -P 3306 -u backup_user -p \
3  --single-transaction \
4  appdb orders > orders.sql

93. 导入 SQL 文件

Bash

1mysql -h pc-example.rwlb.rds.aliyuncs.com \
2  -P 3306 -u restore_user -p \
3  appdb < appdb.sql

94. 导出查询结果

Bash

1mysql -h pc-example.rwlb.rds.aliyuncs.com \
2  -P 3306 -u report_user -p \
3  --batch --raw \
4  -e "SELECT * FROM appdb.orders LIMIT 1000" \
5  > orders.tsv

95. 查看计划任务

SQL

1SELECT
2    event_schema,
3    event_name,
4    status,
5    event_type,
6    execute_at,
7    interval_value,
8    interval_field
9FROM information_schema.events
10ORDER BY event_schema, event_name;

96. 查看分区表

SQL

1SELECT
2    table_schema,
3    table_name,
4    partition_name,
5    partition_method,
6    partition_expression,
7    table_rows
8FROM information_schema.partitions
9WHERE partition_name IS NOT NULL
10ORDER BY table_schema, table_name, partition_ordinal_position;

97. 验证读写地址的节点路由

SQL

1SELECT
2    @@hostname,
3    @@server_uuid,
4    @@global.read_only,
5    CONNECTION_ID();

分别通过主地址、集群地址和自定义地址多次建立新连接，可以辅助确认路由结果。正式判断仍应结合控制台地址配置。

98. 查询数据库内可见的错误和告警

SQL

1SELECT
2    error_number,
3    error_name,
4    sql_state,
5    sum_error_raised,
6    first_seen,
7    last_seen
8FROM performance_schema.events_errors_summary_global_by_error
9WHERE sum_error_raised > 0
10ORDER BY sum_error_raised DESC
11LIMIT 20;

99. 生成快速巡检摘要

SQL

1SELECT 'connections' AS item,
2       COUNT(*) AS value
3FROM information_schema.processlist
4UNION ALL
5SELECT 'running_transactions',
6       COUNT(*)
7FROM information_schema.innodb_trx
8UNION ALL
9SELECT 'lock_waits',
10       COUNT(*)
11FROM performance_schema.data_lock_waits
12UNION ALL
13SELECT 'max_connections',
14       @@global.max_connections;

100. 检查控制台运维项目

数据库内没有一条 SQL 可以完整替代云平台巡检。完成 SQL 检查后，还应在 PolarDB 控制台或 API 中确认：

1集群与节点状态
2主节点和只读节点拓扑
3集群地址、主地址和自定义地址配置
4读写分离与一致性级别
5CPU、内存、连接、IOPS、吞吐和存储使用率
6慢 SQL、SQL 洞察和一键诊断
7参数模板及待重启参数
8自动备份、日志备份和可恢复时间范围
9告警规则、维护窗口和近期变更记录

结语

PolarDB MySQL 版的大部分 SQL 排查方法与 MySQL 8.0 相似，但架构判断不能停留在单机数据库思路。通过集群地址连接时，读请求可能被路由到只读节点；同一条查询在不同连接中可能落到不同计算节点；备份、扩缩容和切换也由云平台统一管理。

因此，实际排查时应把数据库内信息和控制台信息放在一起看。数据库内重点检查会话、事务锁、执行计划、Digest SQL 和空间；控制台重点检查节点拓扑、地址路由、读写分离、监控趋势、SQL 洞察、备份和近期变更。

官方资料

PolarDB MySQL 版文档：https://help.aliyun.com/zh/polardb/polardb-for-mysql/
连接 PolarDB MySQL：https://help.aliyun.com/zh/polardb/polardb-for-mysql/user-guide/connect-to-polardb/
PolarDB 关键术语：https://help.aliyun.com/zh/polardb/polardb-for-mysql/terminology
SQL 洞察：https://help.aliyun.com/zh/polardb/polardb-for-mysql/sql-insight