开源数据库Mysql_DBA运维实战 （DCL/日志）

SQL（Structured Query Language 即结构化查询语言）

a.DDL语句   数据库定义语言： 数据库，表，视图，索引，存储过程，函数，创建删除ALTER(CREATE DROP ALTER) 

b.DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE

c.DQL语句   数据库查询语言：查询数据SELECT

d.DCL语句 数据库控制语言：  例如控制用户的访问权限GRANT，REVOKE

编辑

DCL

DCL的概述

DCL(Data Control Language 数据控制语句)的操作是数据库对象的权限，这些操作的确定使数据更加的安全。

四种权限级别

1.Global  level     所有库,所有表的权限

2.Database  level    某个数据库中的所有表的权限

3.Table  level  库中的某个表的权限

4.Column   level   表中的某个字段 的权限

Mysql用户管理

1.创建用户

CREATE USER user1@'localhost' IDENTIFIED BY 'QianFeng@123456';

代码注释：CREATE  创建（关键字）                                                                                                        USER 用户（关键字）                                                                                                              user1   用户名称（自定义）                                                                                                      @ 分隔符（关键字）                                                                                                                ‘localhost’   允许登录的主机                                                                                                    IDENTIFIED BY 身份认证（关键字）                                                                                      ‘QianFeng@123456’   用户密码     

验证：                select * from mysql.user\G;

 2.删除用户

DROP USER 'user1'@'localhost';

验证：                select * from mysql.user\G;

                           drop user 'user1'@'localhost';

                           select user from mysql.user;

3.修改用户密码

Root修改自己密码

#示例1 mysqladmin -uroot -p'123' password 'new_password' #示例2 SET PASSWORD=password('new_password');

刷新权限：         FLUSH PRIVILEGES;

当root用户，忘记了密码。可以使用pojie的方式来登录系统。修改密码。

原理：使系统在启动时，不加载密码文件。

丢失root用户密码

a.修改mysql启动设置

# vim /etc/my.cnf [mysqld] skip-grant-tables

b.重启mysql，无密码登录

重启mysql程序 # systemctl restart mysqld 无密码登录 # mysql -uroot

c.修改自己的密码

mysql>UPDATE mysql.user SET authentication_string=password('QianFeng@123456789') where user='root' and host='localhost'; mysql> FLUSH PRIVILEGES;

d.修改mysql启动设置

注释掉跳过密码

4.登录MySQL

mysql -P 3306 -u root -p123 mysql -e 'show tables'

  -h    指定主机名         【默认为localhost】

注释：    -P    MySQL服务器端口              【默认3306】    大P

               -u    指定用户名                       【默认root】

               -p    指定登录密码                    【默认为空密码】

               -e    接SQL语句

免登录查询数据库

mysql -uroot -p'QianFeng@123' -hlocalhost -P 3306 mysql -e 'show tables'

 Mysql权限原理

语法格式：

grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified by '密码' with option参数];

注释：

==权限列表：all 所有权限（不包括授权权限）

                      select,update     查询更新

==数据库.表名 ： *.*   所有库下的所有表

                             web.*   web库下的所有表

                             web.box   web库下的box表

grant SELECT (id), INSERT (name,age) ON mydb.mytbl to 'user8'@'localhost' identified by 'QianFeng@123'; 

==客户端主机:    %   所有主机

                            192.168.2%   192.168.2网段的所有主机

                             192.168.119.156    指定主机

                             localhost      指定主机

==with_option参数：            GRANT OPTION   授权的选项

 Mysql权限示例

赋予权限

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

GRANT ALL ON bbs.* TO admin3@'%' IDENTIFIED BY 'QianFeng@13910604684';

准备测试授权账户：

root账户创建:

CREATE USER admin3@'%' IDENTIFIED BY 'QianFeng@123';

root账户授权

GRANT ALL ON bbs.* TO admin3@'%' IDENTIFIED BY 'QianFeng@13910604684';

使用测试账户操作数据库

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

回收权限

查看权限

查看自己的权限  SHOW GRANTS\G

查看别人的权限   SHOW GRANTS FOR admin3@'%'\G

回收权限REVOKE

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

 REVOKE ALL PRIVILEGES  ON bbs.*  FROM admin3@’%’;     //回收所有权限

日志

日志分类

编辑

1 错误日志 ：启动，停止，关闭失败报错。rpm安装日志位置 /var/log/mysqld.log

2 通用查询日志：所有的查询都记下来。

3 二进制日志：实现备份，增量备份。只记录改变数据，除了select都记。

4 中继日志：读取主服务器的binlog，在本地回放。保持一致。

5 slow log：慢查询日志，指导调优，定义某一个查询语句，定义超时时间，通过日志提供调优建议给开发人员。

6 DDL log： 定义语句的日志。

日志的弊端：

日志功能会 降低MySQL数据库的性能 。
日志会 占用大量的磁盘空间 。

Error Log

1）错误日志的介绍

错误日志（error log）:记录MySQL服务进程mysqld在自动/关闭或远行过程中遇到的错误信息

如果哪天mysql服务起不来了，请来这个日志文件看看。

2）错误日志的设置

vim   /etc/my.cnf      进入主配置文件，观察日志是否启动。

log-error=/var/log/mysqld.log    该字段，标记是否启动日志，以及日志位置。

在my.cnf配置文件里面查看错误日志log-error

[root@linzhongniao ~]# sed -n '78,79p' /data/3306/my.cnf   
[mysqld_safe]
log-error=/data/3306/mysql_linzhongniao3306.err

Binary Log

（1）二进制日志的介绍

二进制日志（binary log）:记录数据被修改的相关信息。

（2）二进制日志的设置

从库提升主库必须开启log-slave-updates

# vim   /etc/my.cnf

log_bin   添加该字段。指启动二进制日志

server-id=2   群集问题，必须指定该主机的序号。数字随意

# systemctl restart mysqld  重启数据库

测试：

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

ls /var/lib/mysql/*bin*    查询二进制文件生成完成

mysqlbinlog -v /var/lib/mysql/localhost-bin.000001    查看二进制文件日志

b.进入数据库，进行数据操作

c.完成二进制日志启动，查询测试

mysqlbinlog -v /var/lib/mysql/localhost-bin.000001          找到刚才创建的记录即可

Slow Query Log

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上（不含10秒）的语句，认为是超出了我们的最大忍耐时间值。

它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。例如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响

慢查询日志支持将日志记录写入文件。

开启慢查询日志功能

vim /etc/my.cnf#加入下面2行配置 slow_query_log=1 启动慢查询日志这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。 long_query_time=3 当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。 #重启服务器 #systemctl restart mysqld

查看慢查询日志文件

#查看日志文件已经生成ll /var/lib/mysql/*slow*

模拟慢查询

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

验证慢查询日志

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