logstash將mysql數據同步到elasticsearch方法詳解
環境說明
- 虛擬機一臺
- logstash-7.2.1
- elastic search-7.2.1
- kibana-7.2.1
- MySQL 5.7
- jdk版本為jdk1.8.0_211
mysql安裝
刪除預裝的Mariadb服務
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) # 如果上述指令報錯,可通過瀏覽器直接訪問該網址,自動下載即可 # 安裝MySQL官方repo yum -y localinstall mysql57-community-release-el7-10.noarch.rpm # 安裝MySQL服務器 yum -y install mysql-community-server
啟動MySQL服務
systemctl start mysqld.service #啟動mysql服務 systemctl status mysqld.service #查看MySQL服務工作狀態
修改MySQL配置
# 在mysqld服務運行日志中,查找初次登陸的默認密碼 grep "password" /var/log/mysqld.log # 通過默認初始密碼進入數據庫 mysql -u root -p #進入數據庫之後,修改root用戶密碼 ALTER USER 'root'@'localhost' IDENTIFIED BY 'BUPTbupt@123'; # 進入mysql數據庫,修改root用戶配置,以開啟遠程訪問 use mysql; update user set host = '%' where user = 'root'; # 完成上述配置後,退出MySQL
重啟MySQL服務
#重啟MySQL服務 systemctl restart mysqld.service #並重新登錄查看新密碼是否生效 #關閉yum自動更新,否則以後每次yum都會自動更新mysqld服務 yum -y remove mysql57-community-release-el7-10.noarch
準備測試數據
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/下載與當前jdk版本匹配的mysql-connector
解壓到指定位置
unzip mysql-connector-java-5.1.4.zip mv mysql-connector-java-5.1.4 /root/logstash-7.2.1/
Elasticsearch安裝
官網下載 elasticsearch-7.2.1-linux-x86_64.tar.gz
進入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 # 添加以下內容 fs.file-max=655360 vm.max_map_count=262144 vi /etc/security/limits.conf # 添加以下內容 * 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 #修改內容如下 * soft nproc 204800
激活上述配置項
sysctl -p #CTRL + D退出當前終端,而後重新登錄 ulimit -a #查看內核修改配置是否生效
切換到elasticsearch用戶,進行後續配置
su elasticsearch # 創建數據存儲目錄 mkdir -p /home/elasticsearch/data1/elasticsearch mkdir -p /home/elasticsearch/data2/elasticsearch vi /usr/local/elasticsearch/config/elasticsearch.yml #修改如下內容 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 #修改內容如下 -Xms2g #這個值一般設置為物理內存的一半,不一定非要2g -Xmx2g
關閉防火墻並啟動elastic search服務
#退回到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 &
驗證安裝是否成功
curl http://localhost:9200 #若出現下列內容,說明安裝成功 { "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安裝
- 官網下載kibana-7.2.1-linux-x86_64.tar.gz
解壓並創建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 #打開該文件後,按照下列內容修改配置項 server.port: 5601 server.host: "0.0.0.0" elasticsearch.url: http://es所在虛擬機的IP地址:9200 kibana.index: ".kibana"
放通防火墻並啟動服務
systemctl start firewalld firewall-cmd --add-port=5601/tcp --zone=public --permanent firewall-cmd --reload systemctl stop firewalld
若正常運行,效果如下(運行kibana前,必須保證其連接的elastic search正常工作)
logstash安裝
- 官網下載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中讀取數據,並寫入本機elastic search
vi ~/logstash-7.2.1/config/mylogstash.conf # Sample Logstash configuration for creating a simple # Beats -> Logstash -> Elasticsearch pipeline. input { stdin{ } jdbc{ # 連接的數據庫地址和數據庫,指定編碼格式,禁用ssl協議,設定自動重連 # 此處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" #數據庫用戶名 jdbc_user => "root" # 數據庫用戶名對應的密碼 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 } }
啟動logstash同步mysql到es數據中
cd /root/logstash-7.2.1 ./bin/logstash -f config/mylogstash.conf
以上就是logstash將mysql數據同步到elasticsearch方法詳解的詳細內容,更多關於mysql數據同步elasticsearch的資料請關註WalkonNet其它相關文章!
推薦閱讀:
- 使用logstash同步mysql數據到elasticsearch實現
- 基於Docker搭建ELK 日志系統的方法
- docker安裝elastic search的詳細過程
- Java elasticsearch安裝以及部署教程
- Docker Compose一鍵ELK部署的方法實現