Mobile Alerts & Notifications For Your Database Using Telegram

One of the great features of Telegram is its bot platform. Users can interact with bots by sending them messages, commands and inline requests and it can be controlled by using HTTPS requests to Telegram’s bot API. A bot allows automated systems and servers to send telegram messages to users. Quite often, it can be useful to send stuff to yourself.

In this blog post, we are going to show you how to send push notifications to your mobile via Telegram from the database server. This is a very useful trick to get notified in real-time when a problem happens, especially when the issue occurs randomly and you can’t see a pattern. The basic idea is to detect something and send a push notification via Telegram. Telegram apps can be downloaded from the Apple App Store, Google Play Store, as well as for the desktop version on Windows, Mac and Linux. In our example, we’ll show you howyou can get notified on your cell phone in case a query takes more than 30 seconds to complete.

Create a Detection Script

Firstly, we have to create a detection script. This script will query some stuff, check something and present an output if the conditions are met. Suppose we are having a MySQL database server, and we would like to detect all queries that have been running for more than 30 seconds by scanning the MySQL processlist every 10 seconds. We will utilize information_schema for this purpose where we can populate the needed result using SQL query.

The following bash script should do the job:

#!/bin/bash
# detect_long_query.sh

INTERVAL=10
QUERY_TIME=30

while true
do
        OUTPUT=$(mysql -A -Bse "SELECT * FROM information_schema.processlist WHERE command = 'Query' AND time > $QUERY_TIMEG")
        if [[ ! -z $OUTPUT ]]; then
                echo "Date: $(date)"
                echo "$OUTPUT"
        fi

        sleep $INTERVAL
done

Create an option file so we can automate the user login via “mysql” client:

$ vim ~/.my.cnf
[client]
user=root
password=password

Before running the script, give the script an execution permission beforehand:

$ chmod 755 detect_long_query.sh
$ ./detect_long_query.sh

Now, run a query that would take more than 30 seconds to complete on the database node. You should see the following output:

Date: Thu Oct 31 03:13:14 UTC 2019
*************************** 1. row ***************************
             ID: 4987
           USER: root
           HOST: localhost
             DB: sbtest2
        COMMAND: Query
           TIME: 38
          STATE: Altering table
           INFO: ALTER TABLE sbtest1 FORCE
        TIME_MS: 38030.478
          STAGE: 0
      MAX_STAGE: 0
       PROGRESS: 0.000
    MEMORY_USED: 84696
MAX_MEMORY_USED: 321376
  EXAMINED_ROWS: 0
       QUERY_ID: 101661
    INFO_BINARY: ALTER TABLE sbtest1 FORCE
            TID: 6838

Ctrl+C to exit. The output verbosity of the captured query is informative enough. We want the above output to be pushed to our mobile phone immediately.

Create a Telegram Bot and Channel

Now let’s create a telegram bot and channel. To create a Telegram bot:

1. Download, install and open the Telegram apps (registration is required)
2. Search for the “botfather” telegram bot (he’s the one that’ll assist you with creating and managing your bot).
3. You can start with “/help” to see all the possible commands.
4. Click on or type “/newbot” to create a new bot.
5. Give it a name. Here we are going to use “Long Query Detector”
6. Give the bot a username. It must end up with the word “bot”. Here we are going to use “my_long_query_detector_bot“.

Example as in the following screenshot (via Telegram Desktop app):

You will get a token to access the HTTP API HOME directory of the user(token has been masked partially). Keep it safe for the next section.

Now we have to create a channel. This is the destination of the push notifications. Subscriber to this channel can write and read messages. From the Telegram application, go to “New Channel” and enter the required information:

Next, choose “Public Channel” and write down a unique URL (this is the channel ID as well) for the channel. Here we use “long_query_detector“:

Click SAVE and proceed to add channel members. In the search box, look up “my_long_query_detector_bot” and invite it into the channel:

Channel created. If you clicked on the Channel Info, you should see the following details:

There are 2 members in the channel, the bot and us. The channel name is long-query-detector while the channel ID is long_query_detector. Notification is turned on. We can now proceed to send messages to this channel.

Install Telegram Script

Get the Telegram script and put it into the environment path of the database node:

$ git clone https://github.com/fabianonline/telegram.sh
$ cd telegram.sh/
$ cp telegram /usr/bin/

Now we can test to push a notification to the channel (token has been partially masked):

$ telegram -t 10****8477:AAFduz0qz******************FiVcbnzE -c @long_query_detector "test"

You should get the test message by @my_long_query_detector_bot, as shown in the following screenshot:

Looks good. Now we have to save the token somewhere inside the server where the telegram script would recognize. Create a file called “.telegram.sh” under the HOME directory of the user and append the following line (token has been partially masked):

$ vim ~/.telegram.sh
TELEGRAM_TOKEN="10****8477:AAFduz0qz******************FiVcbnzE"
TELEGRAM_CHAT="@long_query_detector"

We can now push a new notification without the token and channel ID parameters in the command:

$ telegram "test again"

You should get a new message in the channel. Now we can integrate Telegram with our detection script.

Push it Out

Make some amendments to our detection script:

#!/bin/bash
# detect_long_query.sh
INTERVAL=10
QUERY_TIME=30
OUTPUT_FILE=longquery.txt

while true; do

        OUTPUT=$(mysql -A -Bse "SELECT * FROM information_schema.processlist WHERE command = 'Query' AND time > $QUERY_TIMEG")
        if [[ ! -z $OUTPUT ]]; then
                echo "Date: $(date)" > $OUTPUT_FILE
                echo "$OUTPUT" >> $OUTPUT_FILE
                cat $OUTPUT_FILE | telegram -
        fi

        sleep $INTERVAL

done

Pay attention to lines 5, 11, 12 and 13 where we added output redirection to a file and then send the output of the file to the Telegram channel. Now, we are ready to start the detection script in the background:

$ nohup ./detect_long_query.sh &

Let’s try by executing the following statement on the database server (to force table rebuilding for a 5 million-rows table):

MariaDB> ALTER TABLE sbtest2.sbtest1 FORCE;

Wait for a maximum of 40 seconds (30 seconds long query time + 10 seconds interval) and you should see the following push notification coming in:

Cool! You can now sit back and wait knowing that queries exceeding 30 seconds will be logged into this channel and you will get notified.

ClusterControl and Telegram Integration

All ClusterControl notifications can be pushed to Telegram by using ClusterControl integration module configurable at ClusterControl -> Integrations -> 3rd party Notifications -> Add New Integrations. On step 1, we have to choose one of supported integration services before proceeding to step 2. For Telegram, you have to specify the required bot token and the channel ID with a “@” prefix:

Click on the “Test” button where ClusterControl will send out a test notification to the channel for verification. Then, proceed to step 3 to select a cluster (or you can pick multiple clusters) and the event (or you can pick multiple events) that will be triggered by ClusterControl:

Save the setting and you are good. The triggered events will be pushed to the configured Telegram channel. Here is an example of what you would get in the channel if something went wrong:

That’s it for now. Happy notifying!