Setting up a MySQL replica

New Server Setup

Hardware Raid

• Ensure hardware raid is properly configured (raid-10, 256k strip, writeback cache, no read ahead)

  • Verify with MegaCli
    • root@db59:~# MegaCli -LDInfo -lALL -aALL | egrep "Level|Current Cache|Stripe"
    • RAID Level: Primary-1, Secondary-0, RAID Level Qualifier-0
    • Stripe Size: 256kB
    • Current Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU
  • If cache policy says 'ReadAdaptive' instead of 'ReadAheadNone', run MegaCli -LDSetProp NORA -Lall -Aall

XFS

• A newly imaged server should have LVS setup correctly, with /dev/mapper/tank-data mounted at /a. However since lucid doesn't allow us to pass mkfs.xfs options at install time, /a needs to be reformatted with correct sunit/swidth settings.

  • umount /a
  • mkfs.xfs -f -d sunit=512,swidth=4096 -L data /dev/mapper/tank-data
  • mount /a

Puppet

• Make sure server is in the correct groups in site.pp

       node /db6[0-9]\.pmtpa\.wmnet/ {
       include db::core,
               mysql::mysqluser,
               mysql::datadirs,
               mysql::conf,
               mysql::packages }

• Make sure the server is defined in the correct shard section in mysql.pp

       #######################################################################
       ### Cluster Definitions - update if changing / building new dbs
       #######################################################################
       if $hostname =~ /^db(12|32|36|38|42|52|53|59|60|1001|1017|1033|1043|1047)$/ {
               $db_cluster = "s1"
       }

• If the server is intended to be an lvm snapshot host, add here in mysql.pp
  • Do this last, after replication is configured and caught up.

       #######################################################################
       ### LVM snapshot hosts - currently only puppet managed in eqiad
       #######################################################################
       if $hostname =~ /^db(24|25|26|32|33|44|46|1005|1007|1018|1020|1022|1033|1035)$/ {
               $snapshot_host = true
       }

• puppetd --test on the new server

Transferring Data

On New/Intended Slave

• ensure puppet runs cleanly
• cd /a/sqldata
• nc -l 9210 | tar xvi

On Existing DB

This process is only intended for databases using only InnoDB tables!

• Select a source slave based on the intended use of the new slave.
  • If you are creating a slave in the active datacenter intended for site production, use either the secondary master in the failover datacenter which can be found via dns (host sX-secondary) or an active production slave. This way replication data will be based on the active write master.
  • If creating a slave in the failover datacenter, or for analytics/non-site use in any datacenter, select a slave in the alternate datacenter. Replication in this case needs to come from the secondary, not the live write master.
• innobackupex-1.5.1 --stream=tar /a/sqldata --user=root --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
• chown -R mysql: /a
• /etc/init.d/mysql start
  • tail the error log in /a/sqldata and check for any errors

Starting replication

• Check /a/sqldata/xtrabackup_slave_info on the new host, it provides a partial CHANGE MASTER statement based on whichever host the target was replicating from. If this is correct, use this, adding MASTER_HOST and the repl account/password portions that are missing.
• If building a prod slave and had to run the hot backup from the master, use the information in /a/sqldata/xtrabackup_binlog_info as slave_info won't exists.
• If you are building a failover slave, or something for analytics and copied from the secondary master, xtrabackup_slave_info will point to the active master; use xtrabackup_binlog_info instead.
  • Important: If a production slave, this should point to the correct binlog position on the actual write master at which to start replication. If creating a slave in the failover datacenter or for analytics, make sure you have replication information for the second level master. Do not point these dbs to the active master!

Slaves Not In Active Production (db.php)

Make sure they'll receive schema migrations

• Slaves of the production cluster that don't belong in db.php still need to get schema migrations whenever they are applied in prod in order to keep replicating.
• Make sure they're added to the correct section in fenari:/home/w/common/wmf-config/db-secondary.php - it is an override for $wgLBFactoryConf['sectionLoads'] pointing only to slaves not used by the live MW site.

Making use-specific tweaks

Some DBs have differences from the normally generated config. These are applied via the puppet generated my.cnf, and further use case changes for prod slaves should be made similarly. This outlines what we do for the research slaves.

Research Slave

Note: These tweaks are reflected in puppet within mysql.pp as $research_dbs

• Hosts: db42, db1047
• Purpose: slaves of the s1 cluster dedicated to intense queries by the Summer of Research team (aka community intern statisticians).
• Contact:
• Tweaks:
  • no binlog
  • innodb-locks-unsafe-for-binlog
  • slave_transaction_retries = very high