Setting up a MySQL replica
From Wikitech
(Difference between revisions)
Bhartshorne (Talk | contribs) m (→New Server Setup) |
Bhartshorne (Talk | contribs) m (Reverted edits by Bhartshorne (talk) to last revision by Afeldman) |
||
| Line 1: | Line 1: | ||
== Pre-requisite == | == 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 == | == 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 == | == On New/Intended Slave == | ||
| Line 39: | Line 27: | ||
== On Existing DB == | == On Existing DB == | ||
| − | * innobackupex-1.5.1 --stream=tar /a/sqldata --user=root --slave-info | nc NEW-SERVER 9210 | + | * 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. | + | * 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 == | == On New Slave, after data copy == | ||
| Line 47: | Line 35: | ||
* This is required to make the copied data consistent to a specific binlog position. | * 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. | * 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. | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
[[Category:How-To]] | [[Category:How-To]] | ||
[[Category:MySQL]] | [[Category:MySQL]] | ||
Revision as of 21:51, 31 July 2011
Contents |
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"
- Can be run from root@fenari via: (To Do: put this somewhere standard, /home/w/bin?)
- 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 xvi
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.
