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!
