Revision [3672]

Last edited on 2010-03-16 00:55:38 by KenFong
Additions:
===Seeing difference with compression===
tcpdump -i eth0 -s 0 -l -w - dst port 3306

Revision [3671]

Edited on 2010-03-16 00:53:51 by KenFong
Additions:
slave_compressed_protocol=1

Revision [3659]

Edited on 2010-03-08 21:12:25 by WikiAdmin
Additions:
replicate-wild-do-table=database1.%
Deletions:
replicate-do-db=database2
replicate-do-db=database3

Revision [3445]

Edited on 2009-07-23 22:17:34 by WikiAdmin
Additions:
# selectively replicate
# binlog-do-db=db_name
7.Restore snapsnot backup taken in step 1 to slave database. One can also use mysqldump-restore to achieve the same. dump with the --with-master-data option. then the master log position will be recorded in first 30 lines or so in the dump file.
Deletions:
7.Restore snapsnot backup taken in step 1 to slave database

Revision [2234]

Edited on 2009-02-26 02:15:23 by WikiAdmin
Additions:
# rename relay-log.info which contains info about the problematic relay log

Revision [2208]

Edited on 2009-02-16 03:04:23 by WikiAdmin
Additions:
===Fix broken relay logs===
You may instruct mysql to purge current relay logs and restart replication at a certain point. See the following example:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
...
Master_Log_File: db01-bin.000009
Read_Master_Log_Pos: 127484667
Relay_Log_File: ws01-relay.000032
Relay_Log_Pos: 115781533
Relay_Master_Log_File: db01-bin.000009 <-- set master_log_file to this
Slave_IO_Running: No
Slave_SQL_Running: No
...
Exec_Master_Log_Pos: 115781384 <-- set master_log_pos to this
...
1 row in set (0.92 sec)
mysql> stop slave
mysql> change master to master_log_file='db01-bin.000009', master_log_pos=115781384;
mysql> start slave

Revision [2085]

Edited on 2008-11-07 02:28:18 by WikiAdmin
Additions:
log-bin=hostname-bin
relay-log=hostname-relay
Deletions:
log-bin=mysql-bin

Revision [2066]

Edited on 2008-11-03 00:13:58 by WikiAdmin
Additions:
==Missing relay logs==
What if your relay logs are missing? As long as the binlogs are still here, one can do the followings and mysql will fetch fresh copies of relay logs automatically:
service mysql stop
rm -f $MYSQL_DATADIR/relay-log.info
rm -f $MYSQL_RELAYLOG/hostname-relay-bin.*
service mysql start

Revision [1917]

Edited on 2008-09-01 22:04:44 by WikiAdmin
Additions:
log_slave_updates=1
read_only=1

Revision [1781]

Edited on 2008-06-30 08:48:38 by WikiAdmin
Additions:
Some poorly designed software do uses custom functions for primary key and they may cause duplicate entry error when that function generates repeated string as the key. The following tells MySQL to ignore duplicate entry error and move on.
%%(text;my.cnf)
slave-skip-errors = 1062

Revision [1746]

Edited on 2008-06-02 21:34:55 by WikiAdmin
Additions:
MYSQL>grant replication [slave] on *.* to repl@'%' identified by 'blah';
Deletions:
MYSQL>grant replication on *.* to repl@'%' identified by 'blah';

Revision [1621]

Edited on 2008-04-08 00:56:02 by WikiAdmin
Additions:
==Fix broken replication==
If replication is stalled because of a query does not run on slave and you are sure that query is of no significance, do these:
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;

Revision [1577]

Edited on 2008-03-31 01:17:58 by WikiAdmin
Additions:
Fix broken replication: http://homepage.mac.com/kelleherk/iblog/C711669388/E226281480/index.html
# master logs are applied to slave and can be purged
mysql -p$SHARED_PWD -h$MASTER_HOST -se "purge master logs to '$MASTER_LOG'"
Deletions:
# master logs are applied to slave and can be purged
mysql -p$SHARED_PWD -h$MASTER_HOST -se "purge master logs to '$MASTER_LOG'"

Revision [1490]

Edited on 2008-03-13 02:17:36 by WikiAdmin
Additions:
Do it with mysqldump: http://www.onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html

Revision [1426]

Edited on 2008-02-29 20:05:08 by WikiAdmin
Additions:
# In the reversed case, where you want to exclude certain databases
# from being replicated:
replicate-ignore-db=database_tmp
replicate-ignore-db=user_tmp

Revision [1413]

Edited on 2008-02-20 20:38:13 by WikiAdmin
Additions:
exit 0
Deletions:
exit0

Revision [1412]

Edited on 2008-02-20 20:37:25 by WikiAdmin
Additions:
#!/bin/bash
# maintained by kfong@datapipe.com
SLAVE_LOG="" SHARED_PWD="j4s0n1"
MASTER_LOG=`mysql -p$SHARED_PWD -h$MASTER_HOST -se "show master status\G" | grep File | awk '{print $2}'`
if [ $MASTER_LOG == $SLAVE_LOG ]; then
mysql -p$SHARED_PWD -h$MASTER_HOST -se "purge master logs to '$MASTER_LOG'"
Deletions:
#/bin/bash
SLAVE_LOG=""
SHARED_PWD="xxx"
MASTER_LOG=`mysql -p$SHARED_PWD -h$MASTER_HOST -se "show master status\G" | grep File | awk '{print $2}'`
if [ $MASTER_LOG == $SLAVE_LOG ]; then
mysql -pw8TnG4Tn1t -h$MASTER_HOST -se "purge master logs to '$MASTER_LOG'"

Revision [1393]

Edited on 2008-02-11 19:57:41 by WikiAdmin
Additions:
#/bin/bash
MASTER_HOST=""
MASTER_LOG=""
SLAVE_LOG=""
SHARED_PWD="xxx"
MASTER_HOST=`mysql -p$SHARED_PWD -se "show slave status\G" | grep Master_Host | awk '{print $2}'`
MASTER_LOG=`mysql -p$SHARED_PWD -h$MASTER_HOST -se "show master status\G" | grep File | awk '{print $2}'`
SLAVE_LOG=`mysql -p$SHARED_PWD -se "show slave status\G" | grep Master_Log_File | awk '{print $2}' | head -1`
echo MASTER: $MASTER_HOST
echo MASTER_LOG: $MASTER_LOG
echo SLAVE_LOG: $SLAVE_LOG
if [ $MASTER_LOG == $SLAVE_LOG ]; then
# master logs are applied to slave and can be purged
mysql -pw8TnG4Tn1t -h$MASTER_HOST -se "purge master logs to '$MASTER_LOG'"
fi
exit0
Deletions:
#!/bin/sh
# Get last applied log from slave
SLAVELOG=`mysql -hslave.domain.com -uroot -pXXX -Nse "show slave status\G" | grep "Master_Log_File" | head -1 | awk '{print $2}'`
echo "Purging master logs up to $SLAVELOG"
mysql -uroot -pXXX -Nse "purge master logs to '$SLAVELOG'"
exit 0

Revision [1243]

Edited on 2007-12-30 18:06:51 by WikiAdmin
Additions:
replicate-do-db=database1
replicate-do-db=database2
replicate-do-db=database3
Deletions:
replicate-do-db=customers
replicate-do-db=new_kayako
replicate-do-db=wpdfd

Revision [1242]

The oldest known version of this page was created on 2007-12-30 18:06:20 by WikiAdmin
Valid XHTML 1.0 Transitional :: Valid CSS :: Powered by WikkaWiki