Shantanu's Blog

#!/bin/sh
# change the master position after reading the last read position from error log

binfile=`grep "Slave I/O thread exiting, read up to log" /var/log/mysqld.log | tail -1 | awk '{print $12}'`
position=`grep "Slave I/O thread exiting, read up to log" /var/log/mysqld.log | tail -1 | awk '{print $14}'`
echo "CHANGE MASTER TO MASTER_HOST='192.168.50.31', MASTER_USER='slave_user', MASTER_PASSWORD='slave_user', MASTER_LOG_FILE=$binfile MASTER_LOG_POS=$position;"

Labels: mysql tips

# cat status.sh
#!/bin/sh
# Slave Check status
mystatus=`mysql -uroot -pPassWord -e"show slave status\G" | grep 'Slave_SQL_Running' | awk '{print $2}'`
if [[ $mystatus = 'No' ]];then
echo "Slave is not working"
else
echo "Slave is working OK"
fi

Labels: mysql tips

A typical crontab file looks like this.

MAILTO=shantanu.oak+226130-dbserver.cron@gmail.com

## Check slave status cron
*/10 * * * * sh /home/shantanu/status.sh >> /home/shantanu/slave_status_success.txt 2>> /home/shantanu/slave_status_err.txt


## check disk space
1 * * * * sh -xv /home/shantanu/disk.sh 1> /home/shantanu/disk_succ.txt 2>> /home/shantanu/disk_err.txt

## MySQL stats alerts
# record status of both masters
* * * * * mysqladmin -h111.222.333.101 -uroot -pPassWord status >> /home/shantanu/status_success_101.txt 2>> /home/shantanu/status_err_101.txt
* * * * * mysqladmin -h111.222.333.102 -uroot -pPassWord status >> /home/shantanu/status_success_102.txt 2>> /home/shantanu/status_err_102.txt

# alert when status descripency found
* * * * * sh -xv /home/shantanu/myalert.sh 101 1> /home/shantanu/alert_succ_101.txt 2> /home/shantanu/alert_err.txt
* * * * * sh -xv /home/shantanu/myalert.sh 102 1> /home/shantanu/alert_succ_102.txt 2> /home/shantanu/alert_err.txt


## Watch config files
# mail if my.cnf file changed
20 00 * * * find /etc/ -name my.cnf -mtime 0 -exec sh -c 'cat {} | mail -s "`hostname` my.cnf file changed" shantanu.oak+`hostname`@gmail.com' \;

# mail if crontab file changed
30 00 * * * find /var/spool/cron -mtime 0 -type f -exec sh -c 'cat {} | mail -s "`hostname` cron file changed" shantanu.oak+`hostname`@gmail.com' \;


## Backup
# DB structure backup
40 00 * * * mysqldump -h111.222.333.101 -uroot -pPassWord -d -R --all-databases | zip > /home/shantanu/backup_structure_101.sql.zip 2> /home/shantanu/backup_err.txt
45 00 * * * mysqldump -h111.222.333.102 -uroot -pPassWord -d -R --all-databases | zip > /home/shantanu/backup_structure_102.sql.zip 2> /home/shantanu/backup_err.txt


## log alerts to /var/log/messages
# mysql high processes
* * * * * myvar1=$(mysqladmin -h111.222.333.101 -uroot -pPassWord processlist | wc -l | awk '{print int($1)}'); [ $myvar1 -gt 50 ] && logger "MySQL processes are high $myvar1 at $(date)" >/dev/null 2>&1
* * * * * myvar1=$(mysqladmin -h111.222.333.102 -uroot -pPassWord processlist | wc -l | awk '{print int($1)}'); [ $myvar1 -gt 50 ] && logger "MySQL processes are high $myvar1 at $(date)" >/dev/null 2>&1

# ping fail
* * * * * ping 111.222.333.101 -c1 > /dev/null; [[ $? -ne 0 ]] && logger "Could not ping to 101 DB1 server" > /dev/null 2>&1
* * * * * ping 111.222.333.102 -c1 > /dev/null; [[ $? -ne 0 ]] && logger "Could not ping to 102 DB2 server" > /dev/null 2>&1

Labels: mysql tips

If the column TxnDate is "datetime" type shouldn't the first query work just as the second one?

mysql>select * from Response where date(TxnDate) = '2010-02-05';
Empty set (0.11 sec)

mysql>select * from Response where trim(date(TxnDate)) = '2010-02-05';
...
...
207 rows in set (0.12 sec)
_____

This has to do something with the bug No. 29898
DATE() function returns inconsistent results

http://bugs.mysql.com/bug.php?id=29898

This is a very serious bug and I will suggest that the enterprises should think twice before choosing MySQL as their database.

Labels: mysql bug

1) Find the binary position from backup file.

# grep '^-- CHANGE MASTER TO MASTER_LOG_FILE' toamaravati.sql | cut -f3 -d'=' | sed 's/;//'
179487024

mysqlbinlog /var/log/mysql/mysql-bin.* | grep -r "179487024"
#100203 15:55:38 server id 99 end_log_pos 179487024 Query thread_id=2048 exec_time=0 error_code=0

mysqlbinlog /var/log/mysql/mysql-bin.* --start-datetime="2010-02-03 15:55:38" > tocentral.txt

# Generate SQL statements with the required csv files for load data statement
mkdir tmp
mysqlbinlog /var/log/mysql/mysql-bin.* --start-datetime="2010-02-03 15:55:38" --local-load='tmp/' > tocentral.txt

Labels: mysql tips

#!/bin/sh
#ftp the dump file every Wednesday to a remote server

cd /backup/mysqldump/

tosend="ATMamaravati03-Feb-2010-15.sql.zip"

mydate=`date | awk '{print $1}'`
if [[ $mydate -eq 'Wed' && -s /backup/mysqldump/$tosend ]];then
myvar1="SQL data dump file created and today is Wednesday"

ftp -in << mytransfer
open 110.212.191.62
user develop PassWord
cd /home/develop
bin
mput $tosend
quit
exit 0
mytransfer

fi

Labels: shell script

There are times when you need to remove the DEFINER part of the SP or Function. You can use the mysqldump and pipe the output to the perl utility called dump filer. It will remove the unnecessary part and give you the clean output.

http://forge.mysql.com/tools/tool.php?id=242

# Normal Output looks like this...
mysqldump -udeveloper -pmatrixlist -d --routines freshnew1 | more
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `quota_amount`(f_service_no int (6),

# new output looks like this...
mysqldump -udeveloper -pmatrixlist -d --routines freshnew1 | perl /home/develop/dumper.pl --delete | more
/*!50003 CREATE*/ /*!50020 /*!50003 FUNCTION `quota_amount`(f_service_no int (6),

This can be used when you do not care much about permission issues though.

Labels: mysql tips