How To Back Up MySQL Databases With mylvmbackup On Ubuntu 8.10

Version 1.0
Author: Falko Timme

mylvmbackup is a Perl script for quickly creating MySQL backups. It uses LVM's snapshot feature to do so. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, creates a snapshot of the volume containing the MySQL data directory, and unlocks the tables again. This article shows how to use it on an Ubuntu 8.10 server.

I do not issue any guarantee that this will work for you!

1 Preliminary Note

I'm assuming that MySQL is already set up and running on your system. The system must use LVM, and the MySQL data directory (/var/lib/mysql) should have an LVM partition of its own (althouth that is optional).

If you have read Back Up (And Restore) LVM Partitions With LVM Snapshots you know that LVM snapshots require some unused LVM partition for the snapshot. My test system has a second, currently unused hard drive /dev/sdb that will be used by mylvmbackup to create a temporary logical volume for the backup.

This is my current situation:

root@server1:~# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/server1-root
                       20G  808M   18G   5% /
tmpfs                 252M     0  252M   0% /lib/init/rw
varrun                252M   56K  251M   1% /var/run
varlock               252M     0  252M   0% /var/lock
udev                  252M  2.6M  249M   2% /dev
tmpfs                 252M     0  252M   0% /dev/shm
/dev/sda1             471M   23M  425M   6% /boot
/dev/mapper/server1-mysql
                      8.9G  170M  8.3G   2% /var/lib/mysql
root@server1:~#

As you see, I have two LVM partitions, / and /var/lib/mysql (plus an LVM swap partition not shown here). The volume group is named server1, and the volumes are named swap, root, and mysql:

root@server1:~# pvdisplay
  --- Physical volume ---
  PV Name               /dev/sda5
  VG Name               server1
  PV Size               29.52 GB / not usable 3.66 MB
  Allocatable           yes (but full)
  PE Size (KByte)       4096
  Total PE              7557
  Free PE               0
  Allocated PE          7557
  PV UUID               0gCmpE-FGel-9ayg-E2yg-kkEu-B72X-kFvaye

root@server1:~#


root@server1:~# vgdisplay
  --- Volume group ---
  VG Name               server1
  System ID
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  4
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                3
  Open LV               3
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               29.52 GB
  PE Size               4.00 MB
  Total PE              7557
  Alloc PE / Size       7557 / 29.52 GB
  Free  PE / Size       0 / 0
  VG UUID               PH5Hpc-jqeP-BFYs-wWlA-hu03-qwuQ-0cNIu3

root@server1:~#


root@server1:~# lvdisplay
  --- Logical volume ---
  LV Name                /dev/server1/swap
  VG Name                server1
  LV UUID                RCeLCK-MO5p-xoMq-SwTT-n2NV-GaP6-GaemDp
  LV Write Access        read/write
  LV Status              available
  # open                 2
  LV Size                1.00 GB
  Current LE             256
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           254:0

  --- Logical volume ---
  LV Name                /dev/server1/root
  VG Name                server1
  LV UUID                5Wen7n-xYmh-MQz1-fKH5-0XXa-1y2t-V3PYbb
  LV Write Access        read/write
  LV Status              available
  # open                 1
  LV Size                19.53 GB
  Current LE             5000
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           254:1

  --- Logical volume ---
  LV Name                /dev/server1/mysql
  VG Name                server1
  LV UUID                wk8yb6-fDl8-4tg3-tneT-1dDe-wWdy-AfGZ5I
  LV Write Access        read/write
  LV Status              available
  # open                 1
  LV Size                8.99 GB
  Current LE             2301
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           254:2

root@server1:~#

Here's an overview of my two hard drives:

root@server1:~# fdisk -l

Disk /dev/sda: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk identifier: 0x0009353f

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          62      497983+  83  Linux
/dev/sda2              63        3916    30957255    5  Extended
/dev/sda5              63        3916    30957223+  8e  Linux LVM

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk identifier: 0x00000000

Disk /dev/sdb doesn't contain a valid partition table
root@server1:~#

2 Preparing /dev/sdb

Before we can create snapshots on /dev/sdb, we must partition it (Linux LVM) and add it to our volume group (server1).

I will now create the partition /dev/sdb1 and add it to the server1 volume group:

fdisk /dev/sdb

server1:~# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.


The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): <-- n
Command action
   e   extended
   p   primary partition (1-4)
<-- p
Partition number (1-4): <-- 1
First cylinder (1-1305, default 1): <-- [ENTER]
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305): <-- [ENTER]
Using default value 1305

Command (m for help): <-- t
Selected partition 1
Hex code (type L to list codes): <-- 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): <-- w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

pvcreate /dev/sdb1
vgextend server1 /dev/sdb1

That's it - we don't need to create any volumes on it - this will be done by mylvmbackup automatically.