4.10 Replication in MySQL

This section describes the various replication features in MySQL. It serves as a reference to the options available with replication. You will be introduced to replication and learn how to implement it. Toward the end, there are some frequently asked questions and descriptions of problems and how to solve them.

We suggest that you visit our website at http://www.mysql.com/ often and read updates to this section. Replication is constantly being improved, and we update the manual frequently with the most current information.

Subsections

• 4.10.1 Introduction
• 4.10.2 Replication Implementation Overview
• 4.10.3 Replication Implementation Details
• 4.10.4 How To Set Up Replication
• 4.10.5 Replication Features and Known Problems
• 4.10.6 Replication Options in `my.cnf'
• 4.10.7 SQL Commands Related to Replication
• 4.10.7.1 START SLAVE (slave)
• 4.10.7.2 STOP SLAVE (slave)
• 4.10.7.3 SET SQL_LOG_BIN=0|1 (master)
• 4.10.7.4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n (slave)
• 4.10.7.5 RESET MASTER (master)
• 4.10.7.6 RESET SLAVE (slave)
• 4.10.7.7 LOAD TABLE tblname FROM MASTER (slave)
• 4.10.7.8 LOAD DATA FROM MASTER (slave)
• 4.10.7.9 CHANGE MASTER TO master_def_list (slave)
• 4.10.7.10 MASTER_POS_WAIT() (slave)
• 4.10.7.11 SHOW MASTER STATUS (master)
• 4.10.7.12 SHOW SLAVE HOSTS (master)
• 4.10.7.13 SHOW SLAVE STATUS (slave)
• 4.10.7.14 SHOW MASTER LOGS (master)
• 4.10.7.15 SHOW BINLOG EVENTS (master)
• 4.10.7.16 PURGE MASTER LOGS (master)
• 4.10.8 Replication FAQ
• 4.10.9 Troubleshooting Replication

User Comments

Handy mysql log rotation script. For those not
using any chroot environements, comment out both
lines in #chroot section. Set MYSQL_HOME QUERYLOG
SLOWLOG ERRLOG appropiately.


#!/bin/sh
###############################################
# MySQL log rotation
# mjaw@ipartners.pl
###############################################

# chroot
VIRTUAL="/virtual"
VIRTUAL_HOME="${VIRTUAL}/mysql"

# mysql
MYSQL_HOME="${VIRTUAL_HOME}/usr/local/mysql"
DATADIR="${MYSQL_HOME}/var"
LOGDIR="${MYSQL_HOME}/log"
QUERYLOG="${LOGDIR}/querylog"
SLOWLOG="${LOGDIR}/slowlog"
ERRLOG="${LOGDIR}/errlog"

# most universal method for calculating
yesterday's date in YYYYMMDD format
DATE=`/usr/bin/perl -e
"@a=localtime(time-86400);printf('%02d%02d%02d',@a[5]+1900,@a[4]+1,@a[3])"`

PID_FILE=$DATADIR/`/bin/hostname`.pid

if ! [ -s ${PID_FILE} ]; then
echo " Error: pid file not found."
exit 1;
fi

PID=`cat $PID_FILE`

echo -n "Rotating logs: "

if [ -e ${QUERYLOG} ]; then
echo -n "querylog "
/bin/mv ${QUERYLOG} ${QUERYLOG}.${DATE}
fi
if [ -e ${SLOWLOG} ]; then
echo -n "slowlog "
/bin/mv ${SLOWLOG} ${SLOWLOG}.${DATE}
fi
if [ -e ${ERRLOG} ]; then
echo -n "errlog "
/bin/mv ${ERRLOG} ${ERRLOG}.${DATE}
fi

/bin/kill -1 $PID
</PRE>
Run from cron at midnight.

There is additional information on replication
here:
http://www.phpbuilder.com/columns/tanoviceanu20000
912.php3

Overall the implementation was very easy and
rather smooth.

There is a simple error in the perl date example. The year is not using 4 characters. The line should say
"@a=localtime(time-86400);printf('%04d%02d%02d',@a[5]+1900,@a[4]+1,@a[3])"`

there is no need in perl at all. just use 'date' command (man date). example:

DATE=`/bin/date -v -1d '+%Y%m%d'`

this is the very long and ugly way:
DATE=`/usr/bin/perl -e @a=localtime(time-86400);printf('%02d%02d%02d',@a[5]+1900,@a[4]+1,@a[3])"`

this is a better form:
DATE=`date --date='1 days ago' +%Y%m%d`

Does replication support parallel processing?

Add your own comment.