Mysql停止了在Ubuntu22.04上的工作。升级服务器失败。错误

Question

我有一个基本的DigitalOcean液滴(1gb内存和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试图升级却失败了吗？我不记得最近进行过任何升级。这只是一个个人的服务器，我绝不是一个在服务器端的东西。有人知道我怎么解决这个问题吗？

Answer 1

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

1. 首先，我停止了mysql：service mysql stop
2. 停止mysql后，我使用/usr/sbin/mysqld --upgrade=minimal运行MYSQLD，确保升级参数最小。
3. 现在，如果出现错误，比如无法创建sock文件，那么可以创建/var/run/mysqld这样的文件夹。
4. 在执行以下操作时，请检查独立终端中的错误日志：tail -f /var/log/mysql/error.log
5. 现在您将看到mysqld在运行。最后，可以通过此cmd转储所有数据库：

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并安装一个稳定的版本并导入所有数据库。