Too many keys specified; max 64 keys allowed 作者: lovingyu_er 时间: 2020-12-24 18:58:56 分类: MYSQL -view 评论 软件版本: ``` mysqladmin Ver 8.42 Distrib 5.7.26, for Linux on x86_64 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.7.26-0ubuntu0.18.10.1 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 26 days 46 min 0 sec ``` 系统平台: ``` Ubuntu 18.10 \n \l ``` 使用PHP在命令行中,给项目的一个表执行```add cloumn```的操作,需要增加了200多个列并增加相应的索引,出现了```Too many keys specified; max 64 keys allowed``` 的提示... - 阅读剩余部分 -
Ubuntu Mysql8.0 初始化密码修改 作者: lovingyu_er 时间: 2020-12-23 14:36:00 分类: MYSQL -view 评论 折腾的够厉害,在安装以后,发现在```/var/lib/mysql```目录中有初始化数据,先删除该初始化的数据,然后停止```sudo /etc/init.d/mysql stop```, 然后再```command line```模式,运行```sudo mysqld --initialize```,注意观察 ```/var/log/mysql/error.log```文件,会看到有```root```的随机密码。 在日志中,你会看到mysql 安装的一系列的初始化操作. 在```Mysql8.0```中,```Plugin mysqlx```是默认启用的。
一条查询SQL语句在数据库执行的简单介绍 作者: lovingyu_er 时间: 2020-09-03 17:40:00 分类: MYSQL,运维优化,MYSQL -view 评论 ####MYSQL分层架构(客户端/Server层/存储引擎) 结构图大概如下:  #####1. 客户端 客户端,不同的语言有不同的实现数据库连接的驱动,比如PHP有```pdo```,Golang有```xorm```等等,属于应用程序的客户端。 #####2. Server层 Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。 1. 连接器:负责给客户端建立连接,获取权限,维持和管理连接: ``` mysql -uusername -P port -h hostname -p ``` 该命令会与服务器端建立连接,完成经典的tcp握手以后,连接器就开始进行身份验证,也就是需要你输入密码。密码错误,就会显示权限拒绝。如果认证通过,连接器就查询用户的权限,之后的操作对于权限的判断逻辑,都以来这个读取到的权限,即使管理员对这个账户的权限进行了修改,也不会影响已经存在的权限。只有再新建连接才能使用新的连接授权。连接以后,你如果不进行操作,该processlist就处于```sleep```状态。如果大于```wait_timeout```,就会自动断开连接,默认是8小时。 数据库里面,长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。 建立连接的过程通常是比较复杂的,所以我建议你在使用中要尽量减少建立连接的动作,也就是尽量使用长连接。 但是全部使用长连接后,你可能会发现,有些时候 MySQL 占用内存涨得特别快,这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来,可能导致内存占用太大,被系统强行杀掉(OOM),从现象看就是 MySQL 异常重启了。 解决方案: 1. 定期断开长连接。使用一段时间,或者程序里面判断执行过一个占用内存的大查询后,断开连接,之后要查询再重连。 2. 大于 mysql5.7 通过```mysq_reset_connection```来重新初始化连接资源。这个过程不需要重连和重新做权限验证。但是会将连接恢复到刚刚创建完时的状态。 2. 查询缓存 mysql拿到select语句以后,会现在缓存中看看,之前是否有执行过这条语句。之前执行过的select语句会以key-value的形式缓存在内存中。key是以查询的语句作为key,value就是查询的结果。如果命中了缓存,就直接将此value返回给客户端,会提高select的效率。 ####缓存不建议使用的原因: 查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的**查询缓存都会被清空**。因此很可能你费劲地把结果存起来,还没使用呢,就被一个更新全清空了。对于更新压力大的数据库来说,查询缓存的命中率会非常低。**除非你的业务就是有一张静态表,很长时间才会更新一次。**比如,**一个系统配置表,那这张表上的查询才适合使用查询缓存。**好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND,这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句,可以用 SQL_CACHE 显式指定,像下面这个语句一样:(MYSQL8.0版本,直接将此缓存模块去掉了) ``` select SQL_CACHE * from T where ID=10; ``` 注意:**在工程实现上,如果命中查询缓存,会在查询缓存返回结果的时候,做权限验证。查询也会在优化器之前调用 precheck 验证权限** 3. 分析器 如果没有命中缓存,就开始分析器进行工作了。 3.1. mysql开始对你的select语句进行解析了。分析器先进行词法分析:识别里面的字符串分别是什么,代表什么。 3.2. 语法分析, SQL语句是否满足MYSQL的语法:正确,进入下一步;不正确,收到错误提示:```You have an error in your SQL syntax```的错误提醒。一般会有```near```字样的提示。 4. 优化器 优化器这个时候就会涉及到表的索引设计了。如果表中多个索引的时候,优化器会决定使用哪个索引。在表的语句中有多个join的时候,会决定表的join连接顺序。比如: ``` select * from a join b using(ID) where a.c = 10 and b.d=20; ``` 这条语句既可以从表a里面取出c=10的记录ID的值,再根据ID值关联到表b,再判断b里面d的值是否等于20 也可以从表b里面取出d=20记录的ID的值,再根据ID值关联到a,再判断a里面c是否等于10. 两种执行方法的逻辑结果是一样的,但是效率会不一样,而优化器的作用就是决定选择使用哪一个方案。 经过优化器,这个语句的执行方案就确定下来了,然后进入执行器阶段。 5. 执行器 执行sql语句之前,会首先判断你有没有执行查询的权限,如果没有,就返回没有权限的错误。如果有权限,打开表继续执行。打开表的时候,执行器会根据表的引擎定义,去使用这个引擎提供的接口。 案例: ``` select * from T where ID = 10 ; ```` 5.1. 当ID没有索引的时候,调用InnoDB引擎接口,取这个表的第一行,判断ID值是不是10,如果不是,就跳过;是,就将此行存在结果集中。 5.2. 调用引擎接口取“下一行”,重复相同的逻辑判断,直到最后一行 5.3. 执行遍历,完成后,将结果集返回给客户端。 这样,语句就执行完毕了。 对于有索引的表,执行逻辑也差不多,第一次调用的是“取满足条件的第一行”这个接口,之后循环取“满足条件的下一行”这个接口,这些接口都是引擎中已经定义好的。 你会在数据库的慢查询日志中看到一个 **rows_examined** 的字段,**表示这个语句执行过程中扫描了多少行**。这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下,执行器调用一次,在引擎内部则扫描了多行,因此引擎扫描行数跟 rows_examined 并不是完全相同的。我们后面会专门有一篇文章来讲存储引擎的内部机制,里面会有详细的说明。 #####3. 存储引擎 而存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成为了默认存储引擎。 执行 create table 建表的时候,如果不指定引擎类型,默认使用的就是 InnoDB。不过,你也可以通过指定存储引擎的类型来选择别的引擎,比如在 create table 语句中使用 engine=memory, 来指定使用内存引擎创建表。不同存储引擎的表数据存取方式不同,支持的功能也不同。
MYSQL server has gone away .... 的错误提示的解决方案(MYSQL 5.7.26) 作者: lovingyu_er 时间: 2020-06-08 19:19:00 分类: 编程语言,MYSQL,Mysql附录,运维优化,MYSQL -view,朝花夕拾 评论 在进行数据通过Mysql 的命令行的```source```倒入数据的时候,经常会出现这种```MySQL server has gone away```字样的提示,这种问题大部分的原因是因为```max_allow_packet```字段设置的大小有问题,导致的。 ###问题原因: MySQL通过max_allowed_packet配置项来限制接收的数据包大小,默认一般是2M吧,不同的版本,大小限制不一样,超过这个```max_allowed_packet```配置的大小,会出现这个错误提示。 ###解决方案: #####方案1:临时解决方案,不用重启MYSQL 使用```show variables like '%max_allowed_packet%';```查看该选项的设置: ``` mysql> show variables like '%max_allowed_packet%'; +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | max_allowed_packet | 16777216 | | slave_max_allowed_packet | 1073741824 | +--------------------------+------------+ 2 rows in set (0.00 sec) ``` 上述的单位默认为```KB```, 使用命令行临时修改: ```set global variables like "%allowed%"; flush privileges;``` 注意:如果没有变化,请退出命令行然后再重新登录,查看值是否变化,我的Mysql 版本是```MYSQL 5.7.26```(命令:```show variables like '%version%';```)。 ``` mysql> show variables like '%version'; +------------------+-------------------------+ | Variable_name | Value | +------------------+-------------------------+ | innodb_version | 5.7.26 | | protocol_version | 10 | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.26-0ubuntu0.18.10.1 | +------------------+-------------------------+ 4 rows in set (0.00 sec) ``` #####方案2,修改配置文件(需要重启) 修改```MYSQL```配置文件,在```[mysqld]```模块,增加```max_allowed_packet = xxM```,其中``xx``是你想要设置的包的大小限制。然后重启MYSQL服务. 参考文档: 1. ```https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet```
MYSQL5.7 | 5.5 不同版本忘记密码的处理方式 作者: lovingyu_er 时间: 2020-05-29 19:58:00 分类: 编程语言,MYSQL,MYSQL -view 评论 ####MYsql 5.7 版本 mysql 很久没有登录了,root的密码早就忘记了,今天一大早,需要使用root以及相关子账号的权限,但是发现密码不对,记得以前只需要修改mysql的配置文件```[mysqld]```的配置option就可以了。但是今天照办,发现不行; 排查了一下问题的原因,对于MYSQL的用户身份验证: 在免密码的模式,在命令行执行```select * from mysql.user\G```看到在密码加密的一行```plugin```中,在MYSQL的官方文档中,切换到mysql5.7,输入关键词```mysql_native_password```,看到有关mysql_upgrade的详细介绍,文档地址:```https://dev.mysql.com/doc/refman/5.7/en/account-upgrades.html``` ,对比我自己的mysql的plugin信息,发现是```mysql_old_password```的,因此只需要执行一下命令进行修正即可: ```update mysql.user set plugin="mysql_native_password" where User="root" and Host="localhost";flush privileges``` 然后再考虑修改mysql的root的密码的命令: ```update mysql.user set authentication_string=password('new_password') where User="root" and Host="localhost"; flush privileges;``` 即可; 将原来mysql的配置文件中,将```skip-grant-tables``` 注释去掉即可. 最后,重启mysql ```/etc/init.d/mysql restart``` Tips:文档中,如果没有解决你的问题,你可以参考文档:```https://www.cnblogs.com/bing-yu12/p/7566280.html``` 里面对上述的不能解决的问题,做了一些补充! ####MYsql5.5 版本处理 同上一样,在配置文件中的[mysqld]部分,增加```skip-grant-tables```一行,也就是登录的时候,跳过密码的验证, 直接在命令行的模式下执行: ```update user set password=password("Your Password") where user="root";``` ```flush privileges;``` 执行完毕以后,退出命令行,再将上述的```skip-grant-talbes```注释掉,重启``/etc/init.d/mysql restart``` 重启以后,即可.