MySQL The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
版本
数据库版本：8.0.25
问题描述
新创建的账号使用Navicat连接MySQL，测试连接成功但双击打开数据库失败
处理方式
命令窗口登录mysql执行以下命令。
update user set Select_priv = 'Y' where User = 'mysql.infoschema';
1.
执行完后Navicat正常打开
Host is blocked because of many connection errors;unblock with 'mysqladmin flush-hosts'
问题描述
同一个ip在短时间内产生太多中断的数据库连接而导致的阻塞，由于web链接mysql失败不断重连导致
处理方式
MySQL配置文件my.ini中添加 max_connect_errors = 16384（最大错误连接数）,使用以下命令清除错误连接数。
ip根据报错来填写
mysqladmin flush-hosts -h ip -P 3306 -u root -p password或者使用命令窗口登陆MySQL
flush hosts;
flush privileges;Host 'localhost' is not allowed to connect to this MySQL server
数据库版本
8.0.25
错误提示
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
问题描述
使用账号 root/SYSadmin 无法连接到MySQL服务器
处理方式
创建一个新的账号进行登录，由于现场数据版本为mysql8.0.25，使用以下命令进行免密登录。
mysqld --console --skip-grant-tables --shared-memory
另开一个cmd进行登录
mysql -P3307 -uroot -p
1.
创建账号以及授权：
flush privileges;
CREATE USER `test`@`*` IDENTIFIED WITH mysql_native_password BY 'SY@2023';
GRANT Insert, Select ON `mydb`.* TO `root`@`*`;
GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, Create User, Create View, Delete, Drop, Event, Execute, File, Grant Option, Index, Insert, Lock Tables, Process, References, Reload, Replication Client, Replication Slave, Select, Show Databases, Show View, Shutdown, Super, Trigger, Update ON *.* TO `test`@`*`;
flush privileges;
Can not connect to MySQL server (10060)记录排查过程
场景
通过Navicat还是MySQL客户端工具，都无法使用本地分配的IP地址或者127.0.0.1地址进行访问，可以通过localhost或者远程客户端进行访问，之前一直正常，最后通过重启服务器解决问题
原因
未知
尝试分析
1怀疑是IP地址授权问题，检查配置没有问题
2怀疑防火墙问题
已经全部关闭
3怀疑MySQL服务有问题
通过其他服务器远程的方式能够访问
4怀疑有其他的服务影响到MySQL服务
netstat -ano |findstr 3307
排查到网络异常
下次遇到这种情况，优先使用route print排查网络链路的问题
C:\Users\Administrator>route print
===========================================================================
接口列表
10...00 ff 8d 36 bd 4d ......TAP-Windows Adapter V9
5...b0 7b 25 07 85 45 ......Intel(R) I210 Gigabit Network Connection
15...b0 7b 25 07 86 50 ......Intel(R) Ethernet Connection (3) I219-LM
12...00 ff 97 85 39 7f ......Virtual Network Adapter
18...00 ff fe af 2a bf ......Sangfor SSL VPN CS Support System VNIC
1...........................Software Loopback Interface 1
===========================================================================
IPv4 路由表
===========================================================================
活动路由:
网络目标 网络掩码 网关 接口 跃点数
0.0.0.0 0.0.0.0 192.168.11.254 192.168.11.186 25
127.0.0.0 255.0.0.0 在链路上 127.0.0.1 331
127.0.0.1 255.255.255.255 在链路上 127.0.0.1 331
127.255.255.255 255.255.255.255 在链路上 127.0.0.1 331
192.168.11.0 255.255.255.0 在链路上 192.168.11.186 281
192.168.11.186 255.255.255.255 在链路上 192.168.11.186 281
192.168.11.255 255.255.255.255 在链路上 192.168.11.186 281
224.0.0.0 240.0.0.0 在链路上 127.0.0.1 331
224.0.0.0 240.0.0.0 在链路上 192.168.11.186 281
255.255.255.255 255.255.255.255 在链路上 127.0.0.1 331
255.255.255.255 255.255.255.255 在链路上 192.168.11.186 281
===========================================================================192.168.11.186 255.255.255.255 在链路上 192.168.11.186 281
其中这个是自身的IP地址，查看是否已经不存在
Table has no partition for value
场景
MySQL由于安全性要求，版本升级之后，执行插入数据出现Table has no partition for value 错误
已有版本5.7.20-log升级到5.7.26-log
说明
建表过程中，指定时间identify_date（datetime类型）为主键
解决方案
ALTER TABLE `cs_oth_face_capture` PARTITION BY RANGE (to_days(identify_date))
(
PARTITION p_Dec VALUES LESS THAN MAXVALUE
);分区是为了更好的查询，当然建表的时候，不指定分区就可以了
分区可以加快查询的速度，但是时间分区的情况下，如果以天为单位分区，如果每天数据量都是几百万条，显然无法到快速查询的结果
innodb_system data file ibdata1 must be write
背景
操作系统Windows 软件版本MYSQL5.7.2
MySQL崩溃之后，无法启动，日志打印错误:
2020-08-06T12:11:24.667962Z 0 [Warning] option 'wait_timeout': unsigned value 31536000 adjusted to 2147483
2020-08-06T12:11:24.667962Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2020-08-06T12:11:24.667962Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2020-08-06T12:11:24.667962Z 0 [Note] mysqld (mysqld 5.7.20-log) starting as process 2140 ...
2020-08-06T12:11:24.714762Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2020-08-06T12:11:24.714762Z 0 [Note] InnoDB: Uses event mutexes
2020-08-06T12:11:24.714762Z 0 [Note] InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier
2020-08-06T12:11:24.714762Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2020-08-06T12:11:24.714762Z 0 [Note] InnoDB: Number of pools: 1
2020-08-06T12:11:24.714762Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2020-08-06T12:11:24.714762Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-08-06T12:11:24.730362Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-06T12:11:24.745962Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2020-08-06T12:11:24.745962Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2020-08-06T12:11:24.745962Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-08-06T12:11:24.964363Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-08-06T12:11:24.964363Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-08-06T12:11:24.964363Z 0 [ERROR] Failed to initialize plugins.
2020-08-06T12:11:24.964363Z 0 [ERROR] Aborting
2020-08-06T12:11:24.964363Z 0 [Note] Binlog end
2020-08-06T12:11:24.964363Z 0 [Note] Shutting down plugin 'CSV'
2020-08-06T12:11:24.964363Z 0 [Note] mysqld: Shutdown complete
异常有效日志
2020-08-06T12:11:24.745962Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2020-08-06T12:11:24.745962Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2020-08-06T12:11:24.745962Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-08-06T12:11:24.964363Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-08-06T12:11:24.964363Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-08-06T12:11:24.964363Z 0 [ERROR] Failed to initialize plugins.
解决方案
进入MySQL数据库数据文件夹data的目录，删除文件ib_logfile0， ib_logfile1,然后重新启动MySQL
采用指令启动mysqld.exe --defaults-file=F:\WebServer\mysql-5.7.20-winx64\mysql-5.7.20-winx64\my.ini
程序启动之后，打印日志
2020-08-07T08:44:03.157806Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 6364172
2020-08-07T08:44:03.158801Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 6364181
2020-08-07T08:44:03.159799Z 0 [Note] InnoDB: Database was not shutdown normally!
2020-08-07T08:44:03.160796Z 0 [Note] InnoDB: Starting crash recovery.
2020-08-07T08:44:03.364253Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=7] log sequence number 17142644237 is in the future! Current system log sequence number 6364190.
2020-08-07T08:44:03.365249Z 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2020-08-07T08:44:03.368241Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=2] log sequence number 17142553980 is in the future! Current system log sequence number 6364190.
2020-08-07T08:44:03.369239Z 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2020-08-07T08:44:03.371234Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=4] log sequence number 14215691586 is in the future! Current system log sequence number 6364190.
2020-08-07T08:44:03.372231Z 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
202
说明：MySQL数据库产生了损坏，目前暂时没有通过日志分析出更多有用的信息，例如到底是那张表写库失败，是否是系统忽然断电等问题
