MySQL Production Issues

Today I am going to introduce you all to an awesome tool GH-OST . We are using it since many months to Alter tables online. Believe me it saves lot of time and efforts while altering big tables in MySQL. gh-ost has been developed at GitHub. To answer a problem we faced with ongoing, continuous production schema changes requiring modifications to MySQL tables. gh-ost changes the existing online table migration paradigm by providing a low impact, controllable, auditable, operations friendly solution. gh-ost stands for GitHub’s Online Schema Transmogrifier/Transfigurator/Transformer/Thingy. While migrating table gh-ost creates a ghost table in the likeness of your original table, migrate that table while empty, slowly and incrementally copy data from your original table to the ghost table, meanwhile propagating ongoing changes (any INSERT, DELETE, UPDATE applied to your table) to the ghost table. Finally, at the right time, they replace your original table with the ghost

In my previous post I have explain how we are running maxwell as service in Ubuntu. Now in this post I will be explaining how we achieved HA for Backend Instances (MySQL Hosts), Maxwell was using to read binlog. So the stack we have introduced to achieve high availability contains HAProxy (http://www.haproxy.org/) and GTID enabled slaves. HAProxy is a free, very fast and reliable solution offering high availability, load balancing, and proxying for TCP and HTTP-based applications. We have installed HAProxy on the local machine where we are running Maxwell and configured maxwell to connect to localhost where we have used HAProxy primary-secondary backup strategy using below config (/etc/haproxy/haproxy.cfg ): defaults mode tcp timeout connect 5000ms timeout client 86400s timeout server 86400s listen MySQL bind 127.0.0.1:3306 mode tcp option mysql-check user haproxy # mysql-check enables a database server check, t

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.c