unknown variable ‘authentication

unknown variable 'authentication_policy=mysql_native_password'

• 背景
• 解决
• • 尝试一
  • 尝试二(解决)
  • 总结

    背景

    mac上安装多个版本数据库。我是通过dmg安装的，先装的5.7，再装的5.8，然后5.8的能正常用，5.7的启动不起来。报错信息为如下

    2023-12-27T11:50:43.479739Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2023-12-27T11:50:43.479980Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.2023-12-27T11:50:43.480218Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.12) starting as process 90928 ...2023-12-27T11:50:43.481832Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive2023-12-27T11:50:43.483414Z 0 [Warning] You need to use --log-bin to make --binlog-format work.2023-12-27T11:50:43.486307Z 0 [ERROR] Plugin keyring_file reported: 'keyring_file initialization failure. Please check if the keyring_file_data points to readable keyring file or keyring file can be created in the specified location. The keyring_file will stay unusable until correct path to the keyring file gets provided'2023-12-27T11:50:43.486907Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2023-12-27T11:50:43.486915Z 0 [Note] InnoDB: Uses event mutexes2023-12-27T11:50:43.486919Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier2023-12-27T11:50:43.486923Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.32023-12-27T11:50:43.487938Z 0 [Note] InnoDB: Number of pools: 12023-12-27T11:50:43.487999Z 0 [Note] InnoDB: Using CPU crc32 instructions2023-12-27T11:50:43.662577Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M2023-12-27T11:50:43.667975Z 0 [Note] InnoDB: Completed initialization of buffer pool2023-12-27T11:50:43.682914Z 0 [Note] InnoDB: Highest supported file format is Barracuda.2023-12-27T11:50:43.688889Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables2023-12-27T11:50:43.689059Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...2023-12-27T11:50:43.692481Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.2023-12-27T11:50:43.692851Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.2023-12-27T11:50:43.692857Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.2023-12-27T11:50:43.693045Z 0 [Note] InnoDB: Waiting for purge to start2023-12-27T11:50:43.748088Z 0 [Note] InnoDB: 5.7.12 started; log sequence number 25276962023-12-27T11:50:43.748195Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql-5.7/data/ib_buffer_pool2023-12-27T11:50:43.748427Z 0 [Note] Plugin 'FEDERATED' is disabled.2023-12-27T11:50:43.748994Z 0 [Note] InnoDB: Buffer pool(s) load completed at 231227 19:50:432023-12-27T11:50:43.749371Z 0 [ERROR] unknown variable 'authentication_policy=mysql_native_password'2023-12-27T11:50:43.749381Z 0 [ERROR] Aborting2023-12-27T11:50:43.749392Z 0 [Note] Binlog end2023-12-27T11:50:43.749429Z 0 [Note] Shutting down plugin 'ngram'2023-12-27T11:50:43.749437Z 0 [Note] Shutting down plugin 'partition'2023-12-27T11:50:43.749443Z 0 [Note] Shutting down plugin 'BLACKHOLE'2023-12-27T11:50:43.749449Z 0 [Note] Shutting down plugin 'ARCHIVE'2023-12-27T11:50:43.749454Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'2023-12-27T11:50:43.749481Z 0 [Note] Shutting down plugin 'MRG_MYISAM'2023-12-27T11:50:43.749488Z 0 [Note] Shutting down plugin 'MyISAM'2023-12-27T11:50:43.749499Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'2023-12-27T11:50:43.749505Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'2023-12-27T11:50:43.749510Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'2023-12-27T11:50:43.749515Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'2023-12-27T11:50:43.749519Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'2023-12-27T11:50:43.749524Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'2023-12-27T11:50:43.749529Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'2023-12-27T11:50:43.749533Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'2023-12-27T11:50:43.749538Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'2023-12-27T11:50:43.749544Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'2023-12-27T11:50:43.749548Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'2023-12-27T11:50:43.749554Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'2023-12-27T11:50:43.749559Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'2023-12-27T11:50:43.749565Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'2023-12-27T11:50:43.749571Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'2023-12-27T11:50:43.749577Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'2023-12-27T11:50:43.749581Z 0 [Note] Shutting down plugin 'INNODB_METRICS'2023-12-27T11:50:43.749585Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'2023-12-27T11:50:43.749589Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'2023-12-27T11:50:43.749592Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'2023-12-27T11:50:43.749596Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'2023-12-27T11:50:43.749599Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'2023-12-27T11:50:43.749603Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'2023-12-27T11:50:43.749607Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'2023-12-27T11:50:43.749610Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'2023-12-27T11:50:43.749614Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'2023-12-27T11:50:43.749617Z 0 [Note] Shutting down plugin 'INNODB_CMP'2023-12-27T11:50:43.749621Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'2023-12-27T11:50:43.749625Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'2023-12-27T11:50:43.749628Z 0 [Note] Shutting down plugin 'INNODB_TRX'2023-12-27T11:50:43.749632Z 0 [Note] Shutting down plugin 'InnoDB'2023-12-27T11:50:43.749719Z 0 [Note] InnoDB: FTS optimize thread exiting.2023-12-27T11:50:43.749747Z 0 [Note] InnoDB: Starting shutdown...2023-12-27T11:50:43.855004Z 0 [Note] InnoDB: Dumping buffer pool(s) to /usr/local/mysql-5.7/data/ib_buffer_pool2023-12-27T11:50:43.856040Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 231227 19:50:432023-12-27T11:50:45.229428Z 0 [Note] InnoDB: Shutdown completed; log sequence number 25277152023-12-27T11:50:45.230046Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"2023-12-27T11:50:45.230082Z 0 [Note] Shutting down plugin 'MEMORY'2023-12-27T11:50:45.230103Z 0 [Note] Shutting down plugin 'CSV'2023-12-27T11:50:45.230116Z 0 [Note] Shutting down plugin 'sha256_password'2023-12-27T11:50:45.230127Z 0 [Note] Shutting down plugin 'mysql_native_password'2023-12-27T11:50:45.230136Z 0 [Note] Shutting down plugin 'keyring_file'2023-12-27T11:50:45.233646Z 0 [Note] Shutting down plugin 'binlog'2023-12-27T11:50:45.233996Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

    解决

    报错信息主要有两个：

    • keyring_file
    • authentication_policy=mysql_native_password

      第一个感觉不是很关键，因为报错了程序还是继续往下在走。第二个的话，查了一下说是配置错误，这个配置是8.0的，5.7的识别不了。

      尝试一

      既然识别不了，把配置改了不就行了。但是又有一个问题，用的是默认配置，只能新建配置进行覆写。

      这里可以看到，也有keyring的路径，这次我没处理它，但是在对应路径下创建了文件。然后手动创建了启动配置，然后设置了对应的目录，这里注意需要apply一下。

      但是呢，再次重启还是失败，keyring的问题没有修复，指定的配置文件也没有生效。。服务一直处于运行中 》 暂停 》运行中的***循环。突发奇想，ps了一下，发现有惊喜。。。

      理解没啥问题，配置实际也读取到了，但是最后它又加了奇奇怪怪的一些配置，然后其中就有导致我们出错的命令authentication_policy=mysql_native_password。那么，问题来了，这东西是哪里来的呢？

      尝试二(解决)

      查了一些资料，说是一般在/Library/LaunchDaemons目录下，我进去看了一眼，过滤了一下，发现的确有它。

      看了一下文件内容，找到了！

      后面就是把那一行注了，然后把之前指定的配置文件和keyring配置关了

      再次尝试重启，启动成功了！

      2023-12-27T11:56:09.333257Z 0 [ERROR] kevent failed with errno= 9,exiting timer notifier thread.2023-12-27T11:56:09.610993Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2023-12-27T11:56:09.611244Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.2023-12-27T11:56:09.611509Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.12) starting as process 92465 ...2023-12-27T11:56:09.613531Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive2023-12-27T11:56:09.615096Z 0 [Warning] You need to use --log-bin to make --binlog-format work.2023-12-27T11:56:09.616205Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2023-12-27T11:56:09.616218Z 0 [Note] InnoDB: Uses event mutexes2023-12-27T11:56:09.616223Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier2023-12-27T11:56:09.616227Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.32023-12-27T11:56:09.617303Z 0 [Note] InnoDB: Number of pools: 12023-12-27T11:56:09.617372Z 0 [Note] InnoDB: Using CPU crc32 instructions2023-12-27T11:56:09.809280Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M2023-12-27T11:56:09.815378Z 0 [Note] InnoDB: Completed initialization of buffer pool2023-12-27T11:56:09.830516Z 0 [Note] InnoDB: Highest supported file format is Barracuda.2023-12-27T11:56:09.837315Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables2023-12-27T11:56:09.837470Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...2023-12-27T11:56:09.841482Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.2023-12-27T11:56:09.841870Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.2023-12-27T11:56:09.841876Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.2023-12-27T11:56:09.842056Z 0 [Note] InnoDB: Waiting for purge to start2023-12-27T11:56:09.897098Z 0 [Note] InnoDB: 5.7.12 started; log sequence number 25283552023-12-27T11:56:09.897213Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql-5.7/data/ib_buffer_pool2023-12-27T11:56:09.897499Z 0 [Note] Plugin 'FEDERATED' is disabled.2023-12-27T11:56:09.897755Z 0 [Note] InnoDB: Buffer pool(s) load completed at 231227 19:56:092023-12-27T11:56:09.902571Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key2023-12-27T11:56:09.902581Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 33062023-12-27T11:56:09.902757Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';2023-12-27T11:56:09.902809Z 0 [Note] Server socket created on IP: '127.0.0.1'.2023-12-27T11:56:09.934502Z 0 [Note] Event Scheduler: Loaded 0 events2023-12-27T11:56:09.934584Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.Version: '5.7.12'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

      总结

      碰到问题还是看看日志吧，一般都会记录原因。一开始我先是以为版本问题，换了一个5.7的版本，试了一下没用。然后百度、google查资料，也没找到线索。。最后还是得自己分析。。。