铁匠 铁匠
首页
收藏
java
架构之路
常用算法
  • Java
  • nginx
  • 系统运维
  • 系统安全
  • mysql
  • redis
参考文档
关于
链接
  • 分类
  • 标签
  • 归档

专注、不予评判地关注当下
首页
收藏
java
架构之路
常用算法
  • Java
  • nginx
  • 系统运维
  • 系统安全
  • mysql
  • redis
参考文档
关于
链接
  • 分类
  • 标签
  • 归档
  • mysql

    • MySQL-InnoDB 存储引擎概述
    • MySql 索引
    • MySql 事务
    • MySql 日志系统(WAL)
    • MySql order by 的执行过程与优化
    • MySQL 性能分析与常见性能问题总结
    • mysql推荐使用规范
    • MySQL 常用配置整理
    • mysql常用命令整理
      • 连接mysql server
      • DCL
      • DDL
      • 查看表锁情况
      • INFORMATION_SCHEMA
        • 查看所有非系统表(超过200MB)占用磁盘空间情况
        • 查找持续时间超过 60s 的事务
      • 查看mysql进程会话情况
      • 查看系统参数
      • 在 shell 脚本中执行 mysql 命令
    • mycli-强大的MySQL命令行客户端
  • redis

  • 数据库
  • mysql
FengJianxin
2019-11-10
目录

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

# 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

# 查看表锁情况

show status like '%table_name%';
show open tables where In_use > 0;
1
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

# 查找持续时间超过 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

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

# 在 shell 脚本中执行 mysql 命令

  1. 使用 -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
  1. 通过管道
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
  1. 通过 EOF 文本结束符
mysql -h${host} -P${port} -u${user} -p${password} <<EOF
-- sql
EOF
exit;
1
2
3
4
#mysql#手册
MySQL 常用配置整理
mycli-强大的MySQL命令行客户端

← MySQL 常用配置整理 mycli-强大的MySQL命令行客户端→

最近更新
01
策略模式
01-09
02
模板方法
01-06
03
观察者模式
01-06
更多文章>
Theme by Vdoing | Copyright © 2016-2023 铁匠 | 粤ICP备15021633号
  • 跟随系统
  • 浅色模式
  • 深色模式
  • 阅读模式