通過logstash將mysql數(shù)據(jù)同步到elastic search

環(huán)境說明

  • 虛擬機(jī)一臺(tái)
  • logstash-7.2.1
  • elastic search-7.2.1
  • kibana-7.2.1
  • MySQL 5.7
  • jdk版本為jdk1.8.0_211

mysql安裝

  • 刪除預(yù)裝的Mariadb服務(wù) 
    rpm -qa | grep mariadb
yum remove mariadb
ls /etc/my.cnf
ls /var/lib/mysql/
rm -rf /etc/my.cnf
rm -rf /var/lib/mysql/
  • 安裝MySQL 
    # 下載MySQL官方repo
wget -i -c [http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm](http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm)
# 如果上述指令報(bào)錯(cuò)，可通過瀏覽器直接訪問該網(wǎng)址，自動(dòng)下載即可
# 安裝MySQL官方repo
yum -y localinstall mysql57-community-release-el7-10.noarch.rpm
# 安裝MySQL服務(wù)器
yum -y install mysql-community-server
  • 啟動(dòng)MySQL服務(wù) 
    systemctl start mysqld.service #啟動(dòng)mysql服務(wù)
systemctl status mysqld.service #查看MySQL服務(wù)工作狀態(tài)
  • 修改MySQL配置 
    # 在mysqld服務(wù)運(yùn)行日志中，查找初次登陸的默認(rèn)密碼
grep "password" /var/log/mysqld.log
# 通過默認(rèn)初始密碼進(jìn)入數(shù)據(jù)庫
mysql -u root -p
#進(jìn)入數(shù)據(jù)庫之后，修改root用戶密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY 'BUPTbupt@123';
# 進(jìn)入mysql數(shù)據(jù)庫，修改root用戶配置，以開啟遠(yuǎn)程訪問
use mysql;
update user set host = '%' where user = 'root';
# 完成上述配置后，退出MySQL
  • 重啟MySQL服務(wù) 
    #重啟MySQL服務(wù)
systemctl restart mysqld.service
#并重新登錄查看新密碼是否生效
#關(guān)閉yum自動(dòng)更新,否則以后每次yum都會(huì)自動(dòng)更新mysqld服務(wù)
yum -y remove mysql57-community-release-el7-10.noarch
  • 準(zhǔn)備測試數(shù)據(jù) 
     use mysql;
 create table Persons  ( PersonID int, LastName
varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );
 insert into Persons values(1, "Bryant", "Kobe", "1st a "1st ave", "Los");
 insert into Persons values(2, "James", "LeBron", "1st ave", "Miami");
 insert into Persons values(3, "Jordan", "Michale", "1st ave", "DC");

jdbc配置

  • https://downloads.mysql.com/archives/c-j/下載與當(dāng)前jdk版本匹配的mysql-connector
    image.png
  • 解壓到指定位置 
    unzip mysql-connector-java-5.1.4.zip
mv mysql-connector-java-5.1.4 /root/logstash-7.2.1/

elastic search安裝

  • 官網(wǎng)下載elasticsearch-7.2.1-linux-x86_64.tar.gz
  • 進(jìn)入root用戶，解壓并修改目錄屬主 
    tar -zxvf elasticsearch-7.2.1-linux-x86_64.tar.gz -C /usr/local/
cd /usr/local
mv elasticsearch-7.2.1/ elasticsearch
useradd elasticsearch
chown -R elasticsearch:elasticsearch /usr/local/elasticsearch/
  • 修改必要的配置文件 
    vi /etc/sysctl.conf
# 添加以下內(nèi)容
fs.file-max=655360
vm.max_map_count=262144

vi /etc/security/limits.conf
# 添加以下內(nèi)容
*               soft    nproc           204800
*               hard    nproc           204800
*               soft    nofile          655360
*               hard    nofile          655360
*               soft    memlock         unlimited
*               hard    memlock         unlimited  

vi /etc/security/limits.d/20-nproc.conf
#修改內(nèi)容如下
*          soft    nproc     204800
  • 激活上述配置項(xiàng) 
    sysctl -p
#CTRL + D退出當(dāng)前終端，而后重新登錄
ulimit -a #查看內(nèi)核修改配置是否生效
  • 切換到elasticsearch用戶，進(jìn)行后續(xù)配置 
    su elasticsearch
# 創(chuàng)建數(shù)據(jù)存儲(chǔ)目錄
mkdir -p /home/elasticsearch/data1/elasticsearch
mkdir -p /home/elasticsearch/data2/elasticsearch

vi /usr/local/elasticsearch/config/elasticsearch.yml
#修改如下內(nèi)容
cluster.name: elkbigdata
node.name: node-1
path.data: /home/elasticsearch/data1/elasticsearch/,         /home/elasticsearch/data2/elasticsearch
path.logs: /usr/local/elasticsearch/logs
bootstrap.memory_lock: true
network.host: 0.0.0.0  
http.port: 9200
cluster.initial_master_nodes: ["node-1"]
xpack.security.enabled: false
xpack.security.authc.accept_default_password: false

vi /usr/local/elasticsearch/config/jvm.options
#修改內(nèi)容如下
-Xms2g  #這個(gè)值一般設(shè)置為物理內(nèi)存的一半，不一定非要2g
-Xmx2g
  • 關(guān)閉防火墻并啟動(dòng)elastic search服務(wù) 
    #退回到root用戶
systemctl start firewalld
firewall-cmd --add-port=9200/tcp --zone=public --permanent 
firewall-cmd –reload
systemctl stop firewalld

su elasticsearch
cd /usr/local/elasticsearch/bin/
nohup ./elasticsearch &
  • 驗(yàn)證安裝是否成功 
    curl http://localhost:9200
#若出現(xiàn)下列內(nèi)容，說明安裝成功
{
    "name" : "node-1",
    "cluster_name" : "elkbigdata",
    "cluster_uuid" : "jpkQKXwOTYSr_K7D180c5g",
    "version" : {
    "number" : "7.2.1",
    "build_flavor" : "default",
    "build_type" : "tar",
    "build_hash" : "fe6cb20",
    "build_date" : "2019-07-24T17:58:29.979462Z",
    "build_snapshot" : false,
    "lucene_version" : "8.0.0",
    "minimum_wire_compatibility_version" : "6.8.0",
    "minimum_index_compatibility_version" : "6.0.0-beta1"
   },
  "tagline" : "You Know, for Search"
}

kibana安裝

  • 官網(wǎng)下載kibana-7.2.1-linux-x86_64.tar.gz
  • 解壓并創(chuàng)建state用戶 
    tar -zxvf kibana-7.2.1-linux-x86_64.tar.gz -C /usr/local/
cd /usr/local/
mv kibana-7.2.1-linux-x86_64/ kibana
useradd state
chown -R state:state kibana/
  • 修改kibana配置文件 
    vi /usr/local/kibana/config/kibana.yml
#打開該文件后，按照下列內(nèi)容修改配置項(xiàng)
server.port: 5601
server.host: "0.0.0.0"
elasticsearch.url: http://es所在虛擬機(jī)的IP地址:9200
kibana.index: ".kibana"
  • 放通防火墻并啟動(dòng)服務(wù) 
    systemctl start firewalld
firewall-cmd --add-port=5601/tcp --zone=public --permanent
firewall-cmd --reload
systemctl stop firewalld

  • 若正常運(yùn)行，效果如下（運(yùn)行kibana前，必須保證其連接的elastic search正常工作）


    image.png

logstash安裝

  • 官網(wǎng)下載logstash-7.2.1.tar.gz
  • 解壓安裝 
    tar -zxvf logstash-7.2.1.tar.gz -C /root
cd /root
mv logstash-7.2.1/ logstash
  • 編寫配置文件，使logstash能夠從MySQL中讀取數(shù)據(jù)，并寫入本機(jī)elastic search
vi ~/logstash-7.2.1/config/mylogstash.conf

# Sample Logstash configuration for creating a simple
# Beats -> Logstash -> Elasticsearch pipeline.

input {
  stdin{
  }
  jdbc{
    # 連接的數(shù)據(jù)庫地址和數(shù)據(jù)庫，指定編碼格式，禁用ssl協(xié)議，設(shè)定自動(dòng)重連
    # 此處10.112.103.2為MySQL所在IP地址，也是elastic search所在IP地址
    jdbc_connection_string => "jdbc:mysql://10.112.103.2:3306/mysql?characterEncoding=UTF-8&useSSL=FALSE&autoReconnect=true"
    #數(shù)據(jù)庫用戶名
    jdbc_user => "root"
    # 數(shù)據(jù)庫用戶名對應(yīng)的密碼
    jdbc_password => "BUPTbupt@123"
    # jar包存放位置
    jdbc_driver_library => "/root/logstash-7.2.1/mysql-connector-java-5.1.4/mysql-connector-java-5.1.4-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_default_timezone => "Asia/Shanghai"
    jdbc_paging_enabled => "true"
    jdbc_page_size => "320000"
    lowercase_column_names => false
    statement => "select * from Persons"
  }
}

output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    #index => "%{[@metadata][beat]}-%{[@metadata][version]}-%{+YYYY.MM.dd}"
    #此處使用的elastic search并未配置登錄用戶名以及密碼
    #user => "elastic"
    #password => "changeme"
    index => "persons"
    document_type => "_doc"
    document_id => "%{PersonID}"
  }
  stdout {
    codec => json_lines
  }
}

啟動(dòng)logstash同步mysql到es數(shù)據(jù)中

cd /root/logstash-7.2.1
./bin/logstash -f config/mylogstash.conf
image.png

image.png
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容