2 mysql客户端连接和权限

mysql配置文件
[root@node1 ~]# mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
后面的配置文件参数会替换原来的参数

参数的级别：

• 全局

  查看
  (root@localhost) [(none)]> show variables like 'long_query_time';
  +-----------------+----------+
  | Variable_name   | Value    |
  +-----------------+----------+
  | long_query_time | 5.000000 |
  +-----------------+----------+
  对已经存在的会话不生效，对之后的链接生效
  修改
  (root@localhost) [(none)]> set global long_query_time = 5;
  

• session

(root@localhost) [(none)]> show variables like 'long%query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
(root@localhost) [(none)]> set long_query_time = 1.5 ;

查看全局变量对那些进程生效
[performance_schema]> select * from variables_by_thread where variable_name = 'long_query_time' ;
+-----------+-----------------+----------------+
| THREAD_ID | VARIABLE_NAME   | VARIABLE_VALUE |
+-----------+-----------------+----------------+
|        29 | long_query_time | 10.000000      |
|        30 | long_query_time | 10.000000      |
+-----------+-----------------+----------------+

[performance_schema]> select * from threads where thread_id = 29 limit 1\G;
*************************** 1. row ***************************
          THREAD_ID: 29
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND

用户管理

create user 'lei'@'%' identified by '123'; #添加
grant select on sys.* to ‘lei‘@’%’ with grant option; #授权其他人
drop user ‘lei‘@’%’; #删除
alter user 'lei'@'%' identified by '456'; #修改密码
revoke select on sys.* from 'lei'@'%';  #回收权限
with grant option 授予其他用户权限

用户密码在表中的体现

(root@localhost) [mysql]> select user,host,authentication_string from user;
+-----------+-----------+-------------------------------------------+
| user      | host      | authentication_string                     |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| lei       | %         | *531E182E2F72080AB0740FE2F2D689DBE0146E04 |
+-----------+-----------+-------------------------------------------+

• 资源限制：使用with选项

• 每小时链接次数

• 连接数

alter user 'lei'@'%' with 
	max_queries_per_hour
	max_updates_per_hour
	ma_connections_per_hour
	max_user_connections