Raven's Wiki

清风皓月任我飞~~~

0%

mysql-cmd-dcl

发表于 更新于

DCL, Data Control Language, 数据控制语言
用于控制数据权限

  • Grant
  • Revoke

1. 创建新用户

1
2
3
4
5
6
7
create user root@'%' identified WITH mysql_native_password BY 'password';
grant all privileges on *.* to root@'%' with grant option;
flush privileges;

CREATE USER 'raven'@localhost IDENTIFIED BY 'password'
GRANT ALL PRIVILEGES ON *.* TO 'raven'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
flush privileges;

2. 修改用户

1
2
3
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root' PASSWORD EXPIRE NEVER; #修改加密规则
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; #更新一下用户的密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; #更新一下用户的密码

或者使用 mysqladmin:

1
/usr/bin/mysqladmin -u root password new-password

3. 查看支持的引擎

1
show engine;

4. 查看 MySQL 参数

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
37
38
39
40
41
42
-- 是否支持分区
-- have_partintioning 的值为YES, 表示支持分区. 
show variables like '%partition%';
-- 查看最大连接数
show variables like '%max_connections%';
-- 查看字符集
show variables like 'collation_%';
show variables like 'character_set_%';
--  查看缓存信息
show variables like '%cache%';
-- 清除缓存
reset query cache;

-- 查看状态变量
show status;
show global status;

-- com_select: 查询次数
-- com_insert: 插入次数
-- com_update: 更新次数
-- com_delete: 删除次数
-- connections: 登录次数
-- uptime: 运行时长
-- slow_queries: 慢查询次数 
-- handler_read%: 索引使用情况. handler_read_key: 这个值越高越好, 越高表示使用索引查询到的次数. handler_read_rnd_next: 这个值越高, 说明查询低效.
-- Created_tmp%: 统计临时文件和表
-- Handler_%: 统计句柄操作
-- Key_%: 统计键缓存
-- Qcache_%: 检查查询缓存
-- Select_%: 统计各种查询计划
-- Sort_%: 统计各种排序
show global status like 'Created_tmp%';

-- 查看事务隔离级别
select @@tx_isolation;

-- @isolation_level 可以是 read uncommitted | read committed | repeatable read | serializable
-- 设置当前回话的事务隔离级别
set session transaction isolation level @isolation_level;

-- 设置全局事务隔离级别
set global transaction isolation level serializable;

5. 查看连接列表

1
2
show processlist;
-- 通过 kill pid 杀死连接
1
mysql -e 'show processlist\G' | grep 'State:' | sort | uniq -c | sort -rn

6. explain

1
2
3
explain @sql;
-- 查询要用到的分区
explain partitions @sql;

7. 查看表状态

1
2
3
-- 查看表状态
show table status like 'table_name'
show [session|global] status like @status_name;

8. 查看实时 sql 记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 日志开启
set global general_log = 'ON';
-- 日志关闭  
-- set global general_log = 'OFF';

-- 将查询输出到文件中
-- 这个文件会随着访问的增加而不断变大, 所以生产环境建议临时开启, 用完及时关闭. 
set global general_log_file = '/var/log/mysql/general_log.log';

-- 将查询输出到表中
set global log_output = 'TABLE';
-- 查询
select * from mysql.general_log order by event_time desc;

-- 使用完后关闭日志
set global general_log = 'OFF';
-- 清空表 (delete 对于这个表, 不允许使用, 只能用 truncate) 
truncate table mysql.general_log;

9. 查看慢查询

1
2
3
4
5
6
7
8
9
10
11
12
-- 查看慢查询是否开始, 存储文件位置
show variables like 'slow_query%';
-- 查询超过多少秒才记录
show variables like 'long_query_time';

-- 开启慢查询
set global slow_query_log='ON'; 
-- 设置慢查询时间, 默认为 10 秒
set long_query_time = 1;

-- 测试慢查询, 之后可以在慢查询日志文件中找到
select sleep(2)

10. 分析 SQL 执行时间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
show variables like 'PROFILING';
set profiling ='ON';
show profiles;
set profiling ='OFF';

set @query_id=1;
select
    state,
    sum(duration) as total_r,
    ROUND(
        100 * sum(duration) / (
            select sum(duration)
            from information_schema.profiling
            where query_id = @quer_id
        ), 2
    ) as pct_r,
    count(*) as calls,
    sum(duration) / count(*) as "r/call"
from information_schema.profiling
where query_id = @query_id
group by state
order by total_r desc;

11. 表结构优化建议

1
2
-- 会给出每一列的优化建议.
SELECT column_name FROM table_name procedure analyse();

12. 定期回收闲置的数据库空间

1
2
3
optimize table table_name;
alter table table_name engine = innodb;
alter table table_name force;

13. 导入文件

1
source /file/to/source.sql
1
mysql < /file/to/source.sql

14. 导出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 导出查询结果
# -N 不显示列名
# -e 后跟要执行的sql
mysql -N -e "select * from dual" > /file/to/result.txt

mysqldump $database $table_name > /file/to/dump.sql

# -d 没有数据
# --add-drop-table 在每个create语句之前增加一个drop table
# --skip-lock-tables 导出时不锁表
mysqldump -d --add-drop-table $database $table_name > /file/to/dump.sql

# -c 导出insert语句
# -t 没有表结构
mysqldump -t -c $database $table_name > /file/to/dump.sql

# -d 没有数据
# -y 不导出任何表空间信息
# --skip-comments 去除注释
# --skip-quote-names 不用 ``` 引起表和列名
# --compress 服务器与客户端压缩传输
mysqldump -d -y --skip-comments --skip-quote-names --compress $database > /file/to/dump.sql

15. 查看 bin log

1
2
3
4
5
6
-- 查看 bin log 状态
show variables like 'log_%';
show master status;
-- 查看 bin log 文件名称
show master logs;
show binary logs;
1
2
mysqlbinlog mysql-bin.000004 | tail;
mysqlbinlog mysql-bin.000004 > file.txt

16. Resource