Install MySQL server 5.6 – Part 2 Using LVM as backup

LVM snapshots are a quick and easy way to take a backup of your MySQL server’s data files.

I. CONFIGURE LVM

We have 2 disks and need to combine them into a logical volume as below figure:

1. Use the lsblk command to view your available disk devices and their mount points (if applicable) to help you determine the correct device name to use

[root@myDB ~]# lsblk

NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT

sda 8:0 0 8G 0 disk

ââsda1 8:1 0 500M 0 part /boot

ââsda2 8:2 0 7.5G 0 part

ââvg_mydb-lv_root (dm-0) 253:0 0 6.7G 0 lvm /

ââvg_mydb-lv_swap (dm-1) 253:1 0 816M 0 lvm [SWAP]

sdb 8:16 0 9G 0 disk

sdc 8:32 0 9G 0 disk

2. Create physical volume on /dev/sdb and /dev/sdb

[root@myDB ~]# pvcreate /dev/sdb

[root@myDB ~]# pvcreate /dev/sdc

3. Create logical volume group named “vol-group” using 2 physical volumes

[root@myDB ~]# vgcreate vol-group /dev/sdb /dev/sdc

4. Create logical volume named “DATA” on logical volume group named “vol-group”

[root@myDB ~]# lvcreate –L 7G -n DATA vol-group

5. Create filesystem for logical volume “DATA”

[root@myDB ~]# mkfs -t ext4 /dev/vol-group/DATA

[root@myDB ~]# mkdir /mnt/DATA

[root@myDB ~]# mount /dev/vol-group/DATA /mnt/DATA

[root@myDB ~]# df -h /mnt/DATA

6. Modify /etc/fstab to enable automatically mount when the system boots.

[root@myDB ~]# vi /etc/fstab

#Put this into the end of the file

/dev/vol-group/DATA     /mnt/DATA     ext4     rw,noatime     0 0

 

II. MOVE MYSQL TO THE LVM

1. Now we want to move our mysql installation into this folder:

[root@myDB ~]# service mysqld stop

[root@myDB ~]# mkdir -p /mnt/DATA/var/lib

[root@myDB ~]# mv /var/lib/mysql/ /mnt/DATA/var/lib

2. We need to ensure the my.cnf has settings pointing to this new directory:

[root@myDB ~]# vi /etc/my.cnf

[mysqld]

datadir=/mnt/DATA/var/lib/mysql

socket=/mnt/DATA/var/lib/mysql/mysql.sock

#We can keep this default value

symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

[client]

socket=/mnt/DATA/var/lib/mysql/mysql.sock

#We can keep the log and pid outside of the LVM if we want:

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

3. Disable SELinux

[root@myDB ~]# vi /etc/sysconfig/selinux

SELINUX=disabled

4. Now you can start mysqld:

[root@myDB ~]# service mysqld start OR usr/bin/mysqld_safe &

 

III. CREATE AN LVM SNAPSHOT

1. Lock database

In order to guarantee data integrity, the database must remained locked until the LVM snapshot is created. The amount of time that this operation takes will vary based on how much data needs to be flushed to disk, but it is generally very quick.

[root@myDB ~]# mysql –u root –p

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS\G;

# in case if you need to configure replication

# take note Position number and bin file

2. Create snapshop

This snapshot needs to be large enough to accommodate the changes that will be made to the database while the snapshot is present. Check the unallocated space is available for the snapshot:

[root@myDB ~]# vgdisplay vol-group | grep Free

Free PE / Size 765 / 8.00 GiB

# The free space is 8 G so it's fine

Create snapshot:

[root@myDB ~]# lvcreate –L 7G –s –n DATABackup /dev/vol-group/DATA

# Note

  • 7G: size of snapshot
  • s: creates snapshot
  • n: creates name for the snapshot
  • DATABackup: snapshots name
  • /dev/vol-group/DATA: volume which we are going to create a snapshot

Mount snapshot:

[root@myDB ~]# mkdir -p /mnt/snap

[root@myDB ~]# mount /dev/vol-group/DATABackup /mnt/snapshot

Check again:

[root@myDB ~]# lvscan

   ACTIVE '/dev/vg_mydb/lv_root' [6.71 GiB] inherit

   ACTIVE '/dev/vg_mydb/lv_swap' [816.00 MiB] inherit

   ACTIVE Original '/dev/vol-group/DATA' [7.00 GiB] inherit

   ACTIVE Snapshot '/dev/vol-group/DATABackup' [7.00 GiB] inherit

[root@myDB ~]# dir /mnt/DATA/

lost+found var

[root@myDB ~]# dir /mnt/snap/

lost+found var

3. Release the read lock so that normal database operation can resume

[root@myDB ~]# mysql –u root –p

mysql> UNLOCK TABLES;

IV. COPY SNAPSHOT DATA TO ANOTHER SERVER

At this point, there is a consistent, point-in-time snapshot of the MySQL file structure stored in the LVM snapshot. The database can now go on with its business, and the only locking required was to flush any data from memory to disk.

From here, any standard filesystem backup method can be used to store a copy of /var/lib/mysql as mounted under /mnt/snap. This method could range from rsync/ssh, to a simple tarball, to some enterprise backup solution.

We can also use this snapshot data to build MySQL replication

See  Part 1

About Terri

System Administrator @Netpower Datacenter

Posted on 12.10.2015, in Linux, Technical Articles and tagged , , . Bookmark the permalink. 1 Comment.

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

%d bloggers like this: