KfWiki: MysqlReplication

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

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

# 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-do-db=database2
replicate-do-db=database3

# 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
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> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> 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

# maintained by kfong@datapipe.com

MASTER_HOST=""

MASTER_LOG=""

SLAVE_LOG="" SHARED_PWD="j4s0n1"

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