Creating new slave for large MySQL database

gistfile1.md

Creating content-dashboard-db-master new MySQL replica 2013-05-16

• Created 6 512GB 1000 IOPS volumes in us-east-1d zone (reused 4 volumes from previous replica and added 2 new). All volumes are labeled content-dashboard-mysql-fast{0,1,2,3,4,5}.

• Connected the volumes to content-dashboard-db-master machine on /dev/sd{f,g,h,i,j,k} (0 -> f, 1 -> g, 2 -> h, 3 -> i, 4 -> j, 5 -> k).

• Created /dev/md1 RAID0 volume from 6 drives:

    mdadm --create -l0 -n6 /dev/md1 /dev/sdf /dev/sdg /dev/sdh /dev/sdi /dev/sdj /dev/sdk
  

• Create XFS filesystem on /dev/md1:

    mkfs.xfs /dev/md1 -f
  

• Create mount point and mount the /dev/md1 device:

    mkdir /vol-replica && mount -t xfs -o rw,nobarrier,noatime,nodiratime /dev/md1 /vol-replica
  

• Added (temporarily) mdadm configuration to /etc/mdadm.conf. This configuration will be moved to the replica machine later:

    DEVICE /dev/sdf /dev/sdg /dev/sdh /dev/sdi /dev/sdj /dev/sdk
  

• Ran mdadm to find out the new RAID0 UUID:

    mdadm --examine --scan
    # Output
    ARRAY /dev/md1 level=raid0 num-devices=6 UUID=8608d9b1:d79be0a5:02e0e433:92cab958
    ARRAY /dev/md0 level=raid0 num-devices=2 UUID=5e04d357:35fa6f13:8a934771:b68a2e06
  

• Remove the DEVICE line we added in previous step from /etc/mdadm.conf. We’ll need to add this line and the “ARRAY /dev/md1” line to /etc/mdadm.conf on the new slave machine.

• Start initial rsync (while master is running) to minimize downtime: (in screen session)

    rsync --verbose --archive --recursive --exclude=“mysql-bin.*” /vol/mysql /vol-replica
  

Once the initial rsync is finished, we’ll need to do following:

• Open mysql session and run (this session should remain open):

    FLUSH TABLES WITH READ LOCK;
  

• Open another mysql session and run:

    SHOW MASTER STATUS;
  

• Write down mysql-bin file name and position

• Shutdown master mysql:

    mysqladmin -uXXXXX -pXXXXX shutdown
  

• Rsync again:

    rsync --verbose --archive --recursive --exclude=“mysql-bin.*” /vol/mysql /vol-replica
  

• Start master mysql. Downtime is over.

• Unmount /vol-replica volume:

    umount /vol-replica
  

• Disassemble raid:

    mdadm --stop /dev/md1
  

• Disconnect devices /dev/sd{f,g,h,i,j,k} from master.

• Create new machine in us-east-1d zone (used m3.2xlarge EBS-optimized instance with monitoring enabled).

• Connect /dev/sd{f,g,h,i,j,k} devices (mapping should be the same as in previous step).

• Make sure mdadm, xfs are installed

• Add DEVICE and ARRAY lines to /etc/mdadm.conf

• Check that mdadm can see the array

    mdadm --examine --scan
  

• Assemble the array

    mdadm --assemble /dev/md1
  

• Create mount point and mount the array.

    mkdir /vol && mount -t xfs -o rw,nobarrier,noatime,nodiratime /dev/md1 /vol
  

• Install percona server on slave:

    rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
    yum remove MySQL-client-community
    yum install Percona-Server-server-55.x86_64
  

• Copy over my.cnf from $master$:/etc/my.cnf to $slave$:/etc/

• Adjust /etc/my.cnf on slave (change server-id, disable binlog, adjust memory):

    .......
    server-id = 2
    relay-log=relay-bin
    report-host=$slave_host_name$
    ........
  

• Temporary disable slave (add ‘skip-slave-start’ to /etc/my.cnf)

• Disable automatic startup of mysql

    chkconfig --del mysql
  

• Add following to assemble raid, mount and start mysql during boot time to /etc/rc.local:

    mdadm --assemble /dev/md1
    sleep 1
    mount -t xfs -o rw,nobarrier,noatime,nodiratime /dev/md1 /vol
  
    /etc/init.d/mysql start
  

• Delete /var/lib/mysql and add link to /vol/mysql:

    rm -rf /var/lib/mysql
    ln -s /vol/mysql /var/lib/mysql
    chown -R mysql:mysql /vol/mysql
  

• Reboot to make sure the raid is assembled properly, mounted and mysql started. Slave will not start because of ‘skip-slave-start’ in the my.cnf.

• Configure master on slave ($log_file_name$ and $log_file_pos$ are from the SHOW MASTER STATUS recorded earlier):

    CHANGE MASTER TO master_host=‘$master_hostname’,
    master_user=‘$replication_user$’,
    master_password=‘$replication_user_password$’,
    master_log_file=‘$log_file_name$’,
    master_log_pos=$log_file_pos$;
  

• Start the slave:

    START SLAVE;
  

• Monitor replication status:

    SHOW SLAVE STATUS\G;
  

• Remove the 'skip-slave-start' line from /etc/my.cnf