HomePage » Database » MySQL » MysqlReplication


Read: http://bobcares.com/article58.html http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
Do it with mysqldump: http://www.onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html
Fix broken replication: http://homepage.mac.com/kelleherk/iblog/C711669388/E226281480/index.html

Checking binlog status
On master:
show master status
show slave hosts
show logs
show binlog events
# -- Make sure logs have been read by all slaves before deleting them on the master --
# -- Check with show slave status on the slaves --
purge master logs to 'log_name' # e.g purge master logs to 'mysql-bin.010';
purge master logs before 'date' # e.g. purge master logs before '2007-01-01- 00:00:00';
# MYSQL> set global expire_logs_days=5 
# to control when to remove stale binlog. 0 means no binlog will be removed.


On slave:
show slave status\G
slave start;
slave stop;


Steps on setting up replication with existing data
1.Take a snapshot backup of the database server by locking all tables and backup the data directory
MYSQL>grant replication [slave] on *.* to repl@'%' identified by 'blah';
MYSQL>flush tables with read lock;
MYSQL>show master status;
# copy the output of master log name and position
# copy /var/lib/mysql to slave


2.Unlock all tables and shutdown database
MYSQL>unlock tables;
$mysqladmin shutdown


3.Configure the database server to write binary logs and configure its master id
#my.cnf on master
[mysqld]
log-bin=hostname-bin
server-id=1
# selectively replicate
# binlog-do-db=db_name


4.Start master database

5.Stop slave database if running

6.Configure a slave id on slave database
#my.cnf on slave
[mysqld]
server-id=2
master-host=1.2.3.4
master-user=repl
master-password=blah
relay-log=hostname-relay
log-warnings
log_slave_updates=1
read_only=1
slave_compressed_protocol=1

# If you need to replicate a subset of databases only, use the following directives:
# see http://dev.mysql.com/doc/refman/5.0/en/replication-options.html
replicate-do-db=database1
replicate-wild-do-table=database1.%

# In the reversed case, where you want to exclude certain databases 
# from being replicated:
replicate-ignore-db=database_tmp
replicate-ignore-db=user_tmp


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.

8.Start slave database and configure its master
$mysqld_safe --skip-slave-start &
MYSQL>
CHANGE MASTER TO MASTER_HOST='11.2.3.4', MASTER_USER='repl', MASTER_PASSWORD='blah', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=123;


9.Start the slave process and the slave database will catch up with the master automatically. Check log files under /var/lib/mysql for errors
MYSQL> START SLAVE;


Promoting slave to master
To promote a slave database to become a master database, issue the following commands:

On master, if still available
FLUSH LOGS;


On slave
STOP SLAVE;
RESET MASTER;


Forcing a slave to catch up with master
Use the following procedure:

1. On the master, execute these statements:
	  mysql> FLUSH TABLES WITH READ LOCK;
	  mysql> SHOW MASTER STATUS;

Record the replication coordinates (the log filename and offset) from the output of the SHOW statement.

2. On the slave, issue the following statement, where the arguments to the MASTER_POS_WAIT() function are the replication coordinate values obtained in the previous step:
	  mysql> SELECT MASTER_POS_WAIT('log_name', log_offset);

The SELECT statement blocks until the slave reaches the specified log file and offset. At that point, the slave is in synchrony with the master and the statement returns.

3. On the master, issue the following statement to allow the master to begin processing updates again:
	  mysql> UNLOCK TABLES;


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> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;


Do this in a loop:
while [ `mysql -e "show slave status\G" | grep Slave_SQL_Running | awk '{print $2}'` == "No" ]
do
	mysql -e "stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;"
	sleep 2
done


Or perhaps
stop slave; set global slave_skip_errors=1062; start slave;


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.
my.cnf
slave-skip-errors = 1062


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


Script to automatically purge master logs
#!/bin/bash

MASTER_HOST=""
MASTER_LOG=""
SLAVE_LOG=""
SHARED_PWD="xxxx"

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 -p$SHARED_PWD -h$MASTER_HOST -se "purge master logs to '$MASTER_LOG'"
fi
exit 0



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;
# rename relay-log.info which contains info about the problematic relay log
mysql> start slave


Seeing difference with compression

tcpdump -i eth0 -s 0 -l -w - dst port 3306



Slave status

Show slave status filtering lines I don't want:

mysql> pager grep Slave
PAGER set to 'grep Slave'
mysql> show slave status\G
			 Slave_IO_State: Waiting for master to send event
		   Slave_IO_Running: Yes
		  Slave_SQL_Running: Yes
1 row in set (0.00 sec)

There are no comments on this page. [Add comment]

Valid XHTML 1.0 Transitional :: Valid CSS :: Powered by WikkaWiki