开源数据库MySQL DBA运维实战-SQL-3

一.DCL

1.权限级别

• 1.Global level
  所有库，所有表的权限。
• 2.Database level
  某个数据库中的所有表的权限。
• 3 .Table level
  库中的某个表的权限
• 4.Column level
  表中的某个字段，的权限。

2.MySQL用户管理

2.1创建用户

mysql> create user user1@'localhost' identified by 'Q@123456';
select * from mysql.user;
#CREATE  创建（关键字）
#USER 用户（关键字）
#user1   用户名称（自定义）
#@ 分隔符（关键字）
#‘localhost’   允许登录的主机
#IDENTIFIED BY 身份认证（关键字）
#‘Q@123456’   用户密码

2.2删除用户

mysql> drop user 'user1'@'localhost';
mysql> select * from mysql.user;

2.3修改用户密码

• root修改自己的密码

#示例1

[root@localhost ~]# # mysqladmin -uroot -p'123' password 'new_password'   #前提是拥有系统权限，123为旧密码

#update也可以。不过比较复杂

mysql> update mysql.user set authentication_string=password(‘new_password’) where user=’root’ and host=’localhost’;	
mysql> flush privileges;

#示例2

mysql> set password=password('new_password');	#修改密码
mysql> flush privileges;   #（刷新权限）

• 丢失root用户密码

#1.前言
#当root用户，忘记了密码。可以使用破解的方式来登录系统。修改密码。
#原理：使系统在启动时，不加载密码文件。

#2.修改mysql启动设置

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
skip-grant-tables

#3.重启mysql无密码登录

[root@localhost ~]# systemctl restart mysqld   #重启mysql程序
[root@localhost ~]# mysql -uroot   #无密码登录

#4.修改自己密码

mysql> update mysql.user set authentication_string=password('Q@123456789') where user='root' and  host='localhost';
mysql> flush privileges;

#5.修改mysql启动设置，注释掉跳过密码

2.4登录MySQL

[root@localhost ~]# mysql -P 3306 -u root -p123 mysql -e ‘show tables’
#-P MySQL服务器端口  【默认3306】 大P
#-u	指定用户名 【默认root】
#-p	指定登录密码  【默认为空密码】
#此处mysql为指定登录的数据库 
#-e	接SQL语句
#-h	指定主机名 【默认为localhost】

#示例：免登录查询数据库

[root@localhost ~]# mysql -uroot -p'Q@123' -hlocalhost -P 3306 mysql -e 'show tables'

3.MySQL权限管理

• 语法格式： grant 权限列表 on 库名.表名 to ‘用户名’@‘客户端主机’ [identified by ‘密码’ with option参数];

#注释
===权限列表
	all   所有权限（不包括授权权限）
	select,update	 查询更新
==数据库.表名
	*.*   所有库下的所有表
	web.*   web库下的所有表
	web.stu_info    web库下的stu_info表
	
mysql> grant SELECT (id), INSERT (name,age) on mydb.mytbl to 'user8'@'localhost' identified by 'Q@123'; 

==客户端主机
	 %   所有主机
	192.168.2.%		    192.168.2.0网段的所有主机
	192.168.2.168		    指定主机
	localhost                指定主机
==with_option参数
	    GRANT OPTION   授权选项（给授权目标也可以授权的能力）

4.MySQL权限示例

4.1赋予权限

• 授权目标

#授予admin3 对bbs库 所有的表，具有所有权限（不包含授权）

mysql> grant all on bbs.* to admin3@'%' identified by 'Q@123';

• 赋权示例

#1.准备测试账户-root账户创建

mysql> create user admin3@'%' identified by 'Q@123';

#2.授权账户-root账户授权

mysql> grant all on bbs.* to admin3@'%' identified by 'Q@123';

#3.使用测试账户操作数据库

[root@localhost ~]# mysql -uadmin3 -p'Q@123'
mysql> create database bba;
ERROR 1044 (42000): Access denied for user 'admin3'@'%' to database 'bba'
#进入bbs库，创建数据表和插入数据。授权验证完毕

4.2回收权限

• 查看权限

#查看自己的权限

mysql> show grants;

#查看别人的权限

mysql> show grants for admin3@'%'; 

• 回收权限revoke

#语法

REVOKE 权限列表 ON 数据库名 FROM 用户名@‘客户端主机’

#示例

mysql> revoke all privileges  on bbs.*  from admin3@’%’;   #回收所有权限

• 删除用户的版本问题

5.6之前，先 revoke all privilege再drop user
5.7之后，直接 drop user

二.日志

1.日志分类

1 .错误日志 ：启动，停止，关闭失败报错。rpm安装日志位置 /var/log/mysqld.log
2 .通用查询日志：所有的查询都记下来。
3 .二进制日志：实现备份，增量备份。只记录改变数据，除了select都记。
4 .中继日志：读取主服务器的binlog，在本地回放。保持一致。
5 .slow log：慢查询日志，指导调优，定义某一个查询语句，定义超时时间，通过日志提供调优建议给开发人员。
6 .DDL log： 定义语句的日志。

2.Error Log

#进入主配置文件，观察日志是否启动

[root@localhost ~]# vim /etc/my.cnf
log-error=/var/log/mysqld.log   
#该字段，标记是否启动日志，以及日志位置。如果哪天mysql服务起不来了，请来这个日志文件看看。

3.Binary Log

• 默认没有开启二进制日志
• 启动二进制日志

[root@localhost ~]# vim /etc/my.cnf
log_bin   #添加该字段。指启动二进制日志
server-id=2   #集群问题，必须指定该主机的序号。数字随意
[root@localhost ~]# systemctl restart mysqld   #重启数据库

• 测试

#1.查看二进制日志文件当前状态

[root@localhost ~]# ls /var/lib/mysql/*bin*   #查询二进制文件生成完成。
[root@localhost ~]# mysqlbinlog -v /var/lib/mysql/localhost-bin.000001   #查看二进制文件日志。

#2.进入数据库，进行数据操作
#创库，创表，创数据（过程略）

[root@localhost ~]# mysqlbinlog -v /var/lib/mysql/localhost-bin.000001
#查看二进制日志文件，就可以看到刚才的数据库操作记录

4.Slow Query Log

• 默认慢查询日志未启动

4.1开启查询日志功能

[root@localhost ~]# vim /etc/my.cnf   #加入下面两行配置

slow_query_log=1
#启动慢查询日志
这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。

long_query_time=3
#当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。

4.2查看慢查询日志文件

[root@localhost ~]# ll /var/lib/mysql/*slow*
#查看日志文件已经生成

4.3模拟慢查询

mysql>SELECT BENCHMARK(500000000,2*3);
#基本测试语句，测试一个超长时间的查询。

4.4验证慢查询日志

[root@localhost ~]# tail /var/lib/mysql/localhost-slow.log 
#观察长查询日志记录，记录了刚才超长的查询结果。稍后反馈给开发部，进行优化。