mysql

Name: mysql
Author: chaterm

90installs12forks

How to install mysql?

chaterm/terminal-skills/mysql

$npx -y skills add chaterm/terminal-skills --skill mysql

Installs into the current project.

Summary

MySQL 数据库管理与运维

Files · 1

View on GitHub

SKILL.md

1	# MySQL 数据库管理
2
3	## 概述
4	MySQL/MariaDB 数据库的日常管理、备份恢复、性能调优等运维技能。
5
6	## 连接管理
7
8	```bash
9	# 本地连接
10	mysql -u root -p
11
12	# 远程连接
13	mysql -h hostname -P 3306 -u user -p database
14
15	# 执行 SQL 文件
16	mysql -u user -p database < script.sql
17
18	# 执行单条命令
19	mysql -u user -p -e "SHOW DATABASES;"
20	```
21
22	## 用户与权限
23
24	```sql
25	-- 查看用户
26	SELECT user, host FROM mysql.user;
27
28	-- 创建用户
29	CREATE USER 'username'@'%' IDENTIFIED BY 'password';
30
31	-- 授权
32	GRANT ALL PRIVILEGES ON database.* TO 'username'@'%';
33	GRANT SELECT, INSERT ON database.table TO 'username'@'%';
34
35	-- 刷新权限
36	FLUSH PRIVILEGES;
37
38	-- 查看权限
39	SHOW GRANTS FOR 'username'@'%';
40	```
41
42	## 数据库操作
43
44	```sql
45	-- 数据库管理
46	SHOW DATABASES;
47	CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
48	DROP DATABASE dbname;
49	USE dbname;
50
51	-- 表管理
52	SHOW TABLES;
53	DESCRIBE tablename;
54	SHOW CREATE TABLE tablename;
55	```
56
57	## 备份与恢复
58
59	### mysqldump 备份
60	```bash
61	# 备份单个数据库
62	mysqldump -u root -p database > backup.sql
63
64	# 备份所有数据库
65	mysqldump -u root -p --all-databases > all_backup.sql
66
67	# 备份表结构
68	mysqldump -u root -p --no-data database > schema.sql
69
70	# 压缩备份
71	mysqldump -u root -p database \| gzip > backup.sql.gz
72	```
73
74	### 恢复
75	```bash
76	# 恢复数据库
77	mysql -u root -p database < backup.sql
78
79	# 从压缩文件恢复
80	gunzip < backup.sql.gz \| mysql -u root -p database
81	```
82
83	## 性能监控
84
85	```sql
86	-- 查看进程
87	SHOW PROCESSLIST;
88	SHOW FULL PROCESSLIST;
89
90	-- 查看状态
91	SHOW STATUS;
92	SHOW GLOBAL STATUS LIKE 'Threads%';
93	SHOW GLOBAL STATUS LIKE 'Connections';
94
95	-- 查看变量
96	SHOW VARIABLES LIKE 'max_connections';
97	SHOW VARIABLES LIKE '%buffer%';
98
99	-- 慢查询
100	SHOW VARIABLES LIKE 'slow_query%';
101	SHOW GLOBAL STATUS LIKE 'Slow_queries';
102	```
103
104	## 常见场景
105
106	### 场景 1：排查慢查询
107	```sql
108	-- 开启慢查询日志
109	SET GLOBAL slow_query_log = 'ON';
110	SET GLOBAL long_query_time = 1;
111
112	-- 查看慢查询日志位置
113	SHOW VARIABLES LIKE 'slow_query_log_file';
114
115	-- 分析执行计划
116	EXPLAIN SELECT * FROM table WHERE condition;
117	EXPLAIN ANALYZE SELECT * FROM table WHERE condition;
118	```
119
120	### 场景 2：锁问题排查
121	```sql
122	-- 查看锁等待
123	SHOW ENGINE INNODB STATUS\G
124
125	-- 查看当前锁
126	SELECT * FROM information_schema.INNODB_LOCKS;
127	SELECT * FROM information_schema.INNODB_LOCK_WAITS;
128
129	-- 查看事务
130	SELECT * FROM information_schema.INNODB_TRX;
131	```
132
133	### 场景 3：主从复制状态
134	```sql
135	-- 主库状态
136	SHOW MASTER STATUS;
137
138	-- 从库状态
139	SHOW SLAVE STATUS\G
140
141	-- 关键指标
142	-- Slave_IO_Running: Yes
143	-- Slave_SQL_Running: Yes
144	-- Seconds_Behind_Master: 0
145	```
146
147	## 故障排查
148
149	\| 问题 \| 排查方法 \|
150	\|------\|----------\|
151	\| 连接数过多 \| `SHOW PROCESSLIST`, 检查 max_connections \|
152	\| 查询慢 \| `EXPLAIN`, 检查索引 \|
153	\| 锁等待 \| `SHOW ENGINE INNODB STATUS` \|
154	\| 复制延迟 \| `SHOW SLAVE STATUS`, 检查网络和负载 \|
155	\| 磁盘满 \| 检查 binlog, 清理旧日志 \|

People who liked this also viewed

6 picks

Type

azure-storageAzure Storage Services including Blob Storage, File Shares, Queue Storage, Table Storage, and Data Lake. Answers questions about storage access tiers (hot, cool, cold, archive), when to use each tier,SkillsJul 2026471k1.3k
supabase-postgres-best-practicesPostgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.SkillsJul 2026302k2.4k
supabaseUse when doing ANY task involving Supabase. Triggers: Supabase products (Database, Auth, Edge Functions, Realtime, Storage, Vectors, Cron, Queues); client libraries and SSR integrations (supabase-js, SkillsJul 2026179k2.4k
convex-quickstartCreates or adds Convex to an app. Use for new Convex projects, npm create convex@latest, frontend setup, env vars, or the first npx convex dev run.SkillsJul 202690k39
convex-performance-auditAudits Convex performance for reads, subscriptions, write contention, and function limits. Use for slow features, insights findings, OCC conflicts, or read amplification.SkillsJul 202689k39
convex-setup-authSets up Convex auth, identity mapping, and access control. Use for login, auth providers, users tables, protected functions, or roles in a Convex app.SkillsJul 202689k39

mysql

90installs12forks

View on GitHub

How to install mysql?

chaterm/terminal-skills/mysql

$npx -y skills add chaterm/terminal-skills --skill mysql

Installs into the current project.

Summary

MySQL 数据库管理与运维

Files · 1

View on GitHub

SKILL.md

1	# MySQL 数据库管理
2
3	## 概述
4	MySQL/MariaDB 数据库的日常管理、备份恢复、性能调优等运维技能。
5
6	## 连接管理
7
8	```bash
9	# 本地连接
10	mysql -u root -p
11
12	# 远程连接
13	mysql -h hostname -P 3306 -u user -p database
14
15	# 执行 SQL 文件
16	mysql -u user -p database < script.sql
17
18	# 执行单条命令
19	mysql -u user -p -e "SHOW DATABASES;"
20	```
21
22	## 用户与权限
23
24	```sql
25	-- 查看用户
26	SELECT user, host FROM mysql.user;
27
28	-- 创建用户
29	CREATE USER 'username'@'%' IDENTIFIED BY 'password';
30
31	-- 授权
32	GRANT ALL PRIVILEGES ON database.* TO 'username'@'%';
33	GRANT SELECT, INSERT ON database.table TO 'username'@'%';
34
35	-- 刷新权限
36	FLUSH PRIVILEGES;
37
38	-- 查看权限
39	SHOW GRANTS FOR 'username'@'%';
40	```
41
42	## 数据库操作
43
44	```sql
45	-- 数据库管理
46	SHOW DATABASES;
47	CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
48	DROP DATABASE dbname;
49	USE dbname;
50
51	-- 表管理
52	SHOW TABLES;
53	DESCRIBE tablename;
54	SHOW CREATE TABLE tablename;
55	```
56
57	## 备份与恢复
58
59	### mysqldump 备份
60	```bash
61	# 备份单个数据库
62	mysqldump -u root -p database > backup.sql
63
64	# 备份所有数据库
65	mysqldump -u root -p --all-databases > all_backup.sql
66
67	# 备份表结构
68	mysqldump -u root -p --no-data database > schema.sql
69
70	# 压缩备份
71	mysqldump -u root -p database \| gzip > backup.sql.gz
72	```
73
74	### 恢复
75	```bash
76	# 恢复数据库
77	mysql -u root -p database < backup.sql
78
79	# 从压缩文件恢复
80	gunzip < backup.sql.gz \| mysql -u root -p database
81	```
82
83	## 性能监控
84
85	```sql
86	-- 查看进程
87	SHOW PROCESSLIST;
88	SHOW FULL PROCESSLIST;
89
90	-- 查看状态
91	SHOW STATUS;
92	SHOW GLOBAL STATUS LIKE 'Threads%';
93	SHOW GLOBAL STATUS LIKE 'Connections';
94
95	-- 查看变量
96	SHOW VARIABLES LIKE 'max_connections';
97	SHOW VARIABLES LIKE '%buffer%';
98
99	-- 慢查询
100	SHOW VARIABLES LIKE 'slow_query%';
101	SHOW GLOBAL STATUS LIKE 'Slow_queries';
102	```
103
104	## 常见场景
105
106	### 场景 1：排查慢查询
107	```sql
108	-- 开启慢查询日志
109	SET GLOBAL slow_query_log = 'ON';
110	SET GLOBAL long_query_time = 1;
111
112	-- 查看慢查询日志位置
113	SHOW VARIABLES LIKE 'slow_query_log_file';
114
115	-- 分析执行计划
116	EXPLAIN SELECT * FROM table WHERE condition;
117	EXPLAIN ANALYZE SELECT * FROM table WHERE condition;
118	```
119
120	### 场景 2：锁问题排查
121	```sql
122	-- 查看锁等待
123	SHOW ENGINE INNODB STATUS\G
124
125	-- 查看当前锁
126	SELECT * FROM information_schema.INNODB_LOCKS;
127	SELECT * FROM information_schema.INNODB_LOCK_WAITS;
128
129	-- 查看事务
130	SELECT * FROM information_schema.INNODB_TRX;
131	```
132
133	### 场景 3：主从复制状态
134	```sql
135	-- 主库状态
136	SHOW MASTER STATUS;
137
138	-- 从库状态
139	SHOW SLAVE STATUS\G
140
141	-- 关键指标
142	-- Slave_IO_Running: Yes
143	-- Slave_SQL_Running: Yes
144	-- Seconds_Behind_Master: 0
145	```
146
147	## 故障排查
148
149	\| 问题 \| 排查方法 \|
150	\|------\|----------\|
151	\| 连接数过多 \| `SHOW PROCESSLIST`, 检查 max_connections \|
152	\| 查询慢 \| `EXPLAIN`, 检查索引 \|
153	\| 锁等待 \| `SHOW ENGINE INNODB STATUS` \|
154	\| 复制延迟 \| `SHOW SLAVE STATUS`, 检查网络和负载 \|
155	\| 磁盘满 \| 检查 binlog, 清理旧日志 \|

People who liked this also viewed

6 picks

Type