首页
网站首页
公司简介
资讯中心
推荐内容
返回顶部
6中如何定位DDL被阻塞的问题,6不删空用户的影响
发布时间:2020-01-06 04:47
浏览次数:

葡京娱乐网 1葡京娱乐网 2

 

在上一篇文章《MySQL 5.7中如何定位DDL被阻塞的问题》中,对于DDL被阻塞问题的定位,我们主要是基于MySQL 5.7新引入的performance_schema.metadata_locks表。提出的定位方法,颇有种"锦上添花"的意味,而且,也只适用于MySQL 5.7开始的版本。

目录

1 begin
2 
3 declare @i int ;
4 
5 set @i=77541214;
6 
7 update dbo.test set code='AMHD'+CONVERT(varchar,@i),@i=@i+1;
8 
9 end

原文出处:https://www.infoworld.com/article/3210905/sql/10-essential-performance-tips-for-mysql.html

但在实际生产中,MySQL 5.6还是占绝不多数。虽然MySQL 8.0都已经GA了,但鉴于数据库的特殊性,在对待升级的这个事情上,相当一部分人还是秉持着一种“不主动”的态度。

View Code

 

既然MySQL 5.6用者众多,有没有一种方法,来解决MySQL 5.6的这个痛点呢?

MySQL 5.6不删空用户的影响



 

MySQL的10个基本性能技巧

 

问题


MySQL5.6 新建本地可以登录的用户,但在本地始终登录不上,不存在的用户却能登录成功

mysql -uroot -p1234 -S /data/mysql/33562/mysql.sock  -e "select version()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| version() |
+-----------+
| 5.6.39    |
+-----------+

创建了admin@'%' 用户,但在本地无法登陆,但可以远程登陆

sselect user,host,password from mysql.user;
+--------+-----------+-------------------------------------------+
| user   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| root   | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF |
| root   | slave58   |                                           |
| root   | 127.0.0.1 |                                           |
| root   | ::1       |                                           |
|        | localhost |                                           |
|        | slave58   |                                           |
| admin  | %         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| zabbix | 127.0.0.1 | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| super  | 127.0.0.1 | *F85A86E6F55A370C1A115F696A9AD71A7869DB81 |
| super  | slave58   | *F85A86E6F55A370C1A115F696A9AD71A7869DB81 |
+--------+-----------+-------------------------------------------+


show grants for 'admin'@'%';

+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' |
+---------------------------------------------------------------------------------------------------------------+

select password('admin');
+-------------------------------------------+
| password('admin')                         |
+-------------------------------------------+
| *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+-------------------------------------------+

以ip地址无法登陆
mysql -uadmin -padmin   -P33562 -h10.186.30.58


mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'slave58' (using password: YES)

以socket也无法登陆
mysql -uadmin -padmin   -P33562 -S /data/mysql/33562/mysql.sock
或者
mysql -uadmin -padmin   -P33562 -h127.0.0.1

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

不存在的用户却可以登录成功
mysql -utest  -S /data/mysql/33562/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 15
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

 

 

还是之前的测试Demo

分析


1、本是以ip地址进行登录的,错误信息报的是Access denied for user 'admin'@'slave58',自动进行了DNS解析,解析成了主机名,skip-name-resolve 可以禁用DNS解析
2、mysql在验证权限的时候,先验证host,在验证user,最后验证password,即
host->user->password
3、以host或者socket登陆,进行了DNS解析,host;socket被解析成了slave58;localhost, 而mysql.user 中host 为localhost和slave58 对应空用户,就报了访问拒绝错误

 

与所有的关系数据库一样,MySQL正如一头怪兽一般,
它可能会在接到通知的一瞬间陷入停顿,让你的应用程序陷入困境,让你的业务处于危险之中。真是的情况是,常见的错误是导致MySQL性能问题的根源。
工作负载或配置陷阱中的一些微妙之处常常会掩盖这些信息,为了确保MySQL服务器以最快的速度运行,提供稳定一致的性能,消除这些错误是很重要的。
幸运的是,很多MySQL的性能问题都有相似的解决方案,使的故障排除和调优MySQL成为一项易于管理的任务。

会话1开启了事务并执行了三个操作,但未提交,此时,会话2执行了alter table操作,被阻塞。

测试


 

session1> begin;
Query OK, 0 rows affected (0.00 sec)

session1> delete from slowtech.t1 where id=2;
Query OK, 1 row affected (0.00 sec)

session1> select * from slowtech.t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
row in set (0.00 sec)

session1> update slowtech.t1 set name='c' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session2> alter table slowtech.t1 add c1 int; ##被阻塞

session3> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                               |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
|  2 | root | localhost | NULL | Sleep   |   51 |                                 | NULL                               |
|  3 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                   |
|  4 | root | localhost | NULL | Query   |    9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
rows in set (0.00 sec)

启动mysqld时没有加上--skip-name-resolve

以mysql不存在的用户进行登录

mysql -utest -P33562 -h`hostname`
或者
mysql -utest -S /data/mysql/33562/mysql.sock

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.39 MySQL Community Server (GPL)
.....
show grants;
+------------------------------------+
| Grants for @slave58                |
+------------------------------------+
| GRANT USAGE ON *.* TO ''@'slave58' |
+------------------------------------+

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
create table test.test_t1(id int);
Query OK, 0 rows affected (0.05 sec)
drop table test.test_t1;
Query OK, 0 rows affected (0.05 sec)
drop database test;
Query OK, 8 rows affected (0.49 sec)


    具有USAGE  的权限的用户 有test 库的所有权限和information_schema 中表的select权限。
    不存在的用户还可以 drop datase test 这个“神操作”是因为 空用户具有test和test_*库的所有权限,而任何用户都能匹配空用户,mysql5.6初始化后默认就有这个,而mysql5.7默认没有,也没有空用户,空密码用户
    select * from mysql.db;

葡京娱乐网 3

以mysql.user中存在的用户登录如 “问题” 中所示,无法登录


在mysql.user 中存在的用户还不能登录,任意一个不存在的用户还可以登录成功 ,还可以drop database test,这有很大的安全隐患!开始怀疑,这mysql5.6是不是假的o_o


MySQL性能提示1:配置您的工作负载

 

启动mysqld时加上--skip-name-resolve

ps -ef | grep -w 33562 | grep -v grep  | awk '{print $2}' | xargs -l kill

su - mysql -c "/usr/local/mysql/5.6.39/bin/mysqld --no-defaults --basedir=/usr/local/mysql/5.6.39 --datadir=/data/mysql/33562 --pid-file=/data/mysql/33562/clone.pid --port=33562 --server-id=33562 --socket=/data/mysql/33562/mysql.sock --tmpdir=/data/mysql/33562 --skip-name-resolve &"

以mysql.user中不存在的用户进行登录

mysql -utest   -P33562 -h`hostname`
ERROR 1045 (28000): Access denied for user 'test'@'10.186.30.58' (using password: NO)

mysql -utest -S /data/mysql/33562/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 5.6.39 MySQL Community Server (GPL)

以`hostname`登录,host 转化成ip地址,在mysql.user中没有匹配到;
以socket登录 host 被转化成localhost,在mysql.user中匹配到了''@'localost'空用户,登录成功。可见,任意用户都能匹配mysql.user的user为空' '的用户

以mysql.user中存在的用户可以登录成功

mysql -uadmin -padmin   -P33562 -h10.186.30.58
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.39 MySQL Community Server (GPL)

了解服务器究竟把时间花在哪些地方的最佳方法是分析服务器的工作负载,
新葡亰496net,通过分析工作负载,您可以导出最大代价的查询以进行进一步调优,当向服务器发出请求的时候,时间就是最重要的指标,
你几乎不关心任何事情,只关心它完成得有多快。配置工作负载的最佳方法是使用MySQL Enterprise Monitor的查询分析器或Percona工具包中的pt-query-digest之类的工具。

其实,导致DDL阻塞的操作,无非两类: 

结论

1、在没有以 skip-name-resolve 启动启动的情况下,以mysql -uuser -ppassword登录会进行DNS解析,在mysql.user中匹配到错误的 host,即时没有限制主机的用户 ‘user’@'%'也无法登录
2、即时一个并不存在的用户在以`hostname` 或者socket进行登录,若匹配了mysql.user中的host,该host对应的user和password为空,并不存在的用户也能登录成功
3、为了避免进行DNS解析,在my.cnf配置文件中添加skip-name-resolve
4、在刚初始化mysql5.6后,就应该删除mysql.user; mysql.db中空用户,空密码账户(包括root空秘密账户),减少安全隐患
DELETE FROM mysql.user WHERE PASSWORD=' ';
葡京线上开户,DELETE FROM mysql.db WHERE USER=' '; FLUSH PRIVILEGES;
5、可用mysql5.6 自带mysql_secure_澳门新莆京免费网址,installation 脚本删除空用户,空密码账户,删除test库

参考
mysql空用户(user列为空)带来的影响

这些工具捕获服务器执行的查询,并返回一个任务表,按照响应时间的顺序进行排序,立即将代价最大和最耗时的任务排在最前面,这样您就可以看到您的工作重点在哪里。
工作负载分析工具将类似的查询组合在一起,允许您查看缓慢的查询,以及快速但多次执行的查询。

  1. 慢查询  

  2. 葡京娱乐网,表上有事务未提交

译者注:找到一些top的sql或者说是执行频率高的sql,这部分是关注的重点

其中,第一类比较好定位,通过show processlist即能发现。而第二类基本没法定位,因为未提交事务的连接在show processlist中的输出同空闲连接一样。

 

如下面Id为2的连接,虽然Command显示为“Sleep”,其实是事务未提交。

MySQL性能提示2:了解四种基本资源

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                               |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
|  2 | root | localhost | NULL | Sleep   |   77 |                                 | NULL                               |
|  3 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                   |
|  4 | root | localhost | NULL | Query   |   44 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
3 rows in set (0.00 sec)
友情链接: 网站地图
Copyright © 2015-2019 http://www.nflfreepicks.net. 新葡萄京娱乐场网址有限公司 版权所有