mysql常用命令整理
# 连接mysql server
mysql -u${user} -p${password} -h${host} -P${port} -D${dbname}
1
# DCL
-- 创建用户
create user 'myuser'@'192.168.1.%' identified by 'mypwd';
-- 创建数据库
create database mydb;
create database mydb default character set utf8 collate utf8_bin;
create database mydb default character set utf8mb4 collate utf8mb4_bin;
-- 将mydb所有表的所以权限授权给myuser
grant all on mydb.* to 'myuser'@'192.168.1.%';
-- 只授权部分权限，其他权限还有：create、alter、drop、create view等
grant select, insert, update, delete, index on mydb.* to 'myuser'@'192.168.1.%';
-- 撤销用户权限
revoke all privileges on *.* from 'myuser'@'192.168.1.%';
-- 撤销指定库的指定权限
revoke create, alter, drop on mydb.* from 'myuser'@'192.168.1.%';
-- 刷新权限配置
flush privileges;
-- 查看当前用户权限
show grants;
-- 查看其他用户权限
show grants for 'myuser'@'192.168.1.%';
-- 查看拥有权限的db
show databases;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# DDL
-- 创建表
create table if not exists `tb` (
`id` bigint auto_increment primary key,
`c1` varchar(64) not null default '' comment 'c1注释',
`c2` int(11) not null default 0 comment 'c2注释',
`c3` datetime comment 'c3注释'
) engine=INNODB default charset=utf8mb4;
-- 删除表
drop table tb;
-- 修改字段
alter table tb modify c1 varchar(255);
-- 增加字段
alter table tb add column c4 tinyint(4) after c3;
-- 删除字段
alter table tb drop column c4
-- 给字段加索引
alter table tb add index idx_c1 (c1);
alter table tb add index idx_c1_c2 (c1, c2);
alter table tb add unique idx_uni_c2 (c2);
alter table tb add primary key idx_primary_key (c1);
-- 另一种创建索引方式
create index idx_c1 ON tb (c1)
create unique index idx_uni_c2 ON tb (c2)
-- 删除索引
alter table tb drop index idx_c1;
alter table tb drop primary key
drop index idx_c1 ON tb;
-- 查看索引
show index from tblname;
-- 查看表定义语句
show create table tb;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 查看表锁情况
show status like '%table_name%';
show open tables where In_use > 0;
1
2
2
# INFORMATION_SCHEMA
# 查看所有非系统表（超过200MB）占用磁盘空间情况
-- data_pct: 数据占用百分比，index_pct: 索引占用百分比, gap_pct: 空闲空间占用百分比, data_free_MB: 空闲空间占用大小, total_size_MB: 总数据大小
select TABLE_SCHEMA,
TABLE_NAME,
DATA_LENGTH / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) as data_pct,
INDEX_LENGTH / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) as index_pct,
DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) as gap_pct,
DATA_FREE / 1024 / 1024 as data_free_MB,
(DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / 1024 / 1024 as total_size_MB
from INFORMATION_SCHEMA.TABLES
where table_schema not in ('mysql', 'sys', 'performance_schema', 'information_schema')
and (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / 1024 / 1024 > 200
order by gap_pct desc
limit 20;
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 查找持续时间超过 60s 的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
1
# 查看mysql进程会话情况
-- 查看会话列表，与select * from information_schema.processlist等价
show processlist;
-- 通过processId查询会话内容
select * from information_schema.processlist where id = ${processId}
-- 导出长时间执行sql的kill语句（如果线上出现大量慢查询，一个个kill很麻烦，可以导出批量kill语句一次执行）
mysql -h$host -P$port -u$user -p$password -Ne 'select concat("kill ",id,";") from information_schema.processlist where COMMAND="Query" and TIME > {时间阈值} and STATE="Sending data";' > kill.sql
-- 上面导出kill语句的sql，可以根据实际情况调整sql语句（示例是：查询执行时间超过5分钟的sql）
select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;
-- 查询正在执行的会话，按执行时长倒序，查看执行时间前10的sql
select * from information_schema.processlist where Command != 'Sleep' order by Time desc limit 10;
-- 查询连接会话最多的客户端ip
select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;
-- 根据id kill掉会话
kill ${processId}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
information_schema.processlist表的Command字段含义
| 值 | 含义 | 说明 |
|---|---|---|
| Binlog Dump | 主节点正在将二进制日志，同步到从节点 | - |
| Change User | 正在执行一个 change-user 的操作 | - |
| Close Stmt | 正在关闭一个Prepared Statement 对象 | - |
| Connect | 一个从节点连上了主节点 | - |
| Connect Out | 一个从节点正在连主节点 | - |
| Create DB | 正在执行一个create-database 的操作 | - |
| Daemon | 服务器内部线程，而不是来自客户端的链接 | - |
| Debug | 线程正在生成调试信息 | - |
| Delayed Insert | 该线程是一个延迟插入的处理程序 | - |
| Drop DB | 正在执行一个 drop-database 的操作 | - |
| Execute | 正在执行一个 Prepared Statement | - |
| Fetch | 正在从Prepared Statement 中获取执行结果 | - |
| Field List | 正在获取表的列信息 | - |
| Init DB | 该线程正在选取一个默认的数据库 | - |
| Kill | 正在执行 kill 语句，杀死指定线程 | - |
| Long Data | 正在从Prepared Statement 中检索 long data | - |
| Ping | 正在处理 server-ping 的请求 | - |
| Prepare | 该线程正在准备一个 Prepared Statement | - |
| ProcessList | 该线程正在生成服务器线程相关信息 | - |
| Query | 该线程正在执行一个语句 | - |
| Quit | 该线程正在退出 | - |
| Refresh | 该线程正在刷表，日志或缓存；或者在重置状态变量，或者在复制服务器信息 | - |
| Register Slave | 正在注册从节点 | - |
| Reset Stmt | 正在重置 prepared statement | - |
| Set Option | 正在设置或重置客户端的 statement-execution 选项 | - |
| Shutdown | 正在关闭服务器 | - |
| Sleep | 正在等待客户端向它发送执行语句 | - |
| Statistics | 该线程正在生成 server-status 信息 | - |
| Table Dump | 正在发送表的内容到从服务器 | - |
| Time | Unused | - |
# 查看系统参数
show variables like 'innodb_file_per_table';
-- 等价
select @@innodb_file_per_table
1
2
3
2
3
# 在 shell 脚本中执行 mysql 命令
- 使用
-e参数
mysql -h${host} -P${port} -u${user} -p${password} -e "
-- sql
quit"
# 或者
mysql -h${host} -P${port} -u${user} -p${password} -e "source /path/to/file.sql"
1
2
3
4
5
6
2
3
4
5
6
- 通过管道
mysql -h${host} -P${port} -u${user} -p${password} < /path/to/file.sql > /path/to/file.log
-- 导出 sql 查询结果到 csv
mysql -h${host} -P${port} -u${user} -p${password} < /path/to/file.sql | sed 's/\t/,/g' > out.csv
1
2
3
4
2
3
4
- 通过
EOF文本结束符
mysql -h${host} -P${port} -u${user} -p${password} <<EOF
-- sql
EOF
exit;
1
2
3
4
2
3
4