Shulou(Shulou.com)06/03 Report--

The following brings you about the data collection of mysql through logstash increment. I believe you must have read similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I believe you will gain something after reading the incremental collection of mysql data through logstash.

Recently, there is a need to synchronize the data in a table in mysql to es. After analysis, we use the jdbc plug-in of logstash to obtain the data in mysql. In output to es, there are two kinds of acquisition: first, full collection, then incremental collection.

The process that has been verified as follows:

1. Install logstash and mysql

There is no need to say much about the installation of mysql and logstash, and you can find it on the Internet.

The things to pay attention to are:

1) you may need to install: bin/plugin install logstash-input-jdbc

2) driver download https://cdn.mysql.com//Downloads/Connector-J/mysql-connector-java-5.1.48.tar.gz of mysql

two。 Create mysql tables and data

Create table test.zy (id int,str varchar (20)); insert into test.zy values ('1th); insert into test.zy values (' 2l); insert into test.zy values ('3A); insert into test.zy values ('4); insert into test.zy values ('5pm); insert into test.zy values (6') Insert into test.zy values; insert into test.zy values; Insert into test.zy values ('1414'); # incremental collection to verify the inserted data insert into test.zy values ('15'); insert into test.zy values ('16')

3.logstash collects configuration files of mysql

# collect input {jdbc {jdbc_connection_string = > "jdbc:mysql://ip:3306/test" jdbc_user = > "root" jdbc_password = > "123456" jdbc_driver_library = > "mysql-connector-java-5.1.36-bin.jar" jdbc_driver_class = > "com.mysql.jdbc.Driver" statement = > "select * from zy where id >" by field increment : sql_last_value "use_column_value = > true tracking_column = >" id "record_last_run = > true last_run_metadata_path = >" / root/test.log "schedule = >" * / 2 * "}} output {file {path = >". / mysql/test-% {+ YYYY-MM-dd} .txt "} # collect increments according to timestamp Input {jdbc {jdbc_connection_string = > "jdbc:mysql://ip:port/test" jdbc_user = > "root" jdbc_password = > "123456" jdbc_driver_library = > "mysql-connector-java-5.1.36-bin.jar" jdbc_driver_class = > "com.mysql.jdbc.Driver" jdbc_default_timezone = > "Asia/Shanghai" # sets the time zone for sql_last_value records Otherwise, it will affect the effect of incremental collection statement = > "select * from zy1 where time >: sql_last_value" use_column_value = > false record_last_run = > true last_run_metadata_path = > "/ root/test.log" schedule = > "* / 2 *"} output {file {path = > ". / db2/test-% {+ YYYY-MM-dd}. Configuration Analysis of txt "}} input statement executes the statement of myqsl It can also be whether the path of the statementpath followed by the sql file use_column_value uses the column value as a basis to record the location of the last run. If set to true, the column defined by tracking_column is used as: sql_last_value. If set to false, then: sql_last_value reflects the running time of the last SQL. Tracking_column incremental acquisition field name if use_colomn_value is false, you can not write whether record_last_run records the location of the collected data. Last_run_metadata_path sets the frequency of execution of the file schedule sql script that records the location of the collected data.

4. Collection and verification

Start logstash

Cd / usr/local/logstash

Bin/logstash-f conf/logtash.conf

# # logstash automatically loads configuration file bin/logstash-f conf/logtash.conf-- config.reload.automatic

Test whether logtash is in normal use

Bin/logstash-e 'input {stdin {}} output {output {}'

Test and exit

Bin/logstash-f conf/logtash.conf-- config.test_and_exit

Logstash collects output logs:

Output file content view

Sql_last_value 's records.

For the above data about collecting mysql through logstash increments, do you think it is what you want? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.