企业官网建设流程全解析

1. 项目概述：为什么你需要一份“完整”的命令手册？

干了这么多年数据库运维和开发，我电脑里一直存着一个自己整理的MySQL命令文档。每次带新人，或者自己临时忘了某个生僻语法，翻出来看一眼，效率能提升不少。网上命令大全很多，但要么是零散的碎片，要么版本老旧，要么只给命令不给上下文，用起来总差点意思。今天分享的这份“大全”，核心目标就一个：让你手边有一份能直接“开箱即用”、覆盖日常开发运维全场景的MySQL命令参考。

这份大全不是简单的命令罗列。我会按照实际工作流来组织，从最基础的连接、库表操作，到复杂的数据查询、用户权限管理，再到性能排查和日常维护。每个命令都会配上最常用的选项、清晰的示例，以及我踩过坑后总结的注意事项。无论你是刚接触MySQL的新手，需要一份可靠的入门指南；还是经验丰富的DBA，想快速查阅某个特定场景的语法，这份文档都能作为你的案头手册。

关键词自然贯穿全文：MySQL是核心，命令是表现形式，大全和完整意味着我们追求覆盖面的广度与常用场景的深度。接下来，我们就从如何与数据库“对话”开始。

2. 核心操作全流程解析

2.1 连接数据库与基础信息查看

一切操作始于连接。连接MySQL不仅仅是输入密码，不同的连接方式对应不同的工作场景。

1. 标准密码连接这是最常用的方式。在命令行中，使用mysql客户端工具。

mysql -h 主机名 -P 端口号 -u 用户名 -p

• -h：指定数据库服务器地址。如果是连接本机，可以省略或使用-h 127.0.0.1或-h localhost。
• -P：指定端口号，MySQL默认是3306。如果使用默认端口，此参数可省略。注意：-P是大写，小写-p是密码参数。
• -u：指定登录的用户名，例如-u root。
• -p：告诉客户端接下来需要输入密码。出于安全考虑，不建议在命令中直接写入密码（如-p123456），而应在回车后根据提示输入，这样密码不会留在命令行历史记录中。

2. 使用Socket文件连接（本地连接优化）当客户端和MySQL服务器在同一台机器上时，使用Socket文件连接比TCP/IP连接更高效，因为它避免了网络协议栈的开销。

mysql -u 用户名 -p --socket=/tmp/mysql.sock

你需要知道MySQL服务器配置的socket文件路径，通常可以在my.cnf配置文件的[mysqld]部分找到socket参数，或者登录后通过SHOW VARIABLES LIKE 'socket';命令查询。

3. 连接后首要查看的信息成功登录后，别急着操作。先快速查看一下环境信息，做到心中有数。

-- 查看当前连接的服务器版本和状态 SELECT VERSION(), CURRENT_DATE(); -- 或者使用快捷命令 \s -- 查看当前用户和连接来源 SELECT USER(), CURRENT_USER();

\s是mysql客户端的一个快捷命令（status的缩写），它会输出连接ID、服务器版本、协议版本、字符集等丰富信息，非常实用。

注意：生产环境连接数据库，务必使用具有最小必要权限的专用账号，而非root账号。连接后，立即通过SELECT DATABASE();确认当前所在的数据库，避免误操作。

2.2 数据库与表的核心生命周期管理

对数据库和表的增删改查，是DBA和开发者的日常。这里的“查”不仅是查数据，更是查结构、查状态。

1. 数据库（Schema）操作数据库在MySQL中也被称为Schema，两者在大多数语境下等价。

-- 1. 创建数据库，并指定默认字符集和排序规则 CREATE DATABASE `my_app_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 2. 查看所有数据库 SHOW DATABASES; -- 3. 切换到某个数据库 USE my_app_db; -- 4. 查看当前数据库的创建信息 SHOW CREATE DATABASE my_app_db; -- 5. 修改数据库字符集（谨慎！仅影响后续创建的表） ALTER DATABASE my_app_db CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci; -- 6. 删除数据库（极度危险！） DROP DATABASE my_app_db;

实操心得：创建数据库时，强烈建议指定字符集。utf8mb4是现在的绝对主流，因为它支持完整的UTF-8编码，包括表情符号（Emoji）。而MySQL历史上默认的utf8其实只支持最多3字节的字符，是一个“阉割版”。排序规则utf8mb4_unicode_ci和utf8mb4_0900_ai_ci都是基于Unicode标准的，后者是MySQL 8.0引入的更新、更标准的规则。_ci表示大小写不敏感。

2. 数据表操作表是数据的载体，其操作更为频繁和复杂。

-- 1. 创建表 CREATE TABLE `users` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户唯一ID', `username` VARCHAR(50) NOT NULL COMMENT '用户名', `email` VARCHAR(100) NOT NULL COMMENT '邮箱', `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1-正常，0-禁用', `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_username` (`username`), UNIQUE KEY `uk_email` (`email`), KEY `idx_status` (`status`), KEY `idx_created_at` (`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表'; -- 2. 查看当前数据库中的所有表 SHOW TABLES; -- 3. 查看表的结构定义 DESC users; -- 或 DESCRIBE users; -- 4. 查看表的详细创建语句（包含所有选项） SHOW CREATE TABLE users; -- 5. 修改表结构（ALTER TABLE） -- 5.1 增加列 ALTER TABLE users ADD COLUMN `phone` VARCHAR(20) NULL COMMENT '手机号' AFTER `email`; -- 5.2 修改列定义 ALTER TABLE users MODIFY COLUMN `email` VARCHAR(150) NOT NULL COMMENT '电子邮箱地址'; -- 5.3 重命名列 ALTER TABLE users CHANGE COLUMN `phone` `mobile` VARCHAR(20) NULL COMMENT '手机号码'; -- 5.4 删除列 ALTER TABLE users DROP COLUMN `mobile`; -- 5.5 增加索引 ALTER TABLE users ADD INDEX `idx_email_status` (`email`, `status`); -- 5.6 删除索引 ALTER TABLE users DROP INDEX `idx_email_status`; -- 6. 重命名表 RENAME TABLE `users` TO `user_accounts`; -- 7. 清空表（删除所有数据，重置AUTO_INCREMENT） TRUNCATE TABLE user_accounts; -- 8. 删除表 DROP TABLE user_accounts;

踩坑记录：ALTER TABLE是大表操作的“噩梦”。在数据量大的表上直接加列或改索引，可能会导致长时间的锁表，阻塞线上业务。对于MySQL 5.6及以上版本，大部分ALTER操作支持ALGORITHM=INPLACE和LOCK=NONE选项，可以实现在线DDL，减少锁的影响。但修改列数据类型、删除主键等操作仍可能需要复制表数据（ALGORITHM=COPY）并锁表。务必在业务低峰期操作，并先在小规模测试环境验证。另外，TRUNCATE和DELETE FROM table的区别要牢记：TRUNCATE是DDL语句，更快，且会重置自增ID，但不能回滚；DELETE是DML语句，一行行删除，可以带WHERE条件，可以回滚，但慢且会产生大量Undo日志。

2.3 数据的增删改查（CRUD）进阶

这是开发者的主战场。基础的INSERT、SELECT、UPDATE、DELETE谁都会，但写出高效、准确的语句需要技巧。

1. 插入数据（INSERT）

-- 1. 标准插入 INSERT INTO users (username, email, status) VALUES ('john_doe', 'john@example.com', 1); -- 2. 批量插入（强烈推荐，大幅减少网络和SQL解析开销） INSERT INTO users (username, email, status) VALUES ('alice', 'alice@example.com', 1), ('bob', 'bob@example.com', 1), ('charlie', 'charlie@example.com', 0); -- 3. 插入或更新（ON DUPLICATE KEY UPDATE） INSERT INTO users (username, email, status) VALUES ('john_doe', 'john_new@example.com', 1) ON DUPLICATE KEY UPDATE email = VALUES(email), updated_at = CURRENT_TIMESTAMP; -- 4. 从查询结果插入（INSERT ... SELECT） INSERT INTO user_backup (username, email, status) SELECT username, email, status FROM users WHERE created_at < '2023-01-01';

注意事项：批量插入时，单个语句的数据量不宜过大（通常建议几千到一万条以内），否则可能导致网络包过大或binlog事件过大。ON DUPLICATE KEY UPDATE是实现“存在则更新，不存在则插入”的神器，但它依赖于主键或唯一键冲突的判断。

2. 查询数据（SELECT）查询是SQL的灵魂，优化是永恒的主题。

-- 1. 基础查询 SELECT id, username, email FROM users WHERE status = 1 ORDER BY created_at DESC LIMIT 10; -- 2. 聚合查询 SELECT status, COUNT(*) as user_count, MAX(created_at) as latest_user FROM users GROUP BY status HAVING user_count > 10; -- HAVING对分组结果过滤，WHERE对原始行过滤 -- 3. 多表连接（JOIN） -- 假设有另一张表 `orders` SELECT u.username, o.order_id, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id -- 只返回两表都匹配的行 WHERE u.status = 1 ORDER BY o.created_at DESC; -- 4. 子查询 SELECT username FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders WHERE amount > 100); -- 5. 使用EXISTS的子查询（通常比IN性能更好，特别是子查询结果集大时） SELECT u.username FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100); -- 6. 窗口函数（MySQL 8.0+，用于复杂分析） SELECT username, email, created_at, ROW_NUMBER() OVER (ORDER BY created_at) as row_num, RANK() OVER (PARTITION BY status ORDER BY created_at DESC) as rank_in_status FROM users;

性能要点：SELECT *是方便，但也是性能杀手。务必只取需要的列，特别是当表中有TEXT/BLOB等大字段时。JOIN查询时，确保ON条件上的字段有索引。EXPLAIN命令是你的最佳朋友，后面会详细讲。

3. 更新数据（UPDATE）

-- 1. 条件更新 UPDATE users SET status = 0, updated_at = CURRENT_TIMESTAMP WHERE last_login_at < DATE_SUB(NOW(), INTERVAL 1 YEAR); -- 2. 基于子查询的更新 UPDATE orders o JOIN users u ON o.user_id = u.id SET o.priority = 'high' WHERE u.vip_level > 3;

严重警告：执行UPDATE前，务必先执行一个相同条件的SELECT语句，确认影响的行数是否符合预期。永远不要不带WHERE条件运行UPDATE，除非你确实想更新整张表。生产环境操作前，开启事务（BEGIN;）先试一下是个好习惯。

4. 删除数据（DELETE）

DELETE FROM users WHERE status = 0 AND updated_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

和UPDATE一样，必须先确认WHERE条件。对于大量数据的删除，建议分批进行（例如使用LIMIT），避免产生大事务导致主从延迟甚至锁表。

DELETE FROM large_table WHERE condition LIMIT 1000; -- 循环执行，直到影响行数为0

3. 用户、权限与连接管理实战

数据库安全的第一道防线就是权限管理。MySQL的权限系统基于“账号@主机”和权限层级，粒度可以很细。

3.1 用户账号管理

-- 1. 创建用户（MySQL 8.0+ 默认使用caching_sha2_password认证插件，更安全） CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!'; -- 2. 创建用户（兼容旧客户端，使用mysql_native_password） CREATE USER 'legacy_app'@'localhost' IDENTIFIED WITH mysql_native_password BY 'OldPassword'; -- 3. 修改用户密码 ALTER USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'NewStrongPassword456!'; -- 4. 重命名用户 RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost'; -- 5. 删除用户 DROP USER 'app_user'@'192.168.1.%'; -- 6. 查看所有用户 SELECT user, host, plugin FROM mysql.user;

安全准则：

1. 遵循最小权限原则：应用账号只授予其完成功能所必需的最小权限。
2. 限制主机范围：不要使用'user'@'%'（允许从任何主机连接）。应指定具体的IP段或主机名，如'app'@'192.168.1.0/255.255.255.0'或'backup'@'backup-server-hostname'。
3. 使用强密码：密码应包含大小写字母、数字和特殊字符，并定期更换。
4. MySQL 8.0认证插件：新版本默认的caching_sha2_password比旧的mysql_native_password更安全，但一些老的客户端驱动（如某些PHP版本）可能不支持。如果遇到连接问题，可能需要创建用户时指定旧插件，或升级客户端。

3.2 权限授予与回收

权限授予使用GRANT，回收使用REVOKE。

-- 1. 授予特定数据库的所有权限 GRANT ALL PRIVILEGES ON `my_app_db`.* TO 'app_user'@'192.168.1.%'; -- 2. 授予特定表的特定权限（SELECT, INSERT, UPDATE, DELETE） GRANT SELECT, INSERT, UPDATE, DELETE ON `my_app_db`.`users` TO 'report_user'@'10.0.0.100'; -- 3. 授予全局权限（谨慎！） GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor_user'@'localhost'; -- 4. 授予“授予权限”的权限（WITH GRANT OPTION，非常谨慎！） GRANT ALL ON `my_app_db`.* TO 'admin_user'@'localhost' WITH GRANT OPTION; -- 5. 查看某个用户的权限 SHOW GRANTS FOR 'app_user'@'192.168.1.%'; -- 6. 回收权限 REVOKE DELETE ON `my_app_db`.`users` FROM 'report_user'@'10.0.0.100'; -- 7. 回收所有权限 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'192.168.1.%';

权限层级解析：

• *.*：全局权限，作用于所有数据库的所有表。
• database.*：数据库级权限，作用于指定数据库的所有表。
• database.table：表级权限，作用于指定数据库的指定表。
• column：列级权限（较少使用）。

关键命令：FLUSH PRIVILEGES;。在直接修改mysql系统表（如UPDATE mysql.user SET ...）后，必须执行此命令使权限更改立即生效。但使用标准的GRANT和REVOKE语句时，权限会实时更新，通常不需要手动FLUSH PRIVILEGES。

3.3 连接与会话管理

作为DBA，管理数据库连接是常规工作。

-- 1. 查看当前所有连接 SHOW PROCESSLIST; -- 更详细的视图（MySQL 5.7+/ MariaDB 10.5+） SELECT * FROM information_schema.PROCESSLIST; -- 2. 查看连接相关的状态变量 SHOW STATUS LIKE 'Threads_%'; -- 3. 查看连接相关的系统变量 SHOW VARIABLES LIKE 'max_connections'; SHOW VARIABLES LIKE 'wait_timeout'; SHOW VARIABLES LIKE 'interactive_timeout'; -- 4. 终止一个连接 KILL CONNECTION 12345; -- 12345是SHOW PROCESSLIST中的Id KILL QUERY 12345; -- 只终止该连接正在执行的语句，不断开连接

排查技巧：当数据库响应变慢时，首先SHOW PROCESSLIST。关注State列：

• Sleep：空闲连接。
• Locked：等待表锁（MyISAM引擎常见）。
• Sending data/Copying to tmp table/Sorting result：可能正在执行复杂查询。
• Waiting for table metadata lock：通常有未提交的事务或DDL操作阻塞。

wait_timeout和interactive_timeout控制非交互式和交互式连接的空闲超时时间（秒），超时后服务器会断开连接。设置过短会导致应用频繁重连，过长则可能积累大量空闲连接耗尽max_connections。需要根据应用连接池配置来调整。

4. 高级运维与性能排查命令

数据库不仅要能用，还要跑得快、跑得稳。这部分命令是定位问题和优化性能的关键。

4.1 性能诊断与EXPLAIN深度使用

EXPLAIN是SQL优化的“显微镜”，它展示MySQL如何执行一条SELECT语句。

EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 1 ORDER BY o.created_at DESC LIMIT 100;

或者使用更详细的格式（MySQL 8.0.18+）：

EXPLAIN FORMAT=JSON SELECT ...; -- 输出JSON格式的详细执行计划 EXPLAIN ANALYZE SELECT ...; -- MySQL 8.0.18+，实际执行语句并给出各阶段耗时（非常强大）

解读EXPLAIN输出，关键看以下几列：

• type：访问类型，从好到坏大致是：system>const>eq_ref>ref>range>index>ALL。ALL表示全表扫描，需要警惕。
• key：实际使用的索引。如果为NULL，则未使用索引。
• rows：MySQL预估需要扫描的行数。这个值越接近实际返回的行数越好。
• Extra：额外信息，包含重要提示：
  • Using index：使用了覆盖索引，性能极佳。
  • Using where：在存储引擎检索行后，在服务器层进行了过滤。
  • Using temporary：使用了临时表，常见于排序和分组。
  • Using filesort：使用了文件排序，可能成为性能瓶颈。

实操心得：对于复杂查询，EXPLAIN ANALYZE是终极武器。它不仅告诉你执行计划，还告诉你每个步骤实际花了多少时间（例如，-> Index lookup on o using idx_user_id (user_id=u.id) (cost=0.25 rows=1) (actual time=0.012..0.015 rows=1 loops=1000)）。这能帮你精准定位到底是哪个JOIN或哪个排序拖慢了整个查询。

4.2 系统状态与变量查看

了解数据库的实时状态和配置是性能调优的基础。

-- 1. 查看服务器状态（全局计数器） SHOW GLOBAL STATUS; -- 查看会话状态 SHOW SESSION STATUS; -- 2. 查看服务器系统变量（配置） SHOW GLOBAL VARIABLES; SHOW SESSION VARIABLES; -- 3. 查看特定状态/变量 SHOW GLOBAL STATUS LIKE 'Innodb%'; SHOW VARIABLES LIKE 'innodb_buffer_pool%'; -- 4. 查看引擎状态（特别是InnoDB） SHOW ENGINE INNODB STATUS\G -- \G使结果垂直显示，便于阅读

关键状态监控点：

• 连接相关：Threads_connected（当前连接数）,Threads_running（正在执行的连接数）,Max_used_connections（历史最大连接数）。
• 查询相关：Questions（服务器启动以来总查询数）,Queries（包含存储过程的语句数）,Slow_queries（慢查询数量）。
• InnoDB缓冲池：Innodb_buffer_pool_read_requests（逻辑读请求）,Innodb_buffer_pool_reads（从磁盘进行的物理读）。缓冲池命中率≈(1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%，这个值通常应高于99%。
• 锁与事务：Innodb_row_lock_current_waits（当前等待行锁的数量）,Innodb_row_lock_time_avg（平均行锁等待时间）。

4.3 索引管理与优化

索引是数据库的“目录”，管理好索引至关重要。

-- 1. 查看表的所有索引 SHOW INDEX FROM users; -- 2. 分析索引使用情况（更新统计信息，帮助优化器做更好的选择） ANALYZE TABLE users; -- 3. 检查表（主要针对MyISAM，修复可能损坏的表） CHECK TABLE users; -- 4. 优化表（整理碎片，回收空间。对InnoDB表，相当于执行ALTER TABLE ... FORCE） OPTIMIZE TABLE users; -- 5. 强制使用/忽略某个索引（用于测试或特殊情况） SELECT * FROM users USE INDEX (idx_status) WHERE status = 1; SELECT * FROM users IGNORE INDEX (idx_status) WHERE status = 1; SELECT * FROM users FORCE INDEX (primary) WHERE id > 100;

索引维护经验：

1. ANALYZE TABLE会更新表的索引统计信息，当数据发生大量变化后执行，可以使优化器选择更准确的执行计划。在MySQL 8.0中，默认开启了统计信息的自动更新，但有时手动更新仍有必要。
2. OPTIMIZE TABLE对于InnoDB表，如果innodb_file_per_table=ON且表文件存在碎片（例如大量删除后），执行它可以重建表并整理碎片，减少磁盘空间占用。这是一个DDL操作，会锁表，请在业务低峰期进行。
3. 定期使用SHOW INDEX查看索引的Cardinality（基数，即索引列中不同值的数量估算）。这个值相对于表行数越高，索引的选择性越好，优化器越可能使用它。

4.4 备份与恢复关键命令

数据是核心，备份是生命线。

-- 1. 逻辑备份（使用mysqldump命令行工具，非SQL命令） -- 备份单个数据库 mysqldump -u root -p --single-transaction --routines --triggers --events my_app_db > my_app_db_backup.sql -- 备份所有数据库 mysqldump -u root -p --all-databases --single-transaction --routines --triggers --events > full_backup.sql -- 2. 从逻辑备份恢复 mysql -u root -p my_app_db < my_app_db_backup.sql -- 3. 在MySQL内执行数据导出（SELECT ... INTO OUTFILE） SELECT id, username, email INTO OUTFILE '/tmp/users.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM users WHERE status = 1; -- 4. 从文件导入数据（LOAD DATA INFILE） LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (id, username, email) -- 指定列顺序，如果文件包含所有列且顺序一致可省略 SET status = 1, created_at = NOW(); -- 为导入的数据设置额外的固定值或表达式

备份策略要点：

• --single-transaction：对于InnoDB表，此参数会在一个事务中导出数据，确保备份的一致性视图，且不会锁表。这是生产环境在线备份的必备参数。
• --routines：备份存储过程和函数。
• --triggers：备份触发器。
• --events：备份事件调度器。
• SELECT ... INTO OUTFILE和LOAD DATA INFILE是高速数据导入导出的利器，速度比INSERT语句快一个数量级。但文件必须位于数据库服务器上，且需要FILE权限。从安全角度，secure_file_priv系统变量会限制可读写文件的目录。

5. 事务、锁与复制管理

对于需要高可靠性和一致性的应用，理解事务和锁是必须的。主从复制则是实现高可用和读写分离的基石。

5.1 事务控制

-- 1. 开启一个事务 START TRANSACTION; -- 或 BEGIN; -- 2. 提交事务 COMMIT; -- 3. 回滚事务 ROLLBACK; -- 4. 设置事务隔离级别（会话级） SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 5. 查看当前事务隔离级别 SELECT @@transaction_isolation; -- 6. 设置自动提交模式 SET autocommit = 0; -- 关闭自动提交，每条语句都需要显式COMMIT SET autocommit = 1; -- 开启自动提交（默认）

事务使用原则：

1. 事务应尽可能短小，尽快提交，以减少锁的持有时间。
2. 根据业务需求选择合适的隔离级别。READ COMMITTED是平衡一致性和并发性的常用选择。REPEATABLE READ是MySQL InnoDB的默认级别，能防止不可重复读和幻读（通过MVCC和间隙锁）。
3. 在复杂的业务逻辑中，注意处理死锁。InnoDB能自动检测死锁并回滚其中一个事务。可以通过SHOW ENGINE INNODB STATUS查看最近的死锁信息。

5.2 锁信息查看

-- 1. 查看当前InnoDB锁的状态（信息较全面） SHOW ENGINE INNODB STATUS\G -- 重点关注输出中 `LATEST DETECTED DEADLOCK` 和 `TRANSACTIONS` 部分。 -- 2. 通过系统表查看锁信息（MySQL 8.0+ 更清晰） SELECT * FROM performance_schema.data_locks; -- 显示持有的锁 SELECT * FROM performance_schema.data_lock_waits; -- 显示锁等待关系 -- 3. 查看元数据锁（MDL）信息。DDL操作和长时间未提交的事务会阻塞MDL。 SELECT * FROM performance_schema.metadata_locks;

锁排查流程：当发现SQL长时间不执行时：

1. SHOW PROCESSLIST找到阻塞的会话，看其State。
2. 如果是Waiting for table metadata lock，检查performance_schema.metadata_locks和是否有未提交的DDL或长事务。
3. 如果是Waiting for row lock，检查performance_schema.data_locks和data_lock_waits找到锁的持有者和等待者。

5.3 主从复制管理

-- 在主库上操作 -- 1. 查看主库状态，获取File和Position（基于二进制日志的复制） SHOW MASTER STATUS; -- 2. 创建用于复制的用户 CREATE USER 'repl'@'slave_host_ip' IDENTIFIED BY 'ReplPassword123!'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_host_ip'; -- 在从库上操作 -- 3. 配置从库连接主库 CHANGE MASTER TO MASTER_HOST='master_host_ip', MASTER_USER='repl', MASTER_PASSWORD='ReplPassword123!', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', -- 来自主库的SHOW MASTER STATUS MASTER_LOG_POS=154; -- 来自主库的SHOW MASTER STATUS -- 4. 启动从库复制线程 START SLAVE; -- MySQL 8.0+ 推荐使用 START REPLICA; -- 5. 查看从库复制状态 SHOW SLAVE STATUS\G -- MySQL 8.0+ 推荐使用 SHOW REPLICA STATUS\G

关键状态监控（SHOW REPLICA STATUS输出中的列）：

• Replica_IO_Running和Replica_SQL_Running：必须都为Yes，表示IO线程和SQL线程运行正常。
• Seconds_Behind_Master：从库延迟秒数。为0表示完全同步，NULL通常表示复制线程未运行。
• Last_IO_Error/Last_SQL_Error：最近的错误信息。
• Relay_Log_File/Exec_Master_Log_Pos：从库当前执行到的中继日志位置。

复制问题处理：

• 跳过错误：如果从库因某个SQL错误停止（如重复键），可以临时跳过（谨慎！需确保数据一致性可接受）：

  STOP REPLICA; SET GLOBAL sql_replica_skip_counter = 1; -- 跳过1个事件 START REPLICA;

• 重新同步：如果主从数据不一致严重，可能需要重建从库：在主库做完整备份，在从库恢复，然后重新配置复制点位。

6. 日常维护与监控脚本示例

将常用命令封装成SQL脚本或Shell脚本，能极大提升运维效率。

6.1 常用信息查询脚本

可以创建一个名为daily_check.sql的文件，内容如下：

-- 每日健康检查脚本 SELECT '=== 1. 数据库版本与运行时间 ===' AS ''; SELECT VERSION() AS `版本`, NOW() AS `当前时间`, UPTIME AS `运行时间` FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Uptime'; SELECT '=== 2. 连接数统计 ===' AS ''; SHOW STATUS LIKE 'Threads_%'; SHOW VARIABLES LIKE 'max_connections'; SELECT '=== 3. 缓冲池命中率 ===' AS ''; SELECT (1 - Variable_value / ( SELECT Variable_value FROM information_schema.GLOBAL_STATUS WHERE Variable_name = 'Innodb_buffer_pool_read_requests' )) * 100 AS `缓冲池命中率(%)` FROM information_schema.GLOBAL_STATUS WHERE Variable_name = 'Innodb_buffer_pool_reads'; SELECT '=== 4. 慢查询与表锁情况 ===' AS ''; SHOW GLOBAL STATUS LIKE 'Slow_queries'; SHOW GLOBAL STATUS LIKE 'Table_locks_%'; SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%'; SELECT '=== 5. 数据库大小排名（前10） ===' AS ''; SELECT table_schema AS `数据库`, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `大小(MB)` FROM information_schema.tables GROUP BY table_schema ORDER BY `大小(MB)` DESC LIMIT 10; SELECT '=== 6. 最近1小时未使用的索引（示例，需根据实际情况调整） ===' AS ''; -- 此查询依赖于performance_schema，需要先开启相关consumer -- SELECT * FROM sys.schema_unused_indexes WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema');

在命令行执行：mysql -u root -p -A < daily_check.sql。

6.2 自动化备份与清理脚本示例（Shell）

一个简单的备份脚本backup_mysql.sh：

#!/bin/bash # 定义变量 BACKUP_DIR="/data/backups/mysql" DATE=$(date +%Y%m%d_%H%M%S) DB_USER="backup_user" DB_PASS="your_secure_password" LOG_FILE="/var/log/mysql_backup.log" # 创建备份目录 mkdir -p $BACKUP_DIR # 执行全量备份 echo "[$DATE] Starting full backup..." >> $LOG_FILE mysqldump -u$DB_USER -p$DB_PASS --all-databases --single-transaction --routines --triggers --events --flush-logs --master-data=2 | gzip > $BACKUP_DIR/full_backup_$DATE.sql.gz 2>> $LOG_FILE if [ $? -eq 0 ]; then echo "[$DATE] Full backup completed successfully." >> $LOG_FILE # 清理7天前的备份 find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete >> $LOG_FILE 2>&1 echo "[$DATE] Old backups cleaned up." >> $LOG_FILE else echo "[$DATE] ERROR: Full backup failed!" >> $LOG_FILE # 可以在这里添加邮件或钉钉告警 fi

然后通过crontab设置定时任务：0 2 * * * /path/to/backup_mysql.sh（每天凌晨2点执行）。

6.3 性能问题快速排查清单

当收到数据库慢的告警时，可以按以下顺序快速排查：

1. 连接风暴：SHOW PROCESSLIST;查看是否有大量连接，SHOW STATUS LIKE 'Threads_%';确认连接数是否接近max_connections。
2. CPU/IO瓶颈：在服务器上使用top,iostat等命令。在MySQL内，SHOW ENGINE INNODB STATUS查看SEMAPHORES部分，如果有很多线程等待信号量，可能是IO或CPU瓶颈。
3. 慢查询：检查SHOW STATUS LIKE 'Slow_queries';是否在增长。查看慢查询日志（SHOW VARIABLES LIKE 'slow_query_log%';）。
4. 锁竞争：SHOW ENGINE INNODB STATUS查看TRANSACTIONS，SELECT * FROM performance_schema.data_lock_waits;查看锁等待。
5. 复制延迟：在从库执行SHOW REPLICA STATUS\G查看Seconds_Behind_Master。

这份命令大全就像工具箱里的扳手和螺丝刀，熟悉它们每个的用途和用法，才能在数据库出现问题时迅速定位、手到病除。真正的熟练，来自于在无数次故障排查和性能优化中的实际应用。建议你根据自己的工作环境，将最常用的命令组合保存成脚本或笔记，形成你自己的“肌肉记忆”。