MySQL slave with LVM: Difference between revisions
From Lolly's Wiki
Jump to navigationJump to search
No edit summary |
|||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
[[category:MySQL]] | |||
'''UNFINISHED first few lines...''' | '''UNFINISHED first few lines...''' | ||
| Line 4: | Line 5: | ||
===Get the data mount=== | ===Get the data mount=== | ||
< | <syntaxhighlight lang=bash> | ||
master# df -h $(mysql --batch --skip-column-names -e "show variables like 'datadir'" | awk '{print $NF;}') | master# df -h $(mysql --batch --skip-column-names -e "show variables like 'datadir'" | awk '{print $NF;}') | ||
Filesystem Size Used Avail Use% Mounted on | Filesystem Size Used Avail Use% Mounted on | ||
/dev/mapper/vg--mysql-mysql--data 138G 78G 55G 59% /var/lib/mysql | /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;}')" | master# DATADIR="$(mysql --batch --skip-column-names -e "show variables like 'datadir'" | awk '{print $NF;}')" | ||
</ | </syntaxhighlight> | ||
Enough space for a snapshot? | Enough space for a snapshot? | ||
< | <syntaxhighlight lang=bash> | ||
master# lvs /dev/mapper/vg--mysql-mysql--data | master# lvs /dev/mapper/vg--mysql-mysql--data | ||
LV VG Attr LSize Pool Origin Data% Move Log Copy% Convert | LV VG Attr LSize Pool Origin Data% Move Log Copy% Convert | ||
| Line 20: | Line 21: | ||
VG #PV #LV #SN Attr VSize VFree | VG #PV #LV #SN Attr VSize VFree | ||
vg-mysql 2 3 1 wz--n- 199,99g 20,00g | vg-mysql 2 3 1 wz--n- 199,99g 20,00g | ||
</ | </syntaxhighlight> | ||
===Create a concsistent snapshot=== | ===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# 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# lvcreate -l50%FREE -s -n mysql-data-snap /dev/vg-mysql/mysql-data | ||
| Line 35: | Line 36: | ||
master# mysql --batch --skip-column-names -e "show variables like 'innodb_data_file_path'" | 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 | innodb_data_file_path ibdata1:5G;ibdata2:5G;ibdata3:5G;ibdata4:50M:autoextend | ||
</ | </syntaxhighlight> | ||
Set the innodb_data_file_path to the same value on the slave. | Set the innodb_data_file_path to the same value on the slave. | ||
==Copy the data to 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# 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 - ) | slave# ssh -c blowfish master "cd /mnt ; tar cSpzf - ." | ( cd ${DATADIR} ; tar xlvSpzf - ) | ||
</ | </syntaxhighlight> | ||
==Create replication user on master== | ==Create replication user on master== | ||
< | <syntaxhighlight lang=bash> | ||
master# mysql -e "" | master# mysql -e "" | ||
</ | </syntaxhighlight> | ||
==Setup slave== | ==Setup slave== | ||
< | <syntaxhighlight lang=bash> | ||
slave# mysql -e "" | slave# mysql -e "" | ||
</ | </syntaxhighlight> | ||
Latest revision as of 10:18, 2 March 2022
UNFINISHED first few lines...
Create LVM snapshot
Get the data mount
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;}')"
Enough space for a snapshot?
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
Create a concsistent snapshot
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
Set the innodb_data_file_path to the same value on the slave.
Copy the data to the slave
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 - )
Create replication user on master
master# mysql -e ""
Setup slave
slave# mysql -e ""
