无法与运行在MySQL上的MacOS建立连接

Question

我的MySQL似乎无法在我的MacOS Catalina (10.15)上正确运行，根据我的wordpress应用程序，它无法连接到我的MySQL服务器。当我试图在SQL Pro中读取数据库时，SQL也无法建立连接。但是，我可以使用我的终端shell登录MySQL，这让我很困惑。

我的MySQL流程有什么问题？昨天，MySQL在我的wordpress应用程序上运行得很好。唯一的区别是我重新启动了我的电脑，现在，我似乎无法成功地用自制软件启动它，wordpress无法建立与它的连接。以下是有关wordpress和自制软件的详细信息和日志的详细信息。

Wordpress

昨天，我在MacOS上重新启动了我的笔记本电脑，当我重新启动我的电脑后，我试图在本地运行我的wordpress应用程序，但是wordpress抱怨说建立数据库连接时出错了：

Warning: mysqli_real_connect(): Unexpected server response while doing caching_sha2 auth: 109 in /Users/robskrob/code/projects/myblog/wordpress/web/wp/wp-includes/wp-db.php on line 1633

Warning: mysqli_real_connect(): (HY000/2006): MySQL server has gone away in /Users/robskrob/code/projects/kingandpartners/myblog/wordpress/web/wp/wp-includes/wp-db.php on line 1633
MySQL server has gone away

Error establishing a database connection
This either means that the username and password information in your wp-config.php file is incorrect or we can’t contact the database server at 127.0.0.1. This could mean your host’s database server is down.

Are you sure you have the correct username and password?
Are you sure you have typed the correct hostname?
Are you sure the database server is running?
If you’re unsure what these terms mean you should probably contact your host. If you still need help you can always visit the WordPress Support Forums.

MySQL自制日志

我检查了我认为是在/usr/local/var/mysql/Roberts-MBP-4.fios-router.home.err中安装mysql的日志文件--这些是从今天开始的日志：

2020-01-17T23:59:48.010069Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 2098009ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2020-01-18T00:32:58.010992Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 1937613ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2020-01-18T02:26:25.009708Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6753733ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2020-01-18T03:15:04.012850Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 2868086ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2020-01-18T03:17:10.403347Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 113439ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2020-01-18T04:19:50.025641Z 0 [Note] Giving 0 client threads a chance to die gracefully
2020-01-18T04:19:50.026067Z 0 [Note] Shutting down slave threads
2020-01-18T04:19:50.026406Z 0 [Note] Forcefully disconnecting 0 remaining clients
2020-01-18T04:19:50.026859Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2020-01-18T04:19:50.028486Z 0 [Note] Binlog end
2020-01-18T04:19:50.266455Z 0 [Note] Shutting down plugin 'ngram'
2020-01-18T04:19:50.266534Z 0 [Note] Shutting down plugin 'partition'
2020-01-18T04:19:50.266547Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2020-01-18T04:19:50.266558Z 0 [Note] Shutting down plugin 'ARCHIVE'
2020-01-18T04:19:50.266566Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2020-01-18T04:19:50.278568Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2020-01-18T04:19:50.278607Z 0 [Note] Shutting down plugin 'MyISAM'
2020-01-18T04:19:50.278712Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2020-01-18T04:19:50.284742Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2020-01-18T04:19:50.284768Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2020-01-18T04:19:50.284780Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2020-01-18T04:19:50.284789Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2020-01-18T04:19:50.284796Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2020-01-18T04:19:50.284803Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2020-01-18T04:19:50.284810Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2020-01-18T04:19:50.284817Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2020-01-18T04:19:50.284824Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2020-01-18T04:19:50.284831Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2020-01-18T04:19:50.284838Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2020-01-18T04:19:50.284846Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2020-01-18T04:19:50.284855Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2020-01-18T04:19:50.284863Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2020-01-18T04:19:50.284870Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2020-01-18T04:19:50.284877Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2020-01-18T04:19:50.284884Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2020-01-18T04:19:50.284891Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2020-01-18T04:19:50.284898Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2020-01-18T04:19:50.284906Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2020-01-18T04:19:50.284913Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2020-01-18T04:19:50.284920Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2020-01-18T04:19:50.284930Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2020-01-18T04:19:50.284937Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2020-01-18T04:19:50.284944Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2020-01-18T04:19:50.284964Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2020-01-18T04:19:50.284980Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2020-01-18T04:19:50.284987Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2020-01-18T04:19:50.284994Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2020-01-18T04:19:50.285009Z 0 [Note] Shutting down plugin 'InnoDB'
2020-01-18T04:19:50.302153Z 0 [Note] InnoDB: FTS optimize thread exiting.
2020-01-18T04:19:50.302324Z 0 [Note] InnoDB: Starting shutdown...
2020-01-18T04:19:50.448900Z 0 [Note] InnoDB: Dumping buffer pool(s) to /usr/local/var/mysql/ib_buffer_pool
2020-01-18T04:19:50.455916Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 200117 23:19:50
2020-01-18T04:19:51.352113Z 0 [Note] InnoDB: Shutdown completed; log sequence number 26072066420
2020-01-18T04:19:51.354829Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-01-18T04:19:51.354892Z 0 [Note] Shutting down plugin 'MEMORY'
2020-01-18T04:19:51.355317Z 0 [Note] Shutting down plugin 'CSV'
2020-01-18T04:19:51.355373Z 0 [Note] Shutting down plugin 'sha256_password'
2020-01-18T04:19:51.355402Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-01-18T04:19:51.356443Z 0 [Note] Shutting down plugin 'binlog'
2020-01-18T04:19:51.364593Z 0 [Note] /usr/local/opt/mysql/bin/mysqld: Shutdown complete

2020-01-18T04:21:20.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Roberts-MBP-4.fios-router.home.err'.
2020-01-18T04:21:21.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2020-01-18T04:21:22.464837Z 0 [System] [MY-010116] [Server] /usr/local/opt/mysql/bin/mysqld (mysqld 8.0.18) starting as process 773
2020-01-18T04:21:23.267083Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2020-01-18T04:21:24.347624Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2020-01-18T04:22:10.697989Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2020-01-18T04:22:12.302757Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80018' started.
2020-01-18T04:22:20.760953Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80018' completed.
2020-01-18T04:22:20.869345Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-01-18T04:22:20.901909Z 0 [System] [MY-010931] [Server] /usr/local/opt/mysql/bin/mysqld: ready for connections. Version: '8.0.18'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew.
2020-01-18T04:22:21.087121Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2020-01-18T18:40:40.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Roberts-MBP-4.fios-router.home.err'.
2020-01-18T18:40:40.6NZ mysqld_safe A mysqld process already exists
2020-01-18T18:40:42.139017Z 0 [System] [MY-010910] [Server] /usr/local/opt/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.18)  Homebrew.
2020-01-18T18:40:50.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Roberts-MBP-4.fios-router.home.err'.
2020-01-18T18:40:50.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2020-01-18T18:40:50.703294Z 0 [System] [MY-010116] [Server] /usr/local/opt/mysql/bin/mysqld (mysqld 8.0.18) starting as process 15000
2020-01-18T18:40:50.709793Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2020-01-18T18:40:51.867098Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-01-18T18:40:51.893469Z 0 [System] [MY-010931] [Server] /usr/local/opt/mysql/bin/mysqld: ready for connections. Version: '8.0.18'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew.
2020-01-18T18:40:52.023790Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2020-01-18T18:53:44.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Roberts-MBP-4.fios-router.home.err'.
2020-01-18T18:53:44.6NZ mysqld_safe A mysqld process already exists
2020-01-18T18:53:45.316518Z 0 [System] [MY-010910] [Server] /usr/local/opt/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.18)  Homebrew.
2020-01-18T18:53:54.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Roberts-MBP-4.fios-router.home.err'.
2020-01-18T18:53:54.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2020-01-18T18:53:55.004674Z 0 [System] [MY-010116] [Server] /usr/local/opt/mysql/bin/mysqld (mysqld 8.0.18) starting as process 17779
2020-01-18T18:53:55.011065Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2020-01-18T18:53:57.547543Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-01-18T18:53:57.573755Z 0 [System] [MY-010931] [Server] /usr/local/opt/mysql/bin/mysqld: ready for connections. Version: '8.0.18'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew.
2020-01-18T18:53:57.706901Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060

Brew服务列表

当我运行brew services list时，我看到mysql具有状态error

$ brew services list
Name              Status  User     Plist
chromedriver      stopped
docker-machine    stopped
elasticsearch     started robskrob /Users/robskrob/Library/LaunchAgents/homebrew.mxcl.elasticsearch.plist
elasticsearch@2.4 stopped
httpd             stopped
mysql             error   robskrob /Users/robskrob/Library/LaunchAgents/homebrew.mxcl.mysql.plist
nginx             stopped
php               started robskrob /Users/robskrob/Library/LaunchAgents/homebrew.mxcl.php.plist
postgresql        stopped
rabbitmq          stopped
redis             started robskrob /Users/robskrob/Library/LaunchAgents/homebrew.mxcl.redis.plist

MySQL终端会话

我能够在zshell中输入一个mysql控制台会话：

$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.18 Homebrew

Copyright (c) 2000, 2019, 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> show tables;
ERROR 1046 (3D000): No database selected
mysql> use myblog_development;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;

这告诉我，在某种程度上，mysql似乎在运行，但我的wordpress应用程序无法建立到它的连接。

System.log

当我在我的机器上打印系统日志时，我看到了这些关于mysql的奇怪日志。

Jan 18 13:53:44 Roberts-MBP-4 com.apple.xpc.launchd[1] (homebrew.mxcl.mysql[14913]): Service did not exit 5 seconds after SIGTERM. Sending SIGKILL.
Jan 18 13:53:44 Roberts-MBP-4 com.apple.xpc.launchd[1] (homebrew.mxcl.mysql): This service is defined to be constantly running and is inherently inefficient.
Jan 18 13:53:44 Roberts-MBP-4 com.apple.xpc.launchd[1] (homebrew.mxcl.mysql[17411]): Service exited with abnormal code: 1
Jan 18 13:53:44 Roberts-MBP-4 com.apple.xpc.launchd[1] (homebrew.mxcl.mysql): Service only ran for 0 seconds. Pushing respawn out by 10 seconds.

自制论坛

在自制论坛上似乎也有类似的记录问题。

更新1

按照这里的说明，我运行了以下命令，以查看mysql的运行状态是否会得到改善：

$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
/Users/robskrob/Library/LaunchAgents/homebrew.mxcl.mysql.plist: Operation now in progress
$  launchctl remove ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
$ rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

因此，当我运行brew services list时，我现在看到mysql处于更好的状态-- mysql从error转到started，并且一直住在started:，mysql开始了robskrob mysql。

然而，wordpress仍然无法连接到mysql。

来自mysql的System.log的新日志如下：

Jan 18 14:36:05 Roberts-MBP-4 com.apple.xpc.launchd[1] (homebrew.mxcl.mysql): This service is defined to be constantly running and is inherently inefficient.
Jan 18 14:48:44 Roberts-MBP-4 com.apple.xpc.launchd[1] (homebrew.mxcl.mysql): This service is defined to be constantly running and is inherently inefficient.

Answer 1

好的，我在MySQL8.X.X上--我找到了本期，开始相信我的Mysql版本太高级了。以本指南为参考，我删除了我的MySQL版本并删除了所有的MySQL数据库。然后，我安装了自制mysql@5.7，并按照post安装说明进行安装，这样我的shell的PATH中就有了mysql。成功安装mysql@5.7之后，我重新启动了MacOS。然后，我创建了我的wordpress应用程序使用的mysql数据库，并用一些数据播撒了这个数据库。

随着mysql@5.7的新安装和我的wordpress应用程序使用的数据库中的一些锅炉数据，wordpress能够与mysql建立连接。

更新：

我只是想在这里发布，显然升级到自制的mysql 8.x.x有问题：

1. 如何从mysql 8.0.11恢复到特定的旧版本，如5.7.21？
2. 支持MySQL 8