Read Recent Logs only

Hi All,

Sometimes, we have a situation to read recent logs from the log file which continuously growing from the current logs; source can be anything like system logs, application logs, database logs, device logs etc etc...

My Problem- Need to read all recent slow queries of mongo (nosql database) logs and notified when it breaches threshold (400ms).

Solution- It can be achieved by using various scripting/programming language like node.js or other scripting language, I opt 'BASH script.

#!/bin/bash

## Please update the value according to your system/environment.

## set variable

logfilepath=/var/log/mongo                                          # please change the path as per the environment

name=mongod.log                                                         # mongo log file name

logrecord=<path_to_capture_current_script_log>        # for recording current script log

MaxLLN_file=<file_to_hold_max_LLN_value>         # for maintaing Max Last Line Number

slow_query_log_record=<logged_only_slow_queries>

tmpfile=<temp_file_path>

mailnotification=<notification_filename_which we have to monitored/display/forward>

## Set variable for mail

to='abc@xyz.com'

from='Sender:xxx'

sub='<subject_of_the_mail>'

## No need to change/update anything here. Please change cautiously,if needed.

## If file exists having start line number then save it into a file.

if [ -f $MaxLLN_file ];then

    echo -e "[`date +%F_%T`] $MaxLLN_file File exist, max LLN value is copied" >> $logrecord

    startlineno=`cat $MaxLLN_file`

else

    # If file is not exist

    echo -e "[`date +%F_%T`] $MaxLLN_file doesn't exist so startlineno counter start from 1" >> $logrecord

    startlineno=1

    echo 1 > $MaxLLN_file

fi

while true

do

    startlineno=`cat $MaxLLN_file`

    totallineno=`wc -l $logfilepath/$name|awk '{print $1}'`

    if [ $startlineno -le $totallineno ];then

        echo -e "[`date +%F_%T`] Slow log search start from between $startlineno untill $totallineno line number" >> $logrecord

        slowqeries_count=`sed -n "$startlineno","$totallineno"p $logfilepath/$name | grep -c 'ms'`

        if [ $slowqeries_count -ge 1 ];then

            echo -e "[`date +%F_%T`] $slowqeries_count slow queries found between $startlineno and $totallineno line number" >> $logrecord

            sed -n "$startlineno","$totallineno"p $logfilepath/$name | grep 'ms' > $tmpfile

            `cat $tmpfile >> $slow_query_log_record`

            echo -e "Dear All,\n\nBelow are the Slow query logs of Mongo Server `hostname` ( taking greater than or equal to 400ms)\n\n" > $mailnotification

            cat $tmpfile | while read line

            do

                if [ `echo $line | gawk '{print $NF-400}'` -ge 0 ];then echo $line >> $mailnotification

                fi

            done

            echo -e "\n\n Thanks & Best Regards,\nAnurag Bisht" >> $mailnotification

            echo "Subject: $sub" | cat - $mailnotification | sendmail -F"$from" -t "$to"

            echo > $mailnotification;

        else

            echo -e "[`date +%F_%T`] $slowqeries_count Slow queries found between $startlineno and $totallineno line number" >> $logrecord

        fi

        totallineno=`expr $totallineno + 1`

        echo $totallineno > $MaxLLN_file

        echo -e "[`date +%F_%T`] New search will start from `cat $MaxLLN_file` value" >> $logrecord

        echo -e "---------------------------------" >> $logrecord

    fi

done

Import Mysql data (or a CSV file) into MongoDB

If you have Open source MySQL database. And want to move either all, or some of the data into MongoDB (noSQL Database). There may be any other direct/indirect way to do it,  but you can also achieve this thing from below method (Add on) :) 

Step 1. 

Export all of your MySQL data as a CSV file.

select columns INTO OUTFILE '/path/to/csv' 

   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

   LINES TERMINATED BY '\n' from table [where clause]

Step 2.

Import the CSV into MongoDB.

mongoimport -d dbname -c collname --type csv -f fields-sep-by-coma  --drop /path/to/csv 

if your csv file has a header row add --headerline option.

Suppose you have a big table having more than 100 columns then how can you display/show those columns as a comma separated. Here is the way- 

mysql>select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'")) 

from INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_NAME = 'your_table_name' AND 

TABLE_SCHEMA = 'your_database_name';

Secure authentication in Mysql (For DBA Administration purpose)

Dear Folks,

For secure authentication in mysql database server, "mysql_config_editor" tool ( available from mysql 5.6.6 version) enables you to store authentication information in encrypted format like below-

[devpcs-readonly]

user = dba

password = *****

host = 10.0.7.1

[devcpdb-readonly]

user = dba

password = *****

host = 10.0.2.1

[sitpcs-repl]

user = dba

password = *****

host = 172.31.2.14

[sitcpdb-repl]

user = dba

password = *****

host = 172.31.2.1

Above details can be seen by the below command -

mysql_config_editor print --all

Now we will see a demo to save a new authentication connection-

shell> mysql_config_editor set --login-path=<name_to_which_you_want_to_connect> —host=<IP_of_remote/local_server> —user=username —password

Enter Password:xxxxxxxxx

E.g
shell>mysql_config_editor --login-path='uat-pcs-readonly' --host='10.22.0.1' --user='readonly' --password
Enter Password: xxxxxx

Trying to login now-

shell>mysql --login-path=uat-pcs-readonly 
mysql>

You can also use --prompt option for avoiding any discrepancy among the multiple mysql console.

Example-

shell>mysql --login-path=uat-pcs-readonly --prompt="\u@\h [\d@\p] mysql> " 
readonly@172.22.0.1 [(none)@3306] mysql>


In this way, we do not need to write or provide database password again and again.

Obj-
Do not save mysql password in plain text flat file (csv/txt/.sh/etc)

Pros-
For DBA Administration related automate scripts, this feature can be useful in many cases.

Cons-
Anybody can login into mysql database by issuing --login-path option. So do not provide "root" credentials anywhere. Restrict user privileges.

Reference-
http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html

Crash-safe Replication

Crash-safe Replication

If you're familiar with mysql-replication, you know that the replication information is stored in two files: master.info and relay-log.info. 

master.info

This file contain information about the connection to the master—such as hostname, user, and password—but also information about how much of the binary log that has been transferred to the slave.

relay-log.info

This file contain information about the current state of replication, that is, how much of the relay log that has been applied.

The update of these files are arranged so that they are updated after the transaction had been applied. This means that if you have a crash between the transaction commit and the update of the files, the replication progress information would be wrong.

Crash-safe masters

Two problems related to crash-safe replication has been fixed in the master, both of which could cause some annoyance when the master recovered.

• If the master crashed when a binary log was rotated, it was possible that some orphan binlog files ended up in the binary log index file. This was fixed in 5.1 but is also a piece in the puzzle of having crash-safe replication.
• Writing to the binary log is not an atomic operation, and if a crash occurred while writing to the binary log, there were a possibility of a partial event at the end of the binary log.Now, the master recovers from this by truncating the binary log to the last known good position, removing the partially written transaction and rolling back the outstanding transactions in the storage engines.

Crash-safe slaves

Several different solutions for implementing crash-safety—or transactional replication, as it is sometimes known as—have been proposed. The MySQL replication team decided to implement crash-safety by moving the replication progress information into system tables. This is a more flexible solution and has several advantages compared to storing the positions in the InnoDB transaction log:

• If the replication information and data is stored in the same storage engine, it will allow both the data and the replication position to be updated as a single transaction, which means that it is crash-safe.
• If the replication information and data is stored in different storage engines, but both support XA(eXtended Architecture[http://www.percona.com/live/mysql-conference-2013/sites/default/files/slides/XA_final.pdf]), they can still be committed as a single transaction.
• The replication information is flushed to disk together with the transaction data. Hence writing the replication information directly to the InnoDB redo log does not offer a speed advantage, but does not prevent the user from reading the replication progress information easily.
• The tables can be read from a normal session using SQL commands, which also means that it can be incorporated into such things as stored procedures and stored functions.

In order to make the solution flexible, we(MySQL) introduced a general API for adding replication information repositories. This means that we can support multiple types of repositories for replication information. In order to select what type of repository to use, two new options were added. These options are also available as server variables.

master_info_repository

The type of repository to use for the master info data.

relay_log_info_repository

The type of repository to use for the relay log info.

**Both of the variables can be set to either FILE or TABLE. If the variable is set to TABLE the new table-based system will be used and if it is set to FILE, the old file-based system will be used. The default is FILE, so make sure to set the value if you want to use the table-based system.

Selecting replication repository engine

As you know MyISAM is not very transactional, so it is necessary to set this to use InnoDB instead if you really want crash-safety. To change the engine for these tables you can just use a normal ALTER TABLE.

slave> ALTER TABLE mysql.slave_master_info ENGINE = InnoDB;
slave> ALTER TABLE mysql.slave_relay_log_info ENGINE = InnoDB;

Event processing
This implementation of crash-safe slaves work naturally with both statement-based and row-based replication and there is nothing special that needs to be done in the normal cases. However, these tables interleave with the normal processing in a little different ways.

To understand how transactions are processed by the SQL thread, let us consider the following example transaction:
START TRANSACTION;
INSERT INTO articles(user, title, body)
      VALUE (4711, 'Taming the Higgs Boson using Clicker Training', '....');
UPDATE users SET articles = articles + 1 WHERE user_id = 4711;
COMMIT;

This transaction will be written to the binary log and then sent over to the slave and written to the relay log in the usual way. Once it is read from the relay log for execution, it will be executed as if an update statement where added to the end of the transaction, before the commit:
START TRANSACTION;
INSERT INTO articles(user, title, body)
      VALUE (4711, 'Taming the Higgs Boson using Clicker Training', '....');
UPDATE users SET articles = articles + 1 WHERE user_id = 4711;
UPDATE mysql.slave_relay_log_info
   SET Master_log_pos = @@Exec_Master_Log_Pos,
       Master_log_name = @@Relay_Master_Log_File,
       Relay_log_name = @@Relay_Log_File,
       Relay_log_pos = @@Relay_Log_Pos
COMMIT;

In this example, there is a number of pseudo-server variables (that is, they don't exist for real) that have the same name as the corresponding field in the result set from SHOW SLAVE STATUS. As you can see, the update of the position information is now inside the transcation and will be committed with the transaction, so if both articles and mysql.slave_relay_log_info are in the same transactional engine, they will be committed as a unit.

Since a commit to the table is expensive—in the same way as syncing a file to disk is expensive when using files as replication information repository—the updates of the slave_master_info table is not updated with each processed event. Depending on the value of sync_master_info there are a few alternatives.

If sync_master_info = 0
In this case, the slave_master_info table is just updated when the slave starts or stops (for any reason, including errors), if the relay log is rotated, or if you execute a CHANGE MASTER command.
If sync_master_info > 0
Then the slave_master_info table will be updated every sync_master_info event.

MySQL UUID (Universal Unique Identifier)-

Structure of UUID

MySQL uses UUID version 1 which is a 128-bit number represented by a utf8 string of five hexadecimal numbers

• The first three numbers are generated from a timestamp.
• The fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (for example, due to daylight saving time).
• The fifth number is an IEEE 802 node number that provides spatial uniqueness. A random number is substituted if the latter is not available (for example, because the host computer has no Ethernet card, or we do not know how to find the hardware address of an interface on your operating system). In this case, spatial uniqueness cannot be guaranteed. Nevertheless, a collision should have very low probability.

The timestamp is mapped as follows:
When the timestamp has the (60 bit) hexadecimal value: 1d8eebc58e0a7d7. The following parts of the UUID are set:: 58e0a7d7-eebc-11d8-9669-0800200c9a66. The 1 before the most significant digits (in 11d8) of the timestamp indicates the UUID version, for time-based UUIDs this is 1.

Fourth and Fifth parts would be mostly constant if it is generated from a single server. First three numbers are based on timestamp, so they will be monotonically increasing. Lets rearrange the total sequence making the UUID closer to sequential. This makes the inserts and recent data look up faster. Dashes (‘-‘) make no sense, so lets remove them.
58e0a7d7-eebc-11d8-9669-0800200c9a66 => 11d8eebc58e0a7d796690800200c9a66

Data Sanity Check in mySQL replication

Hi Friends,

Today, we will see the method to check data/database schema sanity in mySQL replication architecture. This method describes to check data / schema validation in one to many slave relationship. It may be very useful for the daily basic monitoring purpose to ensuring for data integrity of the database.

mySQL data integrity check-

Open a file in vi or vim editor-

vi check_db_data_sync.sh

##################### This script checks checksum between each replicating tables of

### master and slave database server ##

#! /bin/bash

REPLICATING_TABLES=path_of_replicating_table_list_file
now=`date +%d-%m-%Y_%T`
DIR=path_of_log

## Below CNF are used for the authentication purpose
RLogin_CNF="/etc/rlogin.cnf"
Master_CNF="/etc/master.cnf"
SLAVE_CNF="/etc/slave.cnf" 

## mySQL function 

function check_db_sync()
{
    `mysql --defaults-file=$RLogin_CNF -e "show processlist;" | grep -w "repl"  | awk '{print $3}' | awk -F: '{print $1}' | sort | uniq > /tmp/j1.txt`
    if [ `wc -l /tmp/j1.txt | awk '{print $1}'` -ge 1 ];then

        for slave in `cat /tmp/j1.txt`
        do
            c1=0;
            c2=0;
            echo -e "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
            echo -e "For SLAVE: \t$slave at $now";
            echo -e "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
            for tab in `cat $REPLICATING_TABLES`
            do
                ############# Check checksum in Master Server ###############
                `mysql --defaults-file=$Master_CNF --database cmdb -e "checksum table $tab;"  > master_"$tab".csv`
                ############# Check checksum in Slave Servers ################
                `mysql --defaults-file=$SLAVE_CNF -h$slave --database cmdb -e "checksum table $tab;" > slave_$slave_"$tab".csv`
                mchksum=`tail -1 master_$tab.csv | awk '{print $2}'`
                schksum=`tail -1 slave_$slave_$tab.csv | awk '{print $2}'`
                if [ $mchksum -eq $schksum ]; then
                    c1=`expr $c1 + 1`
                else
                    echo -e "Mis-match Table:\t$tab";
                    c2=`expr $c2 + 1`
                fi
            done
            c=`expr $c1 + $c2`;
            echo -e "Total Table Found: \t$c";
            echo -e "Table data match : \t$c1";
            echo -e "Table data Mis-match : \t$c2";
            mv master_$tab.csv master_$tab_$now.csv_done
            mv slave_$slave_$tab.csv slave_$slave_$tab_$now.csv_done
        done
    else
        echo -e "Slave servers are not found";
    fi
}

## call mySQL function
check_db_sync
rm /tmp/j1.txt
cd $DIR
rm [ms]*_done 

## close vi editor

Now change the mode of the file by using "chmod" linux command, as 

chmod +x check_db_data_sync.sh 

## Execute-

/bin/sh path_of_file/check_db_data_sync.sh

Online Full/Incremental MySQL backup via xtrabackup open source tool

Hi Folks,
Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB. With Percona XtraBackup, you can achieve the following benefits:

• Backups that complete quickly and reliably
• Uninterrupted transaction processing during backups
• Savings on disk space and network bandwidth
• Automatic backup verification
• Higher uptime due to faster restore time

It performs streaming, compressed, and incremental backups to your MySQL backup database.
So lets see, here I will demonstrate you following things-
(i) Automate Full / Incremental backup via shell script
(ii) Keep only last X days of backup

Pre-requisite:-

(i) Download xtrabackup :
      Here I am using xtrabackup version 2.1.4 ( percona-xtrabackup-2.1.4-656-Linux-x86_64.tar.gz )
(ii) qpress-11-linux-x64.tar (it is required for de-compression)
(iii) plugin " perl-DBD-MySQL" should be installed  
      yum install perl-DBD-MySQL
(iv) cp  xtrabackup_56 /usr/bin

(v) USER privilege required for 'xtrabackup' user


  mysql> CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password';

  mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost';

  mysql> FLUSH PRIVILEGES; 

 Now below is the full backup script

Full-backup.sh

#! /bin/bash

now=`date +%d-%m-%Y_%T`
TYPE="full-bak.log"
source /dbscriptlog/script/variable.sh
$XtraBackup/innobackupex --host=$h --user=$u --password=$p --slave-info $BACKUP_DIR/ > $LOG_DIR/$HOSTNAME-$TYPE 2>&1
mv $LOG_DIR/$HOSTNAME-$TYPE $LOG_DIR/$HOSTNAME-$TYPE-$now

## Get the latest backup directory from /backup path
ls -lhtr $BACKUP_DIR > $_file
LAST_BACKUP_DIR=`awk '{print $9}' $_file | tail -1`
rm $_file

## Delete X days older file ###
find $BACKUP_DIR -type d -name '*20*' -mtime +$DAYS_KEEP -exec rm -rf  {} \;

Contents of variable.sh file which is including in full-backup.sh and incr-backup.sh script.


_file=/tmp/p1.txt
HOSTNAME=`hostname`
BACKUP_DIR=/backup/mysql
LOG_DIR=/dbscriptlog/log/backuplog
XtraBackup=<PATH_OF_PERCONA_PKG>/percona-xtrabackup-2.1.4-Linux-x86_64/bin

u=backuser
p='backuppassword'
h='localhost'
DAYS_KEEP=X               ## (Number of Days to store mysql backup)

incr-backup.sh   

#! /bin/bash

now=`date +%d-%m-%Y_%T`
source /dbscriptlog/script/variable.sh
TYPE="incremental-bak.log"
ls -lhtr $BACKUP_DIR > $_file
LAST_BACKUP_DIR=`awk '{print $9}' $_file | tail -1`
rm $_file
$XtraBackup/innobackupex --host=$h --user=$u --password=$p --slave-info --incremental $BACKUP_DIR/ --incremental-basedir=$BACKUP_DIR/$LAST_BACKUP_DIR > $LOG_DIR/$HOSTNAME-$TYPE 2>&1
mv $LOG_DIR/$HOSTNAME-$TYPE $LOG_DIR/$HOSTNAME-$TYPE-$now

## Delete X days older file ###
find $BACKUP_DIR -type d -name '*20*' -mtime +$DAYS_KEEP -exec rm -rf  {} \;