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

Question

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

我的 MySQL 进程出了什么问题？昨天，MySQL 在我的 WordPress 应用程序中运行良好。唯一的区别是我重新启动了计算机，现在我似乎无法使用 homebrew 成功启动它，并且 wordpress 无法与其建立连接。以下是有关 wordpress 和 homebrew 的详细信息和日志的分割

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 Homebrew 日志

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

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 终端 session

我能够在我的 zshell 中进入 mysql 控制台 session :

$ 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 应用程序无法与其建立连接。

系统日志

当我在我的机器上打印系统日志时，我看到这些关于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.

Homebrew 论坛

似乎有一个类似的 logged issue在 Homebrew 论坛中。

更新 1

按照说明操作here我运行这些命令来查看 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/Users/robskrob/Library/LaunchAgents/homebrew.mxcl.mysql.plist`

但是，wordpress仍然无法连接到mysql。

System.log 中 mysql 的新日志是:

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

好吧，我在 Mysql 8.X.X 上——我发现 this issue并开始相信我的 MySQL 版本太高级了。使用this guide作为引用，我删除了我的 MySQL 版本并删除了所有 MySQL 数据库。然后我使用 homebrew mysql@5.7 进行安装，并按照安装后说明进行操作，以便我的 shell 在其 PATH 中包含 mysql。成功安装 mysql@5.7 后，我重新启动了 MacOS。然后我创建了我的 WordPress 应用程序使用的 mysql 数据库，并在该数据库中添加了一些数据。

通过全新安装 mysql@5.7 以及我的 WordPress 应用程序使用的数据库中的一些锅炉数据，WordPress 能够与 mysql 建立连接。

更新:

我只是想在这里发帖，显然升级到 Homebrew 程序的 mysql 8.x.x 存在问题:

1. How to revert from mysql 8.0.11 to a specific older version such as 5.7.21?
2. Homebrew, MySQL 8 support