MySQL slave with LVM

UNFINISHED first few lines...

Create LVM snapshot

Get the data mount

<syntaxhighlight lang=bash> master# df -h $(mysql --batch --skip-column-names -e "show variables like 'datadir'" | awk '{print $NF;}') Filesystem Size Used Avail Use% Mounted on /dev/mapper/vg--mysql-mysql--data 138G 78G 55G 59% /var/lib/mysql master# DATADIR="$(mysql --batch --skip-column-names -e "show variables like 'datadir'" | awk '{print $NF;}')" </source>

Enough space for a snapshot? <syntaxhighlight lang=bash> master# lvs /dev/mapper/vg--mysql-mysql--data

 LV         VG       Attr      LSize   Pool Origin Data%  Move Log Copy%  Convert
 mysql-data vg-mysql -wi-ao--- 140,00g           

master# vgs vg-mysql

 VG       #PV #LV #SN Attr   VSize   VFree 
 vg-mysql   2   3   1 wz--n- 199,99g 20,00g

</source>

Create a concsistent snapshot

<syntaxhighlight lang=bash> master# mysql -e "FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;" > ${DATADIR}/master_status.$(date "+%Y%m%d_%H%M%S") master# lvcreate -l50%FREE -s -n mysql-data-snap /dev/vg-mysql/mysql-data master# mysql -e "UNLOCK TABLES;" master# mount /dev/vg-mysql/mysql-data-snap /mnt

master# cat /mnt/master_status.20151002_225659 File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.002366 263911913

master# mysql --batch --skip-column-names -e "show variables like 'innodb_data_file_path'" innodb_data_file_path ibdata1:5G;ibdata2:5G;ibdata3:5G;ibdata4:50M:autoextend </source>

Set the innodb_data_file_path to the same value on the slave.

Copy the data to the slave

<syntaxhighlight lang=bash> slave# DATADIR="$(mysql --batch --skip-column-names -e "show variables like 'datadir'" | awk '{print $NF;}')" slave# ssh -c blowfish master "cd /mnt ; tar cSpzf - ." | ( cd ${DATADIR} ; tar xlvSpzf - ) </source>

Create replication user on master

<syntaxhighlight lang=bash> master# mysql -e "" </source>

Setup slave

<syntaxhighlight lang=bash> slave# mysql -e "" </source>