Configure MySQL & fluent-plugin-mysql-replicator

Please refer to the tutorial provided by the author.

https://github.com/y-ken/fluent-plugin-mysql-replicator/blob/master/Tutorial-mysql_replicator_multi.md

Following are some error-prone steps:

1. Create the replicator manager database for fluent-plugin-mysql-replicator

wget https://raw.githubusercontent.com/y-ken/fluent-plugin-mysql-replicator/master/setup_mysql_replicator_multi.sql

mysql> source /path/to/setup_mysql_replicator_multi.sql

2. Populate The Sample Table sales in MySQL (optional)

Create the database with:

CREATE DATABASE `replica_test` /*!40100 DEFAULT CHARACTER SET latin1 */;

Create the table with:

USE `replica_test`;
CREATE TABLE `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) DEFAULT NULL,
  `CustomerId` int(11) DEFAULT NULL,
  `ProductId` int(11) DEFAULT NULL,
  `channelName` varchar(300) DEFAULT NULL,
  `purchaseDate` date DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `price` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Show the result.

 >desc sales;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| order_id     | int(11)      | YES  |     | NULL    |                |
| CustomerId   | int(11)      | YES  |     | NULL    |                |
| ProductId    | int(11)      | YES  |     | NULL    |                |
| channelName  | varchar(300) | YES  |     | NULL    |                |
| purchaseDate | date         | YES  |     | NULL    |                |
| quantity     | int(11)      | YES  |     | NULL    |                |
| price        | float        | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

Populate the sales table by sales.csv.

LOAD DATA INFILE '/absolute-path-to-csv/sales.csv' INTO TABLE sales 
  FIELDS TERMINATED BY ',' ENCLOSED BY  '"'
    (`order_id`, `CustomerId`, `ProductId`, `channelName`, `purchaseDate`, `quantity`, `price`)

NOTE: MySQL may complain about that the file does not exist. It may caused by apparmor if you are using ubuntu. Disable or reconfigure it to fix it.

3. Setup the configuration in replicator_manager.settings.

Each table must have their own individual settings in the replicator manager table. E.g.:

Create it with the following SQL command.

USE replicator_manager;
INSERT INTO `replicator_manager`.`settings` VALUES (1, 1, 'sales', 'localhost', 3306, 'root', 'password', 'replica_test', "SELECT id, order_id, CustomerId as `Customer.id` , ProductId as `Product.id`, channelName as channel_name, purchaseDate as Date, quantity as qty, price as total_price from `replica_test`.`sales`", "", 120, 'id', 1, 0, 0)

The MySQL schema to BigObject schema should mapping in query using alias to rename the column name.

4. Setup the configuration in fluent.conf

If you installed fluentd by deb or rpm, the configuration file is /etc/td-agent/td-agent.conf.

<source>
  type mysql_replicator_multi

  # Database connection setting for manager table.
  manager_host localhost
  manager_username $your_mysql_user
  manager_password $your_mysql_password
  manager_database replicator_manager

  # Format output tag for each events. Placeholders usage as described below.
  tag myreplica.${name}.${event}.${primary_key}
  # ${name} : the value of `replicator_manager.settings.name` in manager table.
  # ${event} : the variation of row event type by insert/update/delete.

</source>