Elasticsearch+MySQL数据同步示例
@author:wushuang
版本:Elasticsearch2.4Logstash2.4MySQL5.7
操作系统:CentOS Linux 7
前言:做个备份,免得以后忘记。在网上查阅了不少资料,官网讲的还是很清楚。
一、安装Elasticsearch
https://www.elastic.co/guide/en/elasticsearch/reference/current/setup.html
二、在MySQL上新建库表(若已有库表可跳过)
建库脚本如下:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for contacts
-- ----------------------------
DROP TABLE IF EXISTS `contacts`;
CREATE TABLE `contacts` (
`uid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(80) NOT NULL,
`first_name` varchar(80) NOT NULL,
`last_name` varchar(80) NOT NULL,
UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of contacts
-- ----------------------------
INSERT INTO `contacts` VALUES ('1', 'jim@example.com', 'Jim', 'Smith');
INSERT INTO `contacts` VALUES ('2', '', 'John', 'Smith');
INSERT INTO `contacts` VALUES ('3', 'carol@example.com', 'Carole', 'Smith');
INSERT INTO `contacts` VALUES ('4', 'sam@example.com', 'Sam', 'Smith');
INSERT INTO `contacts` VALUES ('5', 'wushuang@qq.com', 'Rick', 'Roses');
INSERT INTO `contacts` VALUES ('6', 'w@163.com', 'Sum', 'OKFord');
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`USERID` bigint(20) NOT NULL,
`USERNAME` varchar(20) DEFAULT NULL,
`AGE` int(3) unsigned DEFAULT NULL,
`SEX` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`USERID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('201609210935231', 'Quejs', '24', '1');
INSERT INTO `user` VALUES ('201609210936233', 'Smith', '24', '1');
INSERT INTO `user` VALUES ('201609210938234', 'Mends', '26', '0');
INSERT INTO `user` VALUES ('201609210947235', 'wisd', '38', '0');
INSERT INTO `user` VALUES ('201609210948236', 'Losed', '35', '0');
SET FOREIGN_KEY_CHECKS=1;
三、安装Logstash
https://www.elastic.co/guide/en/logstash/current/installing-logstash.html
四、安装logstash-input-jdbc和logstash-output-elasticsearch插件
https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html
https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html
五、新建配置文件
1.需要将MYSQL的jar文件复制到Logstash安装目录下,示例中放在了/home/manager/app/logstash-2.4.0下。如图:
2.在目录/home/manager/app/logstash-2.4.0/bin下新建config文件夹。
命令:切换到/home/manager/app/logstash-2.4.0/bin目录下,mkdir config
3.在目录/home/manager/app/logstash-2.4.0/bin/config/下新建conf目录,命令:mkdir conf
4.新建两个配置文件contacts.conf和user.conf。
user.conf内容:
input {
stdin {
}
jdbc {
# mysql jdbc connection string to our backup databse
jdbc_connection_string => "jdbc:mysql://localhost:3306/mytest"
# the user we wish to excute our statement as
jdbc_user => "manager"
jdbc_password => "manager@2015"
# the path to our downloaded jdbc driver
jdbc_driver_library => "/home/manager/app/logstash-2.4.0/mysql-connector-java-5.1.22.jar"
# the name of the driver class for mysql
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
statement_filepath => "/home/manager/app/logstash-2.4.0/bin/config/sql/user.sql"
schedule => "* * * * *"
type => "user"
}
}
filter {
json {
source => "message"
remove_field => ["message"]
}
}
output {
if[type]=="user"{
elasticsearch {
hosts => ["127.0.0.1:9200"]
index => "mytest"
document_type => "user"
document_id => "%{userid}"
workers => 1
flush_size => 20000
idle_flush_time => 10
template_overwrite => true
}
stdout {
codec => json_lines
}
}
}
contacts.conf内容:
input {
stdin {
}
jdbc {
# mysql jdbc connection string to our backup databse
jdbc_connection_string => "jdbc:mysql://localhost:3306/mytest"
# the user we wish to excute our statement as
jdbc_user => "manager"
jdbc_password => "manager@2015"
# the path to our downloaded jdbc driver
jdbc_driver_library => "/home/manager/app/logstash-2.4.0/mysql-connector-java-5.1.22.jar"
# the name of the driver class for mysql
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
statement_filepath => "/home/manager/app/logstash-2.4.0/bin/config/sql/user.sql"
schedule => "* * * * *"
type => "user"
}
}
filter {
json {
source => "message"
remove_field => ["message"]
}
}
output {
if[type]=="user"{
elasticsearch {
hosts => ["127.0.0.1:9200"]
index => "mytest"
document_type => "user"
document_id => "%{userid}"
workers => 1
flush_size => 20000
idle_flush_time => 10
template_overwrite => true
}
stdout {
codec => json_lines
}
}
}
六、启动,验证
1.到Logstash bin目录下启动Logstash:nohup ./logstash -f ./config/conf/ >/dev/null 2>&1 &
2.验证数据是否同步过来了:curl -XGET 'http://localhost:9200/mytest/contacts/_search?pretty'
七、注意事项
1.只能逻辑删除,物理删除同步不了。
2.以上示例是全量同步,增量的需要每张表加timespan字段。Stackoverflow上可以查一查。
3.性能还得进行压测,目前先实现第一步。
相关推荐
基于Springboot + ElasticSearch +Vue+MySQL构建的博客检索系统 项目经过严格测试,确保可以运行! 简易博客检索系统使用前后端分离,前端使用 Vue ,后端使用 SpringBoot 数据库 MySQL 检索使用 ElasticSearch ...
ElasticSearch + Logstash 自动同步mysql数据
springboot 整合 elasticsearch demo(内含增删改查)以及基本es操作 效果图在这里 :https://blog.csdn.net/Japhet_jiu/article/details/105581997
win7 基于spring boot+elasticsearch+Redis+mysql+mybatis进行搜索引擎web开发实例。内容是爬取IThome热评存储到mysql,然后在redis建立索引。这个是可以正常运行的,但是还有一部分功能没有完善。具体参考...
这个demo 希望可以帮助大家
智能客服 基于springboot+swaggger+elasticsearch+mysql.zip
特别有用的MySQL数据实时同步到ES轻松配置手册 特别有用的MySQL数据实时同步到ES轻松配置手册
基础服务:redis + Elasticsearch + kafka + mysql + mongodb
基于Nutch+ElasticSearch+MySQL+SSM的简易搜索引擎
shell+mysql+nginx+linux+docker+ansible+Elasticsearch+MongoDB+爬虫+Git
scrapy+es+mysql+django打造搜索引擎
elasticsearch-2.3.2.tar.gz和elasticsearch-jdbc-2.3.2.0-dist.zip,可以进行mysql数据库数据同步到es中,亲测有效。详见博客http://www.cnblogs.com/zhongshengzhen/
canal实现mysql到ES数据实时同步
1.安装elasticsearch服务,安装es-ik插件(直接拷贝到plugin/ik目录下),安装es-head插件(需要安装nodejs,npm install后使用grunt server启动),启动es,启动es-head 2.安装mysql服务,(初始化mysqld --initialize ...
万能架构设计:ES+Redis+MySQL,这套组合可应对 80%业务场景 6M大文件,全面详解ES+Redis+MySQL架构,并结合实战FAQ,非常实用,值得收藏
Scrapy + Elasticsearch + Django打造全文搜索引擎源码 Scrapy + Elasticsearch + Django打造全文搜索引擎源码 Scrapy + Elasticsearch + Django打造全文搜索引擎源码 Scrapy + Elasticsearch + Django打造...
搜索引擎:Elasticsearch 6.4.3 安全:Spring Security 邮件任务:Spring Mail 分布式定时任务:Spring Quartz 日志:SLF4J(日志接口) + Logback(日志实现) 前端: Thymeleaf Bootstrap 4.x Jquery Ajax