unknown variable ‘authentication

码农世界 2024-05-27 后端 66 次浏览 0个评论

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 insensitive
2023-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 builtins
2023-12-27T11:50:43.486915Z 0 [Note] InnoDB: Uses event mutexes
2023-12-27T11:50:43.486919Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2023-12-27T11:50:43.486923Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2023-12-27T11:50:43.487938Z 0 [Note] InnoDB: Number of pools: 1
2023-12-27T11:50:43.487999Z 0 [Note] InnoDB: Using CPU crc32 instructions
2023-12-27T11:50:43.662577Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2023-12-27T11:50:43.667975Z 0 [Note] InnoDB: Completed initialization of buffer pool
2023-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 tables
2023-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 start
2023-12-27T11:50:43.748088Z 0 [Note] InnoDB: 5.7.12 started; log sequence number 2527696
2023-12-27T11:50:43.748195Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql-5.7/data/ib_buffer_pool
2023-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:43
2023-12-27T11:50:43.749371Z 0 [ERROR] unknown variable 'authentication_policy=mysql_native_password'
2023-12-27T11:50:43.749381Z 0 [ERROR] Aborting
2023-12-27T11:50:43.749392Z 0 [Note] Binlog end
2023-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_pool
2023-12-27T11:50:43.856040Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 231227 19:50:43
2023-12-27T11:50:45.229428Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2527715
2023-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 insensitive
2023-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 builtins
2023-12-27T11:56:09.616218Z 0 [Note] InnoDB: Uses event mutexes
2023-12-27T11:56:09.616223Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2023-12-27T11:56:09.616227Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2023-12-27T11:56:09.617303Z 0 [Note] InnoDB: Number of pools: 1
2023-12-27T11:56:09.617372Z 0 [Note] InnoDB: Using CPU crc32 instructions
2023-12-27T11:56:09.809280Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2023-12-27T11:56:09.815378Z 0 [Note] InnoDB: Completed initialization of buffer pool
2023-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 tables
2023-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 start
2023-12-27T11:56:09.897098Z 0 [Note] InnoDB: 5.7.12 started; log sequence number 2528355
2023-12-27T11:56:09.897213Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql-5.7/data/ib_buffer_pool
2023-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:09
2023-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 key
2023-12-27T11:56:09.902581Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2023-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 events
2023-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)