Go-mysql-elasticsearch implementation of MySQL and Elasticsearch real-time synchronization in depth

This is a creation in Article, where the information may have evolved or changed.

Introduction:

Go-mysql-elasticsearch is a plug-in developed by domestic authors. Test shows: The advantages of the plug-in: To achieve synchronous increase, deletion, change, check operation. Deficiencies (areas to be perfected):
1, still deal with the development, relatively unstable phase;
2, no log, not easy to troubleshoot problems and view the results of synchronization.
In this paper, the plug-in installation, use, increase and deletion check synchronization test.

1. Go-mysql-elasticsearch Plug-in installation

Step 1: Install Go

Yum Install go

Step 2: Install GODEP

Go get GITHUB.COM/TOOLS/GODEP

Step 3: Get the Go-mysql-elastisearch plugin

Go get Github.com/siddontang/go-mysql-elasticsearch

Step 4: Install the Go-mysql-elastisearch plugin

CD $GOPATH/src/github.com/siddontang/go-mysql-elasticsearch
Make

2.go-mysql-elasticsearch plug-in use

2.1 Modifying a configuration file

[root@5b9dbaaa148a etc]# cat river.toml# MySQL address, user andpassword# user must has replication privilegeinchMYSQL.MY_ADDR ="192.168.1.1:3306"My_user ="Root"My_pass ="password@!"# Elasticsearch ADDRESSES_ADDR ="192.168.1.1:9200"# Path toStore data, like Master.info, andDump MySQL Data Data_dir ="./var"# Inner Http Status addressstat_addr ="192.168.1.1:12800"# Pseudo server id like a slave server_id =1# MySQLorMariadbflavor ="MySQL"# mysqldump Execution Pathmysqldump ="Mysqldump"# MySQL Data Source[[source]]schema ="Test"# only below tables'll be synced to elasticsearch.#"Test_river_[0-9]{4}"  isA wildcard table format, you can UseItifYou have a many sub tables, like table_0000-table_1023# I Don' tThink it isNecessary toSync AllTablesincha database.tables = ["CC"]# Below is  forSpecial Rule Mapping#[[rule]] #schema ="Test"#table ="CC"#index ="Go_river"#type="Go_rivert"# title isMySQL test_river field name, Es_title isThe customized nameinchElasticsearch # [Rule.field] # this wouldMapColumn title toElastic Search My_title # title="Es_title"# this wouldMapColumn tags toElastic Search My_tags and  Use Array type# tags="My_tags,list"# this wouldMapColumn keywords toElastic search Keywords and  Use Array type#keywords =", List"# Wildcard Table rule, the wildcard table must beinchsource tables [[Rule]]schema ="Test"Table ="CC"index ="GOCC"type="gocc_t"# title isMySQL test_river field name, Es_title isThe customized nameinchElasticsearch [[rule.fields]] MySQL ="Mysql101"Elastic ="Es_mysql101"

2.2 Performing a synchronous operation

CD $GOPATH/src/github.com/siddontang/go-mysql-elasticsearch
./bin/go-mysql-elasticsearch-config=./etc/river.toml

3. Go-mysql-elasticsearch plug-in synchronization test results

3.1 Inserting insert operation Real-time synchronization verification (verify OK)

3.1.1Mysql End insert Operation

Mysql> INSERT INTO CC (ID,NAME) VALUES ("test12");
Query OK, 1 row affected (0.06 sec)

3.1.2Mysql query results after insert execution

Mysql> SELECT * from cc where id = 12;
+--+--–+--–+ ——————— +
| ID | name | Status | Modified_at |
+--+--–+--–+ ——————— +
| 12 | test12 | Ok | 2016-06-24 02:27:29 |
+--+--–+--–+ ——————— +
1 row in Set (0.02 sec)

3.1.3ES can query to the new value field.

[root@5b9dbaaa148a bin]# Curl-xget http://192.168.1.1:9200/gocc/_search?pretty-d ' > {"Query":> {"term":> {"id": A}}}'{"took":402,"Timed_out":false,"_shards": {"Total":8,"Successful":8,"Failed":0},"hits": {"Total":1,"Max_score":1.0,"hits": [ {"_index":"GOCC","_type":"gocc_t","_id":" A","_score":1.0,"_source": {"id": A,"Modified_at":"2016-06-24t02:27:29+01:00","Name":"Test12","Status":"OK"}    } ]  }}

3.2 Modify Update operation real-time synchronization verification (verify OK)

3.2.1mysql Performing Update operations

setnamewhereid121 row affected (0.051  1  0

3.2.2mysql Performing a modified query

MySQL Query modified results:

mysql> select * from cc where id = 12;+----+------------+--------+---------------------+| id | name       | status | modified_at         |+----+------------+--------+---------------------+| 12 | test12_001 | ok     | 2016-06-24 02:27:29 |+----+------------+--------+---------------------+1 row in set (0.00 sec)

3.2.3 ES Query Modification results

[root@5b9dbaaa148a bin]# Curl-xget http://192.168.1.1:9200/gocc/_search?pretty-d ' {"Query":{"term":{"id": A}}}'{"took": -,"Timed_out":false,"_shards": {"Total":8,"Successful":8,"Failed":0},"hits": {"Total":1,"Max_score":1.0,"hits": [ {"_index":"GOCC","_type":"gocc_t","_id":" A","_score":1.0,"_source": {"id": A,"Modified_at":"2016-06-24t02:27:29+01:00","Name":"test12_001","Status":"OK"}    } ]  }}

3.3 Delete operation Real-time synchronization verification

3.3.1Mysql performing a delete operation

fromwhereid121 row affected (0.04 sec)

3.3.2 Query table after delete

Mysql> SELECT * from cc;+----+--------------------+--------+---------------------+| id | name | status | modified_at |+----+--------------------+--------+---------------------+|1 | laoyang360 | ok | 0000-xx-xx xx:xx:xx||2 | test002 | ok |  .- .- at  .: -: the||3 | dlllaoyang360 | ok | 0000-xx-xx xx:xx:xx|| One | test11 | ok |  .- .- -  Geneva: the: the||5 | jdbc_test_update08 | ok | 0000-xx-xx xx:xx:xx||7 | test7 | ok | 0000-xx-xx xx:xx:xx||8 | test008 | ok | 0000-xx-xx xx:xx:xx||9 | test009 | ok | 0000-xx-xx xx:xx:xx||Ten | test10 | ok |  .- .- -  Geneva: ,: -|+----+--------------------+--------+---------------------+9Rows in Set (0.02Sec

3.3.3ES Query results after deletion

[root@5b9dbaaa148a bin]# curl-xget http://192.168.1.1:9200/gocc/_search?pretty-d '{"Query":{"term":{"id": A}}}'{"took": +,"Timed_out":false,"_shards": {"Total":8,"Successful":8,"Failed":0},"hits": {"Total":0,"Max_score":NULL,"hits": [ ]  }}

4 Summary

Validation discovery:
(1) Go-mysql-elasticsearch plug-in can be synchronized insert, UPDATE, delete operation.
(2) The visualization does not do well, there is no print log.
(3) Go-mysql-elasticsearch is not very stable, there have been no synchronization success, but no error. Not easy to troubleshoot.

Author: Ming Yi World
Reprint please indicate source, original address: http://blog.csdn.net/laoyang360/article/details/51771483
If you feel this article is helpful, please click on the ' top ' support, your support is I insist on writing the most power, thank you!