docker mysql “ERROR 2002(HY000):Can‘t connect to local MySQL server through socket，mysql容器不断重启”处理方法

错误描述

• 1、错误现象1
  mysql 容器不断重启，如下图：

  

• 2、错误现象2
  进入mysql容器后，执行命令：

  mysql -uroot -p
  

  报错信息：

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket
  

运行环境

• Docker version 20.10.2, build 2291f61
• ubuntu 16.04 LTS
• mysql 5.6.39

szZack的文章

错误分析

先执行 docker logs mysql 查看日志
输入下面命令查看日志信息：

docker logs mysql

可以看到如下错误信息：

2022-09-29 06:43:20 1 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2022-09-29 06:43:20 1 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2022-09-29 06:43:20 1 [Note] InnoDB: Unable to open the first data file
2022-09-29 06:43:20 7f4bf44f9740  InnoDB: Operating system error number 11 in a file operation.
InnoDB: Error number 11 means 'Resource temporarily unavailable'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2022-09-29 06:43:20 1 [ERROR] InnoDB: Can't open './ibdata1'
2022-09-29 06:43:20 1 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2022-09-29 06:43:20 1 [ERROR] Plugin 'InnoDB' init function returned error.
2022-09-29 06:43:20 1 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2022-09-29 06:43:20 1 [ERROR] Unknown/unsupported storage engine: InnoDB
2022-09-29 06:43:20 1 [ERROR] Aborting

这里有2条重要的错误信息：
错误信息1：

2022-09-29 06:43:20 1 [ERROR] InnoDB: Can't open './ibdata1'

错误信息2：

2022-09-29 06:43:20 1 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!

szZack的文章

解决方法

1、使用网上的处理方法

• 1、尝试方法1
  从错误信息：

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysqldata/mysql.sock' (111)
  

  来判断可能是 /etc/my.cnf 中的配置错误
  判断 mysql.sock 的路径在 my.cnf 是否与错误信息的路径一致，如果不一致，参考下面这篇文章去解决：
  https://www.jb51.net/article/56952.htm
  http://t.zoukankan.com/magmell-p-8384525.html

  我的 my.cnf 的mysql.sock 路径与错误信息的路径一致，故不是该问题导致的。

• 2、尝试方法2
  执行命令：

  service mysqld status
  

  提示信息：

  mysqld is stopped
  

  试图启动mysql服务

  service mysqld start
  

  提示报错：

  mysqld: unrecognized service
  

  判断一下mysql是否安装

  安装方法参考这篇解决：
  https://blog.youkuaiyun.com/u012979864/article/details/79626427

  我的安装了mysql，不是这个错误。

• 3、尝试方法3
  如果 mysql 启动正常，参考这篇文章进行解决：
  https://blog.youkuaiyun.com/qq_30938705/article/details/87166459

  我的 mysql 明显启动不正常，而且mysql容器不断重启，不是这个问题。

2、我的处理方法

• 错误分析：
  从错误信息：

  [ERROR] InnoDB: Can't open './ibdata1'
  

  来判断，与 ibdata1 有关系，由于 docker 进程有异常中断过，我怀疑是文件损坏导致 ibdata1 无法打开。故采取下面的步骤解决了该问题：

szZack的文章

• 2.1 进入容器

  docker exec -it mysql bash
  

• 2.2 删除 ib_logfile0 ib_logfile1 ibdata1
  先查找文件位置

  find / -name ib_logfile0 
  

  我的文件路径：

  /var/lib/mysql
  

  删除文件 ib_logfile0 ib_logfile1 ibdata1：

  rm ib_logfile0  ib_logfile1  ibdata1
  

• 2.3 重启mysql容器
  先退出mysql容器

  exit
  

  再重启mysql容器

  docker restart mysql
  

• 2.4 测试mysql
  进入容器：

  docker exec -it mysql bash
  

  执行命令（以root账号进入mysql，可根据需要修改）：

  mysql -u root -p
  

  输入密码后，得到如下信息：

  root@xxx:/# mysql -u root -p
  Enter password:
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 1
  Server version: 5.6.39 MySQL Community Server (GPL)
  
  Copyright (c) 2000, 2018, 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.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  mysql> 
  
  

  出现上面的信息则说明 mysql 启动正常了。
  到此，我的问题解决了。

参考

1.http://t.zoukankan.com/magmell-p-8384525.html
2.https://blog.youkuaiyun.com/qq_37844454/article/details/122834493
3.https://www.jb51.net/article/56952.htm
4.https://blog.youkuaiyun.com/u012979864/article/details/79626427

szZack的文章