mysql 客户端 —— 交互查询与批量执行

分类：08 数据库工具 适用：MySQL 5.7 / 8.0 / 8.4，MariaDB 10.x 兼容大部分 一句话：连库、查状态、看锁、导数据、批量执行 SQL，DBA 每天都在用。

1. 它解决什么问题

• 连上数据库看当前连接、慢查询、锁等待。
• 一条命令执行 SQL 并输出到文件（脚本化）。
• 排查"连接数满了""表被锁了""复制延迟了"。
• 导出查询结果给业务方。

2. 连接方式

# 基本
mysql -h 10.0.0.5 -P 3306 -u app -p
mysql -h 10.0.0.5 -u app -p'密码'          # 密码紧跟 -p，无空格
mysql -u root -p -S /var/lib/mysql/mysql.sock    # 本地 socket

# 指定库与字符集
mysql -h host -u app -p --default-character-set=utf8mb4 mydb

# 执行单条命令
mysql -u app -p -e "select version(), now();"

# 执行 SQL 文件
mysql -u app -p mydb < init.sql
mysql -u app -p mydb -e "source /tmp/patch.sql"

# 输出到文件（制表符分隔）
mysql -u app -p -e "select * from t" mydb > /tmp/t.tsv

密码不要写在命令行：会出现在 ps、shell history、审计日志里。

推荐用配置文件：

cat > ~/.my.cnf <<'EOF'
[client]
user=app
password=YourPass
host=10.0.0.5
default-character-set=utf8mb4
EOF
chmod 600 ~/.my.cnf

mysql mydb            # 自动读取

或临时文件：

mysql --defaults-extra-file=/tmp/my.cnf mydb

3. 交互模式常用命令

竖排显示：

select * from information_schema.processlist where id = 123\G

4. 运维必查

4.1 当前连接

show processlist;
show full processlist;                  -- 不截断 SQL
select * from information_schema.processlist where time > 60 order by time desc;
select user, host, count(*) from information_schema.processlist group by user, host order by 3 desc;

4.2 锁与阻塞

-- 8.0
select * from performance_schema.data_locks;
select * from performance_schema.data_lock_waits;

-- 5.7
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_locks;

-- 立刻看谁在等谁
select
  r.trx_id waiting_trx, r.trx_mysql_thread_id waiting_thread,
  b.trx_id blocking_trx, b.trx_mysql_thread_id blocking_thread,
  r.trx_query waiting_query, b.trx_query blocking_query
from information_schema.innodb_lock_waits w
join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id
join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id;

show engine innodb status\G

重点看 LATEST DETECTED DEADLOCK 与 TRANSACTIONS 段的 LOCK WAIT。

4.3 容量与状态

-- 库表大小
select table_schema, round(sum(data_length+index_length)/1024/1024,2) as mb
from information_schema.tables group by table_schema order by mb desc;

-- 连接数与上限
show variables like 'max_connections';
show status like 'Threads_connected';
show status like 'Threads_running';
show status like 'Max_used_connections';

-- QPS/TPS 粗算（间隔取两次）
show global status like 'Questions';
show global status like 'Com_select';
show global status like 'Com_insert';
show global status like 'Com_update';
show global status like 'Com_delete';

-- 慢查询
show variables like 'slow_query_log%';
show variables like 'long_query_time';

4.4 复制（主从）

show master status\G           -- 8.0.22+ 用 show binary log status
show slave status\G            -- 8.0.22+ 用 show replica status
show replica status\G

关注 Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running、Last_Error。

5. 执行与格式化

# 批量执行
mysql -u app -p mydb < patch.sql

# 多条语句（用 -e 时以分号分隔）
mysql -u app -p -e "select 1; select 2;"

# 输出不带头部、制表符分隔（便于 awk）
mysql -u app -p -N -B -e "select id,name from t" mydb

# 输出 HTML / XML
mysql -u app -p --html -e "select * from t" mydb > t.html
mysql -u app -p --xml  -e "select * from t" mydb > t.xml

# 安全模式：禁止无 where 的 update/delete
mysql -u app -p --safe-updates mydb

-N 去表头、-B 批处理（Tab 分隔），这两个组合是脚本处理的标配。

6. 权限与账号

-- 看当前用户
select current_user(), user();

-- 看权限
show grants;
show grants for 'app'@'10.0.0.%';

-- 建只读账号
create user 'ro'@'10.0.0.%' identified by 'StrongPass!';
grant select on mydb.* to 'ro'@'10.0.0.%';
flush privileges;

-- 锁定/解锁
alter user 'app'@'%' account lock;
alter user 'app'@'%' account unlock;

-- 改密码
alter user 'app'@'%' identified by 'NewPass!';

7. 实战场景

7.1 连接数打满应急

show variables like 'max_connections';
show status like 'Threads_connected';
show status like 'Threads_running';

-- 找出长时间 Sleep 的连接
select id, user, host, db, command, time, state
from information_schema.processlist
where command = 'Sleep' and time > 600
order by time desc;

-- 批量生成 kill 语句（先看再执行）
select concat('kill ', id, ';') from information_schema.processlist
where command = 'Sleep' and time > 600;

排查方向：连接池配置、应用未关闭连接、wait_timeout 过长。

7.2 表被元数据锁卡住

现象：一个 ALTER TABLE 卡住，后续所有查询排队。

select * from performance_schema.metadata_locks;
select * from sys.schema_table_lock_waits\G      -- 8.0 的 sys 视图最好用

处理：找到阻塞的长时间事务，kill 该连接，或等事务结束。不要直接 kill ALTER，先确认没有大事务在跑。

7.3 导出查询结果给业务

mysql -u ro -p -N -B -e "
select id, mobile, created_at from users
where created_at >= '2026-11-01'
" appdb > /tmp/users.tsv

敏感字段要脱敏后再给。

7.4 检查字符集与时区

show variables like 'character_set%';
show variables like 'collation%';
show variables like 'time_zone';
select @@system_time_zone, now();

跨时区业务常见"时间差 8 小时"，根源是 time_zone 与驱动配置不一致。

7.5 一条命令健康检查

mysql -u monitor -p -N -B -e "
select
 (select count(*) from information_schema.processlist) as conns,
 (select variable_value from performance_schema.global_status where variable_name='Threads_running') as running,
 (select variable_value from performance_schema.global_status where variable_name='Uptime') as uptime;
"

8. 常见坑

1. -p 后面加空格：-p 密码 会把密码当成库名，正确是 -p密码 或交互输入。
2. 密码进 shell history：用 ~/.my.cnf 或 MYSQL_PWD（也不安全，但比命令行好）。
3. show processlist 截断 SQL：用 show full processlist 或查 information_schema。
4. \G 只作用于当前语句，写多条时要分别加。
5. source 路径是客户端本地，不是服务器路径。
6. -e 里含 ; 会被 shell 解释，SQL 要用引号包住。
7. 字符集不一致导致乱码：连接、库、表、列四级，用 show variables like 'character_set%' 逐个查。
8. kill 只是发信号：大事务回滚可能耗时很久，kill 后连接仍显示 Killed 状态。
9. 5.7 与 8.0 的视图名不同：innodb_locks 在 8.0 已移除，改 performance_schema.data_locks。
10. --safe-updates 需要 where 里用索引列，否则仍会拒绝。
11. socket 与 TCP 连接权限不同：'user'@'localhost'（socket）与 'user'@'127.0.0.1'（TCP）是两条账号。
12. flush privileges 不是必须的：用 grant/create user 语句时自动生效，只有直接改 mysql.user 表才需要。
13. 客户端版本过老：连 8.0 可能报 caching_sha2_password 认证失败，升级客户端或改认证插件（不推荐降低安全性）。

9. 速查表

mysql -h <host> -P 3306 -u <user> -p [db]
mysql --defaults-extra-file=~/.my.cnf db
mysql -e "SQL" db
mysql db < file.sql
mysql -N -B -e "SQL" db > out.tsv

# 交互
status / \s
show full processlist;
show engine innodb status\G
show variables like 'max_connections';
show status like 'Threads_%';
select * from performance_schema.data_lock_waits;
show replica status\G
kill <id>;

核心原则：密码不进命令行；先 select 再 kill；线上改数据先备份。