连接与身份验证

这是所有操作的基础。

# 1. 基本连接
mysql -h <主机名> -P <端口号> -u <用户名> -p
# 示例：连接到本地 3306 端口的 MySQL，用户为 root
mysql -h localhost -P 3306 -u root -p
# 2. 连接到特定数据库
mysql -h <主机名> -P <端口号> -u <用户名> -p <数据库名>
# 示例：连接到 testdb 数据库
mysql -h localhost -P 3306 -u root -p testdb
# 3. 直接在命令行执行 SQL（不进入交互模式）
mysql -h <主机名> -P <端口号> -u <用户名> -p -e "<SQL语句>"
# 示例：查看所有数据库
mysql -h localhost -P 3306 -u root -p -e "SHOW DATABASES;"
# 4. 使用配置文件连接（推荐，避免在命令行暴露密码）
# 在 ~/.my.cnf 或 /etc/my.cnf 中配置
[client]
host = localhost
port = 3306
user = root
password = your_password
# 然后可以直接连接
mysql

服务器状态与监控

了解服务器的健康状况是运维的核心。

-- 1. 查看服务器基本信息
SELECT version(); -- MySQL 版本
SHOW VARIABLES; -- 查看所有系统变量
SHOW STATUS; -- 查看所有状态变量
-- 2. 关键性能指标（非常重要）
-- 查看服务器运行了多长时间（秒）
SHOW GLOBAL STATUS LIKE 'Uptime';
-- 查询连接数
SHOW GLOBAL STATUS LIKE 'Threads_connected'; -- 当前连接数
SHOW GLOBAL STATUS LIKE 'Max_used_connections'; -- 历史最大连接数
SHOW VARIABLES LIKE 'max_connections'; -- 最大连接数配置
-- 查看慢查询
SHOW GLOBAL STATUS LIKE 'Slow_queries';
-- 查看 InnoDB 引擎状态（核心）
SHOW ENGINE INNODB STATUS;
-- 3. 查看正在执行的线程（监控当前活动）
SHOW PROCESSLIST; -- 查看所有连接
SHOW FULL PROCESSLIST; -- 查看更完整的信息
-- 找出消耗资源的 SQL
-- 按查询时间排序
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;
-- 4. 查看锁信息
-- 查看当前正在锁定的表
SHOW OPEN TABLES WHERE In_use > 0;
-- 查看引擎锁状态
SHOW ENGINE INNODB STATUS;
-- 在输出中找到 "LATEST DETECTED DEADLOCK" 或 "TRANSACTIONS" 部分
-- 5. 查看二进制日志（用于数据恢复和主从复制）
SHOW BINARY LOGS; -- 列出所有二进制日志文件
SHOW MASTER STATUS; -- 查看当前正在写入的二进制日志信息
SHOW BINLOG EVENTS IN 'mysql-bin.000001'; -- 查看某个日志文件的事件

数据库与表管理

日常维护的基础操作。

-- 1. 数据库操作
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
DROP DATABASE mydb;
SHOW DATABASES;
-- 2. 表操作
USE mydb;
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));
DROP TABLE users;
SHOW TABLES;
DESCRIBE users; -- 或 SHOW COLUMNS FROM users;
-- 3. 表维护与优化
-- 分析表，更新索引统计信息，优化查询
ANALYZE TABLE users;
-- 修复表（修复已损坏的表）
REPAIR TABLE users;
-- 优化表，消除碎片，提高性能
OPTIMIZE TABLE users;
-- 检查表是否有错误
CHECK TABLE users;

用户与权限管理

确保数据库安全的关键。

-- 1. 创建用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';
-- 2. 授权
-- 授予 app_user 对 mydb 数据库的所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'%';
-- 授予 app_user 只读权限
GRANT SELECT ON mydb.* TO 'app_user'@'%';
-- 3. 刷新权限（修改权限后必须执行）
FLUSH PRIVILEGES;
-- 4. 查看用户权限
SHOW GRANTS FOR 'app_user'@'%';
-- 5. 撤销权限
REVOKE ALL PRIVILEGES ON mydb.* FROM 'app_user'@'%';
-- 6. 删除用户
DROP USER 'app_user'@'%';

备份与恢复

运维工作的重中之重,防止数据丢失。

使用 mysqldump 进行逻辑备份

# 备份单个数据库
mysqldump -u <用户名> -p <数据库名> > backup.sql
# 备份多个数据库
mysqldump -u <用户名> -p --databases db1 db2 > backup.sql
# 备份所有数据库
mysqldump -u <用户名> -p --all-databases > all_backup.sql
# 只备份数据结构（不包含数据）
mysqldump -u <用户名> -p --no-data <数据库名> > schema.sql
# 只备份数据（不包含结构）
mysqldump -u <用户名> -p --no-create-info <数据库名> > data.sql
# 备份时压缩（推荐，节省空间）
mysqldump -u <用户名> -p <数据库名> | gzip > backup.sql.gz
# 排除某些表
mysqldump -u <用户名> -p <数据库名> --ignore-table=<数据库名>.big_table > backup.sql

使用 mysql 命令恢复

# 从备份文件恢复数据库（注意：会覆盖现有数据）
mysql -u <用户名> -p <数据库名> < backup.sql
# 如果备份文件包含 `CREATE DATABASE` 语句，则不需要指定数据库名
mysql -u <用户名> -p < all_backup.sql

使用 mydumper / myloader (多线程，速度快)

mydumper 是 mysqldump 的高性能替代品,适合大数据量。

# 安装 (CentOS)
# yum install mydumper myloader
# 备份
mydumper -u root -p -B mydb -o /path/to/backup
# 恢复
myloader -u root -p -B mydb -d /path/to/backup

主从复制管理

保证高可用和读写分离。

-- 在主库上执行
-- 1. 创建用于复制的用户
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;
-- 2. 查看主库状态（记录 File 和 Position）
SHOW MASTER STATUS;
-- 在从库上执行
-- 1. 停止复制
STOP SLAVE;
-- 2. 配置主库信息
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='master_host_ip',
  SOURCE_PORT=3306,
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='repl_password',
  SOURCE_LOG_FILE='mysql-bin.000001', -- 从主库 SHOW MASTER STATUS 获取
  SOURCE_LOG_POS=154;                  -- 从主库 SHOW MASTER STATUS 获取
-- 3. 启动复制
START SLAVE;
-- 4. 查看复制状态（关键！）
SHOW SLAVE STATUS\G
-- 关注以下两个状态是否为 "Yes"：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- 如果不是，检查 Last_IO_Error 和 Last_SQL_Error 错误信息。

性能调优与分析

定位并解决性能瓶颈。

-- 1. 慢查询日志
-- 启用慢查询日志（临时，重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 设置超过1秒的查询为慢查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
-- 永久生效，需要修改 my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
-- 分析慢查询日志
mysqldumpslow -s t /var/log/mysql/mysql-slow.log -- 按时间排序，显示最慢的查询
pt-query-digest /var/log/mysql/mysql-slow.log -- 使用 Percona Toolkit 分析（更强大）
-- 2. 使用 EXPLAIN 分析查询计划
EXPLAIN SELECT * FROM users WHERE name = 'John';
-- 关注关键字段：
-- type: 访问类型，从最好到最差 system > const > eq_ref > ref > range > index > ALL
-- key: 实际使用的索引
-- rows: 预估需要扫描的行数
-- Extra: 额外信息，如 Using filesort (需要额外排序), Using temporary (需要临时表)，这些是需要优化的点。
-- 3. 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes; -- 查看未被使用的索引（可能需要安装 sys 库）

系统维护与日志管理

# 1. 重启/停止/启动 MySQL 服务
sudo systemctl restart mysqld
sudo systemctl stop mysqld
sudo systemctl start mysqld
# 2. 查看错误日志
tail -f /var/log/mysql/error.log
# 或者使用 mysqladmin
mysqladmin -u root -p status
mysqladmin -u root -p variables
# 3. 清理二进制日志（释放磁盘空间）
-- 在主库上执行
RESET MASTER; -- 删除所有二进制日志（危险！）
PURGE BINARY LOGS TO 'mysql-bin.000010'; -- 删除 mysql-bin.000010 之前的所有日志
PURGE BINARY LOGS BEFORE '2025-01-01 00:00:00'; -- 删除指定时间之前的日志

掌握这些命令是成为一名合格 MySQL 运维工程师的基础，在实际工作中,还需要结合具体的业务场景和服务器环境进行灵活运用。