Home / ubuntu / Questions / 1134627
In Process
cmak.fr
Asked: 2019-04-18 03:24:09 +0800 CST

How to Build report from mysqlcheck output

  • 772

I need to build a report from the output of MySQL database check and auto-repair.
But dont want the full report each time : only in case of repair was needed

A scheduled (cron) a task runs mysqlcheck
I want to get informed by email of eventual repair needed, repair success, repair fails...
But dont want the full report each time : only repairs

I run the mysqlcheck like this:

mysqlcheck --user=myuser --password=mypwd -c --auto-repair dbname > ouput.log

Found on a blog that the mysqlcheck output may looks like this in case of repair

Repairing tables
dbname.table1
warning  : Number of rows changed from 3 to 1
status   : OK
dbname.table2
warning  : Number of rows changed from 5454 to 5455
status   : OK
dbname.table3
warning  : Number of rows changed from 471859 to 471860
status   : OK

Since i cant find any official documentation about how looks the exact output, I need help to build a report text file made by processing the output of mysqlcheck.
Perhaps some regex magician can make a report giving repaired tables names, faillure notice and so.

This refers to the mysql server available on official Ubuntu repositories
mysql-server 5.7.25-0ubuntu0.18.04.2

bash mysql output text-processing

3 Answers

  1. This simple script is getting the mysqlcheck result and checking the output for "Repairing Tables". As this string only comes when the table is damaged.

    If exist then it sends an email to the desired address.

    #!/bin/bash

result=`mysqlcheck --user=myuser --password=mypass --auto-repair dbname;`

if [[ $result == *"Repairing tables"* ]]; then

  echo $result | mailx -s 'email subject'  [email protected]

fi
    • 1

  2. --silent mode

    When running mysqlcheck pass the parameter -s or --silent and only error messages will print.

    Modify your command like this:

    mysqlcheck --user=myuser --password=mypwd -c --auto-repair --silent dbname

    No need to redirect output to a working file and parse it. You do need cron setup to send emails. From this Q&A: How do I set Cron to send emails?

    MAILTO="[email protected]"
* * * * * mysqlcheck --user=myuser --password=mypwd -c --auto-repair --silent dbname
    • 1

  3. I believe the best thing would be to set it up this way:

    • in crontab -e schedule a bashscript e.g:
     - * * * * /bin/bash /root/custom-mysqlrepair.sh
    • create such script with something like:
    #!/bin/bash
#hint: adding user and password to .my.cnf should remove the need for user and password in this command
OUTPUT=$((mysqlcheck --user=myuser --password=mypwd -c --auto-repair dbname) 2>&1)
OUTPUT_NO_NEWLINES=`$OUTPUT | tr '\n'`
if [[ $string == *"Number of rows changed from"* ]]; then
  echo $OUTPUT
fi
    • make the script executable and place it in /root/

    now what's going to happen:

    • cron runs script
    • script tries to run your command and saves output to variable
    • IF the variable contains your desired word, print whole output
    • crontab should catch output into MAILTO output and email it normally

    Please note this is still untested theory, but should get you going.

    • 0