MySQL Technical Track

gh-ost hooks for better visibility

5 min read

Oct 24, 2017

In this post I will show you how to leverage Github's gh-ost hooks for greatly improving visibility of ongoing schema changes for your organization. One of the potential uses of gh-ost hooks is to send status updates. I will present you with some examples of how to do that via email and Slack.

What are gh-ost hooks?

gh-ost has built-in support for hooks, i.e. external scripts with predefined names, that will be called by gh-ost at certain points of a schema change operation. The complete list is available here, but the most useful gh-ost hooks for me are:

gh-ost-on-startup-hook
- called at the start of a schema change operation
gh-ost-on-status-hook
- ran every 10 minutes as the tool reports the status of the operation
gh-ost-on-success-hook
- executed when the operation succeeds
gh-ost-on-failure-hook
- invoked only if the operation fails for some reason
gh-ost-on-before-cut-over-hook
- called when the cut-over is ready - specially useful in the case you are manually doing the cutover
gh-ost-on-begin-postponed-hook
- invoked if you postpone the automatic cutover (by manually creating the flag file or issuing the postpone command over the unix socket interface)

Status variables

gh-ost exposes information via status variables that are passed to the hooks for reference. The full list of status variables is available in the doc files, but I will mention the ones I find most useful:

GH_OST_DATABASE_NAME
- db against which gh-ost is running the change
GH_OST_TABLE_NAME
- self explanatory
GH_OST_DDL
- the alter statement gh-ost is executing
GH_OST_ELAPSED_SECONDS
- total runtime

Using gh-ost hooks

To work with gh-ost hooks, simply create your scripts on any directory (e.g. /user1/ghost_hooks/) using the exact names gh-ost expects. The scripts require execute permission to be set:

chmod +x /user1/ghost_hooks/*

Finally, include the following parameter to your gh-ost command, specifying the location you created:

gh-ost --hooks-path=/user1/ghost_hooks/ ...

Integrating with email

A simple way to use gh-ost hooks is have emails sent to people on important events. I usually go with on-startup, on-failure and on-success hooks. Keep in mind gh-ost-on-status-hook will send an email every 10 minutes (for each gh-ost process you have) so you might want to implement some filtering rules to avoid excessive noise to your inbox if using that one.

Examples

The following scripts require the mailx package installed on the host where you run gh-ost. The host should of course be properly configured to send email (firewall rules, DNS, etc.). Note the use of status variables as mentioned before. gh-ost-on-startup-hook

#!/bin/bash
 # Sample hook file for gh-ost-on-startup
 
 notify_email=ivan@test.com
 
 text="$(date) gh-ost started on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME, change statement: $GH_OST_DDL"
 echo "$text" | mailx -v -s "gh-ost started on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME" $notify_email

gh-ost-on-failure-hook

#!/bin/bash
 # Sample hook file for gh-ost-on-failure
 
 notify_email=ivan@test.com
 
 text="$(date) gh-ost failed on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME; ghost: $GH_OST_OLD_TABLE_NAME, change statement: $GH_OST_DDL, failed after $GH_OST_ELAPSED_SECONDS"
 echo "$text" | mailx -v -s "gh-ost failed on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME" $notify_email

gh-ost-on-success-hook

#!/bin/bash
 # Sample hook file for gh-ost-on-success
 
 notify_email=ivan@test.com
 
 text="$(date) gh-ost successfully completed on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME, change statement: $GH_OST_DDL. Copied $GH_OST_COPIED_ROWS rows in $GH_OST_ELAPSED_COPY_SECONDS seconds. Total runtime was $GH_OST_ELAPSED_SECONDS seconds."
 
 echo "$text" | mailx -v -s "gh-ost successful on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME" $notify_email

gh-ost-on-status-hook

#!/bin/bash
 
 # Sample hook file for gh-ost-on-status
 notify_email=ivan@test.com
 
 text="$(date) gh-ost running on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME, change statement: $GH_OST_DDL. Time elapsed: ${GH_OST_ELAPSED_SECONDS}. Detailed status: ${GH_OST_STATUS}"
 echo "$text" | mailx -v -s "gh-ost running on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME" $notify_email

Integrating with Slack

Rather than sending email, a more elegant solution if your organization uses Slack (or a similar IM platform) is to integrate gh-ost to post messages to using web hooks. The following instructions instructions are for Slack, but should serve as a guideline for other IM platforms as well.

Go to https://my.slack.com/services/new/incoming-webhook/
Choose a channel (I recommend having a dedicated channel for gh-ost e.g. #gh-ost if possible)
Click the button to create the webhook

That will lead you to the newly created webhook page, where you can see the URL which was assigned to your webhook. It will look something like this:

https://hooks.slack.com/services/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

From this page you can also customize the user this integration will post as (e.g. ghostbot) and upload a fancy custom icon. At this point you are ready to try posting a message to the webhook to validate it works e.g.

curl -X POST --data-urlencode 'payload={"channel": "#gh-ost", "username": "ghostbot", "text": "Test posting to #gh-ost channel"}' https://hooks.slack.com/services/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

The last step is creating the scripts to post to the channel on gh-ost's behalf.

Examples

gh-ost-on-startup-hook

#!/bin/bash
 # Sample hook file for gh-ost-on-startup
 
 text="$(date) gh-ost started on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME, change statement: $GH_OST_DDL"
 curl -X POST --data-urlencode 'payload={"channel": "#gh-ost", "username": "ghostbot", "text": '"'$text'"'}' https://hooks.slack.com/services/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

gh-ost-on-status-hook

#!/bin/bash
 
 # Sample hook file for gh-ost-on-status
 
 text="$(date) gh-ost running on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME, change statement: $GH_OST_DDL. Time elapsed: ${GH_OST_ELAPSED_SECONDS}. Detailed status: ${GH_OST_STATUS}"
 curl -X POST --data-urlencode 'payload={"channel": "#gh-ost", "username": "ghostbot", "text": '"'$text'"'}' https://hooks.slack.com/services/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

gh-ost-on-failure-hook

#!/bin/bash
 # Sample hook file for gh-ost-on-failure
 
 text="$(date) gh-ost failed on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME; ghost: $GH_OST_OLD_TABLE_NAME, change statement: $GH_OST_DDL, failed after $GH_OST_ELAPSED_SECONDS"
 curl -X POST --data-urlencode 'payload={"channel": "#gh-ost", "username": "ghostbot", "text": '"'$text'"'}' https://hooks.slack.com/services/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

gh-ost-on-success-hook

#!/bin/bash
 # Sample hook file for gh-ost-on-success
 
 text="$(date) gh-ost successfully completed on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME, change statement: $GH_OST_DDL. Copied $GH_OST_COPIED_ROWS rows in $GH_OST_ELAPSED_COPY_SECONDS seconds. Total runtime was $GH_OST_ELAPSED_SECONDS seconds."
 curl -X POST --data-urlencode 'payload={"channel": "#gh-ost", "username": "ghostbot", "text": '"'$text'"'}' https://hooks.slack.com/services/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

gh-ost-on-before-cut-over-hook

#!/bin/bash
 # Sample hook file for gh-ost-on-before-cut-over
 
 text="$(date) gh-ost is ready for cutover on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME; change statement: $GH_OST_DDL. Connect to the host and issue echo "cut-over""
 curl -X POST --data-urlencode 'payload={"channel": "#gh-ost", "username": "ghostbot", "text": '"'$text'"'}' https://hooks.slack.com/services/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

gh-ost-on-begin-postponed-hook

#!/bin/bash
 # Sample hook file for gh-ost-on-begin-postponed
 
 text="$(date) gh-ost cutover postponed on $(hostname) for $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME, change statement: $GH_OST_DDL. Migration cut-over requires user interaction to proceed; will continue keeping new table in sync in the meantime"
 curl -X POST --data-urlencode 'payload={"channel": "#gh-ost", "username": "ghostbot", "text": '"'$text'"'}' https://hooks.slack.com/services/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

Final words

gh-ost is a very interesting tool for performing online schema changes, and it is quickly becoming more and more popular. This kind of well-thought interface for integrations are definitely one of the reasons. Using gh-ost hooks already? Have some good ones to share? Let me know in the comments section below!