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。