mysql学习知识点记录

MYSQL

记录学习过程中的一些知识点。

1.创建数据库用户

实验环境：主机名：hisdb 主机ip：192.168.101.122 客户端ip：192.168.101.85

mysql有三种方法可以创建数据库用户
方法一：CREATE USER

mysql> create user 'test'@'192.168.101.85' identified by 'Huawei12#$';
Query OK, 0 rows affected (0.09 sec)
注：这里创建用户的时候也可以不加@ip，这里的ip并不一定是mysql服务器ip地址，这里的ip地址代表的是客户端的ip地址，也就是此用户想要在哪个ip客户端登录mysql。如果不加ip就代表在所有地址都可以以此用户登录mysql数据库，在用户表中该用户在host列的值是%代表在所有host都可以用此用户连接mysql数据库，这点也可以在用户表中看出。
mysql>  select user,host from mysql.user;
+------------------+----------------+
| user             | host           |
+------------------+----------------+
| test             | 192.168.101.85 |
| mysql.infoschema | localhost      |
| mysql.session    | localhost      |
| mysql.sys        | localhost      |
| root             | localhost      |
+------------------+----------------+
5 rows in set (0.00 sec)
--这里我们创建用户test的时候指定了host的ip地址为192.168.101.85，所以我们只能在85用test登录mysql数据库，在其他地址包括本机登录会报错如下：
[root@hisdb etc]# mysql -utest -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
--如果想放开某用户在所有ip都可登录mysql，可直接修改mysql.user用户表host值为%，或指定host值为某ip（root用户除外）

方法二：在user表中insert用户

mysql> INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('localhost', 'test2','Huawei12#$', '', '', '');
Query OK, 1 row affected (0.01 sec)
mysql> exit
Bye
mysql>  select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | %         |
| mysql.session    | %         |
| mysql.sys        | %         |
| root             | %         |
| test             | %         |
| test2            | localhost |
+------------------+-----------+
6 rows in set (0.01 sec)

mysql> exit
Bye
[root@hisdb etc]# mysql -utest2 -p'Huawei12#$'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test2'@'localhost' (using password: YES)

这里有问题没有解决，不知道为什么这种方式创建的用户为什么在哪里都不能登录数据库，用上面语法创建完用户后，发现user表中的密码列authentication_string是真实值，而不是一个hash值，这就是导致新建用户不能登录的原因，我在user表中将新建用户的authentication_string值改为其他用户的密码hash值后 FLUSH PRIVILEGES;后就能正常登录了。

我在8.0.27中想要用hash值的方法插入密码，又报错，原因未知

mysql> INSERT INTO mysql.user(Host, User,  authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('%', 'test5', password"Huawei12#$", '', '', '');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"Huawei12#$", '', '', '')' at line 1

方法三：用GRANT语法创建用户，优点：方便在创建用户的时候授权

mysql> grant select(user_id,username) on mysql.user to mo_user@'%' identified by '123345';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123345'' at line 1
--有问题，有语法错误，还没解决。

2.开启查询慢日志记录

mysql> show VARIABLES like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)
开启慢日志记录

set GLOBAL slow_query_log=1;   --1为开启0为关闭
慢日志时间记录阈值，默认是10s超过10s记录。
mysql> show VARIABLES like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
修改阈值

mysql> set global long_query_time=0;
Query OK, 0 rows affected (0.00 sec)
再次查询阈值，发现还是10s
mysql> show VARIABLES like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
退出session重新创建session插叙时间
mysql> show VARIABLES like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

也可以将没有走索引的sql记录到慢日志中去

默认关闭
mysql> show VARIABLES like '%log_queries_not_using_indexes%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)
开启
mysql> set global log_queries_not_using_indexes=1;   --开启1关闭0
Query OK, 0 rows affected (0.00 sec)

mysql> show VARIABLES like '%log_queries_not_using_indexes%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)

慢日志路径

mysql> show VARIABLES like '%slow_query_log_file%';
+---------------------+---------------------------------------------+
| Variable_name       | Value                                       |
+---------------------+---------------------------------------------+
| slow_query_log_file | /usr/local/mysql-8.0.27/data/hisdg-slow.log |
+---------------------+---------------------------------------------+
1 row in set (0.00 sec)

注：如果日志时间和系统时间不一致可修改log_timestamps参数值为system。