problems_mysql

目录

1 centos6.9下rpm方式安装mysql后mysql服务无法启动

以下两种方式启动都报错：启动失败：

[root@node03 ~]# service mysqld start
MySQL Daemon failed to start.
Starting mysqld: [FAILED]

[root@node03 ~]# /etc/init.d/mysqld start
MySQL Daemon failed to start.
Starting mysqld: [FAILED]

原因：旧版本的MySQL软件移除时没有删除原来的数据文件，即 /var/lib/mysql/路径下的内容。
解决方法：删除该路径下的所有内容，包括文件和文件夹。

再次启动，报错另一个错误：

[root@node03 ~]# service mysqld start
Initializing MySQL database: 2019-06-26T16:37:53.977336Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-06-26T16:37:54.359914Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-06-26T16:37:54.431696Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-06-26T16:37:54.502670Z 0 [ERROR] unknown variable 'default-character-set=utf8'
2019-06-26T16:37:54.502687Z 0 [ERROR] Aborting
[FAILED]

原因：/etc/my.cnf的配置文件中，我配置了default-character-set，但MySQL5.7貌似不认该参数。
解决方法：把所有的default-character-set配置项注释掉，或删除掉。

再次启动，报第三个错误：

[root@node03 ~]# service mysqld start
Initializing MySQL database: 2019-06-26T16:46:34.713514Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-06-26T16:46:34.715422Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2019-06-26T16:46:34.715447Z 0 [ERROR] Aborting
[FAILED]

原因：由于第二次的启动失败，在/var/lib/mysql/路径下残留了一些文件，由于MySQL初始化时需要该路径下没有任何内容，所以启动失败。
解决方法：清空该路径下的所有内容。

再次启动，成功！

[root@node03 ~]# service mysqld start
Initializing MySQL database: 2019-06-26T16:47:53.295945Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp se er option (see documentation for more details).
2019-06-26T16:47:53.633116Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-06-26T16:47:53.699952Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-06-26T16:47:53.817335Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 246937da-9832-11e9-9e76-00505632a066.
2019-06-26T16:47:53.826311Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-06-26T16:47:53.832203Z 1 [Note] A temporary password is generated for root@localhost: nlaJ#thW=9zQ
[ OK ]
Starting mysqld: [ OK ]

2 centos7.7安装mysql5.7.29成功但启动失败

执行systemctl start mysqld报错，报错内容如下：

[root@node7 system]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

排查步骤：

  1. 执行systemctl status mysqld.service查看报错内容：
[root@node7 system]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: start-limit) since Fri 2020-02-14 11:37:23 CST; 23min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 2653 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 2632 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

Feb 14 11:37:22 node7 systemd[1]: Failed to start MySQL Server.
Feb 14 11:37:22 node7 systemd[1]: Unit mysqld.service entered failed state.
Feb 14 11:37:22 node7 systemd[1]: mysqld.service failed.
Feb 14 11:37:23 node7 systemd[1]: mysqld.service holdoff time over, scheduling restart.
Feb 14 11:37:23 node7 systemd[1]: Stopped MySQL Server.
Feb 14 11:37:23 node7 systemd[1]: start request repeated too quickly for mysqld.service
Feb 14 11:37:23 node7 systemd[1]: Failed to start MySQL Server.
Feb 14 11:37:23 node7 systemd[1]: Unit mysqld.service entered failed state.
Feb 14 11:37:23 node7 systemd[1]: mysqld.service failed.
  1. 执行journalctl -xe _SYSTEMD_UNIT=mysqld.service查看报错内容：
# 仅列出关键的错误信息：
Feb 14 12:22:52 node7 mysqld_pre_systemd[2707]: mysqld: Can't create directory '/var/lib/mysql/' (Errcode: 17 - File exists)
Feb 14 12:22:53 node7 mysqld_pre_systemd[2707]: 2020-02-14T04:22:52.998152Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please us
e --explicit_defaults_for_timestamp server option (see documentation for more details).
Feb 14 12:22:53 node7 mysqld_pre_systemd[2707]: 2020-02-14T04:22:52.999596Z 0 [ERROR] Aborting
Feb 14 12:22:53 node7 mysqld[2728]: 2020-02-14T04:22:53.182523Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit
_defaults_for_timestamp server option (see documentation for more details).


Feb 14 12:22:53 node7 mysqld[2728]: 2020-02-14T04:22:53.210779Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
Feb 14 12:22:53 node7 mysqld[2728]: 2020-02-14T04:22:53.210800Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the direct
ory.
Feb 14 12:22:53 node7 mysqld[2728]: 2020-02-14T04:22:53.210808Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
Feb 14 12:22:53 node7 mysqld[2728]: 2020-02-14T04:22:53.811648Z 0 [ERROR] Plugin 'InnoDB' init function returned error.

错误13说明没有权限。执行命令chmod -R 777 /var，还是无效。

然后，按照网上的资料，执行setenforce 0，也无效。（执行结果 SELinux is disabled）
提示一下：这个error number 就是系统的错误号，也是程序里的errorno。
附上linux的 错误码信息：

errno.00 is: Success    成功
errno.01 is: Operation not permitted         不允许此类操作
errno.02 is: No such file or directory         没有此文件或目录
errno.03 is: No such process                    没有此进程
errno.04 is: Interrupted system call           中断系统调用
errno.05 is: Input/output error                   输入输出错误
errno.06 is: No such device or address    没有此设备或地址
errno.07 is: Argument list too long            参数过长
errno.08 is: Exec format error                   执行格式错误
errno.09 is: Bad file descriptor                  无效的文件描述符
errno.10 is: No child processes                 不存在子进程
errno.11 is: Resource temporarily unavailable   资源暂时不可用
errno.12 is: Cannot allocate memory         分配内存失败
errno.13 is: Permission denied                   权限错误
errno.14 is: Bad address                           地址错误
errno.15 is: Block device required             块设备请求
errno.16 is: Device or resource busy         设备或资源忙
errno.17 is: File exists                                文件已经存在
errno.18 is: Invalid cross-device link          无效的交叉连接设备
errno.19 is: No such device                       没有此设备
errno.20 is: Not a directory                        不是一个目录
errno.21 is: Is a directory                           是一个目录
errno.22 is: Invalid argument                     参数非法
errno.23 is: Too many open files in system    系统打开文件太多
errno.24 is: Too many open files               打开文件太多
errno.25 is: Inappropriate ioctl for device          设备部支持该操作
errno.26 is: Text file busy                           文本文件忙
errno.27 is: File too large                           文件太大
errno.28 is: No space left on device            设备没有空间，一般为存储设备
errno.29 is: Illegal seek                               非法的seek操作
errno.30 is: Read-only file system               只读文件系统
errno.31 is: Too many links                         太多连接
errno.32 is: Broken pipe                              管道破裂
errno.33 is: Numerical argument out of domain          数值参数超出取值范围
errno.34 is: Numerical result out of range                  数值结果超出取值范围
errno.35 is: Resource deadlock avoided                   资源死锁
errno.36 is: File name too long                        文件名太长
errno.37 is: No locks available                         没有可用的锁
errno.38 is: Function not implemented            函数没有执行
errno.39 is: Directory not empty                      目录非空
errno.40 is: Too many levels of symbolic links 太多级符号连接
errno.41 is: Unknown error 41                         41未知错误
errno.42 is: No message of desired type         不被接受的消息类型
errno.43 is: Identifier removed                        标识符已被删除
errno.44 is: Channel number out of range     。。。
errno.45 is: Level 2 not synchronized
errno.46 is: Level 3 halted
errno.47 is: Level 3 reset
errno.48 is: Link number out of range
errno.49 is: Protocol driver not attached
errno.50 is: No CSI structure available
errno.51 is: Level 2 halted
errno.52 is: Invalid exchange
errno.53 is: Invalid request descriptor
errno.54 is: Exchange full
errno.55 is: No anode
errno.56 is: Invalid request code
errno.57 is: Invalid slot
errno.58 is: Unknown error 58
errno.59 is: Bad font file format
errno.60 is: Device not a stream
errno.61 is: No data available
errno.62 is: Timer expired
errno.63 is: Out of streams resources
errno.64 is: Machine is not on the network
errno.65 is: Package not installed
errno.66 is: Object is remote
errno.67 is: Link has been severed
errno.68 is: Advertise error
errno.69 is: Srmount error
errno.70 is: Communication error on send
errno.71 is: Protocol error
errno.72 is: Multihop attempted
errno.73 is: RFS specific error
errno.74 is: Bad message
errno.75 is: Value too large for defined data type
errno.76 is: Name not unique on network
errno.77 is: File descriptor in bad state
errno.78 is: Remote address changed
errno.79 is: Can not access a needed shared library
errno.80 is: Accessing a corrupted shared library
errno.81 is: .lib secion in a.out corrupted
errno.82 is: Attempting to link in too many shared libraries
errno.83 is: Cannot exec a shared library directly
errno.84 is: Invalid or incomplete multibyte or wide character
errno.85 is: Interrupted system call should be restarted
errno.86 is: Streams pipe error
errno.87 is: Too many users
errno.88 is: Socket operation on non-socket
errno.89 is: Destination address required
errno.90 is: Message too long
errno.91 is: Protocol wrong type for socket
errno.92 is: Protocol not available
errno.93 is: Protocol not supported
errno.94 is: Socket type not supported
errno.95 is: Operation not supported
errno.96 is: Protocol family not supported
errno.97 is: Address family not supported by protocol
errno.98 is: Address already in use
errno.99 is: Cannot assign requested address
errno.100 is: Network is down
errno.101 is: Network is unreachable
errno.102 is: Network dropped connection on reset
errno.103 is: Software caused connection abort
errno.104 is: Connection reset by peer
errno.105 is: No buffer space available
errno.106 is: Transport endpoint is already connected
errno.107 is: Transport endpoint is not connected
errno.108 is: Cannot send after transport endpoint shutdown
errno.109 is: Too many references: cannot splice
errno.110 is: Connection timed out
errno.111 is: Connection refused
errno.112 is: Host is down
errno.113 is: No route to host
errno.114 is: Operation already in progress
errno.115 is: Operation now in progress
errno.116 is: Stale NFS file handle
errno.117 is: Structure needs cleaning
errno.118 is: Not a XENIX named type file
errno.119 is: No XENIX semaphores available
errno.120 is: Is a named type file
errno.121 is: Remote I/O error
errno.122 is: Disk quota exceeded
errno.123 is: No medium found
errno.124 is: Wrong medium type
errno.125 is: Operation canceled
errno.126 is: Required key not available
errno.127 is: Key has expired
errno.128 is: Key has been revoked
errno.129 is: Key was rejected by service
errno.130 is: Owner died
errno.131 is: State not recoverable
errno.132 is: Unknown error 132
132-255全是Unknown error

参考链接：https://blog.csdn.net/chenjiayi_yun/article/details/16920967

终极解决方法：更换docker镜像，从docker中拉取mysql的镜像docker pull mysql:5.7，直接创建并启动容器，直接可以使用，很方便。
具体的使用方法：

docker pull mysql:5.7
docker run -d --name node7 -e MYSQL_ROOT_PASSWORD=root -p :33060:3306 --hostname node7 -v /usr/apps/:/usr/apps/ mysql:5.7
docker exec -it node7 bash
mysql -u root -p root

3 Java使用jdbc连接MySQL数据库时中文输出乱码

Java使用jdbc连接MySQL数据库时中文输出乱码, 插入数据表中的值, 中文全是问号。
解决方法：Java使用jdbc连接MySQL数据库时设置编码：
eg1.(设置本地连接: 省略了 localhost:3306 )
jdbc:mysql://192.168.6.6:3306/flink?useSSL=false&useUnicode=true&characterEncoding=UTF-8
注：如果是连接本地数据库，192.168.6.6:3306可以省略。

4 mysql设置timpstamp的默认值为 '0000-00-00 00:00:00' 时报错

  1. 报错内容：ERROR 1067 (42000): Invalid default value for 'updateaDate'

  2. 原因和解决方法：
    sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 默认是这个配置，这个配置不允许日期字段的默认值为'0000-00-00 00:00:00'，
    所以改成了下面的配置：
    sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

记得修改完后，重启mysql服务： /etc/init.d/mysqld restart 或者 service mysqld restart。

  1. 注意事项
    一般在my.ini或my.cnf下配置该sql-mode，
    linux下的为my.cnf，其路径一般为 /etc/my.cnf 或 /etc/mysql/my.cnf 或 /usr/my.cnf；win下的是my.ini，一般会在安装目录的根目录。

5 MySQL导出数据到文件报错

  1. 执行如下语句
    mysql> select * from users into outfile "F:\Develop\MySQL57\Uploads\users.txt" lines terminated by "\r\n";

  2. 报错信息
    ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

  3. 原因
    MySQL的时候限制了导入与导出的目录权限，只能在规定的目录下才能导入。
    我们需要通过下面命令查看 secure-file-priv 当前的值是什么：

mysql> show variables like '%secure%';
+--------------------------+-----------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | F:\Develop\MySQL57\Uploads\ |
+--------------------------+-----------------------------+
3 rows in set, 1 warning (0.00 sec)

  1. 再次执行
    mysql> select * from users into outfile "F:/Develop/MySQL57/Uploads/users.txt" lines terminated by "\r\n";
    Query OK, 1 row affected (0.00 sec)
    成功！

  2. 注意
    windows和linux下，目录的分隔符都要为 / ，不能为 \ ，否则还是报同样的错误。

6 JavaWeb项目连接mysql报错

报错内容如下：Tue May 26 23:04:21 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
解决方法：在连接mysql的url中拼接?autoReconnect=true&useSSL=false
完整的url如下：jdbc.c3p0.url=jdbc:mysql://127.0.0.1:3306/testdb?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false

7 MySQL导出数据到文件报错

执行如下语句：

mysql> select * from users into outfile "F:\Develop\MySQL57\Uploads\users.txt" lines terminated by "\r\n";

errorlog:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

RCA: MySQL的时候限制了导入与导出的目录权限，只能在规定的目录下才能导入。
我们需要通过下面命令查看 secure-file-priv 当前的值是什么 ：

mysql> show variables like '%secure%';
+--------------------------+-----------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | F:\Develop\MySQL57\Uploads\ |
+--------------------------+-----------------------------+
3 rows in set, 1 warning (0.00 sec)

再次执行：

mysql> select * from users into outfile "F:/Develop/MySQL57/Uploads/users.txt" lines terminated by "\r\n";
Query OK, 1 row affected (0.00 sec)

成功！

note: windows和linux下，目录的分隔符都要为 / ，不能为 \ ，否则还是报同样的错误。

8 mysql设置timpstamp的默认值为 '0000-00-00 00:00:00' 时报错

desc: mysql设置timpstamp的默认值为 '0000-00-00 00:00:00' 时报错：

ERROR 1067 (42000): Invalid default value for 'updateaDate'

RCA:

# sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE
,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 默认是

这个配置，这个配置不允许日期字段的默认值为'0000-00-00 00:00:00'

solution: 改成下面的配置：

sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_
AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

记得修改完后，重启mysql服务： /etc/init.d/mysqld restart 或者 service mysqld restart。 两个命令的效果一样。

note: 一般在my.ini或my.cnf下配置该sql-mode，
linux下的为my.cnf，其路径一般为 /etc/my.cnf 或 /etc/mysql/my.cnf 或 /usr/my.cnf
win下的是my.ini，一般会在安装目录的根目录.

9 ubuntu18.04安装mysql后设置参数后无法使用root连接mysql

desc & action:
ubuntu上安装了mysql5.7.35，直接通过sudo apt install mysql-server 命令来安装mysql。
装完之后，运行sudo mysql_secure_isntallation 命令来设置root的密码，以及其他初始化设置。
并在/etc/mysql/my.cnf中配置字符集和关闭强密码验证。
然后执行 systemctl restart mysql 来重启mysql服务，重启服务失败、报错，注释掉my.cnf中的一些配置后可以重启，但是执行 mysql -uroot -ppassword 一直报错如下：

user@work:/develop/ideaws/shells/linux# mysql -uroot -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

之后各种折腾，一度以为是未安装validate_password插件的问题，于是在my.cnf中设置skip-grant-tables 后，免密登录mysql，然后执行命令INSTALL PLUGIN validate_password SONAME 'validate_password.so' 安装插件，又报错，原来是因为设置了skip-grant-tables 后，无法加载该插件。
后来卸载了mysql，使用deb包的方式安装，在官网上下载了deb安装包。还是一样的错误。

solution:
最终这么解决的：使用root账户来操作。

note: 在ubuntu上配置mysql时，使用root账户来操作！

10 mysql5.7连接报错

执行语句和报错内容如下：

witt@work:~/logs/inventorys$ mysql -uuser1 -p"password1" -h hostname -P 3306
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1129 (HY000): Host '192.168.1.3' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

RCA: 未知
SOLUTION：执行这条命令就行：mysqladmin flush-hosts

11

12

13

14

15

posted @ 2021-08-25 15:39  mediocrep  阅读(312)  评论(0)  编辑  收藏  举报
既然选择了远方，便只顾风雨兼程！