BlueXIII's Blog

macOS下使用brew快速安装Kafka

Posted on 2018-11-06

安装

1	brew install kafka

如果缺少JDK8依赖，或安装了JDK10，则需要先通过brew cask插件安装JDK8：

1	brew cask install java8

安装位置

1 2	/usr/local/Cellar/zookeeper /usr/local/Cellar/kafka

配置文件位置

1 2	/usr/local/etc/kafka/server.properties /usr/local/etc/kafka/zookeeper.properties

启动

直接启动：

1 2	zookeeper-server-start /usr/local/etc/kafka/zookeeper.properties & kafka-server-start /usr/local/etc/kafka/server.properties &

或通过brew services插件启动

1 2	brew services start zookeeper brew services start kafka

创建topic

1	kafka-topics.sh --create --zookeeper localhost:2181 --replication-factor 1 --partitions 1 --topic test

查看创建的topic

1	kafka-topics.sh --list --zookeeper localhost:2181

发送一些消息

1	kafka-console-producer.sh --broker-list localhost:9092 --topic test

消费消息

1	kafka-console-consumer.sh --bootstrap-server localhost:9092 --topic test --from-beginning

附：brew services说明

https://github.com/Homebrew/homebrew-services

附：brew cask说明

https://caskroom.github.io

Docker安装OracleExpress

Posted on 2018-11-06

GitHub地址

https://github.com/wnameless/docker-oracle-xe-11g

安装

Ubuntu 16.04环境:

1	docker pull wnameless/oracle-xe-11g:16.04

Ubuntu 18.04环境:

1	docker pull wnameless/oracle-xe-11g

启动

1 2	docker run -d -p 10022:22 -p 1521:1521 -e ORACLE_ALLOW_REMOTE=true wnameless/oracle-xe-11g:16.04 docker run -d -p 10022:22 -p 1521:1521 -e ORACLE_ALLOW_REMOTE=true myoracle

开放22和1521端口

查看启动日志

1	docker logs -f c154cf832b7a1c77092b2cbe47457153bf853d223d861d0f1cac6af4f73e3ea1

sqlplus测试

Oracle默认连接参数：

hostname: localhost
port: 1521
sid: xe
username: system
password: oracle

本地连接
su - oracle
cd $ORACLE_HOME
bin/sqlplus / as sysdba

或远程连接
sqlplus system/oracle@127.0.0.1:1521/xe

SSH连接

1 2	ssh-copy-id -i -p10022 root@127.0.0.1 #admin ssh root@127.0.0.1 -p 10022

docker exec连接

1	docker exec -it 5d9430198388 bash

挂载宿主磁盘

docker run -d -v /Users/bluexiii/extra/dump:/dump -p 10022:22 -p 1521:1521 -e ORACLE_ALLOW_REMOTE=true wnameless/oracle-xe-11g:16.04

docker run -d -v /Users/bluexiii/extra/dump:/dump -p 10022:22 -p 1521:1521 -e ORACLE_ALLOW_REMOTE=true myoracle

之后就可以impdp了

保存变更

1 2	docker diff 5d9430198388 docker commit 5d9430198388 myoracle

参考文档

https://my.oschina.net/Cleverdada/blog/801124
https://hacpai.com/article/1492914187689

Kettle学习笔记

Posted on 2018-11-06

本文简要整理一下Kettle学习过程中的相关文档

官网

https://community.hitachivantara.com/docs/DOC-1009855-data-integration-kettle
https://github.com/pentaho/pentaho-kettle

下载

https://sourceforge.net/projects/pentaho/files/Data%20Integration/

JDK10下启动报错，需切至低版本JDK

官方文档

https://wiki.pentaho.com/display/EAI/Pan+User+Documentation

中文教程

https://www.jianshu.com/p/4d8171150faf
https://juejin.im/entry/590c215ca22b9d0058e8e56b
https://ask.hellobi.com/blog/yuguiyang1990/category/1532

ktr执行

./pan.sh -file=”./scripts/APP_CODE.ktr”
./pan.sh -file=”./scripts/APP_ZHIJI_CORRESPONDING.ktr”
./pan.sh -file=”./scripts/USER_BASIC_INFO.ktr”

kjb执行

./kitchen.sh -file=”./scripts/heimdall.kjb”

命令行输出乱码

1	export LANG=zh_CN.UTF-8

MySQL插表乱码

数据源选项中
Advanced，添加:
set names utf8;

Options，添加：
characterEncoding = utf8

InfluxDB学习笔记

Posted on 2018-11-06

官网

https://www.influxdata.com/time-series-platform/influxdb/

InfluxDB是InfluxData的核心产品。InfluxDB是一个开源分布式时序、时间和指标数据库，使用Go语言编写，无需外部依赖。其设计目标是实现分布式和水平伸缩扩展。

教程

https://jasper-zhang1.gitbooks.io/influxdb/content/Concepts/key_concepts.html
https://docs.influxdata.com/influxdb/v1.5/
https://www.jianshu.com/p/48104975d60a
https://www.jianshu.com/p/a373784c0bf9
https://www.jianshu.com/p/b51ba7f88fb0
https://jkzhao.github.io/2017/12/15/时序数据库InfluxDB/
http://www.ywnds.com/?p=10763
https://anyof.me/articles/390
https://xtutu.gitbooks.io/influxdb-handbook/content/an_zhuang_shi_yong.html

https://www.jianshu.com/p/a1344ca86e9b
https://code-examples.net/zh-CN/docs/influxdata/influxdb/v1.3/troubleshooting/frequently-asked-questions/index

安装方式

https://influxdata.com/downloads/

macOS

1 2	brew update brew install influxdb

或

1 2	https://dl.influxdata.com/influxdb/releases/influxdb-1.5.4_darwin_amd64.tar.gz tar zxvf influxdb-1.5.4_darwin_amd64.tar.gz

Docker

1	docker pull influxdb

Ubuntu

1 2	wget https://dl.influxdata.com/influxdb/releases/influxdb_1.5.4_amd64.deb sudo dpkg -i influxdb_1.5.4_amd64.deb

CentOS

1 2	wget https://dl.influxdata.com/influxdb/releases/influxdb-1.5.4.x86_64.rpm sudo yum localinstall influxdb-1.5.4.x86_64.rpm

配置

配置文件路径

1
2
3

/usr/local/etc/influxdb.conf
或
/etc/influxdb/influxdb.conf

生成默认配置

1	influxd config > /etc/influxdb/influxdb.generated.conf

名词

name	desc
database	数据库
measurement	数据库中的表
points	表里面的一行数据
time	每个数据记录时间，是数据库中的主索引(会自动生成)
fields	各种记录值（没有索引的属性）也就是记录的值：温度，湿度
tags	各种有索引的属性：地区，海拔
series	tags的组合，类似复合索引

命令行方式登入

1 2	influx influx -precision rfc3339 # 显示标准时间

创建数据库

1
2
3

show databases
create database mydb
use mydb

显示所有表

1	show measurements

新建表/插入数据

无建表语句，第一次insert后自动创建 p

INSERT cpu,host=serverA,region=us_west value=0.64
INSERT temperature,machine=unit42,type=assembly external=25,internal=37

其中:  
cpu  # 表名
host=serverA,region=us_west  # tag
value=0.64  # field

删除表

1	drop measurement weather

series操作

series表示这个表里面的数据，可以在图表上画成几条线，series主要通过tags排列组合算出来。

1	show series from weather

查询数据

1
2
3

SELECT * FROM /.*/ LIMIT 1
SELECT * FROM cpu_load_short
SELECT * FROM cpu_load_short WHERE value > 0.9

查询/修改RETENTION

SHOW RETENTION POLICIES ON "mydb"
单位有 w/d/h/s
ALTER RETENTION POLICY "autogen" ON "mydb" DURATION 100d DEFAULT
ALTER RETENTION POLICY "autogen" ON "mydb" DURATION 0s DEFAULT

用户管理

显示用户
SHOW USERS
创建用户
CREATE USER "username" WITH PASSWORD 'password'
创建管理员权限的用户
CREATE USER "username" WITH PASSWORD 'password' WITH ALL PRIVILEGES
删除用户
DROP USER "username"

HTTP创建和删除数据库

curl -i -XPOST http://localhost:8086/query –data-urlencode “q=CREATE DATABASE mydb”
curl -POST http://localhost:8086/query –data-urlencode “q=DROP DATABASE mydb”

HTTP添加数据

单条

1	curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'cpu_load_short,host=server01,region=us-west value=0.64 1434055562000000000'

多条

1
2
3

curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'cpu_load_short,host=server02 value=0.67
cpu_load_short,host=server02,region=us-west value=0.55 1422568543702900257
cpu_load_short,direction=in,host=server01,region=us-west value=2.0 1422568543702900257'

使用HTTP查询数据

1	curl -GET 'http://localhost:8086/query?pretty=true' --data-urlencode "db=mydb" --data-urlencode "q=show measurements"

或直接在浏览器中:

1	http://localhost:8086/query?pretty=true&db=mydb&q=show%20measurements

1	curl -G 'http://localhost:8086/query?pretty=true' --data-urlencode "db=mydb" --data-urlencode "q=SELECT \"value\" FROM \"cpu_load_short\" WHERE \"region\"='us-west'"

查询多条用分号分割：

1	curl -GET 'http://localhost:8086/query?db=_internal' --data-urlencode "q=show databases;show measurements"

时间格式

epoch=[h,m,s,ms,u,ns]

1	curl -G 'http://localhost:8086/query' --data-urlencode "db=mydb" --data-urlencode "epoch=s" --data-urlencode "q=SELECT value FROM cpu_load_short WHERE region='us-west'"

指定每次查询数据大小

chunk_size

1	curl -G 'http://localhost:8086/query' --data-urlencode "db=mydb" --data-urlencode "chunk_size=200" --data-urlencode "q=SELECT value FROM cpu_load_short WHERE region='us-west'"

WEB控制台

1.3内置8086WEB管理已经移除，需要使用TICK工具栈中的Chronograf来进行管理。

下载安装说明：
https://portal.influxdata.com/downloads

1	brew install chronograf

使用:
http://localhost:8888

测试数据导入

https://docs.influxdata.com/influxdb/v1.6/query_language/data_download/

CREATE DATABASE NOAA_water_database

curl https://s3.amazonaws.com/noaa.water-database/NOAA_data.txt -o NOAA_data.txt
influx -import -path=NOAA_data.txt -precision=s -database=NOAA_water_database

influx -precision rfc3339 -database NOAA_water_database

数据查询详细语法

https://jasper-zhang1.gitbooks.io/influxdb/content/Query_language/data_exploration.html

备份

# 全量
influxd backup -portable /mnt/disk/bak/influxdb/total
# 指定库
influxd backup -portable -database heimdall /mnt/disk/bak/influxdb/heimdall
# 指定时间
influxd backup -portable -database heimdall -start 2018-09-01T00:00:00Z -end 2018-09-30T23:59:59Z  /mnt/disk/bak/influxdb/heimdall

恢复

# 恢复到名为heimdall_bak的库
influxd restore -portable -db heimdall -newdb heimdall_bak /mnt/disk/bak/influxdb/heimdall

# 移动库
create database heimdall_new 
use heimdall_bak
SELECT * INTO heimdall_new..:MEASUREMENT FROM /.*/ GROUP BY *
drop heimdall_bak

连续查询

自动缩小取样存储到新的measurement中

CREATE CONTINUOUS QUERY "cq_basic" ON "transportation"
BEGIN
  SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h)
END

使用brew services管理服务

Posted on 2018-11-06

简介

官网：
https://github.com/Homebrew/homebrew-services

macOS使用launchctl命令加载开机自动运行的服务，brew service可以简化lauchctl的操作。

以MySQL为例，使用launchctl启动:

1 2	ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

如使用brew service可以简化为:

1	brew services start mysql

常用命令

brew services list  # 查看使用brew安装的服务列表
brew services run formula|--all  # 启动服务（仅启动不注册）
brew services start formula|--all  # 启动服务，并注册
brew services stop formula|--all   # 停止服务，并取消注册
brew services restart formula|--all  # 重启服务，并注册
brew services cleanup  # 清除已卸载应用的无用的配置

配置文件目录

1 2	/Library/LaunchDaemons # 开机自启，需要sudo ~/Library/LaunchAgents # 用户登录后自启

以homebrew.mxcl.kafka.plist为例：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Label</key>
    <string>homebrew.mxcl.kafka</string>
    <key>WorkingDirectory</key>
    <string>/usr/local</string>
    <key>ProgramArguments</key>
    <array>
        <string>/usr/local/opt/kafka/bin/kafka-server-start</string>
        <string>/usr/local/etc/kafka/server.properties</string>
    </array>
    <key>RunAtLoad</key>
    <true/>
    <key>KeepAlive</key>
    <true/>
    <key>StandardErrorPath</key>
    <string>/usr/local/var/log/kafka/kafka_output.log</string>
    <key>StandardOutPath</key>
    <string>/usr/local/var/log/kafka/kafka_output.log</string>
</dict>
</plist>

在这里可以找到服务路径、启动参数、日志路径等

macOS下安装多个JDK并切换

Posted on 2018-11-06

一些废话

起因是macOS下安装Oracle官方的JDK10后，kettle启动报错，需要切至JDK8。
于是需要借助brew cask来安装不同版本的JDK，并自已写脚本切换。

安装

使用brew cask安装JDK

brew tap caskroom/versions
brew cask install java6  #JDK6
brew cask install java8  #JDK8
brew cask install java   #JDK10

切换脚本

vi .zshrc

# Switch JDK
export JAVA_6_HOME="/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home"
export JAVA_8_HOME="/Library/Java/JavaVirtualMachines/jdk1.8.0_172.jdk/Contents/Home"
export JAVA_10_HOME="/Library/Java/JavaVirtualMachines/jdk-10.0.1.jdk/Contents/Home"
export JAVA_HOME=$JAVA_8_HOME  # Default JDK8
alias jdk6="export JAVA_HOME=$JAVA_6_HOME;java -version"  # to JDK6 
alias jdk8="export JAVA_HOME=$JAVA_8_HOME;java -version"  # to JDK8
alias jdk10="export JAVA_HOME=$JAVA_8_HOME;java -version" # to JDK10

brew换源

Posted on 2018-11-06

替换

替换brew.git:
cd "$(brew --repo)"
git remote set-url origin https://mirrors.ustc.edu.cn/brew.git

替换homebrew-core.git:
cd "$(brew --repo)/Library/Taps/homebrew/homebrew-core"
git remote set-url origin https://mirrors.ustc.edu.cn/homebrew-core.git

重置

重置brew.git:
cd "$(brew --repo)"
git remote set-url origin https://github.com/Homebrew/brew.git

重置homebrew-core.git:
cd "$(brew --repo)/Library/Taps/homebrew/homebrew-core"
git remote set-url origin https://github.com/Homebrew/homebrew-core.git

ss+curl方式

export ALL_PROXY=socks5://127.0.0.1:1086

Oracle+MySQL+InfluxDB简易备份脚本

Posted on 2018-11-06

Oracle

#!/bin/bash
# ##################################################################
# Oracle定时Expdp脚本
# SQL> CREATE DIRECTORY backup as '/home/oracle/dump';
# SQL> grant read,write on directory backup to SYSTEM; 
# crontab -e
# 30 00 * * *    . $HOME/.bash_profile; cd /home/oracle/sh; ./bak_exp.sh
# ##################################################################

# 环境变量
USERNAME=system
PASSWORD=yourpass
SID=orcl
SCHEMA=yoursid
DUMP_DIR=/home/oracle/dump
DIRECTORY=BAKDIR
PARALLEL=1
DMP_FILE=BAK_`date +%Y%m%d%H%M%S`.dmp
LOG_FILE=BAK_`date +%Y%m%d%H%M%S`.log

# expdp导出
expdp $USERNAME@$SID/$PASSWORD schemas=$SCHEMA DIRECTORY=$DIRECTORY DUMPFILE=$DMP_FILE logfile=$LOG_FILE parallel=$PARALLEL;

# 压缩dmp
cd $DUMP_DIR
gzip $DMP_FILE

# 删除旧的dmp
find $backup_dir -name "*.dmp" -mtime +1 -exec rm {} \;

MySQL

#!/bin/bash
# ##################################################################
# MySQL定时备份
# crontab -e
# 30 01 * * *    . $HOME/.bash_profile; cd /home/youruser/mysqlbak/sh; ./bak_mysql.sh
# ##################################################################

# 环境变量
USERNAME=root
PASSWORD=yourpass
DATABASE=yourdb
BASE_DIR=/home/youruser/mysqlbak
DUMP_FILE=$BASE_DIR/BAK_`date +%Y%m%d%H%M%S`.sql

# 备份
cd $BASE_DIR
mysqldump -u$USERNAME -p$PASSWORD $DATABASE > $DUMP_FILE

# 压缩dmp
gzip $DUMP_FILE

# 删除旧的dmp
find $BASE_DIR -name "*.sql.gz" -mtime +7 -exec rm {} \;

InfluxDB

#!/bin/bash
# ##################################################################
# InfluxDB定时备份
# crontab -e
# 30 00 * * *    . $HOME/.bash_profile; cd /home/youruser/influxbak/sh; ./bak_influx.sh
# ##################################################################

# 环境变量
DATABASE=yourdb
BASE_DIR=/home/youruser/influxbak
DUMP_DIR=$BASE_DIR/BAK_`date +%Y%m%d%H%M%S`

# 备份
mkdir $DUMP_DIR
influxd backup -portable -database $DATABASE $DUMP_DIR

# 删除旧的dmp
cd $BASE_DIR
find $BASE_DIR -type d -name "BAK_*" -mtime +7 -exec rm -rf {} \;

CentOS7下Oracle11g静默安装

Posted on 2018-11-06

添加组和用户

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle

下载安装介质

下载并解压到/home/oracle/database

修改hosts

1 2	/etc/hosts 172.172.172.97 wfzj97

关闭selinux

vi /etc/selinux/config
SELINUX=disabled

setenforce 0
getenforce

关闭firewalld

1 2	systemctl stop firewalld systemctl disable firewalld

修改内核参数

vi /etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 1073741824
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

sysctl -p

修改/etc/security/limits.conf

oracle           soft    nproc           2047
oracle           hard    nproc           16384
oracle           soft    nofile          1024
oracle           hard    nofile          65536
oracle           soft    stack           10240

修改/etc/pam.d/login文件

1 2	session required /lib64/security/pam_limits.so session required pam_limits.so

修改/etc/profile

if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
   ulimit -p 16384
   ulimit -n 65536
  else
   ulimit -u 16384 -n 65536
  fi
fi

创建安装目录

mkdir -p /app/oracle/product/11.2.0
mkdir /app/oracle/oradata
mkdir /app/oracle/inventory
mkdir /app/oracle/fast_recovery_area
chown -R oracle:oinstall /app/oracle
chmod -R 775 /app/oracle

安装依赖

yum -y install gcc make binutils gcc-c++ compat-libstdc++-33 elfutils-libelf-devel elfutils-libelf-devel-static elfutils-libelf-devel ksh libaio libaio-devel numactl-devel sysstat unixODBC unixODBC-devel pcre-devel

设置Oracle用户环境变量

su - oracle
vim .bash_profile

添加：
ORACLE_BASE=/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0
ORACLE_SID=orcl
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH

编辑静默安装响应文件

vi /home/oracle/database/response/db_install.rsp

oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=gongxm
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/app/oracle/inventory
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOME=/app/oracle/product/11.2.0
ORACLE_BASE=/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
DECLINE_SECURITY_UPDATES=true

静默安装oracle

1	./runInstaller -silent -ignorePrereq -ignoreSysPrereqs -responseFile /home/oracle/database/response/db_install.rsp

执行root用户脚本

切回root

1 2	sh /app/oracle/inventory/orainstRoot.sh sh /app/oracle/product/11.2.0/root.sh

配置监听

su - oracle
netca -silent -responseFile /home/oracle/database/response/netca.rsp

修改dbca静默安装配置

vi /home/oracle/database/response/dbca.rsp

GDBNAME = "orcl"
SID = "orcl"
SYSPASSWORD = "oracle"
SYSTEMPASSWORD = "oracle"
SYSMANPASSWORD = "oracle"
DBSNMPPASSWORD = "oracle"
DATAFILEDESTINATION =/app/oracle/oradata
RECOVERYAREADESTINATION=/app/oracle/fast_recovery_area
CHARACTERSET = "ZHS16GBK"
TOTALMEMORY = "1638"

静默配置dbca

1	dbca -silent -responseFile /home/oracle/database/response/dbca.rsp

安装完成检查

lsnrctl status
sqlplus / as sysdba

CentOS7下Oracle11g自启配置

Posted on 2018-11-06

编辑init.d脚本

vi /etc/init.d/dbora

#! /bin/sh -x
#
# chkconfig: 2345 80 05
# description: start and stop Oracle Database Enterprise Edition on Oracle Linux 5 and 6
#

# In /etc/oratab, change the autostart field from N to Y for any
# databases that you want autostarted.
#
# Create this file as /etc/init.d/dbora and execute:
#  chmod 750 /etc/init.d/dbora
#  chkconfig --add dbora
#  chkconfig dbora on

# Note: Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for your installation.
# ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
ORACLE_HOME=/app/oracle/product/11.2.0

#
# Note: Change the value of ORACLE to the login name of the oracle owner
ORACLE=oracle

PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH

case $1 in
'start')
        echo -n $"Starting Oracle: "
        su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" &
        ;;
'stop')
        echo -n $"Shutting down Oracle: "
        su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME" &
        ;;
'restart')
        echo -n $"Shutting down Oracle: "
        su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME" &
        sleep 5
        echo -n $"Starting Oracle: "
        su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" &
        ;;
*)
        echo "usage: $0 {start|stop|restart}"
        exit
        ;;
esac

exit

赋权及加入自启

1
2
3

chmod 750 /etc/init.d/dbora
sudo chkconfig --add oracledb
chkconfig dbora on

编辑oratab

vi /etc/oratab

1	orcl:/app/oracle/product/11.2.0:Y

N改为Y

服务启停

1 2	service dbora start service dbora stop