【004】Linux MySQL 学习笔记-用户，权限与日志管理

1 用户管理

1. 查看用户

MariaDB [(none)]> select user,host,password from mysql.user;

2. 创建用户
   1).创建登录无限制的用户
   创建无密码无限制登陆ip的账户

MariaDB [(none)]> create user 'xiaomi';        

2).创建只能在指定机器登录的用户

MariaDB [(none)]> create user xiao@localhost;         
#@'XXX' 表示可以在哪些机器上登录,XXX可以是以下写法:
    192.168.10.11 -  具体的ip地址
    192.168.10.% -  一个网段内所有的主机
    tomcat1.qf.com -  具体的主机名
    %.qf.com -  一个域内所有的主机
    % - 任意主机,可省略,与1)相同

3).创建只能在指定主机登录且需要密码验证的用户

MariaDB [(none)]> create user mi@'192.168.10.%' identified by 'password';

---

客户端登陆服务器：
语法：mysql -u 用户名 -p -h 服务器的ip地址

\# mysql -u remote -h 192.168.10.1 -p  

删除用户
语法: drop user 用户名@主机;

MariaDB [(none)]> drop user 'xiaomi';                     #只写用户名相当于 '用户名'@'%'
MariaDB [(none)]> drop user 'xiao'@localhost;

修改密码的几种方法

1). # mysqladmin -u root -p123 password '456'
2). MariaDB [(none)]> set password for 'root'@'localhost'=password('1');
3). MariaDB [(none)]> update mysql.user set password=password('2') where user='root' and host='localhost';

2 权限管理

2.1 权限分类

• 全局层级
  全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。
• 数据库层级
  数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。
• 表层级
  表级权限适用于一个给定的表中的所有列。这些权限存储在mysql.tables_priv表中。
• 列层级
  列级权限适用于一个给定的表中的单一列。这些权限存储在mysql.columns_priv表中。

授权 - grant
语法：grant 权限列表 on 库名.表名 to 用户@主机 identified by ‘密码字符串’;
权限列表: 到对应的表中查询
1).授全局权限

MariaDB [(none)]> grant all on *.* to 'user1'@'192.168.1.71' identified by '123';
MariaDB [(none)]> flush privileges;  

2).授库级权限

MariaDB [(none)]> grant select,insert,update on db1.* to 'user2'@'192.168.1.%' identified by '123';

3).授表级权限

MariaDB [(none)]> grant select on db1.t1 to 'user3'@'%' identified by '456';

4).授列级权限

MariaDB [(none)]> grant select(id,name) on  db1.t1 to 'user4'@'%' identified by '456';

2.2 查看权限

语法: show grants for ‘用户名’@‘主机’;

MariaDB [(none)]> show grants for 'user1'@'192.168.1.71';

• 回收权限
  语法：revoke 权限 on 库名.表名 from ‘用户’@‘主机’;

MariaDB [(none)]> revoke all on *.*  from  xiaomi@'%';   #回收全部权限
MariaDB [(none)]> revoke insert on *.*  from  mi@'%';    #回收部分权限

• 用户和权限管理原则
  权限控制主要出于安全考虑，建议遵循以下原则:
  1.只授予能满足需要的最小权限，防止用户干坏事。比如说用户只是需要查询，那就只给select权限就可以了。
  2.创建用户的时候限制用户的登陆主机
  3.初始化数据库的时候删除没有密码的用户
  4.为每个用户设置满足密码复杂度的密码
  5.定期清理不需要的用户。回收权限或者删除用户

---

创建用户（授权）后连接不上服务器：

1. 服务器端防火墙，SELINUX是否关闭
2. 服务器端查看user表，host、user、password列的数据
3. 客户端确认用户名、密码、服务器IP输入是否正确

3 日志管理

3.1 几种日志

日志存放的位置：数据目录下

1. 错误日志：记录mysql服务器的启动、运行和关闭过程。

/mysql/data/mysql-1.qf.com.err
 
MariaDB [(none)]> show variables like 'log_error';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /mysql/data/mysql-1.qf.com.err |
+---------------+------------------------------+
1 row in set (0.03 sec)

2. 二进制日志 (*****)
   记录用户对数据库所做的所有的ddl和dml操作，不会记录查询语句，对数据的灾难恢复有至关重要的作用。
   ddl：create、drop、alter、truncate
   dml：insert、update、delete
   二进制日志作用：做AB复制、恢复

3. 通用查询日志：general_log
   记录所有对数据库的请求信息(包括登陆、查询)，默认不开启
   日志量较大、占用磁盘空间，一般不开启

4. 慢查询日志：记录的是查询时间超过指定时间的查询语句，一般用于优化查询，默认不开启

3.2 开启各种日志，修改配置文件

1、开启通用查询日志(一般不开启)

# vim /etc/my.cnf
    general_log = on
    general_log_file = select.log      #=号右边为日志文件名,可自定义

2、开启慢查询日志

# vim /etc/my.cnf
    slow_query_log = 1                    #打开慢查询日志, 默认为0
    slow_query_log_file = slow-log    #=号右边的日志文件名可自定义,默认为:主机名-slow.log
    long_query_time = 5                   #设置慢查询超时时间  单位是：秒

验证:

# mysql -u root -p2
    MariaDB [(none)]> select sleep(6);   #执行一个超过5秒的查询操作

# cat /var/lib/mysql/slow-log 

3、开启二进制日志

# vim /etc/my.cnf
    log-bin = my-bin                    #=号右边的日志文件名可自定义  

3.3 二进制日志的使用及管理

• 二进制日志的查看 mysqlbinlog

# mysqlbinlog log_bin.000001 
        # at 4   #事件发生的位置信息
         #170606 14:51:00  #事件发生的时间信息

• 使用二进制日志进行数据恢复

使用位置点进行恢复

# mysqlbinlog --start-position=245 --stop-position=534 log_bin.000001 |mysql -u root -p2
    --start-position   起始位置点
    --stop-position   结束位置点

使用事件发生的时间

# mysqlbinlog --start-datetime='17-06-06 15:25:04' --stop-datetime='17-06-06 15:26:15' log_bin.000002 | mysql -u root -p2
    --start-datetime  起始时间
    --stop-datetime  结束时间

位置点和时间点可以混合使用

• 查看所有的二进制日志信息

MariaDB [(none)]> show master logs;

• 手动生成新的二进制日志

MariaDB [(none)]> flush logs;

• 查看二进制日志里的操作记录

MariaDB [(none)]> show binlog events in 'bin-log.000006';

• 查看当前使用的二进制日志文件及所处的位置

MariaDB [(none)]> show master status\G

• 删除二进制日志

二进制日志会记录大量的信息（其中包含一些无用的信息）。如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。
但是，贸然删除可能导致数据库崩溃时无法进行恢复，所以在删除二进制日志前先将其和数据库备份一份。 另外,严禁采用物理方法删除二进制日志。

•   删除二进制文件的方法如下：
  

1）删除某个日志文件之前的所有日志

MariaDB [(none)]> purge master logs to 'log_bin.000002';

2）删除什么时间以前的日志文件(文件级)

MariaDB [(none)]> purge master logs before '2018-03-07 15:16:52';

3）删除所有的二进制日志(勿效仿):

MariaDB [(none)]> reset master;

Reference

1. 课堂笔记