☰

Mysql 在 Ubuntu 22.04 上停止工作。无法升级服务器。错误

tag-icon • tag-icon mysql ubuntu-22.04
Mysql 在 Ubuntu 22.04 上停止工作。无法升级服务器。错误

我有一个基本的 DigitalOcean droplet（1GB RAM 和 1 CPU），上面有几个网站。有几个 nodejs 网站和几个 Wordpress 网站。所有网站的流量都很低。它通常工作正常，但今天没有一个 wordpress 网站在工作。所以我登录后发现 CPU 使用率固定在 100%，而正常情况下约为 2-3%。我重新启动了服务器，CPU 使用率下降了。但网站仍然无法正常工作。所以当我尝试访问网站时，它出错并说没有数据库连接。所以我检查了 mysql 的状态，它没有启动。我试图重新启动服务，但失败了。以下是结果sudo systemctl status mysql

mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: activating (start) since Tue 2023-05-09 19:44:41 CDT; 48s ago
    Process: 3859 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 3867 (mysqld)
     Status: "Server upgrade in progress"
      Tasks: 28 (limit: 1115)
     Memory: 491.4M
        CPU: 5.561s
     CGroup: /system.slice/mysql.service
             └─3867 /usr/sbin/mysqld

May 09 19:44:41 sk-webservice.com systemd[1]: mysql.service: Scheduled restart job, restart counter is at 7.
May 09 19:44:41 sk-webservice.com systemd[1]: Stopped MySQL Community Server.
May 09 19:44:41 sk-webservice.com systemd[1]: mysql.service: Consumed 5.529s CPU time.
May 09 19:44:41 sk-webservice.com systemd[1]: Starting MySQL Community Server...

然后我得到以下内容/var/log/mysql/error.log

2023-05-10T00:41:48.072177Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.33-0ubuntu0.22.04.1) starting as process 3037
2023-05-10T00:41:48.081239Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-05-10T00:41:48.660106Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-05-10T00:41:49.429920Z 4 [System] [MY-013381] [Server] Server upgrade from '80032' to '80033' started.
2023-05-10T00:42:44.456107Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Deprecate spatial reference systems -- Deprecation must happen before new SRSs are added/updated since there -- typically are new SRSs with the same names. REPLACE INTO mysql.st_spatial_reference_systems(id, catalog_id, name, organization, organization_coordsys_id, definition, description) VALUES (6956, 1, 'VN-2000 / TM-3 zone 481 (6956 deprecated)', 'EPSG', 6956, 'PROJCS["VN-2000 / TM-3 zone 481",GEOGCS["VN-2000",DATUM["Vietnam 2000",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],TOWGS84[-191.90441429,-39.30318279,-111.45032835,0.00928836,-0.01975479,0.00427372,0.252906278],AUTHORITY["EPSG","6756"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4756"]],PROJECTION["Transverse Mercator",AUTHORITY["EPSG","9807"]],PARAMETER["Latitude of natural origin",0,AUTHORITY["EPSG","8801"]],PARAMETER["Longitude of natural origin",102,AUTHORITY["EPSG","8802"]],PARAMETER["Scale factor at natural origin",0.9999,AUTHORITY["EPSG","8805"]],PARAMETER["False easting",0,AUTHORITY["EPSG","8806"]],PARAMETER["False northing",500000,AUTHORITY["EPSG","8807"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["E",EAST],AXIS["N",NORTH],AUTHORITY["EPSG","6956"]]', 'Deprecated since EPSG Dataset 9.3. Superseded by SRID 5896.'); ' failed with error code = 1205, error message = 'Lock wait timeout exceeded; try restarting transaction'.
2023-05-10T00:42:44.463355Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2023-05-10T00:42:44.463830Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-05-10T00:42:45.325713Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.33-0ubuntu0.22.04.1)  (Ubuntu).

所以看起来好像 mysql 正在尝试升级但失败了？我不记得最近运行过任何升级。这只是一个个人服务器，我绝不是服务器方面的专家。有人知道我该如何解决这个问题吗？

答案1

我今天遇到了同样的问题，这就是我解决问题的方法。

  1. 首先我停止了mysql：service mysql stop

  2. 停止 mysql 后，我运行带有升级的 MYSQLD：/usr/sbin/mysqld --upgrade=minimal 确保升级参数最少。

  3. 现在，如果您收到无法创建 sock 文件之类的错误，那么您可以创建 /var/run/mysqld 之类的文件夹

  4. 执行此操作时，请在单独的终端中检查错误日志：tail -f /var/log/mysql/error.log

  5. 现在你会看到 mysqld 正在运行。最后，你可以通过这个 cmd DUMP 所有数据库：

for DB in $(mysql -u root -pPassword -e 'show databases' -s --skip-column-names); do mysqldump -u root -pPassword $DB > "$DB.sql"; done

  1. 现在您可以卸载 mysql 并安装一个稳定的版本并导入所有数据库。

答案2

首先我停止了 mysql： service mysql stop

停止 mysql 后，我运行带有升级的 MYSQLD：/usr/sbin/mysqld --upgrade=minimal 确保升级参数最少。

现在，如果您收到无法创建 sock 文件之类的错误，那么您可以创建 /var/run/mysqld 之类的文件夹

我按照上述步骤启动了 mysql 服务，它正在运行

相关内容