Setting up a MySQL replica
From Wikitech
(Difference between revisions)
Bhartshorne (Talk | contribs) (→Pre-requisite) |
Bhartshorne (Talk | contribs) (→New Server Setup: updating the procedure from recent experience.) |
||
| Line 5: | Line 5: | ||
== New Server Setup == | == New Server Setup == | ||
| − | + | # Ensure hardware raid is properly configured (raid-10, 256k strip, writeback cache, no read ahead) | |
| − | + | #: '''ToDo''' add in MegaCli line to show the raid level clearly | |
| − | + | #: root@db1047:~# MegaCli -LDInfo -Lall -Aall | egrep -e "Current Cache Policy" -e "Stripe Size" | |
| − | + | #: Stripe Size: 256kB | |
| − | + | #: Current Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU | |
| − | * | + | # 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) |
| − | * Install current prod mysql version | + | #* 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" | |
| − | + | #* '''Note''' xfsfix needs to be updated. The xfs settings do not correctly apply. Domas has blessed the following output as "good enough": | |
| − | * [ -s /etc/mysql/my.cnf ] && > /etc/mysql/my.cnf | + | #:: root@db1047:~# xfs_info /a | grep swidth |
| − | + | #:: = sunit=64 swidth=512 blks | |
| − | + | #* '''Note''' xfsfix will erase the contents of /a. This is fine because there's no data there yet, but be aware. | |
| − | + | # Update /etc/fstab to mount /a with nobarrier,noatime options instead of deafults. Verify it is mounted via uuid. | |
| + | #: root@db1047:~# cat /etc/fstab | ||
| + | #:: [excerpted] | ||
| + | #: # /a was on /dev/sda6 during installation | ||
| + | #: UUID=f1363f7d-8a44-4abe-9e38-bf2171e265c8 /a xfs nobarrier,noatime 0 2 | ||
| + | # remount /a: <code>umount /a; mount /a</code> | ||
| + | # mkdir /a/sqldata /a/tmp ; chown -R mysql: /a | ||
| + | #* '''Note''' Puppet makes these directories if they don't exist, so don't be surprised if they magically appear or already exist. You will have to create them after reformatting the partition above. | ||
| + | # 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) | ||
| + | # Empty out /etc/mysql/my.cnf; it is not used. (/etc/my.cnf is used instead) | ||
| + | #* [ -s /etc/mysql/my.cnf ] && > /etc/mysql/my.cnf | ||
== On New/Intended Slave == | == On New/Intended Slave == | ||
Revision as of 00:25, 28 July 2011
Contents |
Pre-requisite
- (deprecated - this is now in puppet. Good to verify anyways though:
dpkg -l xtrabackup). Install current xtrabackup, i.e. xtrabackup_1.4-193.lucid.25_amd64.deb on all servers involved
New Server Setup
- Ensure hardware raid is properly configured (raid-10, 256k strip, writeback cache, no read ahead)
- ToDo add in MegaCli line to show the raid level clearly
- root@db1047:~# MegaCli -LDInfo -Lall -Aall | egrep -e "Current Cache Policy" -e "Stripe Size"
- Stripe Size: 256kB
- Current Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU
- 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"
- Note xfsfix needs to be updated. The xfs settings do not correctly apply. Domas has blessed the following output as "good enough":
- root@db1047:~# xfs_info /a | grep swidth
- = sunit=64 swidth=512 blks
- Note xfsfix will erase the contents of /a. This is fine because there's no data there yet, but be aware.
- Can be run from root@fenari via: (To Do: put this somewhere standard, /home/w/bin?)
- Update /etc/fstab to mount /a with nobarrier,noatime options instead of deafults. Verify it is mounted via uuid.
- root@db1047:~# cat /etc/fstab
- [excerpted]
- # /a was on /dev/sda6 during installation
- UUID=f1363f7d-8a44-4abe-9e38-bf2171e265c8 /a xfs nobarrier,noatime 0 2
- root@db1047:~# cat /etc/fstab
- remount /a:
umount /a; mount /a - mkdir /a/sqldata /a/tmp ; chown -R mysql: /a
- Note Puppet makes these directories if they don't exist, so don't be surprised if they magically appear or already exist. You will have to create them after reformatting the partition above.
- 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)
- Empty out /etc/mysql/my.cnf; it is not used. (/etc/my.cnf is used instead)
- [ -s /etc/mysql/my.cnf ] && > /etc/mysql/my.cnf
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.
