Sources - Pirionfr/lookatch-agent GitHub Wiki

Sources are independent modules that can read data from a type.

Currently supported sources are :

• MySQL Query
• MySQL CDC
• PostgresSQL Query
• PostgresSQL CDC
• MSSQL Query (alpha)
• Tail file (alpha)
• Syslog (alpha)

MySQL Query use JDBC driver to connect.

Parameter to set:

host          database IP or public hostname
port          database port
user          replication user
password      password of replication user

User must have this right:

SELECT

MySQL CDC is use to determine (and track) the data that has changed.

currently, MySQL 5.5+ binlog is supported.

Parameter to set:

host          database IP or public hostname
port          database port
user          replication user
password      password of replication user
old_value     set true if you want old value in events

User must have this right:

SELECT
REPLICATION CLIENT
REPLICATION SLAVE

For Example:

CREATE USER 'test'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'test'@'%' IDENTIFIED BY 'password';

create database test;

GRANT SELECT ON *.* TO 'test'@'localhost';
GRANT SELECT ON *.* TO 'test'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'test'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'test'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'test'@'localhost';
GRANT REPLICATION SLAVE ON *.* TO 'test'@'%';

FLUSH PRIVILEGES;

Check in your mysql.cnf file this following line are set and uncomment:

bind-address            = 0.0.0.0
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_format 			= ROW

PostgresSQL Query use JDBC driver to connect.

User must have this right:

CONNECT
SELECT

For examples :

$ CREATE USER monUser WITH NOCREATEDB PASSWORD 'monPassword';;
$ GRANT CONNECT ON DATABASE maBase TO monUser;
$ GRANT SELECT ON ALL TABLES IN SCHEMA public TO monUser;

To connect to the replication slot we use the PostgreSQL driver and toolkit for Go pgx ( https://github.com/jackc/pgx )

we connect the agent to the replication slot and wait for Replication Message.

In parallel every 10 seconds (to prevent network overload that a continuous ack sending could generate) we send replication status of the connection and send the current replication offset position, we also check if the connection is alive and reconnect if not.

we can receive 2 kind of message :

• a wall Message
• a Server Heart beat

The wall Message is Unmarshalled and send to the output channel. if the event is not readable we skip it.

The Server Heart beat help to update status. if ServerTime = 1 the server is requesting a standby status, so we send one. That way, if a disconnection occurs menure client, when reconnecting will be able to restart from previously saved point instead of reading from beginning. It also prevent PG from storing replication history locally.

• Required postgresql-
• Required postgresql-server-dev-
• Required libpq-dev
• Required make
• Required gcc

$ git clone https://github.com/eulerto/wal2json.git
$ PATH=/path/to/bin/pg_config:$PATH
$ USE_PGXS=1 make
$ USE_PGXS=1 make install

Copy generates file named "wal2json.so" in PG libs folder.

$ mv wal2json.so /usr/lib/postgresql/<version>/lib/

Restart PG:

$ service postgresql restart

this SQL command had to be performed on every databases you want to replicate, just replace "maBase" by your database name.

$  SELECT * FROM pg_create_logical_replication_slot('collector_maBase', 'wal2json');

User must have this right:

REPLICATION

For examples :

$ CREATE USER monUser WITH NOCREATEDB PASSWORD 'monPassword';
$ ALTER ROLE monUser WITH REPLICATION;
$ GRANT CONNECT ON DATABASE maBase TO monUser;
$ GRANT SELECT ON ALL TABLES IN SCHEMA public TO monUser;