Running Maxwell (MySQL Binlog Reader) As Service In Ubuntu



As Many of DBA's are already running Maxwell (http://maxwells-daemon.io/) if not Let me introduce you to Maxwell. It reads MySQL binlogs and writes row updates as JSON to Kafka, Kinesis, or other streaming platforms. Maxwell is very nice tool to build pipelines from MySQL to other NoSQL DB's like (Elastic Search, AeroSpike, Cassandra etc).

A common approach which is being used widely is to run maxwell on a box which reads bin-logs from MySQL box and write it to Kafka (A Distributed Streaming Platform) Later consumer's consume data from kafka and write it to its own DB (Elastic Search, AeroSpike etc).

Now major issue is to run maxwell as service on Ubuntu VM. Which we tried to solve using Upstart Job for Ubuntu 14.x and Systemd service for Ubuntu 16.x.

Maxwell Binaries can be downloaded from https://github.com/zendesk/maxwell/releases.

Upstart for Ubuntu 14.x (Assuming maxwell binary placed in /opt/maxwell/ directory):

Create file: /etc/init/maxwell.conf
       
description "Start and stop the maxwell service "

author "Ankit Kumar"

env APP_HOME=/opt/maxwell/

start on (net-device-up

          and local-filesystems

          and runlevel [2345])

stop on runlevel [016]

respawn

respawn limit 5 60

limit nofile 24000 24000

pre-start script

    test -x $APP_HOME/bin/maxwell || { stop; exit 1; }

    test -e $APP_HOME/config.properties || { stop; exit 1; }

end script

script

    echo $$ > /var/run/maxwell.pid

    chdir $APP_HOME

    exec $APP_HOME/bin/maxwell

end script

post-stop script

    rm -f /var/run/maxwell.pid

end script
Now maxwell can be started using command service maxwell start & can be stopped using command service maxwell stop.

Upstart for Ubuntu 16.x (Assuming maxwell binary placed in /opt/maxwell/ directory):

Create file: /etc/systemd/system/maxwell.service
       
[Unit]

Description=Maxwell service

After=network.target

[Service]

WorkingDirectory=/opt/maxwell/

Restart=always

RestartSec=3

ExecStart=/opt/maxwell/bin/maxwell

#ExecStartPost=/bin/bash "touch /var/run/maxwell.pid && `echo $MAINPID` > /var/run/maxwell.pid"

ExecStop=/bin/kill -9 $MAINPID

KillMode=mixed

TimeoutSec=180

[Install]

WantedBy=multi-user.target
Enable Maxwell systemd service: systemctl enable maxwell.service

Now maxwell can be started using command systemctl start maxwell.service & can be stopped using command systemctl stop maxwell.service.

Monitoring Maxwell Service: Using /var/run/maxwell.pid monitoring on maxwell service can be enabled.

Comments

Post a Comment

Popular posts from this blog

Shell/Bash Commands Execution from MySQL Client or Stored Procedure/function

Image
Today I am going to explain ways to execute shell or bash commands from mysql clients or stored procedure and function. There are basically 2 method to do so: Method 1: Use MySQL Client inbuilt feature  To run single command: \! command or system command. eg \! uptime or system command  To get terminal \! bash or \! sh Method 2: Deploy external plugin ( lib_mysqludf_sys ) Step 1: Download lib_mysqludf_sys from github: git clone https://github.com/mysqludf/lib_mysqludf_sys.git Step 2: Install libmysqlclient15-dev, for Ubuntu you can use: apt-get install libmysqlclient15-dev Step 3: Note down o/p of: mysql -uroot -pxxxx -e "select @@plugin_dir;" Step 4: Change directory to git clone dir. cd lib_mysqludf_sys/ Step 5: Compile and put plugin in plugin dir gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o {value from step 3}lib_mysqludf_sys.so -fPIC Step 6: Create necessary functions for lib_mysqludf_sys: mysql -uroo
Read more

How to remove/deregister an instance from PMM?

Image
We all need a monitoring system like pmm to monitor our database. Let me give a brief about how pmm works. So PMM uses prometheus to store metrics/graphs and grafana to display them. As seen in the architecture diagram prometheus uses consul to get the list of host to scrap metrics. More information on architecture and installation are available in docs. We were facing issues related to down hosts and if setup alerts in grafana for down host and host is terminated it continuously throws alerts. So problem was how can we remove/deregister instances from pmm. We can access consul UI using below link: http://PMM-SERVER/consul/#/dc1/ Previously there was option available on this ui to deregister host. while in the new releases of consul it has been removed see link . But now we can use below procedure from command line to deregister hosts from consul. Get list of nodes: curl -s 'http://USER:PASSWORD@PMM-SERVER-IP/v1/internal/ui/nodes?dc=dc1' | python -mjso
Read more

MySQL: How to monitor MySQL Replication Lag in MilliSeconds With PMM and pt-heartbeat

Image
There could be various requirements at application end which need realtime slaves. Let me introduce a solution to a problem we used to face how can we monitor whether our slaves are real time or they are lagging in Milliseconds. Unfortunately there is no built in feature in MySQL to get Replication Lag in MilliSeconds. Perhaps there is a tool provisioned in pt-toolkit named as pt-heartbeat . It generates heartbeat events on master and monitoring system can monitor time difference on slave to calculate lag. How to deploy pt-heartbeat in your environment (Assuming OS as UBUNTU xx.xx): Step 1: Download pt-heartbeat using below command. wget http://percona.com/get/pt-heartbeat -P /usr/bin/ && chmod +x /usr/bin/pt-heartbeat Step 2: It requires a database where it can create a table. Let's create it on master. mysql -uUser -pPassword -e "create database if not exists percona;" Step 3: Create heartbeat table in percona db. pt-heartbeat -D percon
Read more