Synchronizing Binlog-based MySQL Data to the MRS Cluster

Configuring MySQL

1. Start the binlog, open the my.cnf file in MySQL, and check whether server_id, log-bin, and binlog_format are configured in the [mysqld] block. If they are not configured, run the following command to add configuration items and restart MySQL. If they are configured, skip this step.

   $ vi my.cnf
   
   [mysqld]
   server_id=1
   log-bin=master
   binlog_format=row

2. Maxwell needs to connect to MySQL, create a database named maxwell for storing metadata, and access the database to be synchronized. Therefore, you are advised to create a MySQL user for Maxwell to use. Log in to MySQL as user root and run the following commands to create a user named maxwell (XXXXXX indicates the password and needs to be replaced with actual one).
   • If Maxwell is deployed on a non-MySQL server, the created user maxwell must have a permission to remotely log in to the database. In this case, run the following command to create the user:

     mysql> GRANT ALL on maxwell.* to 'maxwell'@'%' identified by 'XXXXXX';

     mysql> GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE on *.* to 'maxwell'@'%';

   • If Maxwell is deployed on the MySQL server, the created user maxwell can be configured to log in to the database only on the local host. In this case, run the following command:

     mysql> GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE on *.* to 'maxwell'@'localhost' identified by 'XXXXXX';

     mysql> GRANT ALL on maxwell.* to 'maxwell'@'localhost';

Installing Maxwell

1. Download the installation package at https://github.com/zendesk/maxwell/releases and select the maxwell-XXX.tar.gz binary file for download. In the file name, XXX indicates a version number.
2. Upload the tar.gz package to any directory (the /opt directory of the Master node used as an example here).
3. Log in to the server where Maxwell is deployed and run the following command to go to the directory where the tar.gz package is stored.

   cd /opt

4. Run the following commands to decompress the maxwell-XXX.tar.gz package and go to the maxwell-XXX directory:

   tar -zxvf maxwell-XXX.tar.gz

   cd maxwell-XXX

Configuring Maxwell

If the conf directory exists in the maxwell-XXX folder, configure the config.properties file. For details about the configuration items, see Table 1. If the conf directory does not exist, change config.properties.example in the maxwell-XXX folder to config.properties.

Starting Maxwell

1. Log in to the server where Maxwell is deployed.
2. Run the following command to go to the Maxwell installation directory:

   cd /opt/maxwell-1.21.0/

   

   For the first time to use Maxwell, you are advised to change log_level in conf/config.properties to debug (debug level) so that you can check whether data can be obtained from MySQL and sent to Kafka after startup. After the entire process is debugged, change log_level to info, and then restart Maxwell for the modification to take effect.

   # log level [debug | info | warn | error]

   log_level=debug

3. Run the following commands to start Maxwell:

   source /opt/client/bigdata_env

   bin/Maxwell

   bin/maxwell --user='maxwell' --password='XXXXXX' --host='127.0.0.1' \

   --producer=kafka --kafka.bootstrap.servers=kafkahost:9092 --kafka_topic=Maxwell

   In the preceding commands, user, password, and host indicate the username, password, and IP address of MySQL, respectively. You can configure the three parameters by modifying configurations of the configuration items or using the preceding commands. kafkahost indicates the IP address of the Core node in the streaming cluster.

   There can be security risks if a command contains the authentication password. You are advised to disable the command recording function (history) before running the command.

   If information similar to the following appears, Maxwell has started successfully:

   Success to start Maxwell [78092].

Verifying Maxwell

1. Log in to the server where Maxwell is deployed.
2. View the logs. If the log file does not contain an ERROR log and the following information is displayed, the connection between Maxwell and MySQL is normal:

   BinlogConnectorLifecycleListener - Binlog connected.

3. Log in to the MySQL database and update, create, or delete test data. The following provides operation statement examples for your reference.

   --Creating a database
   create database test;
   --Creating a table
   create table test.e (
     id int(10) not null primary key auto_increment,
     m double,
     c timestamp(6),
     comment varchar(255) charset 'latin1'
   );
   -- Adding a record
   insert into test.e set m = 4.2341, c = now(3), comment = 'I am a creature of light.';
   --Updating a record
   update test.e set m = 5.444, c = now(3) where id = 1;
   --Deleting a record
   delete from test.e where id = 1;
   --Modifying a table
   alter table test.e add column torvalds bigint unsigned after m;
   --Deleting a table
   drop table test.e;
   -- Deleting a database
   drop database test;

4. Check the Maxwell logs. If no WARN/ERROR is displayed, Maxwell is installed and configured properly.
   To check whether the data is successfully uploaded, set log_level in the config.properties file to debug. When the data is successfully uploaded, the following JSON data is printed immediately. For details about the fields, see Format of the Maxwell Generated Data and Description of Common Fields.

   {"database":"test","table":"e","type":"insert","ts":1541150929,"xid":60556,"commit":true,"data":{"id":1,"m":4.2341,"c":"2018-11-02 09:28:49.297000","comment":"I am a creature of light."}}
   ......

   

   After the entire process is debugged, you can change the value of log_level in the config.properties file to info to reduce the number of logs to be printed and restart Maxwell for the modification to take effect.

   # log level [debug | info | warn | error]
   log_level=info

Stopping Maxwell

1. Log in to the server where Maxwell is deployed.
2. Run the command to obtain the Maxwell process ID (PID). The second field in the command output is PID.

   ps -ef | grep Maxwell | grep -v grep

3. Run the following command to forcibly stop the Maxwell process:

   kill -9 PID

Format of the Maxwell Generated Data and Description of Common Fields

The data generated by Maxwell is in JSON format. The common fields are described as follows:

• type: operation type. The options are database-create, database-drop, table-create, table-drop, table-alter, insert, update, and delete.
• database: name of the database to be operated
• ts: operation time, which is a 13-digit timestamp
• table: name of the table to be operated
• data: content after data is added, deleted, or modified
• old: content before data is modified or schema definition before a table is modified
• sql: SQL statement for DDL operations
• def: schema definition for table creation and modification
• xid: unique transaction ID.
• commit: check whether such operations as data addition, deletion, and modification have been submitted.