MySQL Replication

dev.mysql.com/doc/refman/5.0/en/replica...
dev.mysql.com/doc/refman/5.0/en/innodb-...

parent: server where replication master is.
child: server where replication slave is.
nest: the name of the database being replicated.

setting up “parent”

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'<ip of replication server>' IDENTIFIED by '<pass>';
mysql> FLUSH TABLES WITH READ LOCK;
mysql> ^Z
# /usr/bin/mysqldump --defaults-extra-file=/etc/mysql/debian.cnf  --master-data --complete-insert --add-drop-table --quick --quote-names --databases nest > /var/backups/mysql/replication_dump
# fg
mysql> UNLOCK TABLES;

codetitle. /etc/mysql/my.cnf

# We want mysql to bind to all addresses so we can do fail-over
#bind-address           = 127.0.0.1
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
sync_binlog		= 1

setting up "child’

codetitle. /etc/mysql/my.cnf

# We want mysql to bind to all addresses so we can do fail-over
#bind-address           = 127.0.0.1
relay-log             = child-relay-bin
log-warnings          = 1
log-slave-updates     = 1
low-priority-updates  = 1
delay-key-write       = ALL
server-id             = 2
# this makes things myisam
skip-innodb
# this makes the slave only replicate the 'nest' db
# add a line for each database you want to replicate
replicate-do-db       = nest

start up mysql:

/etc/init.d/mysql start

load the dump:

mysql -u root -p < nest.dump

Set the child variables needed for replication:

(the LOG_FILE and LOG_POS values are at the top of nest.dump if you used —master-data to mysqldump on parent.)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='parent-vpn.riseup.net',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='<pass>',
    -> MASTER_LOG_FILE='mysql-bin.000010',
    -> MASTER_LOG_POS=11977;
mysql> START SLAVE;



mysql> SHOW PROCESSLIST\G
Id User Host db Command Time State Info
8 root localhost NULL Query 0 NULL show processlist
9 system user _ NULL Connect 4 Waiting for master to send event NULL
10 system user _ NULL Connect 3 Has read all relay log; waiting for the slave I/O thread to update it NULL

This means that everything is up-to-date and it is just waiting for updates to come in from the master, hot diggity!

On parent, you can also do this to see:

mysql> SHOW PROCESSLIST\G
Id User Host db Command Time State Info
11 root localhost NULL Query 0 NULL show processlist
12 repl 10.8.0.3:48668 NULL Binlog Dump 929 Has sent all binlog to slave; waiting for binlog to be updated NULL

Connected and all binlogs have been sent, just waiting for updates before sending more, hot diggity dog!

ucarp

Create the ucarp up and down scripts.

parent:

# mkdir /etc/ucarp

codetitle. /etc/ucarp/mysql-up.sh

#!/bin/sh
exec 2> /dev/null
/sbin/ip addr add 10.0.1.17/24 dev "$1"

codetitle. /etc/ucarp/mysql-down.sh

#!/bin/sh
exec 2> /dev/null
/sbin/ip addr del 10.0.1.17/24 dev "$1"


chmod +x /etc/ucarp/*

Start on childe:

ucarp --interface=eth5 -v 43 -p dur -a 10.0.1.17 -s 10.0.1.3 --upscript=/etc/ucarp/mysql-up.sh --downscript=/etc/ucarp/mysql-down.sh

response:

[INFO] Local advertised ethernet address is [00:07:e9:18:3d:cb]
[WARNING] Switching to state: BACKUP
[WARNING] Spawning [/etc/ucarp/mysql-down.sh eth5]
Oct 21 18:06:03 child ucarp[15246]: [INFO] Local advertised ethernet address is [00:07:e9:18:3d:cb]
Oct 21 18:06:03 child ucarp[15246]: [WARNING] Switching to state: BACKUP
Oct 21 18:06:03 child ucarp[15246]: [WARNING] Spawning [/etc/ucarp/mysql-down.sh eth5]
[WARNING] Switching to state: MASTER
[WARNING] Spawning [/etc/ucarp/mysql-up.sh eth5]
Oct 21 18:06:07 child ucarp[15246]: [WARNING] Switching to state: MASTER
Oct 21 18:06:07 child ucarp[15246]: [WARNING] Spawning [/etc/ucarp/mysql-up.sh eth5]

On parent create the same scripts in /etc/ucarp and then start it up:

# ucarp --interface=eth2 -v 43 -p dur -a 10.0.1.17 -s 10.0.1.2 --upscript=/etc/ucarp/mysql-up.sh --downscript=/etc/ucarp/mysql-down.sh

response:

[INFO] Local advertised ethernet address is [00:02:b3:5b:e1:f9]
[WARNING] Switching to state: BACKUP
[WARNING] Spawning [/etc/ucarp/mysql-down.sh eth2]