03天MySQL客户端连接与权限管理

####### 作业：编写一个自动安装脚本

MySQL的配置文件

mysql 5.7 初始化是有密码的

root@dell-PowerEdge-R740:/home/tbs# mysqld --help --verbose | grep inse

# 初始化安装时加上这个参数mysql安装时密码是空
--initialize-insecure

• 文件名： my.cnf (my.ini)
• 文本文件
• 可有多个配置文件
• 参数替换原则

#mysql 配置文件顺序是依次读取，相同的参数会被后面的覆盖
root@dell-PowerEdge-R740:/home/tbs# mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 

-- 查看mysql所有参数
tbs@localhost:[(none)]>show variables;

-- 查看数据库所在路径
tbs@localhost:[(none)]>show variables like "datadir";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

mysql 参数分为全局的和会话级别的

-- 会话级别的
tbs@localhost:[(none)]>set session long_query_time = 1.5;
Query OK, 0 rows affected (0.00 sec)

-- 全局的
show global variable long_query_time = 1.5;

对已有的连接没有用，对于会话级别的参数，新会话才会生效。

• 从作用域上可分为global和session
• 从类型上又可分为可修改和只读参数
• 用户可在线修改非只读参数
• 只读参数只能通过配置文件修改并重启
• 所有参数的修改都不持久化

# 查看变量，可通过like进行过滤
show [global|session] variables [like 'pattern'| where expr]

# 修改global或session的参数
set [global | session] variables = xxx

tbs@localhost:[performance_schema]>show tables like "%variables%";
+--------------------------------------------+
| Tables_in_performance_schema (%variables%) |
+--------------------------------------------+
| global_variables                           |
| session_variables                          |
| user_variables_by_thread                   |
| variables_by_thread                        |
+--------------------------------------------+
4 rows in set (0.00 sec)

怎么看到其他会话的变量情况？

通过user_variables_by_thread表来查看

-- 查看每个会话的变量情况
tbs@localhost:[performance_schema]>select * from variables_by_thread where variable_name = "long_query_time";
+-----------+-----------------+----------------+
| THREAD_ID | VARIABLE_NAME   | VARIABLE_VALUE |
+-----------+-----------------+----------------+
|        27 | long_query_time | 1.500000       |
|        28 | long_query_time | 10.000000      |
|        29 | long_query_time | 10.000000      |
|        30 | long_query_time | 10.000000      |
+-----------+-----------------+----------------+
4 rows in set (0.00 sec)

-- 通过THREAD_ID查看会话的信息
tbs@localhost:[performance_schema]>select * from threads where thread_id = 27 limit 1\G
*************************** 1. row ***************************
          THREAD_ID: 27
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 2
   PROCESSLIST_USER: tbs
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: performance_schema
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 16
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 64980
1 row in set (0.00 sec)

-- 查看看所有会话信息
SELECT
	*
FROM
	threads
WHERE
	THREAD_ID IN (
		SELECT
			THREAD_ID
		FROM
			variables_by_thread
	);

用户权限管理

所有库权限
指定库权限
指定表权限
执行列权限

删除所有用户名为空的用户
不允许密码为空的用户存在
管理员用户可以有所有库权限
开发应用只需给相应库的权限

-- 创建用户
create user 'davaid'@'192.168.111.1' identified by '123'
-- 删除用户
drop user;

tbs@localhost:[performance_schema]>show grants;
+------------------------------------------+
| Grants for tbs@%                         |
+------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'tbs'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)

tbs@localhost:[performance_schema]>grant select ,update,insert,delete on test.*;

不要用grant创建用户，先创建用户，在grant权限。

revoke 仅删除权限，不删除用户。

用户权限

tbs@localhost:[mysql]>select user, host, authentication_string  from user;
+------------------+-----------+-------------------------------------------+
| user             | host      | authentication_string                     |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *26A6ED86B95AE05042D4ECCDF0E168E98C54945E |
| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| debian-sys-maint | localhost | *97355F25EA257138F5BDEF4589977998E382BAB0 |
| tbs              | %         | *C1782C76F831D6435F19187FED2B52DA82398D0F |
| david            | %         | *26A6ED86B95AE05042D4ECCDF0E168E98C54945E |
+------------------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)

-- 建议不要修改这四张表，用户权限管理的元数据表
tbs@localhost:[mysql]>desc user;

tbs@localhost:[mysql]>select *  from db \G

tbs@localhost:[mysql]>desc tables_priv;

tbs@localhost:[mysql]>desc columns_priv;

常用权限
SQL语句
存储管理

user中authentication_string是表示的是用户密码，使用的是password函数加密，不可逆的。

用户的资源管理