ERROR 1123 (HY000): Can't initialize function 'group_replication'; Plugin initialization function fa

最新推荐文章于 2024-08-28 10:14:36 发布

吥輕誩放棄

最新推荐文章于 2024-08-28 10:14:36 发布

阅读量4.7k

点赞数

分类专栏： MySQL 5.7 文章标签： MySQL MGR Group Replication

本文链接：https://blog.csdn.net/sandy9919/article/details/90436957

版权

MySQL 5.7 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

MySQL Group Replication安装group_replication.so报错

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
ERROR 1123 (HY000): Can't initialize function 'group_replication'; Plugin initialization function failed.

mysql版本5.7.24，支持Group Replication功能，plugin目录中存在"group_replication.so"文件

[root@node1 logs]# ls -rtlh /app/mysql5.7/lib/plugin/ | grep group*
-rwxr-xr-x. 1 mysql mysql  21M May 17 16:06 group_replication.so

查看日志node.err

2019-05-21T08:39:24.050325Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-05-21T08:39:24.050480Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2019-05-21T08:39:24.050545Z 0 [Note] /app/mysql5.7/bin/mysqld (mysqld 5.7.24) starting as process 11564 ...
2019-05-21T08:39:24.108727Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-05-21T08:39:24.108774Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-05-21T08:39:24.108782Z 0 [Note] InnoDB: Uses event mutexes
2019-05-21T08:39:24.108789Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-05-21T08:39:24.108796Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-05-21T08:39:24.108803Z 0 [Note] InnoDB: Using Linux native AIO
2019-05-21T08:39:24.109331Z 0 [Note] InnoDB: Number of pools: 1
2019-05-21T08:39:24.109540Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-05-21T08:39:24.112278Z 0 [Note] InnoDB: Initializing buffer pool, total size = 512M, instances = 1, chunk size = 128M
2019-05-21T08:39:24.168366Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-05-21T08:39:24.178496Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-05-21T08:39:24.209811Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-05-21T08:39:24.269905Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-05-21T08:39:24.269987Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-05-21T08:39:24.325815Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-05-21T08:39:24.326728Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-05-21T08:39:24.326745Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-05-21T08:39:24.327934Z 0 [Note] InnoDB: 5.7.24 started; log sequence number 2591468
2019-05-21T08:39:24.328131Z 0 [Note] InnoDB: Loading buffer pool(s) from /app/mysql5.7/data/ib_buffer_pool
2019-05-21T08:39:24.328387Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-05-21T08:39:24.354922Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2019-05-21T08:39:24.354946Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2019-05-21T08:39:24.355008Z 0 [Note] IPv6 is available.
2019-05-21T08:39:24.355029Z 0 [Note]   - '::' resolves to '::';
2019-05-21T08:39:24.355063Z 0 [Note] Server socket created on IP: '::'.
2019-05-21T08:39:24.377232Z 0 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2019-05-21T08:39:24.377288Z 0 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2019-05-21T08:39:24.377303Z 0 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2019-05-21T08:39:24.377539Z 0 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2019-05-21T08:39:24.377551Z 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2019-05-21T08:39:24.377769Z 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2019-05-21T08:39:24.449899Z 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2019-05-21T08:39:24.449926Z 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
2019-05-21T08:39:24.456587Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190521 16:39:24
2019-05-21T08:39:24.456616Z 0 [Note] Failed to start slave threads for channel ''
2019-05-21T08:39:24.502029Z 0 [Note] Event Scheduler: Loaded 0 events
2019-05-21T08:39:24.504720Z 0 [Note] /app/mysql5.7/bin/mysqld: ready for connections.
Version: '5.7.24'  socket: '/app/mysql5.7/tmp/mysql.sock'  port: 3306  Source distribution
2019-05-22T00:23:31.539765Z 3 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2019-05-22T00:23:31.539846Z 3 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2019-05-22T00:23:31.539874Z 3 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2019-05-22T00:23:31.539922Z 3 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2019-05-22T00:23:31.539939Z 3 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2019-05-22T00:23:31.540438Z 3 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2019-05-22T00:23:31.540521Z 3 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2019-05-22T00:23:31.540544Z 3 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
2019-05-22T00:24:04.337450Z 3 [ERROR] For the creation of replication channels the master info and relay log info repositories must be set to TABLE
2019-05-22T00:24:04.337517Z 3 [ERROR] Plugin group_replication reported: 'Failure during Group Replication handler initialization'
2019-05-22T00:24:04.337529Z 3 [ERROR] Plugin 'group_replication' init function returned error.
2019-05-22T00:24:04.337559Z 3 [Note] Shutting down plugin 'group_replication'
2019-05-22T00:24:04.337591Z 3 [ERROR] Plugin group_replication reported: 'Failure when unregistering the server state observers'
2019-05-22T00:24:04.337608Z 3 [ERROR] Plugin group_replication reported: 'Failure when unregistering the transactions state observers'
2019-05-22T00:24:04.337615Z 3 [ERROR] Plugin group_replication reported: 'Failure when unregistering the binlog state observers'
2019-05-22T00:32:25.164673Z 3 [ERROR] Slave: Cannot create new master info structure when repositories are of type FILE. Convert slave repositories  to TABLE to replicate from multiple sources.
2019-05-22T00:37:43.690841Z 4 [ERROR] For the creation of replication channels the master info and relay log info repositories must be set to TABLE
2019-05-22T00:37:43.690918Z 4 [ERROR] Plugin group_replication reported: 'Failure during Group Replication handler initialization'
2019-05-22T00:37:43.690927Z 4 [ERROR] Plugin 'group_replication' init function returned error.
2019-05-22T00:37:43.690948Z 4 [Note] Shutting down plugin 'group_replication'
2019-05-22T00:37:43.690970Z 4 [ERROR] Plugin group_replication reported: 'Failure when unregistering the server state observers'
2019-05-22T00:37:43.690978Z 4 [ERROR] Plugin group_replication reported: 'Failure when unregistering the transactions state observers'
2019-05-22T00:37:43.690984Z 4 [ERROR] Plugin group_replication reported: 'Failure when unregistering the binlog state observers'

分析日志中的"warning&ERROR"，其中大量的'user' entry 'root@localhost' ignored in --skip-name-resolve mode.引起了关注。

这条warning是由于my.cnf中配置了“skip-name-resolve”，目的是不再进行反解析（ip不反解成域名），这样可以加快数据库的反应时间。启用后，在mysql的授权表中就不能使用主机名了，只能使用IP ，出现此警告是由于mysql.user表中已经存在有相关的帐号信息。

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| rep           | 10.6.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

这样问题就明了啦！启用了“skip-name-resolve”参数后，mysql.session@localhost就失效了。但组复制插件安装的必要条件是创建mysql.session用户。参考《MySQL 5.7 Reference Manual --- 17.2.1.4 Launching Group Replication》

解决方案：

注释掉my.cnf中的 “skip-name-resolve”参数。

日志输出

2019-05-22T00:52:03.845506Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-05-22T00:52:03.845632Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2019-05-22T00:52:03.845696Z 0 [Note] /app/mysql5.7/bin/mysqld (mysqld 5.7.24-log) starting as process 31206 ...
2019-05-22T00:52:03.853580Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-05-22T00:52:03.853640Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-05-22T00:52:03.853650Z 0 [Note] InnoDB: Uses event mutexes
2019-05-22T00:52:03.853657Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-05-22T00:52:03.853665Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-05-22T00:52:03.853672Z 0 [Note] InnoDB: Using Linux native AIO
2019-05-22T00:52:03.854174Z 0 [Note] InnoDB: Number of pools: 1
2019-05-22T00:52:03.854373Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-05-22T00:52:03.856599Z 0 [Note] InnoDB: Initializing buffer pool, total size = 512M, instances = 1, chunk size = 128M
2019-05-22T00:52:03.898026Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-05-22T00:52:03.905625Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-05-22T00:52:03.917632Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-05-22T00:52:03.943583Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-05-22T00:52:03.943667Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-05-22T00:52:03.990381Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-05-22T00:52:03.991310Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-05-22T00:52:03.991334Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-05-22T00:52:03.992117Z 0 [Note] InnoDB: Waiting for purge to start
2019-05-22T00:52:04.042544Z 0 [Note] InnoDB: 5.7.24 started; log sequence number 2591600
2019-05-22T00:52:04.042689Z 0 [Note] InnoDB: Loading buffer pool(s) from /app/mysql5.7/data/ib_buffer_pool
2019-05-22T00:52:04.043284Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-05-22T00:52:04.044510Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190522  8:52:04
2019-05-22T00:52:04.044851Z 0 [Warning] unknown variable 'loose-group_replication_group_name=fd434dd6-7ba3-11e9-af87-000c29346684'
2019-05-22T00:52:04.044876Z 0 [Warning] unknown variable 'loose-group_replication_start_on_boot=OFF'
2019-05-22T00:52:04.044883Z 0 [Warning] unknown variable 'loose-group_replication_local_address=10.6.0.137:20001'
2019-05-22T00:52:04.044889Z 0 [Warning] unknown variable 'loose-group_replication_group_seeds=10.6.0.137:20001,10.6.0.138:20002,10.6.0.139:20003'
2019-05-22T00:52:04.044895Z 0 [Warning] unknown variable 'loose-group_replication_bootstrap_group=OFF'
2019-05-22T00:52:04.044910Z 0 [Warning] unknown variable 'loose-group_replication_enforce_update_everywhere_checks=OFF'
2019-05-22T00:52:04.069579Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2019-05-22T00:52:04.069606Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2019-05-22T00:52:04.069665Z 0 [Note] IPv6 is available.
2019-05-22T00:52:04.069684Z 0 [Note]   - '::' resolves to '::';
2019-05-22T00:52:04.069706Z 0 [Note] Server socket created on IP: '::'.
2019-05-22T00:52:04.074463Z 0 [Note] Failed to start slave threads for channel ''
2019-05-22T00:52:04.082748Z 0 [Note] Event Scheduler: Loaded 0 events
2019-05-22T00:52:04.082943Z 0 [Note] /app/mysql5.7/bin/mysqld: ready for connections.
Version: '5.7.24-log'  socket: '/app/mysql5.7/tmp/mysql.sock'  port: 3306  Source distribution

再次安装group_replication.so，

mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |

......   略去输出信息

| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.00 sec)