mysql常用命令

连接mysql server

mysql -u${user} -p${password} -h${host} -P${port} -D${dbname}

DCL

# 创建用户
create user 'myuser'@'192.168.1.%' identified by 'mypwd';

# 创建数据库
create database mydb;

# 将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;

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;

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', 'dumbo')
  and (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / 1024 / 1024 > 200
order by gap_pct desc
LIMIT 20;

查看mysql进程会话情况

-- 查看会话列表
show processlist;

-- 通过processId查询会话内容
select * from information_schema.processlist where id = ${processId}

mysql -Ne 'select concat("kill ",id,";") from information_schema.processlist where OMMAND="Query", TIME > {时间阈值}, STATE="Sending data";' > kill.sql

-- 查询正在执行的会话,按执行时间倒序,查看执行时间较长的sql
select * from information_schema.processlist where Command != 'Sleep' order by Time desc;

-- 查询执行时间超过5分钟的sql
select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

-- 查询连接会话最多的客户端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}

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';