MySQL Replication


 * 1) old howto with some examples of using screen to manage the multipl sessions for
 * 2) http://www.howtoforge.com/mysql_master_master_replication

Master_Log_File: mysql-bin.000059 The name of the master binary log file from which the I/O thread is currently reading.

Read_Master_Log_Pos: 38192 The position in the current master binary log file up to which the I/O thread has read.

This contains the name of the bin-log on the master corresponding to which the local relay SQL thread has reached; Relay_Master_Log_File: mysql-bin.000015

Exec_Master_Log_Pos: 18973761 The position in the current master binary log file to which the SQL thread has read and executed,

innodb_flush_log_at_trx_commit=0
 * 1) set on slave to improve replication performance

SHOW MASTER STATUS; 
 * 1) shows log file and postition that master is writing to

SHOW BINARY LOGS; PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
 * 1) delete old files

SET SQL_LOG_BIN=0;
 * 1) controls loggging to binary log

SET GLOBAL EXPIRE_LOGS_DAYS
 * 1) SET GLOBAL EXPIRE_LOGS_DAYS


 * 1) Deletes all binary logs in the index file! Resets binary log index
 * 2) RESET MASTER;

Used on slave Requires SUPER privileges Configures the slave server connection to the maste Slave should not be running The user need REPLICATION SLAVE privileges on masteThe user need REPLICATION SLAVE privileges on maste

START SLAVE and STOP SLAVE

relay-log.info , master.info Used on slave Removes all info on replication position Relay logs are unconditionally removed! Deletes ... even if they have not been fully applied... even if they have not been fully applied RESET SLAVE

FLUSH TABLES WITH READ LOCK;

max_allowed_packet=20M

SET sql_log_bin = 0; SET sql_log_bin = 1;

iptables -I INPUT 1 -p tcp --dport mysql -j REJECT


 * 1) skipping replication errors of some type
 * 2) --slave-skip-errors=xxx

STOP SLAVE; RESET SLAVE; START SLAVE;

stop slave; set global sql_slave_skip_counter=1; start slave;
 * 1) skip the current error;

stop slave; reset slave; reset master; iptables -I INPUT 1 -p tcp --dport mysql -j REJECT

mysqlshow --status myInnodbDatabase myTable | awk '{print $35}'

mysqlbinlog --offset=128694 mysql-bin.000013

drop database webistrano_production ;

http://www.ducea.com/2008/02/13/mysql-skip-duplicate-replication-errors/ http://blog.vuksan.com/2010/08/19/skippingmysql-replication-errors/
 * 1) more on skipping slave errrors

http://www.mysqlperformanceblog.com/2007/01/19/tmp_table_size-and-max_heap_table_size/
 * 1) max heap size notes


 * 1) save lag dely

Snapshot backups

 * 1) mysql using LVM for mysql backups

http://www.lenzg.net/mylvmbackup/

Replication management Tools

 * 1) patches for mmm problems
 * 2) https://code.launchpad.net/~gryp/mysql-mmm/mysql-mmm-MoveSlavesMoreConsistent


 * 1) problem with mmm script for monitoring;
 * 2) http://code.openark.org/blog/mysql/problems-with-mmm-for-mysql

Recovering from Replication sync errors
http://www.xaprb.com/blog/2007/11/08/how-mysql-replication-got-out-of-sync/
 * 1) getting out of sync problms;

Monitoring Replication

 * 1) nagios scri[pts for monitring replicatons
 * 2) http://openquery.com/resources/tools/nagios_scripts


 * 1) fairly nice AJAX mysql editor

http://www.xaprb.com/blog/2007/01/25/how-to-calculate-table-checksums-in-mysql/
 * 1) checking replications