首页 > 其他分享 >数仓：业务数据采集

数仓：业务数据采集

时间：2023-01-18 21:12:54浏览次数：62  
标签：opt 数仓 采集 maxwell 业务 cluster master mysql root

MySQL 安装

安装包准备

  1. 上传安装包和 JDBC 驱动。
(base) quanjunyi@Tys-MacBook-Pro ~ % docker cp 01_mysql-community-common-5.7.16-1.el7.x86_64.rpm cluster-master:/opt/
(base) quanjunyi@Tys-MacBook-Pro ~ % docker cp 02_mysql-community-libs-5.7.16-1.el7.x86_64.rpm cluster-master:/opt/
(base) quanjunyi@Tys-MacBook-Pro ~ % docker cp 03_mysql-community-libs-compat-5.7.16-1.el7.x86_64.rpm cluster-master:/opt/
(base) quanjunyi@Tys-MacBook-Pro ~ % docker cp 04_mysql-community-client-5.7.16-1.el7.x86_64.rpm cluster-master:/opt/
(base) quanjunyi@Tys-MacBook-Pro ~ % docker cp 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm cluster-master:/opt/
(base) quanjunyi@Tys-MacBook-Pro ~ % docker cp mysql-connector-java-5.1.27-bin.jar cluster-master:/opt/
  1. 卸载自带的 Mysql-libs（如果之前安装过 MySQL，要全都卸载掉）。
[root@cluster-master opt]# rpm -qa | grep -i -E mysql\|mariadb | xargs -n1 sudo rpm -e --nodeps
  1. 由于所用镜像没有如下工具，所以需要安装。
# 卸载MySQL依赖，虽然机器上没有装MySQL，但是这一步不可少
[root@cluster-master opt]# yum remove mysql-libs
Loaded plugins: fastestmirror, ovl
No Match for argument: mysql-libs
No Packages marked for removal

# 下载依赖并安装
[root@cluster-master opt]# yum install libaio
[root@cluster-master opt]# yum -y install autoconf

开始安装

  1. 安装 MySQL 依赖。
[root@cluster-master opt]# rpm -ivh 01_mysql-community-common-5.7.16-1.el7.x86_64.rpm
[root@cluster-master opt]# rpm -ivh 02_mysql-community-libs-5.7.16-1.el7.x86_64.rpm
[root@cluster-master opt]# rpm -ivh 03_mysql-community-libs-compat-5.7.16-1.el7.x86_64.rpm
  1. 安装 mysql-client
[root@cluster-master opt]# rpm -ivh 04_mysql-community-client-5.7.16-1.el7.x86_64.rpm
  1. 安装 mysql-server
[root@cluster-master opt]# rpm -ivh 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm
  1. 启动 MySQL
[root@cluster-master opt]# systemctl start mysqld

启动不了，不打算按照上面方法进行安装了。

卸载 MySQL

  1. yum 检查
[root@cluster-master opt]# yum list installed | grep mysql
mysql-community-client.x86_64              5.7.16-1.el7                installed
mysql-community-common.x86_64              5.7.16-1.el7                installed
mysql-community-libs.x86_64                5.7.16-1.el7                installed
mysql-community-libs-compat.x86_64         5.7.16-1.el7                installed
mysql-community-server.x86_64              5.7.16-1.el7                installed

[root@cluster-master opt]# yum remove mysql-community-client.x86_64
[root@cluster-master opt]# yum remove mysql-community-common.x86_64
  1. rpm 检查
[root@cluster-master opt]# rpm -qa | grep -i mysql
  1. 口令查找 MySQL 的安装目录和残存文件
[root@cluster-master opt]# whereis mysql
mysql: /usr/share/mysql
[root@cluster-master opt]# rm -rf /usr/share/mysql

mysql:[root@cluster-master opt]# find / -name mysql
/var/lib/mysql
/usr/lib/python2.7/site-packages/ansible/modules/database/mysql
[root@cluster-master opt]# rm -rf /var/lib/mysql

[root@cluster-master lib]# rm -rf /var/log/mysqld.log
[root@cluster-master lib]# rm -rf /var/log/mysql.log
  1. 查看 MySQL 配置文件
[root@cluster-master opt]# rm -rf /etc/my.cnf

[root@cluster-master etc]# chkconfig --list | grep -i mysql
[root@cluster-master etc]# chkconfig --del mysqld
error reading information on service mysqld: No such file or directory

重新安装

  1. 下载安装包
[root@cluster-master opt]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
  1. 解压文件
[root@cluster-master opt]# tar xzvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
  1. 将解压后的文件重新命名
[root@cluster-master opt]# mv mysql-5.7.24-linux-glibc2.12-x86_64 mysql
  1. 在 /opt/mysql 目录下创建 data 目录
[root@cluster-master opt]# cd mysql
[root@cluster-master mysql]# mkdir data
  1. 编译安装并初始化 mysql
[root@cluster-master mysql]# cd bin/
[root@cluster-master bin]# ./mysqld --initialize --user=root --datadir=/opt/mysql/data/ --basedir=/opt/mysql

[root@cluster-master bin]# ./mysqld --initialize --user=root --datadir=/opt/mysql/data/ --basedir=/opt/mysql
2023-01-16T16:46:23.498103Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
# 在my.cnf中添加innodb_use_native_aio = 0
2023-01-16T16:46:23.541972Z 0 [ERROR] InnoDB: Linux Native AIO interface is not supported on this platform. Please check your OS documentation and install appropriate binary of InnoDB.
2023-01-16T16:46:23.543495Z 0 [Warning] InnoDB: Linux Native AIO disabled.
2023-01-16T16:46:23.980946Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-01-16T16:46:24.150150Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-01-16T16:46:24.306767Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5011ef3d-95bd-11ed-851a-0242ac140002.
2023-01-16T16:46:24.314370Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-01-16T16:46:24.377260Z 1 [Note] A temporary password is generated for root@localhost: Rwy04/qeTm1Y
  1. 创建 myslq 组和 mysql 用户
[root@cluster-master etc]# cat /etc/group | grep mysql
mysql:x:27:
[root@cluster-master etc]# cat /etc/passwd |grep mysql
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/false

# 更改mysql目录下所有的目录及文件夹所属的用户组和用户，以及权限
[root@cluster-master mysql]# chown -R mysql:mysql /opt/mysql
[root@cluster-master mysql]# chmod -R 755 /opt/mysql
  1. 启动 mysql
# 清空data目录下的内容
[root@cluster-master mysql]# rm -rf data/
[root@cluster-master mysql]# mkdir data

#  初始化
[root@cluster-master bin]# ./mysqld --initialize --user=root --datadir=/opt/mysql/data/ --basedir=/opt/mysql
2023-01-16T17:15:11.673653Z 0 [Warning] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line

2023-01-16T17:15:11.675399Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-01-16T17:15:12.256161Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-01-16T17:15:12.435479Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-01-16T17:15:12.543774Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 562dfb32-95c1-11ed-adf0-0242ac140002.
2023-01-16T17:15:12.549210Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-01-16T17:15:12.578107Z 1 [Note] A temporary password is generated for root@localhost: WgNk)ihLq8j2

# 启动mysql
[root@cluster-master bin]# /opt/mysql/support-files/mysql.server start
/opt/mysql/support-files/mysql.server: line 239: my_print_defaults: command not found
/opt/mysql/support-files/mysql.server: line 259: cd: /usr/local/mysql: No such file or directory
Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)

# 启动时没找见(/usr/local/mysql/bin/mysqld_safe)这个命令，我的安装路径不是默认的，是（/opt/mysql）
# 配置my.cnf文件的basedir为当前的安装路径在使用mysql.server启动就好了
basedir=/opt/mysql

# 再次启动就成功了
[root@cluster-master bin]# /opt/mysql/support-files/mysql.server start
Starting MySQL........ SUCCESS!
  1. 登录 mysql
[root@cluster-master bin]# ./mysql -u root -p
Enter password:WgNk)ihLq8j2
  1. 密码重置
# 设置密码
mysql> set password for root@localhost = password('root');

远程连接

  1. 开放远程连接
# 进入MySQL库
mysql> use mysql;
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

# 修改user表，把Host表内容修改为%
mysql> update user set user.Host='%' where user.User='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  1. 外部工具连接 mysql

连接mysql

业务模拟数据

建表语句

  1. 通过 Navicat 创建数据库，设置数据库名称为 gmall，编码为 utf-8，排序规则为 utf8_general_ci。

创建数据库

  1. 导入数据库结构脚本（gmall.sql）。

执行sql

生成数据

  1. 在 master 的 /opt/module/ 目录下创建 db_log 文件夹。
[root@cluster-master etc]# cd /opt/module/
[root@cluster-master module]# mkdir db_log
  1. 把 gmall2020-mock-db-2021-11-14.jar 和 application.properties 上传到 master 的 /opt/module/db_log 路径上。
(base) quanjunyi@Tys-MacBook-Pro ~ % docker cp application.properties cluster-master:/opt/module/db_log/
(base) quanjunyi@Tys-MacBook-Pro ~ % docker cp gmall2020-mock-db-2021-11-14.jar cluster-master:/opt/module/db_log/
  1. 根据需求修改 application.properties 相关配置。
logging.level.root=info

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://cluster-master:3306/gmall?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=root

logging.pattern.console=%m%n

mybatis-plus.global-config.db-config.field-strategy=not_null

# 业务日期
mock.date=2020-06-14
# 是否重置  注意：第一次执行必须设置为1，后续不需要重置，所以要设置为0
mock.clear=1
# 是否重置用户 注意：第一次执行必须设置为1，后续不需要重置，所以要设置为0
mock.clear.user=1

# 生成新用户数量
mock.user.count=100
# 男性比例
mock.user.male-rate=20
# 用户数据变化概率
mock.user.update-rate:20

# 收藏取消比例
mock.favor.cancel-rate=10
# 收藏数量
mock.favor.count=100

# 每个用户添加购物车的概率
mock.cart.user-rate=50
# 每次每个用户最多添加多少种商品进购物车
mock.cart.max-sku-count=8 
# 每个商品最多买几个
mock.cart.max-sku-num=3 

# 购物车来源:用户查询,商品推广,智能推荐,促销活动
mock.cart.source-type-rate=60:20:10:10

# 用户下单比例
mock.order.user-rate=50
# 用户从购物中购买商品比例
mock.order.sku-rate=50
# 是否参加活动
mock.order.join-activity=1
# 是否使用购物券
mock.order.use-coupon=1
# 购物券领取人数
mock.coupon.user-count=100

# 支付比例
mock.payment.rate=70
# 支付方式:支付宝,微信,银联
mock.payment.payment-type=30:60:10


# 评价比例:好,中,差,自动
mock.comment.appraise-rate=30:10:10:50

# 退款原因比例:质量问题,商品描述与实际描述不一致,缺货,号码不合适,拍错,不想买了,其他
mock.refund.reason-rate=30:10:20:5:15:5:5
  1. 并在该目录下执行，如下命令，生成 2020-06-14 日期数据
[root@cluster-master db_log]# java -jar gmall2020-mock-db-2021-11-14.jar

Maxwell 安装

Maxwell 简介

实时监 控Mysql 数据库的数据变更操作（包括 insert、update、delete），并将变更数据以 JSON 格式发送给 Kafka、Kinesi 等流数据处理平台。Maxwell 输出的 JSON 字段说明：

字段 解释
database 变更数据所属的数据库
table 表更数据所属的表
type 数据变更类型
ts 数据变更发生的时间
xid 事务id
commit 事务提交标志，可用于重新组装事务
data 对于 insert 类型，表示插入的数据；对于 update 类型，标识修改之后的数据；对于 delete 类型，表示删除的数据
old 对于 update 类型，表示修改之前的数据，只包含变更字段

Maxwell 的工作原理是实时读取 MySQL 数据库的二进制日志，从中获取变更数据，再将变更数据以 JSON 格式发送至 Kafka 等流处理平台。

二进制日志是 MySQL 服务端非常重要的一种日志，它会保存 MySQL 数据库的所有数据变更记录。其主要作用包括主从复制和数据恢复。

MySQL 的主从复制，就是用来建立一个和主数据库完全一样的数据库环境，这个数据库称为从数据库。

  1. Master 主库将数据变更记录，写到二进制日志中。
  2. Slave 从库向 mysql master 发送 dump 协议，将 master 主库的 binary log events 拷贝到它的中继日志。
  3. Slave 从库读取并回放中继日志中的事件，将改变的数据同步到自己的数据库。

Maxwell 安装

  1. 上传 maxwell-1.29.2.tar.gz 并解压缩至 /opt/module 目录下。
(base) quanjunyi@Tys-MacBook-Pro ~ % docker cp maxwell-1.29.2.tar.gz cluster-master:/opt/

[root@cluster-master opt]# tar -zxvf maxwell-1.29.2.tar.gz -C /opt/module/
  1. 启用 MySQL Binlog
[root@cluster-master maxwell-1.29.2]# vim /etc/my.cnf

# 增加如下配置
[mysqld]
# 数据库id
server-id = 1
# 启动binlog，该参数的值会作为binlog的文件名
log-bin=mysql-bin
# binlog类型，maxwell要求为row类型
binlog_format=row
# 启用binlog的数据库，需根据实际情况作出修改
binlog-do-db=gmall
  1. 重启 MySQL 服务
[root@cluster-master bin]# /opt/mysql/support-files/mysql.server restart
Shutting down MySQL. SUCCESS!
Starting MySQL......... SUCCESS!
  1. 查看 Binlog 是否生效
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 | gmall        |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

Maxwell 要求 Binlog 采用Row-based 模式。

创建 Maxwell 所需数据库和用户

Maxwell 需要在 MySQL 中存储其运行过程中的所需的一些数据，包括 binlog 同步的断点位置（Maxwell 支持断点续传）等等，故需要在 MySQL 为 Maxwell 创建数据库及用户。

  1. 创建数据库
mysql> CREATE DATABASE maxwell;
Query OK, 1 row affected (0.01 sec)
  1. 创建 Maxwell 用户并赋予其必要权限
# 密码为maxwell
mysql> CREATE USER 'maxwell'@'%' IDENTIFIED BY 'maxwell';
Query OK, 0 rows affected (0.02 sec)

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

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

Maxwell 配置

  1. 修改 Maxwell 配置文件名称
[root@cluster-master maxwell-1.29.2]# cd /opt/module/maxwell-1.29.2/
[root@cluster-master maxwell-1.29.2]# cp config.properties.example config.properties
  1. 修改 Maxwell 配置文件
[root@cluster-master maxwell-1.29.2]# vim config.properties

#Maxwell数据发送目的地，可选配置有stdout|file|kafka|kinesis|pubsub|sqs|rabbitmq|redis
producer=kafka
#目标Kafka集群地址
kafka.bootstrap.servers=cluster-master:9092,cluster-slave1:9092

#目标Kafka topic，可静态配置，例如:maxwell，也可动态配置，例如：%{database}_%{table}
kafka_topic=maxwell

#MySQL相关配置
host=cluster-master
user=maxwell
password=maxwell
jdbc_options=useSSL=false&serverTimezone=Asia/Shanghai

Maxwell 使用

启动 Kafka

若 Maxwell 发送数据的目的地为 Kafka 集群，则需要先确保 Kafka 集群为启动状态。

[root@cluster-master qjy]# bin/xcall jps
[root@cluster-master qjy]# bin/kf.sh start

Maxwell 启停

  1. 启动 Maxwell
[root@cluster-master qjy]# /opt/module/maxwell-1.29.2/bin/maxwell --config /opt/module/maxwell-1.29.2/config.properties --daemon
Redirecting STDOUT to /opt/module/maxwell-1.29.2/bin/../logs/MaxwellDaemon.out
Using kafka version: 1.0.0

[root@cluster-master qjy]# jps
98558 Maxwell
  1. 停止 Maxwell
[root@cluster-master qjy]# ps -ef | grep maxwell | grep -v grep | grep maxwell | awk '{print $2}' | xargs kill -9

Maxwell 起停脚本

  1. 创建并编辑 Maxwell 启停脚本
[root@cluster-master qjy]# cd /home/qjy/bin/
[root@cluster-master bin]# vim mxw.sh
[root@cluster-master bin]# chmod 777 mxw.sh
  1. 脚本内容如下
#!/bin/bash

MAXWELL_HOME=/opt/module/maxwell-1.29.2

status_maxwell(){
    result=`ps -ef | grep com.zendesk.maxwell.Maxwell | grep -v grep | wc -l`
    return $result
}

start_maxwell(){
    status_maxwell
    if [[ $? -lt 1 ]]; then
        echo "start Maxwell"
        $MAXWELL_HOME/bin/maxwell --config $MAXWELL_HOME/config.properties --daemon
    else
        echo "Maxwell is running"
    fi
}

stop_maxwell(){
    status_maxwell
    if [[ $? -gt 0 ]]; then
        echo "stop Maxwell"
        ps -ef | grep com.zendesk.maxwell.Maxwell | grep -v grep | awk '{print $2}' | xargs kill -9
    else
        echo "Maxwell is not running"
    fi
}

case $1 in
    start )
        start_maxwell
    ;;
    stop )
        stop_maxwell
    ;;
    restart )
       stop_maxwell
       start_maxwell
    ;;
esac
  1. 起停 Maxwell
# 启动
[root@cluster-master qjy]# bin/mxw.sh start
# 停止
[root@cluster-master qjy]# bin/mxw.sh stop

增量数据同步

  1. 启动 Kafka 消费者
[root@cluster-slave1 kafka_2.12-3.0.0]# bin/kafka-console-consumer.sh --bootstrap-server cluster-master:9092 -topic maxwell
  1. 模拟生成数据（不是第一次执行，需要修改配置文件）
[root@cluster-master db_log]# vim application.properties
mock.clear=0
mock.clear.user=0

[root@cluster-master db_log]# java -jar gmall2020-mock-db-2021-11-14.jar
  1. 观察 Kafka 消费者
{"database":"gmall","table":"comment_info","type":"insert","ts":1673968354,"xid":31577,"commit":true,"data":{"id":1615366474515464202,"user_id":181,"nick_name":null,"head_img":null,"sku_id":11,"spu_id":3,"order_id":5026,"appraise":"1204","comment_txt":"评论内容：47284787361557527653155851915757865121982342392985","create_time":"2020-06-14 15:12:33","operate_time":null}}

全量数据同步

有时只有增量数据是不够的，我们可能需要使用到 MySQL 数据库中从历史至今的一个完整的数据集。这就需要我们在进行增量同步之前，先进行一次历史数据的全量同步。这样就能保证得到一个完整的数据集。

  1. 启动 Kafka 消费者
[root@cluster-slave1 kafka_2.12-3.0.0]# bin/kafka-console-consumer.sh --bootstrap-server cluster-master:9092 -topic maxwell
  1. Maxwell 提供了 bootstrap 功能来进行历史数据的全量同步。
[root@cluster-master maxwell-1.29.2]# /opt/module/maxwell-1.29.2/bin/maxwell-bootstrap --database gmall --table user_info --config /opt/module/maxwell-1.29.2/config.properties
connecting to jdbc:mysql://cluster-master:3306/maxwell?allowPublicKeyRetrieval=true&connectTimeout=5000&serverTimezone=Asia%2FShanghai&zeroDateTimeBehavior=convertToNull&useSSL=false
  1. 观察 Kafka 消费者
{"database":"gmall","table":"user_info","type":"bootstrap-insert","ts":1674039994,"data":{"id":1000,"login_name":"um4wwf4k","nick_name":"悦悦","passwd":null,"name":"舒悦","phone_num":"13726391371","email":"[email protected]","head_img":null,"user_level":"1","birthday":"1998-09-14","gender":"F","create_time":"2020-06-14 07:10:39","operate_time":null,"status":null}}

第一条 type 为 bootstrap-start 和最后一条 type 为 bootstrap-complete 的数据，是 bootstrap 开始和结束的标志，不包含数据，中间的 type 为 bootstrap-insert 的数据才包含数据。一次 bootstrap 输出的所有记录的 ts 都相同，为 bootstrap 开始的时间。

采集通道配置

采集通道

数据采集通道

Maxwell 配置

  1. 修改 Maxwell 配置文件 config.properties
[root@cluster-master maxwell-1.29.2]# vim config.properties
  1. 配置参数如下
log_level=info

producer=kafka
kafka.bootstrap.servers=cluster-master:9092,cluster-slave1:9092

# kafka topic 配置
kafka_topic=topic_db

host=cluster-master
user=maxwell
password=maxwell
jdbc_options=useSSL=false&serverTimezone=Asia/Shanghai
  1. 重新启动 Maxwell
[root@cluster-master qjy]# bin/mxw.sh restart

通道测试

  1. 启动 Zookeeper 以及 Kafka 集群。
  2. 启动一个 Kafka Console Consumer，消费 topic_db 数据。
[root@cluster-slave1 kafka_2.12-3.0.0]# bin/kafka-console-consumer.sh --bootstrap-server
  1. 生成模拟数据
[root@cluster-master db_log]# java -jar gmall2020-mock-db-2021-11-14.jar
  1. 观察 Kafka 消费者是否能消费到数据。
{"database":"gmall","table":"comment_info","type":"insert","ts":1674046242,"xid":53907,"commit":true,"data":{"id":1615693160322764806,"user_id":292,"nick_name":null,"head_img":null,"sku_id":32,"spu_id":11,"order_id":5116,"appraise":"1204","comment_txt":"评论内容：69696583642368794414899554571493598254739617393385","create_time":"2020-06-14 12:50:41","operate_time":null}}

踩坑

安装 mysql-server 报错

# 这是由于yum安装了旧版本的GPG keys所造成，从rpm版本4.1后，在安装或升级软件包时会自动检查软件包的签名。
[root@cluster-master opt]# rpm -ivh 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm
warning: 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
	libnuma.so.1()(64bit) is needed by mysql-community-server-5.7.16-1.el7.x86_64
	net-tools is needed by mysql-community-server-5.7.16-1.el7.x86_64
	
[root@cluster-master opt]# rpm -ivh 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm --force --nodeps

启动 MySQL 报错

[root@cluster-master opt]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

# 查看具体信息
[root@cluster-master opt]# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: start-limit) since Sun 2023-01-15 16:13:03 UTC; 8min ago
  Process: 42372 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=127)
  Process: 42315 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

Jan 15 16:13:03 cluster-master systemd[1]: mysqld.service: control process ex...7
Jan 15 16:13:03 cluster-master systemd[1]: Failed to start MySQL Server.
Jan 15 16:13:03 cluster-master systemd[1]: Unit mysqld.service entered failed....
Jan 15 16:13:03 cluster-master systemd[1]: mysqld.service failed.
Jan 15 16:13:03 cluster-master systemd[1]: mysqld.service holdoff time over, ....
Jan 15 16:13:03 cluster-master systemd[1]: Stopped MySQL Server.
Jan 15 16:13:03 cluster-master systemd[1]: start request repeated too quickly...e
Jan 15 16:13:03 cluster-master systemd[1]: Failed to start MySQL Server.
Jan 15 16:13:03 cluster-master systemd[1]: Unit mysqld.service entered failed....
Jan 15 16:13:03 cluster-master systemd[1]: mysqld.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

# 查看mysql的权限
[root@cluster-master opt]# cd /var/lib/mysql
[root@cluster-master mysql]# ls -lrt
total 32
-rw------- 1 mysql mysql 1675 Jan 15 16:11 ca-key.pem
-rw-r--r-- 1 mysql mysql 1074 Jan 15 16:11 ca.pem
-rw------- 1 mysql mysql 1675 Jan 15 16:11 server-key.pem
-rw-r--r-- 1 mysql mysql 1078 Jan 15 16:11 server-cert.pem
-rw------- 1 mysql mysql 1675 Jan 15 16:11 client-key.pem
-rw-r--r-- 1 mysql mysql 1078 Jan 15 16:11 client-cert.pem
-rw------- 1 mysql mysql 1675 Jan 15 16:11 private_key.pem
-rw-r--r-- 1 mysql mysql  451 Jan 15 16:11 public_key.pem

# 显然以下方法无法解决问题
[root@cluster-master mysql]# chown mysql:mysql -R /var/lib/mysql

# 查看错误日志，发现没有任何显示
[root@cluster-master mysql]# vim /var/log/mysqld.log

# 网上也有说是磁盘空间不足，但是查看发现并不是这个问题。
[root@cluster-master mysql]# df
Filesystem     1K-blocks     Used Available Use% Mounted on
overlay         61202244 14040236  44020684  25% /
tmpfs              65536        0     65536   0% /dev
tmpfs            4071128        0   4071128   0% /sys/fs/cgroup
shm                65536        0     65536   0% /dev/shm
/dev/vda1       61202244 14040236  44020684  25% /etc/hosts
tmpfs            4071128    51140   4019988   2% /run
tmpfs             814228        0    814228   0% /run/user/0

# 再用journalctl -xe查看日志
[root@cluster-master qjy]# journalctl -xe
-- Logs begin at Mon 2023-01-09 04:26:01 UTC, end at Mon 2023-01-16 12:28:36 UTC
. --
Jan 16 12:00:27 cluster-master mysqld_pre_systemd[46497]: /usr/sbin/mysqld: erro
r while loading shared libraries: libnuma.so.1: cannot open shared object file:
No such file or directory
Jan 16 12:00:27 cluster-master mysqld[46554]: /usr/sbin/mysqld: error while load
ing shared libraries: libnuma.so.1: cannot open shared object file: No such file
 or directory
Jan 16 12:00:27 cluster-master systemd[1]: mysqld.service: control process
 exited, code=exited status=127
Jan 16 12:00:27 cluster-master systemd[1]: Failed to start MySQL Server.

# 尝试以下，发现无效
[root@cluster-master mysql]# yum -y install numactl.x86_64

# 再次查看错误日志
[root@cluster-master mysql]# vim /var/log/mysqld.log
2023-01-16T13:28:46.168901Z 0 [ERROR] InnoDB: Linux Native AIO interface is not supported on this platform. Please check your OS documentation and install appropriate binary of InnoDB.
2023-01-16T13:28:46.169057Z 0 [Note] InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf

2023-01-16T13:28:43.013776Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it

2023-01-16T13:28:43.086659Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2023-01-16T13:28:43.087315Z 0 [ERROR] Aborting

# 折磨一天无果，放弃，不打算按照上面方法进行安装了。

查看 stoped 进程

[root@cluster-master db_log]# ps -A -ostat,ppid,pid,cmd | grep -e '^[T]'

Kafka 报错

在测试增量数据同步时，在启动消费者时，Kafka 报如下错误：

[2023-01-17 11:12:13,666] WARN [Consumer clientId=consumer-console-consumer-60947-1, groupId=console-consumer-60947] Bootstrap broker cluster-master:9092 (id: -1 rack: null) disconnected (org.apache.kafka.clients.NetworkClient)

在 kafka 的启动日志 /opt/module/kafka_2.12-3.0.0/logs/server.log 中查看到以下错误：

[2023-01-17 11:02:35,658] WARN Session 0x200057f67de0009 for sever cluster-slave2/172.20.0.4:2181, Closing socket connection. Attempting reconnect except it is a SessionExpiredException. (org.apache.zookeeper.ClientCnxn)
org.apache.zookeeper.ClientCnxn$SessionExpiredException: Unable to reconnect to ZooKeeper service, session 0x200057f67de0009 has expired
	at org.apache.zookeeper.ClientCnxn$SendThread.onConnected(ClientCnxn.java:1434)
	at org.apache.zookeeper.ClientCnxnSocket.readConnectResult(ClientCnxnSocket.java:154)
	at org.apache.zookeeper.ClientCnxnSocketNIO.doIO(ClientCnxnSocketNIO.java:86)
	at org.apache.zookeeper.ClientCnxnSocketNIO.doTransport(ClientCnxnSocketNIO.java:350)
	at org.apache.zookeeper.ClientCnxn$SendThread.run(ClientCnxn.java:1290)

大致是 zookeeper 会话过期了，所以尝试重启 zookeeper 试试。

[root@cluster-master qjy]# bin/kf.sh stop
[root@cluster-master qjy]# bin/zk.sh stop
[root@cluster-master qjy]# bin/zk.sh start
[root@cluster-master qjy]# bin/kf.sh start

再次尝试测试增量数据同步，报如下错误：

[2023-01-17 11:46:06,616] WARN [Consumer clientId=consumer-console-consumer-61767-1, groupId=console-consumer-61767] Connection to node 2147483645 (cluster-slave2/172.20.0.4:9092) could not be established. Broker may not be available. (org.apache.kafka.clients.NetworkClient)

在集群中所有的 kafka 的 config/server.properties 文件中修改以下配置，然后重启 Kafka 即可。

# IP是本机的IP
advertised.listeners=PLAINTEXT://172.20.0.2:9092

Kafka 报错2

在测试采集通道的时候，启动 slave1 上 kafka 的消费者时，报如下错误：

[root@cluster-slave1 kafka_2.12-3.0.0]# bin/kafka-console-consumer.sh --bootstrap-server cluster-master:9092 -topic topic_db
[2023-01-18 11:30:08,381] WARN [Consumer clientId=consumer-console-consumer-68459-1, groupId=console-consumer-68459] Error while fetching metadata with correlation id 2 : {topic_db=LEADER_NOT_AVAILABLE} (org.apache.kafka.clients.NetworkClient)

在集群中所有的 kafka 的 config/server.properties 文件中修改以下配置，然后重启 Kafka 即可。

# IP是本机的IP
listeners=PLAINTEXT://172.20.0.2:9092

参考文章

https://www.bmabk.com/index.php/post/72678.html

https://www.jianshu.com/p/1665e117934f

https://blog.csdn.net/yunheming/article/details/86499106

https://blog.csdn.net/weixin_43201015/article/details/87105630

标签：opt,数仓,采集,maxwell,业务,cluster,master,mysql,root
From： https://www.cnblogs.com/fireonfire/p/17060565.html

相关文章

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99