Setting up a MySQL replica

Pre-requisite

• Install current xtrabackup, i.e. xtrabackup_1.4-193.lucid.25_amd64.deb on all servers involved (To Do: add to wfm apt repo, install via puppet on all dbs)

New Server Setup

• Ensure hardware raid is properly configured (raid-10, 256k strip, writeback cache, no read ahead)
• Setup /a xfs volume according to Domas' xfsfix (i.e. mkfs.xfs -f -d sunit=512,swidth=4096 -L data /dev/sda3 with uuid preservation)
  • Can be run from root@fenari via: (To Do: put this somewhere standard, /home/w/bin?)
    • cat /home/midom/xfsfix | ssh NEWDB "python" - to see what will run. if ok:
    • cat /home/midom/xfsfix | ssh NEWDB "python | bash"
• Add to /etc/fstab as /a via device uuid and include nobarrier,noatime options, mount /a
• Install current prod mysql version
  • Currently copying /usr/local/mysql-at-facebook from the source db and symlinking to /usr/local/mysql (To Do: make dpkg of our facebook patched mysql 5.1)
  • useradd -d /home/mysql mysql
• mkdir /a/sqldata /a/tmp ; chown -R mysql: /a
• [ -s /etc/mysql/my.cnf ] && > /etc/mysql/my.cnf
• Install /etc/my.cnf by copying from another host in the same mysql cluster
  • Set server-id to the first, third, and fourth octets of the server ip address (i.e. 10.0.6.42 = 10642)
  • Alter buffer pool size and other tunables if hardware sufficiently differs from the source server

On New/Intended Slave

• cd /a/sqldata
• nc -l 9210 | tar xvif -

On Existing DB

• innobackupex-1.5.1 --stream=tar /a/sqldata --user=root --password=PASSWORD --slave-info | nc NEW-SERVER 9210
• This results in a brief write lock towards the end of the process while the table definition files (but not data) are copied. For enwiki, this is around 200Mb of data. For this reason, this shouldn't be run on a write master if slaves are available.

On New Slave, after data copy

Do this before starting mysql!

• innobackupex-1.5.1 --apply-log --use-memory=22G --ibbackup=xtrabackup_51 /a/sqldata
• This is required to make the copied data consistent to a specific binlog position.
• Start mysql, and CHANGE MASTER based on the information in /a/sqldata/xtrabackup_slave_info on the new host. Important: this should point to the correct binlog position on the actual write master on the cluster at which to start replication. innobackupex will also display the log position on the slave the copy was made from, but we don't want to chain replication.