Setup master/slave replication between two MySQL servers

This howto is tested on:

• Debian 7.0 Wheezy

Prerequisite

This howto needs:

• two MySQL servers, installed as described by Install and setup MySQL on Debian.

Master server configuration

Parameters

Provide the MySQL username dedicated to replication:

REPLICATION_USER="replication"

Provide the name of the network interface used to communicate with the slave server:

NET_DEV="eth0"

Environment preparation

Install the needed software:

command apt-get install apg

Configuration

Enable the point-in-time restauration and the slow query log (and other enhancements):

command wget 'https://raw.github.com/biapy/howto.biapy.com/master/mysql/base-optimisations.cnf' \
    --quiet --no-check-certificate --output-document='/etc/mysql/conf.d/base-optimisations.cnf'

Allow master server access from the network:

command sed -i -e 's/^bind-address.*/#\0/' '/etc/mysql/my.cnf'

Assign the ID n°1 to the master server:

command sed -i -e 's/^#*server-id.*/server-id = 1/' '/etc/mysql/my.cnf'

Reload the configuration:

command service mysql restart

Compute a random password for the replication MySQL user:

REPLICATION_PASSWORD="$(command apg -q -a 0 -n 1 -M NCL)"

Create the MySQL replication user:

command echo "GRANT REPLICATION SLAVE ON *.* TO '${REPLICATION_USER}'@'%' IDENTIFIED BY '${REPLICATION_PASSWORD}';" \
    | command mysql --user='root' --password

Detect master server IP address:

REPLICATION_IP="$(command ifconfig "${NET_DEV}" \
    | command grep 'inet ' \
    | command sed -e 's/^.*inet [^:]*:\([^ ]*\) .*$/\1/')"

Detect debian-sys-maint MySQL user password:

MAINTENANCE_PASSWORD="$(command grep --max-count=1 'password' '/etc/mysql/debian.cnf' \
    | command sed -e 's/^.* = //g')"

Replication initialization

Warning: for this section, the server does not access write requests.

Freeze the tables contents by blocking write operations:

command echo "FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;" \
    | command mysql --user='root' --password

Detect the binary log status:

MASTER_STATUS="$(command echo "SHOW MASTER STATUS;" \
    | command mysql --batch --skip-column-names \
        --skip-line-numbers --skip-pager \
        --user='root' --password)"

Compute the status informations:

BINLOG_FILE="$(command echo ${MASTER_STATUS} | command cut --delimiter=' ' --fields=1)"
BINLOG_POSITION="$(command echo ${MASTER_STATUS} | command cut --delimiter=' ' --fields=2)"

Export master server data:

command mysqldump --all-databases --events --user=root --password > '/tmp/master.sql'

Restore normal server behaviour by allowing write operations:

command echo "UNLOCK TABLES;" \
    | command mysql --user='root' --password

Slave server parameters

Use the parameters provided by the following command line to configure the slave server:

echo "REPLICATION_IP='${REPLICATION_IP}'
REPLICATION_USER='${REPLICATION_USER}'
REPLICATION_PASSWORD='${REPLICATION_PASSWORD}'
MAINTENANCE_PASSWORD='${MAINTENANCE_PASSWORD}'
BINLOG_FILE='${BINLOG_FILE}'
BINLOG_POSITION='${BINLOG_POSITION}'
"

Slave server configuration

Parameters

Provide the master server IP:

REPLICATION_IP='xx.xx.xx.xx'

Provide the username dedicated to replication on the master server:

REPLICATION_USER='replication'

Provide the replication user password:

REPLICATION_PASSWORD='some-password'

Provide the maintenance user password:

MAINTENANCE_PASSWORD='some-password'

Provide the master server binary log current filename:

BINLOG_FILE='mysql-bin.000007'

Provide the master server binary log current position:

BINLOG_POSITION='107'

Importing master server data

Copy master server SQL export on the slave server:

command scp -C ${REPLICATION_IP}:'/tmp/master.sql' '/tmp/master.sql'

Replace the slave server data by the master server one:

command mysql --user='root' --password < '/tmp/master.sql'

Configuration

Assign ID n°2 to slave server:

command sed -i -e 's/^#*server-id.*/server-id = 2/' '/etc/mysql/my.cnf'

Update the maintenance user password:

command sed -i \
    -e "s/password = .*\$/password = ${MAINTENANCE_PASSWORD}/g" \
  '/etc/mysql/debian.cnf'

Reload the configuration:

command service mysql restart

Replication initialization

Stop the replication (if needed):

command echo 'STOP SLAVE;' \
    | command mysql --user=root --password

Configuration the master server status:

command echo "CHANGE MASTER TO
MASTER_HOST='${REPLICATION_IP}',
MASTER_USER='${REPLICATION_USER}',
MASTER_PASSWORD='${REPLICATION_PASSWORD}',
MASTER_PORT=3306,
MASTER_LOG_FILE='${BINLOG_FILE}',
MASTER_LOG_POS=${BINLOG_POSITION};" \
    | command mysql --user=root --password

Start the replication:

command echo 'START SLAVE;' \
    | command mysql --user=root --password

Thanks

• Thanks to William's blog (fr) for Réplication MySQL maître-esclave (fr).