Mysql: Daily Checklist

 

 

Then leave a small post on how to mount an automatic notification via mail that tells us what is the status of our database. For example we can manage it a bit before starting our work day and before we leave or while on vacation (jaja. ..). This example is about a MySQL 5.x running on Debian. Steps as follows:

  • Check server uptime, memory and disk space.
     
  • Check the latest backups or logs of the same
     
  • Obtaining the status of the database
     
  • Error logs from the database.
     
  • Notification via mail.

The system is to build a script that builds our report in a file and then mail it. Moreover, in this example the state of the database I get to mysqlreport *. This gives us enough information to know that has happened recently in the database and can detect any problem if we know what the baseline.

* Be careful because it is available from version 5.x

Step 1) Edit the script based báscia checklist.sh. As you can see has no secret because it is always just redirect the output of a specific command to a temporary file which is then attached as the body of mail or attachment. The format and style and are things of each ...

 

 

 

#!/bin/bash

#

#  Mysql Daily Checklist

#

# Parameters

LOG=/scripts/checklist/check.txt

 

# Here starts the longline action

TXT=''

SL=''

#  State Machine

TXT='>>> 0.UPTIME'

echo $TXT > $LOG

w >> $LOG

# Server Memory

TXT='>>> 1.MEMORY'

echo $SL >> $LOG

echo $TXT >> $LOG

free -m >> $LOG

# Disk Space

TXT='>>> 2.DISK ESPACE  '

echo $SL >> $LOG

echo $TXT >> $LOG

df -h >> $LOG

# Latest backups

TXT='>>>3.BACKUPS'

echo $SL >> $LOG

echo $TXT >> $LOG

ls -lh -t /var/backups_mysql/ >> $LOG

# State mysql

TXT='>>>4.State   MYSQL'

echo $SL >> $LOG

echo $TXT >> $LOG

mysqlreport –user root –password pwd –all >> $LOG

 

#  Notificatiojn via email

echo “Checklist”|mail .s “MYSQL > Daily Checklist ” ilmasacratore@dataprix.com < $LOG

# Delete temp file

rm $LOG

 

 

 

 

 

Step 2) Execute Permissions and programming

For Unix systems you can use crontab to schedule the execution, after assigning execute permissions to the file for the user who will put it in cron

Example email:

 

 

 

 

0.UPTIME
 09:00:01 up 13:20,  0 users,  load average: 0.00, 0.00, 0.00
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHAT

>>> 1.MEMORY
             total       used       free     shared    buffers     cached
Mem:           884        877          6          0         44        745
-/+ buffers/cache:         88        795
Swap:          737          0        737

>>> 2.Disk Space
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              17G  8.4G  7.9G  52% /
tmpfs                 443M     0  443M   0% /lib/init/rw
tmpfs                 443M     0  443M   0% /dev/shm

>>> 3.BACKUPS
>>> 4.MYSQL STATE

MySQL 5.0.32-Debian_7et  uptime 0 13:9:20       Wed Jul 14 09:00:01 2010

__ Key _________________________________________________________________
Buffer used   387.00k of  16.00M  %Used:   2.36
  Current       1.84M            %Usage:  11.52
Write ratio      0.03
Read ratio       0.10

__ Questions ___________________________________________________________
Total           7.61k    0.16/s
  Com_          5.47k    0.12/s  %Total:  71.93
  COM_QUIT        807    0.02/s           10.61
  DMS             665    0.01/s            8.74
  +Unknown        602    0.01/s            7.92
  QC Hits          61    0.00/s            0.80
Slow                6    0.00/s            0.08  %DMS:   0.90
DMS               665    0.01/s            8.74
  SELECT          648    0.01/s            8.52         97.44
  UPDATE           12    0.00/s            0.16          1.80
  DELETE            4    0.00/s            0.05          0.60
  INSERT            1    0.00/s            0.01          0.15
  REPLACE           0    0.00/s            0.00          0.00
Com_            5.47k    0.12/s           71.93
  show_create   1.15k    0.02/s           15.10
  show_status     797    0.02/s           10.48
  show_variab     797    0.02/s           10.48

__ SELECT and Sort _____________________________________________________
Scan            3.92k    0.08/s %SELECT: 605.09
Range               0    0.00/s            0.00
Full join           1    0.00/s            0.15
Range check         0    0.00/s            0.00
Full rng join       0    0.00/s            0.00
Sort scan           7    0.00/s
Sort range          1    0.00/s
Sort mrg pass       0    0.00/s

__ Query Cache _________________________________________________________
Memory usage  106.50k of  16.00M  %Used:   0.65
Block Fragmnt   0.68%
Hits               61    0.00/s
Inserts            65    0.00/s
Prunes              1    0.00/s
Insrt:Prune      65:1    0.00/s
Hit:Insert     0.94:1

__ Table Locks _________________________________________________________
Waited              0    0.00/s  %Total:   0.00
Immediate       1.32k    0.03/s

__ Tables ______________________________________________________________
Open               64 of   64    %Cache: 100.00
Opened          2.01k    0.04/s

__ Connections _________________________________________________________
Max used            3 of  100      %Max:   3.00
Total             810    0.02/s

__ Created Temp ________________________________________________________
Disk table      1.19k    0.03/s
Table           3.52k    0.07/s
File                5    0.00/s

 

 

Now, who likes or knows a little of topic you can think a thousand things to add or things that are missing. For some specific queries as the number of processos so we can use from the command line mysqladmin-u-p <user> <key> <option> for more interesting facts ... Everyone who makes it his own way!

 

  • Star
  • Star
  • Star
  • Star
  • Star