sql

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/FAILURE
9月 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 12975
2020-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] Aborting
2020-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 13444
2020-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] Aborting
2020-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 13495
2020-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] Aborting
2020-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 13531
2020-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] Aborting
2020-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 14030
2020-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 ([e-mail]) (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-release
CentOS 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的问题