解决Mawell1.29.2启动SQLException: You have an error in your SQL syntax问题

 问题背景

 此前在openEuler24.03 LTS环境下的Hive使用了MySQL8.4.2，在此环境下再安装并启动Maxwell1.29.2时出现如下问题

[ERROR] Maxwell: SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MASTER STATUS' at line 1

原因分析及解决方案

从Maxwell1.42.0版本才开始支持MySQL8.4版本。

而从Maxwell1.30.0开始，不支持JDK8，需要JDK11，也就是说Maxwell1.42.0也需要JDK11，如果要使用JDK8，需要使用比Maxwell1.30.0以下的版本，例如：Maxwell 1.29.2。

但通过解压tar包方式安装Hive，支持的Java版本最高为JDK8。

综合以上：Hive最高只能支持JDK8，在安装有Hive的环境下同时安装Maxwell，就只能安装Maxwell1.30.0以下，例如：Maxwell1.29.2；Hive和Maxwell同时需要MySQL，而Maxwell1.29.2不支持MySQL8.4，就需要降低MySQL版本，例如：MySQL8.0.42。

降低MySQL版本

降低MySQL版本为MySQL8.0.42，可参考：openEuler24.03 LTS下安装MySQL8.0.42

修复Hive

修改Hive MySQL驱动

查看MySQL8.0.x可用的驱动

https://mvnrepository.com/artifact/com.mysql/mysql-connector-j

看到MySQL8.0.33为8.0.x的最新驱动，点击图上8.0.33

点击图片箭头指向的jar，下载得到mysql-connector-j-8.0.33.jar，并将下载得到的jar上传到Linux Hive的lib目录(/opt/module/hive-3.1.3/lib/)下

[liang@node1 lib]$ ls | grep mysql-conn
mysql-connector-j-8.0.33.jar
mysql-connector-j-8.4.0.jar

删除原来的mysql-connector-j-8.4.0.jar

[liang@node1 lib]$ rm -rf mysql-connector-j-8.4.0.jar
[liang@node1 lib]$ ls | grep mysql-conn
mysql-connector-j-8.0.33.jar

初始化Hive元数据库

创建metastore数据库，metastore作为Hive的元数据库

[liang@node1 lib]$ mysql -uroot -p000000
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.42 Source distribution

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

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> create database metastore;
Query OK, 1 row affected (0.00 sec)

mysql> quit;
Bye
[liang@node1 lib]$

初始化Hive元数据库有两种方法：

方法1.如果此前有备份元数据，直接导入之前的备份了元数据库的数据，这样此前的Hive元数据不会丢失，能查到此前存在的表。

方法2.使用schematool命令初始化元数据库，此时元数据库是新的，不能查到此前存在的表。

根据实际情况选择其中一个方法操作，这里使用方法2演示。

$ schematool -initSchema -dbType mysql -verbose

初始化成功后，部分输出如下

0: jdbc:mysql://node1:3306/metastore> /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
No rows affected (0.001 seconds)
0: jdbc:mysql://node1:3306/metastore> !closeall
Closing: 0: jdbc:mysql://node1:3306/metastore?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
beeline>
beeline> Initialization script completed
schemaTool completed

到此Hive修复完成！ 

设置Maxwell

说明：

1.这里使用的Maxwell版本为Maxwell1.29.2；

2.此前已完成下载Maxwell并解压Maxwell，同时设置了Maxwell的环境变量，配置了config.properties，参考：安装Maxwell教程

开启bin-log

创建需要开启bin-log的数据库，例如：test

[liang@node1 ~]# mysql -uroot -p000000
...
mysql> create database test;

 修改mysql-server.cnf

[liang@node1 ~]$ sudo vim /etc/my.cnf.d/mysql-server.cnf

注意：这里修改的是/etc/my.cnf.d下的cnf文件。不建议直接修改/etc/my.cnf文件，否则可能会造成MySQL重启失败。

 重启MySQL

[liang@node1 my.cnf.d]$ sudo systemctl restart mysqld

查看MySQL状态

[liang@node1 my.cnf.d]$ sudo systemctl status mysqld
● mysqld.service - MySQL 8.0 database server
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: disa>
     Active: active (running) since Thu 2025-05-08 00:52:19 CST; 7s ago
    Process: 6909 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status>
    Process: 6935 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (co>
   Main PID: 6972 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 21353)
     Memory: 361.9M ()
     CGroup: /system.slice/mysqld.service
             └─6972 /usr/libexec/mysqld --basedir=/usr

5月 08 00:52:18 node1 systemd[1]: Starting MySQL 8.0 database server...
5月 08 00:52:19 node1 systemd[1]: Started MySQL 8.0 database server.

按q返回Linux命令行。 

查看master logs

mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       157 | No        |
+------------------+-----------+-----------+
1 row in set (0.00 sec)

创建maxwell数据库、创建maxwell用户并赋予其必要权限

mysql> CREATE DATABASE maxwell;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'maxwell'@'%' IDENTIFIED BY 'maxwell';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON maxwell.* TO 'maxwell'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'maxwell'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql>

启动ZooKeeper

$ zkServer.sh start

启动Kafka

$ kafka-server-start.sh -daemon $KAFKA_HOME/config/server.properties

启动Maxwell

[liang@node1 maxwell-1.29.2]$ bin/maxwell --config config.properties
Using kafka version: 1.0.0
01:12:46,841 INFO  Maxwell - Starting Maxwell. maxMemory: 786956288 bufferMemoryUsage: 0.25
01:12:46,883 INFO  SchemaStoreSchema - Creating maxwell database
01:12:47,042 INFO  ProducerConfig - ProducerConfig values:
        acks = 1
        batch.size = 16384
        bootstrap.servers = [node1:9092]
        buffer.memory = 33554432
        client.id =
        compression.type = snappy
        connections.max.idle.ms = 540000
        enable.idempotence = false
        interceptor.classes = null
        key.serializer = class org.apache.kafka.common.serialization.StringSerializer
        linger.ms = 0
        max.block.ms = 60000
        max.in.flight.requests.per.connection = 5
        max.request.size = 1048576
        metadata.max.age.ms = 300000
        metric.reporters = []
        metrics.num.samples = 2
        metrics.recording.level = INFO
        metrics.sample.window.ms = 30000
        partitioner.class = class org.apache.kafka.clients.producer.internals.DefaultPartitioner
        receive.buffer.bytes = 32768
        reconnect.backoff.max.ms = 1000
        reconnect.backoff.ms = 50
        request.timeout.ms = 30000
        retries = 0
        retry.backoff.ms = 100
        sasl.jaas.config = null
        sasl.kerberos.kinit.cmd = /usr/bin/kinit
        sasl.kerberos.min.time.before.relogin = 60000
        sasl.kerberos.service.name = null
        sasl.kerberos.ticket.renew.jitter = 0.05
        sasl.kerberos.ticket.renew.window.factor = 0.8
        sasl.mechanism = GSSAPI
        security.protocol = PLAINTEXT
        send.buffer.bytes = 131072
        ssl.cipher.suites = null
        ssl.enabled.protocols = [TLSv1.2, TLSv1.1, TLSv1]
        ssl.endpoint.identification.algorithm = null
        ssl.key.password = null
        ssl.keymanager.algorithm = SunX509
        ssl.keystore.location = null
        ssl.keystore.password = null
        ssl.keystore.type = JKS
        ssl.protocol = TLS
        ssl.provider = null
        ssl.secure.random.implementation = null
        ssl.trustmanager.algorithm = PKIX
        ssl.truststore.location = null
        ssl.truststore.password = null
        ssl.truststore.type = JKS
        transaction.timeout.ms = 60000
        transactional.id = null
        value.serializer = class org.apache.kafka.common.serialization.StringSerializer

01:12:47,076 INFO  AppInfoParser - Kafka version : 1.0.0
01:12:47,076 INFO  AppInfoParser - Kafka commitId : aaa7af6d4a11b29d
01:12:47,121 INFO  Maxwell - Maxwell v1.29.2 is booting (MaxwellKafkaProducer), starting at Position[BinlogPosition[mysql-bin.000003:157], lastHeartbeat=0]
01:12:47,229 INFO  AbstractSchemaStore - Maxwell is capturing initial schema
01:12:47,396 INFO  BinlogConnectorReplicator - Setting initial binlog pos to: mysql-bin.000003:157
01:12:47,418 INFO  BinaryLogClient - Connected to node1:3306 at mysql-bin.000003/157 (sid:6379, cid:24)
01:12:47,418 INFO  BinlogConnectorReplicator - Binlog connected.

看到能正常启动了，说明Maxwell启动问题已解决！

完成！enjoy it！