Jan 30 2009

Compile the Linux Kernel and Create Distributable Debian Packages

Category: Deployment,Kerneljgoulah @ 6:04 PM


Compiling a kernel is actually a fairly easy thing to do these days. I’m going to go over how to do this on a Debian box since that happens to be my distro of choice. This will work just as well on Ubuntu. You can always wait for the packaged version, but you’ll always be a little behind some of the cutting edge features. This method allows you to get the latest upgrades that are incorporated into the kernel, or even to apply cutting edge kernel patches against the kernel source.

Getting the Source

You can always find the kernel at kernel.ftp.org. Login as anonymous and with your email address as the password:

$ ftp ftp.kernel.org
Connected to pub.us.kernel.org.
220 Welcome to ftp.kernel.org.
Name (ftp.kernel.org:jgoulah): anonymous
331 Please specify the password. {email address}

Change directories into the 2.6.x series

ftp> cd pub/linux/kernel/v2.6

We want linux-2.6.28.tar.bz2, which is the newest at the time of this article

ftp> binary
200 Switching to Binary mode.
ftp> get linux-2.6.28.tar.bz2
ftp> exit

Now you have the kernel.

You may also need these tools depending what you’ve installed so far

apt-get install kernel-package libncurses5-dev fakeroot wget bzip2 build-essential

Extract and Configure the Source

We’ll put the tarball into /usr/src

$ sudo mv linux-2.6.28.tar.bz2 /usr/src/

Extract it

$ cd /usr/src
$ sudo tar xjf linux-2.6.28.tar.bz2

Its good measure to point a symlink to your current kernel

$ sudo ln -s linux-2.6.28 linux

And change into the directory

cd /usr/src/linux

If you have any patches, now is the time to install them

bzip2 -dc /usr/src/patch.bz2 | patch -p1

Clean things up

make clean && make mrproper

Now we can finally configure the kernel. Its a really smart idea to copy your existing configuration into the current kernel as a starting point. You certainly don’t want to lose any of your current modules.

$ sudo cp /boot/config-`uname -r` .config

There is one more step to load in your old settings

$ sudo make menuconfig

Now select Load an Alternate Configuration File
Enter your config file .config when it prompts you

When you exit out make sure to save and then you can do a diff against your old config and see the new kernel options:

$ diff /boot/config-`uname -r` .config

You can go back into menuconfig to make any changes necessarily, which is typically some new module you’d like to try out. For this kernel version I’m at least enabling ext4 and minstrel.

Compiling the Kernel

$ sudo make-kpkg clean

On this command you will want to set the string that gets appended to the version in the new kernel name. I usually just do something like -custom-buildX where X is the number of times I’ve changed configurations on this kernel version and rebuilt it. You can name it whatever you like as long as it begins with a minus (-) and doesn’t contain spaces

$ sudo fakeroot make-kpkg --initrd \ 
--append-to-version=-custom-build1 kernel_image kernel_headers

Go get a sandwich or something, depending on your computer this can take a while.

Installing the Kernel

The cool part about this is we’ve just created two .deb files that can be installed on other Debian servers, no re-compilation necessary. The files will look something like this, given the above parameter to the append-to-verson option from above


So install them like a regular Debian package

$ sudo dpkg -i linux-image-2.6.28-custom-build1_2.6.28-custom-build1-10.00.Custom_i386.deb
Selecting previously deselected package linux-image-2.6.28-custom-build1.
(Reading database ... 418301 files and directories currently installed.)
Unpacking linux-image-2.6.28-custom-build1 (from linux-image-2.6.28-custom-build1_2.6.28-custom-build1-10.00.Custom_i386.deb) ...
Setting up linux-image-2.6.28-custom-build1 (2.6.28-custom-build1-10.00.Custom) ...
Running depmod.
Finding valid ramdisk creators.
Using mkinitramfs-kpkg to build the ramdisk.
Other valid candidates: mkinitramfs-kpkg mkinitrd.yaird
Running postinst hook script /sbin/update-grub.
You shouldn't call /sbin/update-grub. Please call /usr/sbin/update-grub instead!

Searching for GRUB installation directory ... found: /boot/grub
Searching for default file ... found: /boot/grub/default
Testing for an existing GRUB menu.lst file ... found: /boot/grub/menu.lst
Searching for splash image ... none found, skipping ...
Found kernel: /boot/vmlinuz-2.6.28-custom-build1
Found kernel: /boot/vmlinuz-2.6.26-custom-2.6.26
Found kernel: /boot/vmlinuz-2.6.26-custom-build7
Found kernel: /boot/vmlinuz-2.6.26-custom-build6
Found kernel: /boot/vmlinuz-2.6.26-custom-build5
Found kernel: /boot/vmlinuz-2.6.26-custom-build4
Found kernel: /boot/vmlinuz-2.6.26-custom-build3
Found kernel: /boot/vmlinuz-2.6.26-custom-build2
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-
Found kernel: /boot/vmlinuz-2.6.18-6-686
Updating /boot/grub/menu.lst ... done

And the kernel headers

$ sudo dpkg -i linux-headers-2.6.28-custom-build1_2.6.28-custom-build1-10.00.Custom_i386.deb
Selecting previously deselected package linux-headers-2.6.28-custom-build1.
(Reading database ... 418509 files and directories currently installed.)
Unpacking linux-headers-2.6.28-custom-build1 (from linux-headers-2.6.28-custom-build1_2.6.28-custom-build1-10.00.Custom_i386.deb) ...
Setting up linux-headers-2.6.28-custom-build1 (2.6.28-custom-build1-10.00.Custom) ...

That’s pretty much it. You can look at your grub config

$ vim /boot/grub/menu.lst

Scroll down and you’ll see an entry for your new kernel. The topmost entry is the default, but remember you can also choose a different kernel at boot

title       Debian GNU/Linux, kernel 2.6.28-custom-build1
root        (hd1,0)
kernel      /boot/vmlinuz-2.6.28-custom-build1 root=/dev/sdb1 ro
initrd      /boot/initrd.img-2.6.28-custom-build1

You can see the correct files installed into /boot

$ ls -al /boot/*2.6.28*
-rw-r--r-- 1 root root   63928 2009-01-28 23:56 /boot/config-2.6.28-custom-build1
-rw-r--r-- 1 root root 1958212 2009-01-30 17:34 /boot/initrd.img-2.6.28-custom-build1
-rw-r--r-- 1 root root 1173217 2009-01-29 00:07 /boot/System.map-2.6.28-custom-build1
-rw-r--r-- 1 root root 2899952 2009-01-29 00:07 /boot/vmlinuz-2.6.28-custom-build1

We are done, reboot

$ sudo shutdown -r now


We’ve seen in how just a few commands a new kernel can be configured and installed with some additional options while keeping the current configuration. Not only that, but we’ve produced Debian package files that can be installed onto other machines. This is one easy way to upgrade your kernel across many servers without having to wait for your vendor to release it.

Tags: , , , , , , ,

Jan 25 2009

Quick DB Setups with MySQL Sandbox

Category: Databasesjgoulah @ 2:57 PM


There are various reasons to setup quick “sandbox” instances of MySQL. You can use them to test different types of replication (such as master-master or various slave topologies), to test your code against different versions of MySQL, or to setup instances of MySQL on a per developer basis where each person has their own database running on a different port so they can breakdown/setup the DB easily or make schema changes without affecting other team members. A perfect tool to do all of these things easily is MySQL Sandbox.

Download the Prerequisites

To use MySQL sandbox effectively you need two things, the MySQL sandbox tool itself, and a MySQL tarball that the sandbox script can use to setup instances.

You can download the latest MySQL sandbox version like so (2.0.12 as of this writing):

wget http://launchpad.net/mysql-sandbox/mysql-sandbox-2.0/2.0/+download/mysql_sandbox_2.0.12.tar.gz

You can download the MySQL tarball from any MySQL mirror. Its important to get the non RPM, Intel C/C++ compiled, glibc-2.3 version (look for a tar.gz file with “icc” in the filename) for example if you want version 5.1:

wget ftp://mirror.anl.gov/pub/mysql/Downloads/MySQL-5.1/mysql-5.1.30-linux-i686-icc-glibc23.tar.gz

Installing an Instance with MySQL Sandbox

First you need to extract the MySQL Sandbox tool and change directory to it:

$ tar xzvf mysql_sandbox_2.0.12.tar.gz
$ cd mysql_sandbox_2.0.12

The easiest and quickest way to create an instance is:

$ ./make_sandbox /path/to/mysql-X.X.XX-osinfo.tar.gz

where mysql-X.X.XX-osinfo.tar.gz is the MySQL tarball we just downloaded. And you’re done.

However, this will put the sandbox in a directory under your home directory ($HOME/sandboxes/msb_X_X_XX), which may or may not suit your purposes. It sets it up with default users, passwords, ports, and directory name. Lets fine tune things a bit.

Setting up a Custom Tuned Instance

I want to put my instance into a partition I created called /mnt/mysql_sandboxes. I’ve created a subdirectory in there called tarballs, which holds the MySQL tarball that we downloaded above which MySQL Sandbox will extract for setup. Since I’m installing version 5.1.30 I want to call the directory that houses the MySQL data files 5.1.30_single, but you can call it anything you like. I’ll create a default user named jgoulah and a password goulah. By default it sets the port to the version number without the dots (5130 in this case) so we’ll give it a custom port so that it listens on 10000 instead.

mysql_sandbox_2.0.12 $  ./make_sandbox \
/mnt/mysql_sandboxes/tarballs/mysql-5.1.30-linux-i686-icc-glibc23.tar.gz \
--upper_directory=/mnt/mysql_sandboxes/ --sandbox_directory=5.1.30_single \
--db_user=jgoulah --db_password=goulah --sandbox_port=10000

Here’s the output:

unpacking /mnt/mysql_sandboxes/tarballs/mysql-5.1.30-linux-i686-icc-glibc23.tar.gz
Executing ./low_level_make_sandbox \
        --basedir=/mnt/mysql_sandboxes/tarballs/5.1.30 \
        --sandbox_directory=msb_5_1_30 \
        --install_version=5.1 \
        --sandbox_port=5130 \
        --no_ver_after_name \
        --upper_directory=/mnt/mysql_sandboxes/ \
        --sandbox_directory=5.1.30_single \
        --db_user=jgoulah \
        --db_password=goulah \
        --basedir=/mnt/mysql_sandboxes/tarballs/5.1.30 \
        --sandbox_port=10000 \
    The MySQL Sandbox,  version 2.0.12 16-Oct-2008
    (C) 2006,2007,2008 Giuseppe Maxia, Sun Microsystems, Database Group
installing with the following parameters:
upper_directory                = /mnt/mysql_sandboxes/
sandbox_directory              = 5.1.30_single
sandbox_port                   = 10000
datadir_from                   = script
install_version                = 5.1
basedir                        = /mnt/mysql_sandboxes/tarballs/5.1.30
my_file                        =
operating_system_user          = jgoulah
db_user                        = jgoulah
db_password                    = goulah
my_clause                      = log-error=msandbox.err
prompt_prefix                  = mysql
prompt_body                    =  [\h] {\u} (\d) > '
force                          = 0
no_ver_after_name              = 1
verbose                        = 0
load_grants                    = 1
no_load_grants                 = 0
do you agree? ([Y],n) y
loading grants
. sandbox server started
installation options saved to current_options.conf.
To repeat this installation with the same options,
use ./low_level_make_sandbox --conf_file=current_options.conf
Your sandbox server was installed in /mnt/mysql_sandboxes//5.1.30_single

Its now installed and started up, we can see that the process is running with the correct options:

$  ps -ef | grep mysql | grep jgoulah
jgoulah  11128     1  0 13:48 pts/3    00:00:00 /bin/sh /mnt/mysql_sandboxes/tarballs/5.1.30/bin/mysqld_safe --defaults-file=/mnt/mysql_sandboxes//5.1.30_single/my.sandbox.cnf
jgoulah  11203 11128  0 13:48 pts/3    00:00:00 /mnt/mysql_sandboxes/tarballs/5.1.30/bin/mysqld --defaults-file=/mnt/mysql_sandboxes//5.1.30_single/my.sandbox.cnf --basedir=/mnt/mysql_sandboxes/tarballs/5.1.30 --datadir=/mnt/mysql_sandboxes//5.1.30_single/data --user=jgoulah --log-error=/mnt/mysql_sandboxes//5.1.30_single/data/msandbox.err --pid-file=/mnt/mysql_sandboxes//5.1.30_single/data/mysql_sandbox10000.pid --socket=/tmp/mysql_sandbox10000.sock --port=10000

And we can connect to it on the port 10000:

$  mysql -u jgoulah --protocol=TCP  -P 10000 -pgoulah

You can also go into the directory where we’ve installed this and there are some convenience scripts:

$ cd /mnt/mysql_sandboxes/5.1.30_single/

You can run the use script to connect into mysql (same thing we just did above except we don’t have to remember our port, user, or pass):

$ ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.30 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql [localhost] {jgoulah} ((none)) >

Stop the instance:

$  ./stop

Or start it back up again:

$  ./start
. sandbox server started

There are a few other scripts which you can experiment with in this directory which are documented here

Setting up a Replicated Instance

The nice thing about this tool is it will also setup replicated instances of MySQL with a single command. This allows you to test your application under a replicated environment, or even test different replication topologies including multiple slaves or multi-master replication.

We’ll use similar options as the single instance above, except we’ll use port 11000 this time (the slaves get port + 1, …, port + n where n is number of slaves). We’ll put the install into /mnt/mysql_sandboxes/5.1.30_replicated. Note this time we use the make_replication_sandbox script:

mysql_sandbox_2.0.12 $  ./make_replication_sandbox \
/mnt/mysql_sandboxes/tarballs/mysql-5.1.30-linux-i686-icc-glibc23.tar.gz \
--upper_directory=/mnt/mysql_sandboxes/ \
--replication_directory=5.1.30_replicated --sandbox_base_port=11000
installing and starting master
installing slave 1
installing slave 2
starting slave 1
. sandbox server started
starting slave 2
. sandbox server started
initializing slave 1
initializing slave 2
replication directory installed on /mnt/mysql_sandboxes//5.1.30_replicated

Now we have a master and two slaves going. Note that the command to setup replicated sandboxes will not let us specify the user as we did with the single instance, but two users are created by default:

User: root@localhost Password: msandbox
User: msandbox@% Password: msandbox

You can run the use script as shown above, or connect directly to the master:

$  mysql -u msandbox --protocol=TCP  -P 11000 -pmsandbox

Create a database:

mysql> create database jg_repl_test;
mysql> exit;

Connect to one of the slaves:

$  mysql -u msandbox --protocol=TCP  -P 11001 -pmsandbox
mysql> show databases like '%jg_%';
| Database (%jg_%) |
| jg_repl_test     |

And we can see that it has replicated across.

There are various options you can give the make_replication_sandbox command, for example you can give it the –master_master option to setup a multi master instance.


We’ve seen how to create a single instance of MySQL and also a replicated master with two slaves. Each of these takes only a few seconds to setup once you have the directory layout decided. There really isn’t a much easier way to setup scratch instances of MySQL for all kinds of different purposes.


Jan 22 2009

MySQL Snapshots using LVM

Category: Databasesjgoulah @ 2:50 PM


LVM snapshots are a quick and easy way to take a backup of your MySQL server’s data files. The idea is to acquire a read lock on all tables, flush all server caches to disk, and create a snapshot of the volume containing the MySQL data directory, then unlock the tables again. This gives an atomic snapshot that takes only milliseconds. A mysql instance can then be launched to point at this data directory and you can perform a mysqldump to get data for setting up slaves, or you can take a completely destroyed DB and use the data directory to restore it to this point in time.

Setup the LVM

Figure out the partition you want to put the LVM on, in our case we’ll use /data2

$ df -h | grep data2
/dev/sda2             371G  195M  352G   1% /data2

We’ll need to run fdisk on it.  If you have data on here, save it to somewhere else!

$ sudo fdisk /dev/sda2
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 49932.
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)
Partition number (1-4): 1
First cylinder (1-49932, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-49932, default 49932):
Using default value 49932

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)

You can review the change with the ‘p’ command:

Command (m for help): p

Disk /dev/sda2: 410.7 GB, 410704680960 bytes
255 heads, 63 sectors/track, 49932 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

     Device Boot      Start         End      Blocks   Id  System
/dev/sda2p1               1       49932   401078758+  8e  Linux LVM

You might need to reboot here, so go ahead and run sudo shutdown -r now

Now unmount the disk we want to create the lvm on and create a physical volume

$ sudo umount /dev/sda2
$ sudo pvcreate /dev/sda2
Physical volume "/dev/sda2" successfully created

You can see our new physical volume with pvdisplay

$ sudo pvdisplay
  "/dev/sda2" is a new physical volume of "382.50 GB"
  --- NEW Physical volume ---
  PV Name               /dev/sda2
  VG Name
  PV Size               382.50 GB
  Allocatable           NO
  PE Size (KByte)       0
  Total PE              0
  Free PE               0
  Allocated PE          0
  PV UUID               myxfgQ-alfA-y8sX-af4a-ifoA-3S29-YD3tuK

Now create a volume group on the physical volume, since we’re using this for mysql we’ll call it mysql

 $ sudo vgcreate mysql /dev/sda2
  Volume group "mysql" successfully created

And you can view it

$ sudo vgdisplay
  --- Volume group ---
  VG Name               mysql
  System ID
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  1
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                0
  Open LV               0
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               382.50 GB
  PE Size               4.00 MB
  Total PE              97919
  Alloc PE / Size       0 / 0
  Free  PE / Size       97919 / 382.50 GB
  VG UUID               0VZxkR-873N-E6Vj-H6SV-FCWO-3jHF-ykHRiX

Create a logical volume on the volume group, we’ll put it on our mysql volume group and call it maindb. We want it to be about half (or less) of the entire amount of space we have to make a backup, in this case thats about half of 382GB so we’ll go with 190:

$ sudo lvcreate --name maindb --size 190G mysql
  Logical volume "maindb" created

And you can view it:

$ sudo lvdisplay
  --- Logical volume ---
  LV Name                /dev/mysql/maindb
  VG Name                mysql
  LV UUID                4hlcMf-cs5a-xjYU-yxjE-Fx2f-Pngv-z0RAEH
  LV Write Access        read/write
  LV Status              available
  # open                 0
  LV Size                190.00 GB
  Current LE             48640
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:0

Now we have to put an actual filesystem on it:

$ sudo mkfs.ext3 /dev/mysql/maindb
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
24903680 inodes, 49807360 blocks
2490368 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
1520 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624, 11239424, 20480000, 23887872

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 26 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.

And now we can actually re mount it on the /data2 folder:

$ sudo mount /dev/mysql/maindb /data2

And add it to fstab, making sure to comment out the old entry

#LABEL=/data2            /data2                  ext3    defaults        1 2
/dev/mysql/maindb       /data2                  ext3       rw,noatime   0 0

Note that we do not have to create the other logical volume on the volume group that is used for the snapshot, as the lvmbackup tool will take care of it for us.

Move MySQL to the LVM Partition

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

$ sudo mkdir -p /data2/var/lib
$ sudo mv /var/lib/mysql/ /data2/var/lib/

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

log-bin = /data2/var/lib/mysql/bin.log



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


Now you can start mysqld:

sudo /usr/bin/mysqld_safe &

Install mylvmbackup

Grab the latest mylvmbackup utility:

$ wget http://www.lenzg.org/mylvmbackup/mylvmbackup-current.tar.gz

Extract and install, its just a script so there is no compilation necessary:

$ tar xzvf mylvmbackup-0.11.tar.gz
$ cd mylvmbackup-0.11
$ sudo make install

You’ll also need some perl modules:

Now we need to setup the config file for mylvmbackup, located at /etc/mylvmbackup.conf

There are lots of options but at the very least we need to set:

password={your root pass here}





You may also want to change the backupdir parameter, as this is where the tarball of your database backup is stored,  so you’ll need the space for that.

You’ll want to make sure this backup directory exists, so if you are using the default:

$ sudo mkdir -p /var/tmp/mylvmbackup/backup

And also the mount directory (this is where the LVM snapshot is mounted so a mysqldump can be taken:

$ sudo mkdir -p /var/tmp/mylvmbackup/mnt

Create the Backup

Now you can simply run the mylvmbackup command as root.   Heres what the output looks like:

$ sudo mylvmbackup
20090122 13:26:47 Info: Connecting to database...
20090122 13:26:47 Info: Flushing tables with read lock...
20090122 13:26:47 Info: Taking position record...
20090122 13:26:47 Info: Running: /usr/sbin/lvcreate -s --size=190G 
--name=maindb_snapshot /dev/mysql/maindb
File descriptor 3 (socket:[101615]) leaked on lvcreate invocation.
 Parent PID 7209: /usr/bin/perl
  Logical volume "maindb_snapshot" created
20090122 13:26:47 Info: DONE: taking LVM snapshot
20090122 13:26:47 Info: Unlocking tables...
20090122 13:26:47 Info: Disconnecting from database...
20090122 13:26:47 Info: Mounting snapshot...
20090122 13:26:47 Info: Running: /bin/mount -o rw /dev/mysql/maindb_snapshot 
20090122 13:26:47 Info: DONE: mount snapshot
20090122 13:26:47 Info: Running: /bin/mount -o bind,ro 
20090122 13:26:47 Info: DONE: bind-mount position directory
20090122 13:26:47 Info: Recovering InnoDB...
20090122 13:26:47 Info: Running: echo 'select 1;' | mysqld_safe 
--skip-grant --bootstrap --skip-ndbcluster --skip-slave-start
Starting mysqld daemon with databases from /var/tmp/mylvmbackup/mnt/backup
STOPPING server from pid file /var/tmp/mylvmbackup_recoverserver.pid
090122 13:26:47  mysqld ended
20090122 13:26:47 Info: DONE: InnoDB recovery on snapshot
20090122 13:26:47 Info: Copying my.cnf...
20090122 13:26:47 Info: Taking actual backup...
20090122 13:26:47 Info: Creating tar archive 
20090122 13:26:47 Info: Running: cd '/var/tmp/mylvmbackup/mnt' ;
'/bin/tar' cvf - backup/  
 /bin/gzip --stdout --verbose 
--best ->
/bin/tar: backup/var/lib/mysql/mysql.sock: socket ignored
20090122 13:26:48 Info: DONE: create tar archive
20090122 13:26:48 Info: Cleaning up...
20090122 13:26:48 Info: LVM Usage stats:
20090122 13:26:48 Info:LV VG Attr LSize Origin Snap% Move Log Copy% Convert
20090122 13:26:48 Info:   maindb_snapshot mysql swi-a- 190.00G maindb   0.00
  Logical volume "maindb_snapshot" successfully removed

And now you should have a tarball in your backup directory, with all the files needed to restore:

$ ls /var/tmp/mylvmbackup/backup


Jan 13 2009

Using SQLT to Create Database Diffs

Category: Schema Versioningjgoulah @ 11:19 PM


Database diffs can be useful for a variety of use cases, from outputting the differences in your development versus production schema to full blown database versioning systems.   A database diff is similar in nature to a file diff, but instead of outputting the differences between two files,  it will output the set of alterations to be made to bring one databases schema in sync with another.   There are a variety of tools that will accomplish this for you, but I am going to use a tool called SQL::Translator.   SQLT can do quite a lot for you, such as converting among different schema dialects (think MySQL to Oracle) or outputting an ERD of your database, but I’ll stick with using it as a simple diff tool for this article.


The tool can be found here on CPAN.   I suggest using local::lib for perl module installation to avoid needing to install things as root.  I’ve written up detailed instructions in the past, but its pretty straightforward if you follow the bootstrap instructions.  In any case, if you are lucky you will be able to just do a normal cpan install like so:

cpan SQL::Translator

For our examples you’ll also need the DBD::MySQL driver. This can be a real pain to install from CPAN so you may want to try to use your systems package management for this. Under Debian this is called libdbd-mysql-perl and in Redhat its called perl-DBD-MySQL.

Diffing Two DDL Files

Once you have SQLT installed, you’ll want to grab the DDL files from the databases that you are interested in producing diffs for.  If you are using mysql this is easy enough to do with mysqldump.

So lets say you want to compare your development and production databases, we’d grab those schema dumps, assuming your dev db is on host ‘dev’ and prod db on host ‘prod’:

mysqldump --no-data -h dev -u your_dbuser -p your_dbname > dev_db.sql
mysqldump --no-data -h prod -u your_dbuser -p your_dbname > prod_db.sql

The SQLT install comes with a handy script called sqlt-diff which you can use to diff two different DDL files. So in the case of MySQL all you have to run is:

sqlt-diff dev_db.sql=MySQL prod_db.sql=MySQL > my_diff.sql

You should be able to pass other arguments where we’ve used ‘MySQL’ here, and you can get a list via:

sqlt -l

Diffing DDL Against a Database

The above may be all you need, but perhaps you want to diff a DDL against the actual database for whatever reason. I recently used this approach to write a versioning system in which the database changes could be output into a file containing the alterations as well as a new DDL for each “version”, this way changes are not lost or forgotten.

SQLT doesn’t currently have any scripts to handle this (yet) so we’ll have to use the perl module itself to do what we want. A simple perl script is all we need.


use strict;
use warnings;
use SQL::Translator;
use SQL::Translator::Diff;
use DBI;

# set the path of the file you want to diff against the DB
$ddl_file = "/path/to/file_to_diff.sql";

# make sure to fill in db_name, host_name, and db_port
my $dsn = "DBI:mysql:database=db_name;host=host_name;port=db_port";

# you'll have to set the db_user and db_pass here
DBI->connect($dsn, 'db_user', 'db_pass', {'RaiseError' => 1});

# create a SQLT object that connect to the database via our dbh
my $db_tr = SQL::Translator->new({
      add_drop_table => 1,
      parser => 'DBI',
      parser_args => {  dbh => $dbh }

$db_tr->parse($db_tr, $dbh);

# create a SQLT object that corresponds to our ddl file
my $file_tr = SQL::Translator->new({
    parser => 'MySQL'
my $out = $file_tr->translate( $ddl_file ) or die $file_tr->error;

# perform the actual diff (some of the options you use may vary)
my $diff = SQL::Translator::Diff::schema_diff(
    $file_tr->schema,  'MySQL',
    $db_tr->schema,  'MySQL',
    ignore_constraint_names => 1,
    ignore_index_names => 1,
    caseopt => 1

# now just print out the diff
my $file;
my $filename = "diff_output.sql"
if(!open($file, ">$filename")) {
  die("Can't open $filename for writing ($!)");
print $file $diff;

So we’ve used the SQL Translator tool to read in our database, read in our DDL, and produce a diff. Pretty neat!


We’ve really only scratched the surface with some of the capabilities of SQL::Translator, but its a pretty handy thing to be able to easily diff various schemas. Its important that your development and production schema are in sync, and stay in sync, and this gives an easy way to start on automating that process.

Tags: , , , , , , ,

Jan 11 2009

Intelligent Version Control and Branching

Category: Version Controljgoulah @ 12:28 PM


Most shops these days seem to know that using a Version Control System is necessary for the organization of a large software project involving multiple developers.   It’s essential to allow each developer to work on their part of the project and commit the changes to a central repository, which the rest of the developers can then pull down into their working sandbox.  Its an effective way to develop that avoids overwriting of changes, and allows for easy rollback and history.  However, one problem I see over and over again is either lack of branching altogether,  or doing it in a manual way that drifts the branch further out of sync with trunk as time goes on, and then trying to manually merge this with the trunk code when the branch is done.   For those who don’t know, trunk is basically your mainline, in which code can be branched off of for features that won’t interfere with trunk until they are merged at some later point.  This allows for testing changes without bothering other developers, but still being able to keep a commit history.

The difficult thing about this branching process is that if you try to manually merge code that has been on a branch for some time, the continuous development on trunk will have brought the two to such different places that you end up trying to resolve a ton of conflicts by hand.  Conflicts happen when changes are made to the same file and same lines in that file by different people.  Trunk is eventually a different bunch of code than when the branch was started, so by the end of your branch you could be developing against what is essentially a different codebase.  Anyone that has tried to do merging manually knows how painful this process can be.

Luckily, tools exist to help us with the process.  This article is going to focus on two tools that can interact with an SVN repository to help us with intelligent branching.   The tools I’m going to review are SVK and Git, and I will go over each separately. In fact they should not be used simultaneously since Git doesn’t know anything about the metadata SVK expects to be in your SVN props. In other words do not attempt to create a branch with SVK and then merge with Git, it just won’t work. Git by the way, can be used standalone, without SVN,  but I’m going to show you how to use it with SVN because that seems to be the version control system of choice in most shops these days. SVK is just a wrapper around SVN, its not a standalone tool. The great thing about both of these is you don’t necessarily have to migrate all of your developers over at once.  They can continue day to day work flow while you harness the power of branching.  Eventually people will see the advantage and try it out too.  Under the covers you are still storing your code in an SVN repository.

Using SVK

Installing SVK from source is well beyond the scope of this article, so I’m assuming you have a package management system that will handle this installation for you.  Once you have SVK installed, using it is very straightforward for SVN users.  In fact, its mostly the same exact commands, with a few additions.  Like SVN, you can do ‘svk help <command>’  to get detailed help on a particular command.

Checking Out Your Repository

Checking out your repository is very similar to SVN, and is done with the ‘checkout’ command:

svk co {repo}

where {repo} is your repository url (eg. http://svn.yourdomain.com/svn/repo)

You’ll be prompted for a base URI to mirror from and its ok to press enter here:

New URI encountered: http://svn.yourdomain.com/svn/repo
Choose a base URI to mirror from (press enter to use the full URI): ENTER

You’ll be prompted for a depot path and its also ok to accept the default

Depot path: [//mirror/repo] ENTER

SVK is a decentralized version control system and thus needs to mirror your SVN repository locally. You’ll see a prompt like this and you’ll be ok to accept the default:

Synchronizing the mirror for the first time:
a : Retrieve all revisions (default)
h : Only the most recent revision
-count : At most 'count' recent revisions
revision : Start from the specified revision
a)ll, h)ead, -count, revision? [a] ENTER

This step can take quite a while, depending on how large your repository is. Just be patient and it will finish up eventually.

Working with SVK

From here SVK is very similar to SVN. You’ll have checked out the entire repository, so if your SVN is setup according to standard you will see a trunk, branches, and tags directory under your repository root folder.

SVK has the same command set with one caveat, that when you update you have to give the -s flag:

svk up -s

This allows SVK to sync the mirror with the repository before updating your code.

Branching With SVK

The point of this article is how to use these tools for branching, so lets create a branch. There are a few ways to do this but a nice way is to copy trunk to a branch on the mirror itself:

svk cp //mirror/{repo}/trunk //mirror/{repo}/branches/my_branch_name
cd {repo}/branches
svk up -s # pull the new branch back from the mirror

Now you can work, work, work, and commit as you please. Here is where SVK really shines. You can pull from the repository path you branched from at any time. In this case we branched from trunk, so we can pull trunks changes into our branch.

svk pull

SVK will track all of the metadata surrounding the change, and if there happens to be a conflict it is resolved at pull time. Now the trunk code is merged into your branch. You are in sync with trunk, in addition to your branches changes.

The last thing to know is how to push your branch back to trunk when you are done. It doesn’t hurt to do one last pull to make sure you are in sync with trunk, and then:

svk push -l

Now your branch has been merged back to trunk. That is it! Painless and easy. You’ve already resolved the conflicts at pull time which SVK has tracked, so the push is simple. SVK has done all the hard work for us.

Its a good habit to get rid of your branch once you are done:

rm -rf branches/branch_name
svk rm branches/branch_name
svk commit -m "deleted old branch" branches/branch_name

Using Git

Using Git with SVN is a similar process, and like SVK, Git is also a distributed system. In a nutshell this means we can have local branches and remote branches as well. Its important to understand this concept when using Git much more so than when using SVK.  Its probably a good idea to go over the SVN to Git tutorial to see how familiar SVN commands map to Git.

Checking Out Your Repository

With Git you essentially clone your SVN repository. Here I’m using a repository called testrepo located on the same host so I am using the file protocol. You can use whichever protocol you normally use to access your SVN repository.

git svn clone --prefix=svn/ file:///usr/local/svn/testrepo -T trunk -b branches -t tags

You don’t have to use the –prefix here, but it allows you to be able to distinguish between local branches and those representing remote svn branches easily, so I highly recommend it.

Viewing Local and Remote Branches

With Git its important to remember that we have both local and remote branches. Local branches only exist for our sandbox, while remote branch are shared out on the SVN repository for others to use.

You can view your local branches like so:

$ git branch
* master

So far we only have the default master branch. When git svn is finished importing all of the svn history it sets up a master branch for you to work with. The tip of that branch will be the latest commit in the svn repository (note this is not necessarily trunk). The star indicates the current branch that we are on.

Its not a bad idea to have master correspond to trunk, and to be sure we can run:

git reset --hard svn/trunk

You can also view your remote branches. These exist out on the SVN repository we’ve cloned:

$ git branch -r

So far I can see that a trunk exists, as well as a branch called testbug. But these are remote, so to work with them we need to pull them down locally. Right now I’m interested in trunk, so we’ll grab that:

$ git checkout -b trunk svn/trunk
Switched to a new branch "trunk"

Anytime you would like to update the code with changes from the repository, you can do it like so:

git svn rebase

Branching With Git

Now lets assume we want to create a new branch called foo. This command will actually create the new branch on the SVN repository.

$ git svn branch foo

Since the branch is still only remote, we need to get a local copy:

$ git checkout -b foo svn/foo

Now lets actually do something on the branch, so we can show how merge operations work. We’ll just create a simple file:

$ touch myfile
$ echo "stuff" > myfile
$ cat myfile

And commit that to the local repository:

$ git add myfile
$ git commit -m "my new file"

Note this only commits to the local repository and other users on the branch won’t see the change until we commit it to the remote repository. Its a good idea to do a dry run to make sure we are committing to the right place. We can use ‘-n’ for dry run:

$ git svn dcommit -n
Committing to file:///usr/local/svn/testrepo/branches/foo ...

This looks correct, its going to commit to branches/foo on our remote repository, so run the command without the -n option.

$ git svn dcommit

Now lets switch back to trunk so we can perform a merge.

$ git checkout trunk

Now that we are on trunk, our new file doesn’t exist, which makes sense since it was created on the branch. But we want to merge our branch foo back into trunk, which can be done like so:

$ git merge --no-ff foo

And again we want to commit that local change to the remote repository:

$ git svn dcommit

There’s one more important thing to note. Normally you don’t need –no-ff unless you are using git against svn like this. –no-ff is needed so you get a real merge commit. If you don’t have that and a fast-forward is possible you’ll end up with the commit from the svn branch on top of the history, in which case the branch that commit on top is part of will be used for committing.

Therefore its a good idea to add this option to the default merge options for anything maintained with git-svn. Open up the .git/config and add the following:

branch.foo.mergeoptions = --no-ff

This allows you to use git merge and not remember the –no-ff option.


We’ve seen how to use two distributed revision control systems to help us manage our remote SVN repositories. We know how to create a branch, and stay in sync with the development on trunk. We have also learned now to merge our changes from the branch back into trunk in a very painless and easy way.

Tags: , , , ,