Mysql: Reset slave replication

stop slave;
reset slave;

CHANGE MASTER TO MASTER_HOST='10.250.1.33', MASTER_USER='replication', MASTER_PASSWORD='replipass', MASTER_LOG_FILE='mysql-bin-log.000012', MASTER_LOG_POS=107;

start slave;

Mysql: replication check by comparing count rows

//get all tables from db and add them in dbase.tables file
SELECT TABLE_NAME,SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbasename' GROUP BY TABLE_NAME;

//add tables_check.sh script on each dbase server
#!/bin/bash

for x in `cat dbase.tables`;
do mysql -u root -psecret --skip-column-names -e "select count(*) from $x" dbasename;
done

//add replication_count_check.sh script on the 'master' server
#!/bin/bash

 ssh root@192.168.1.1 'sh tables_check.sh' > tables_check1.log
 ssh root@192.168.1.2 'sh tables_check.sh' > tables_check2.log

//ssh -o 'StrictHostKeyChecking no' can be also used if not added in know_hosts

if (cmp tables_check1.log tables_check2.log);
then echo "OK" ;
else  echo "Rows are not matching" | mail -s 'Dbase: Replication problem' user@email.com ; fi



**don't forget to exchange ssh keys

SEC: Haproxy monitoring on log files

//install SEC
yum install sec


//edit sec conf file and add the input file
vim /etc/sysconfig/sec


//add haproxy.sec file in /etc/sec/
# SEC - Simple Event Correlator Configuration File
#
# Author: Steve Moitozo <god <> zilla <> us>
# Created: 20070304
# Description:
# SEC is being used for real-time notification of
# web server failure events. When HAProxy recognizes
# a server failure it writes information about the
# failure to its log file. SEC monitors the log file
# looking for certain events and sends notification
# e-mails to administrators
#
# match on a line like this:
# Server http_proxy/www0 is DOWN. 0 active and 2 backup servers left. Running on backup. 0 sessions active, 0 requeued, 0 remaining in queue.
# Take the name of the instance (http_proxy/www0) and put it in $1
# Take the server status (DOWN or UP) and put it in $2
# Take the rest of the line and put it in $3
#
type=Single
ptype=RegExp
pattern=Server\s+(\S+)\s+\S+\s+(\S+)(.*)
desc=$0
action=pipe '%t server $1 went $2 $3' /bin/mail -s 'HAProxy: $1 went $2' user@mail.com

//start sec
service sec start

//additionally sec can be started with

/usr/bin/sec --conf=/etc/sec/sshd.rules --input=/var/log/secure

Haproxy: Remote logging with rsyslog on Centos

Enable receiving logs port 514/UDP and log them in specified haproxy log files
//edit rsyslog.conf
vim /etc/rsyslog.conf

//enable udp

$ModLoad imudp
$UDPServerRun 514

//add the log files

local0.*       -/var/log/haproxy0.log

local1.*       -/var/log/haproxy1.log

Mysql: Setting ibdata log files

//edit my.cnf and add innodb parameters
vi /etc/my.cnf
innodb_buffer_pool_size = 2G
innodb_file_per_table
innodb_log_buffer_size=8M
innodb_log_file_size=512M

//dump all dbases
mysqldump -u root --all-databases > /mnt/share/dump/alldb.sql

//backup mysql content
cd /var/lib/mysql

cp -ax * /mnt/share

service mysqld stop

//remove /var/lib/mysql except mysql db
rm -rf *
cp -a /mnt/share/mysql.sock .
cp -aR /mnt/share/mysql/ .
service mysqld start

//import all dbases and restart 
mysql -u root < /mnt/share/dumb/alldb.sql
service mysqld restart

http://www.epigroove.com/blog/optimize-mysql-the-thread-cache
http://mysqldatabaseadministration.blogspot.com/2005/11/mysql-5-optimization-and-tuning-guide.html
http://serverfault.com/questions/42789/how-to-increase-memory-usage-in-mysql-server-to-improve-speed