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

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