MySQL-dump

We start of with partitioning. Partitioning 8 disks can be a hassle, so we take the first disk as a master and copy the partition table automatically to all the other disks.



The easiest way to do it is to use the command line partitioner, sfdisk, for this in a loop. "sfdisk -d /dev/sdb" will dump the partition table of /dev/sdb in a way that it can be fed to another sfdisk instance, so the following loop will work just fine.



Now we want to turn these disks into 4 RAID-1 pairs. We do not build a RAID-10 out of them, because we want to use LVM for this later on.

First, we copy the default raidtab sample into place, and then modify it to match our requirements. To define a RAID device, we have to specify a "raiddev" line first, and after that all definitions for the raid metadevice (md). Finally we have to add a device and raid-disk pair for each physical disk we want to include in the raid. The device names the physical device file, and the raid-disk gives an index number for that disk inside our metadevice.



We now have to mkraid all of our devices to initialize them. We will then find statistics for the devices in /proc/mdstat.



All of this leaves us with four metadevices, each of them a mirror pair of two physical disks. We now want to create a RAID-0 like structure from them, but we want some more flexibility, so we are using LVM for this.

LVM is a method to slice your physical devices into physical extents of equal size. All of them end up in a big bag called a volume group. You can build logical volumes from the free extents in your volume group as you see fit, you can change logical volumes in size, migrate them and snapshot them. This gives you a lot of flexibility in storage management you would not have if you made a static RAID-0 from your RAID-1's.

If you want to put any devices into a volume group, you have to label them as physical volumes first. This is done using pvcreate, and can be checked using pvdisplay.



We end up with four physical volumes which now need to be added to a volume group. A volume group has a name, ours will be called "system", and determines the extent size used with all physical volumes inside. Be default, the PE size is pretty small, 4M, and you'd want something larger such as 128M. But for our toy disks, the 4M default is just right.

Let's do it.



We have put /dev/md0 into the volume group and created it that way. We have later added two additional volumes to the group, /dev/md1 and /dev/md2. We will add /dev/md3 even later to demonstrate migration and simulate a hardware update.

Our config now looks like this:



We do have a VG with 3 PVs in it. It is 600M in size, made out of 150 4M slices, all of which are free for allocation.

You may have noticed the UUID specifier for each physical or logical volume and for each volume group. LVM deals with devices completely on the basis of UUIDs and not device names, so renaming and renumbering devices will leave LVM unfazed and it will find your disks no matter under which device name they are present.

We now want to make logical disks. True to Wide Thin Disk Striping we want to spread all of our partitions across all of our disks, so we need to specify interleaves and everything. For MySQL, we do need a data partition where datadir will reside, and a log partition where the binlog, the relay log and all other logs go. As per MySQL recommendation, we use reiserfs 3.6.



Using 4M extents, a 100M filesystem is 25 extents in size. But because we requested a stripe factor of 3, we need a number of extents in our logical volume that can be divided by 3 evenly. Thus, we get a 27 extent sized volume. We now can create filesystems and mount them.



Onto this system we will now install MySQL.



This will now keep most logs in /export/log and the datadir in /export/data. If you enter the command line client and insert some data, you'll see how the simulated disks in the vmware blink in unison.

Now the fun can start. While the filesystem is mounted and MySQL is running, we can easily add disk space.



Now we add the fourth disk /dev/md3 into the system, and clean up disk 3, /dev/md2, in order to remove it. All the time the database keeps running and the filesystems are mounted. In order to be able to do this, the kernel must have loaded the dm_mirror module, which it does not automatically do in Suse Linux.



Another nice trick: Snapshots, using the dm_snapshot module. A snapshot volume need not be as large as the original volume, because Snapshots are copy-on-write: When you make a snapshot, nothing is done. When you read from the snapshot, the requested block is read from the original volume. If a block is changed on the original volume, the original content it is copied to the snapshot first. Now, if you read from the snapshot, the saved copy is read from the snapshot instead, shadowing the modified data from the original.

Even fancier: Snapshots are writeable. How about snapshotting your database and then testing the migration on a snapshot first, while production goes on uninterrupted on the main system?



We can see how /dev/system/data is marked as "source of /dev/system/backup [active]" and how the COW (copy-on-write) table on /dev/system/backup is currently 0.38% filled. The mounted snapshot is shown to be as large as the original file system. The space allocated to the snapshot can be smaller - it must be large enough to keep all changes to the original filesystem for the duration of the lifetime of the snapshot.

We can now take our time and save the snapshot to tape. Then we must get rid of it.