连接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;
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;

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;

导出、导入

# master-data: binlog 位点信息写入注释
# single-transaction: 不会锁表
mysqldump -uroot -ppassword -hlocalhost -P3306 --master-data=2 --single-transaction --skip-tz-utc --all-databases > /path/to/db.sql

查看表锁情况

show status like '%table_name%';
show open tables where In_use > 0;

INFORMATION_SCHEMA

查看所有非系统表（超过200MB）占用磁盘空间情况

-- data_pct: 数据占用百分比，index_pct: 索引占用百分比, gap_pct: 空闲空间占用百分比, data_free_MB: 空闲空间占用大小, total_size_MB: 总数据大小
select TABLE_SCHEMA,
       TABLE_NAME,
       TABLE_ROWS,
       DATA_LENGTH / 1024 / 1024 as data_MB,
       DATA_LENGTH / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)  as data_pct,
       INDEX_LENGTH / 1024 / 1024 as index_MB,
       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 total_size_MB desc
limit 20;

查找持续时间超过 60s 的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

查看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}

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

binlog 相关

-- 查看正在写入的 binlog 信息
show master status

-- 查看 binlog 文件列表
show binary logs

-- binlog 相关参数

-- log_bin: 是否开启 binlog 
how variables like '%log_bin%';

-- 格式
show variables like '%binlog_format%';

-- binlog_row_image 记录数据修改前后内容
how variables like 'binlog_row_image';

在 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"

2. 通过管道

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

3. 通过 EOF 文本结束符

mysql -h${host} -P${port} -u${user} -p${password} <<EOF
-- sql
EOF
exit;