5 Step LVM/XFS for MySQL

In order to quickly setup an LVM managed XFS filesystem for a MySQL DB follow these 5 easy steps:

# Create the physical volume you wish to allocate (Type=LVM)
fdisk
# Assign this new volume using pvcreate e.g. sdb1
pvcreate /dev/sdb1
# Create a volume group named dbdata
vgcreate dbdata /dev/sdb1
# Create a logical volume named mysql_data 35GB
lvcreate –name mysql_data –size 35G dbdata
# Now format using XFS – these options are ideal for larger workloads (make sure to install xfsprogs)
mkfs.xfs -f -l size=128m,lazy-count=1 /dev/dbdata/mysql_data

It may be useful to add an entry to your fstab like (optimized for writes):
/dev/dbdata/mysql_data /var/lib/mysql xfs logbufs=8,logbsize=256k,nobarrier,largeio,noatime,nodiratime 0 2

Finally, some useful commands for creating snapshots (you can optionally also flush tables with read lock and run xfs_freeze to ensure consistency):
# to create a snapshot, make sure some space is available on your volume group and run:
lvcreate –snapshot –size 1G –name backup_mysql_`date +%Y%m%d%H%M%S` /dev/dbdata/mysql_data
# to remove a snapshot just run lvremove and specify the snapshot name
lvremove -f /dev/dbdata/backup_mysql_20131109111700
# to count all snapshots
lvscan |grep ‘Snap’|wc -l
# to find the oldest snapshot for deletion
lvscan |grep ‘Snap’| cut -d”‘” -f2 | sort | head -1

Remember, this procedure is good in a small environment however when working with RAID arrays make sure you specify UUIDs instead and tune your stripe width / stripe unit sizes during XFS formatting. Also you will want to tweak your snapshot sizes.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s