mysql 谬误解决

[root@aliyun ~]# $sudo systemctl status mysqld● mysqld.service - MySQL Server   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)   Active: failed (Result: exit-code) since 二 2020-09-29 23:51:24 CST; 7s ago     Docs: man:mysqld(8)           http://dev.mysql.com/doc/refman/en/using-systemd.html  Process: 9882 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)  Process: 9859 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 9882 (code=exited, status=1/FAILURE)   Status: "Server startup in progress"9月 29 23:51:23 aliyun systemd[1]: Starting MySQL Server...9月 29 23:51:24 aliyun systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE9月 29 23:51:24 aliyun systemd[1]: Failed to start MySQL Server.9月 29 23:51:24 aliyun systemd[1]: Unit mysqld.service entered failed state.9月 29 23:51:24 aliyun systemd[1]: mysqld.service failed.

执行命令 tail -n 50 /var/log/mysqld.log

2020-09-29T14:54:47.587779Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 129752020-09-29T14:54:47.610146Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.2020-09-29T14:54:47.611012Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.2020-09-29T14:54:47.611068Z 0 [ERROR] [MY-010119] [Server] Aborting2020-09-29T14:54:47.612228Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21)  MySQL Community Server - GPL.2020-09-29T15:13:03.348418Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 134442020-09-29T15:13:03.384928Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.2020-09-29T15:13:03.385123Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.2020-09-29T15:13:03.385189Z 0 [ERROR] [MY-010119] [Server] Aborting2020-09-29T15:13:03.395177Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21)  MySQL Community Server - GPL.2020-09-29T15:17:13.585271Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 134952020-09-29T15:17:13.616247Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.2020-09-29T15:17:13.616544Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.2020-09-29T15:17:13.616601Z 0 [ERROR] [MY-010119] [Server] Aborting2020-09-29T15:17:13.617788Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21)  MySQL Community Server - GPL.2020-09-29T15:17:39.637824Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 135312020-09-29T15:17:39.651063Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.2020-09-29T15:17:39.651264Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.2020-09-29T15:17:39.651317Z 0 [ERROR] [MY-010119] [Server] Aborting2020-09-29T15:17:39.652382Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21)  MySQL Community Server - GPL.2020-09-29T15:28:08.142572Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 140302020-09-29T15:28:08.172640Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.2020-09-29T15:28:08.172826Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.2020-09-29T15:28:08.172888Z 0 [ERROR] [MY-010119] [Server] Aborting

cat /etc/my.cnf mysql的配置文件
设置mysql数据库的数据的寄存目录

datadir=/usr/local/mysql/data

执行命令 systemctl status mysqld.service

● mysqld.service - MySQL Server   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)   Active: active (running) since 二 2020-09-29 23:56:56 CST; 44s ago     Docs: man:mysqld(8)           http://dev.mysql.com/doc/refman/en/using-systemd.html  Process: 9922 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 9995 (mysqld)   Status: "Server is operational"   CGroup: /system.slice/mysqld.service           └─9995 /usr/sbin/mysqld

systemctl start mysqld.service
此时便胜利

查看linux版本 cat /etc/redhat-release
Linux version 3.10.0-1127.19.1.el7.x86_64 (mockbuild@kbuilder.bsys.centos.org) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-39) (GCC) ) #1 SMP Tue Aug 25 17:23:54 UTC 2020[root@aliyun ~]# cat /etc/redhat-releaseCentOS Linux release 7.8.2003 (Core)

查问mysql明码
cat /var/log/mysqld.log|grep password
后果如下

2020-09-29 21:43:35 28981 [Note] Shutting down plugin 'sha256_password'2020-09-29 21:43:35 28981 [Note] Shutting down plugin 'mysql_old_password'2020-09-29 21:43:35 28981 [Note] Shutting down plugin 'mysql_native_password'2020-09-29 21:45:15 29224 [Note] Shutting down plugin 'sha256_password'2020-09-29 21:45:15 29224 [Note] Shutting down plugin 'mysql_old_password'2020-09-29 21:45:15 29224 [Note] Shutting down plugin 'mysql_native_password'2020-09-29 22:33:16 29482 [Note] Shutting down plugin 'sha256_password'2020-09-29 22:33:16 29482 [Note] Shutting down plugin 'mysql_old_password'2020-09-29 22:33:16 29482 [Note] Shutting down plugin 'mysql_native_password'2020-09-29 22:40:21 12289 [Note] Shutting down plugin 'sha256_password'2020-09-29 22:40:21 12289 [Note] Shutting down plugin 'mysql_old_password'2020-09-29 22:40:21 12289 [Note] Shutting down plugin 'mysql_native_password'2020-09-29T15:56:51.612207Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: eAu-swLqm33&

应用 mysql -uroot -p登录
登录胜利后设置mysql明码
alter user 'root'@'localhost' identified by '19930515Aa.';
应用本地连接mysql出错
Host '211.161.244.24' is not allowed to connect to this MySQL server
批改mysql配置

use mysql;create user 'root'@'%' identified with mysql_native_password by 'Abcd123456.';grant all privileges on *.*'root'@'%';flush privileges;

获取分类视图失败

"select  * from category order by  category asc"}Error: ER_NO_SUCH_TABLE: Table 'book.category' doesn't exist

mysql8.0及以上版本，对于only_full_group_by的问题