Browse Month: March 2016

Accelerating MySQL file imports

Just a quick note on how to speed up the slow process of importing SQL files on Linux Systems. I wanted to import a 50 MiB MySQL dump (and monitor it using pv), which just took way too long.

pv database.sql | mysql -u user -p  database
Enter password: 
 148KiB 0:00:13 [13.8KiB/s] [>         ]  0% ETA 1:13:21
 160KiB 0:00:14 [13.8KiB/s] [>         ]  0% ETA 1:13:03
 228KiB 0:00:19 [12.5KiB/s] [>         ]  0% ETA 1:09:29

I’m importing a 50 MiB file on a SSD here, so that can’t be right. Did I mention pv is great?

Anyway, the solution is to disable autocommit mode, which performs a log flush to disk for every insert. More information here.
Just open the SQL file with any text editor and add these statements to the very top and very bottom of the file (in nano, use CTRL + w + v to jump to the bottom):

SET autocommit=0;

That’s it. SQL imports should now be finished in no time.

49.1MiB 0:00:52 [ 960KiB/s] [=========>] 100%

Virtual machine backup without downtime

Using QCOW2 file based VMs in Linux has lots of neat features. One of my favourites is the virsh blockcopy operation (assuming you are using libvirt, or are familiar with it – please read the the blockcopy section in virsh’s man before proceeding).

With libvirt, it’s possible to make use of a powerful snapshot toolkit. For now, I only want to copy an image for backup purposes without having to shut the virtualized guest down. This is where the blockcopy command comes into play. It’s simple enough, the only requirement is to temporarily undefine the guest during the blockcopy operation.

You can test it with a few commands – but be careful. Clone your VM and it’s configuration manually (shutdown & cp to somewhere else) beforehand, as files are easily overwritten by accident. Both the name of the guest and of the image are identical in my example (guest123). The target device is sda – yours might be vda or hda, take a look the guest configuration, namely the disk section. Depending on your hardware and the size of the guest, the blockcopy process might take some time. I’m using htop / iotop to monitor activity during the operation.

virsh dumpxml --security-info guest123 > guest123.xml
virsh undefine guest123
virsh -q blockcopy guest123.qcow2 sda guest123-backup.qcow2 --wait --finish
virsh define guest123.xml

That’s it. You now have a backup image of your running guest, without any downtime. Libvirt does not sparse the copied image, meaning it’s as large as the original image at the moment the operation finishes.

I’m using a cron & a simple script to periodically pull backups of my VMs. It’s assuming the name of the guest and image file are the same, as in the example above. It can be used as follows:

$ ./libvirt-backup guest123

With several VMs, each one gets it’s own cronjob. For the moment, my crontab looks similar to this:

# m h  dom mon dow   command
 05 00 * * 1 /vm/backup/ guest1
 05 01 * * 1 /vm/backup/ guest2
 05 02 * * 1 /vm/backup/ guest3

I am having the in the same directory as the images (/vm/backup). It works for now, but I might change that setup in the future. Don’t forget to set the executable flag.

$ chmod +x

And the script itself. It checks if the target file is truly *.qcow2, and if the guest is running, logs the time & size of the VM, dumps the XML, undefines the VM, blockcopies the guest to /vm/backup and adds the current date to the file name, defines the VM again, transfers the copy to a “target-host” using rsync and deletes the local copy. Important note – using the “-S”flag with rsync transfers sparsed QCOW2 files, saving space & bandwidth.

GUEST_LOCATION=`virsh domstats $GUEST | grep block.0.path | cut -d = -f2-`
BLOCKDEVICE=`virsh domstats $GUEST | grep | cut -d = -f2-`
DATE=`date +%F_%H-%M`
GUEST_SIZE=`du -sh $GUEST_LOCATION | awk '{ print $1 }'`
        if [ `qemu-img info $GUEST_LOCATION | grep --count "file format: qcow2"` -eq 0 ]; then
                echo "Image file for $GUEST not in qcow2 format."
                exit 0; 
	if [ `virsh list | grep running | awk '{print $2}' | grep --count $GUEST` -eq 0 ]; then
		echo "$GUEST not active, skipping.."
		exit 0;	
	logger "Guest backup for $GUEST starting - current image size at $GUEST_SIZE"
	virsh dumpxml --security-info $GUEST > $XML_DUMP/$GUEST-$DATE.xml
	virsh undefine $GUEST > /dev/null 2>&1
	virsh -q blockcopy $GUEST $BLOCKDEVICE $BACKUP_LOCATION/$GUEST-$DATE.qcow2 --wait --finish
	virsh define $XML_DUMP/$GUEST-$DATE.xml > /dev/null 2>&1 
	rsync -S $BACKUP_LOCATION/$GUEST-$DATE.qcow2 target-host:/libvirt_daily_backups/$GUEST/
	rsync -S $XML_DUMP/$GUEST-$DATE.xml target-host:/libvirt_daily_backups/$GUEST/
	logger "Guest backup for $GUEST done"
exit 0;

The blockcopy and rsync operations are rather I/O heavy. If you are scheduling VM backups, it’s always good practise to leave enough time between cronjobs and to avoid other processes on your system which might be triggered at similar times, such as a smartd scans for example.
Also, as mentioned before – development on KVM/QEMU and libvirt is ongoing & very active. For Debian based systems, it might be worth considering to upgrade to unstable APT sources for at least these packages.

Erasing hard disks fast & securely with OpenSSL

Erasing & overwriting disks with dd can take a very long time, both with /dev/null and /dev/urandom. Most modern CPUs are capable of AES-NI, accelerating cryptographic operations while reducing system load dramatically. That’s why I’m using OpenSSL to erase my disk drives. The advantages are clear – encrypted pseudorandom data output and maximum I/O throughput. Studies have shown that one wipe is sufficient on magnetic HDDs.


openssl enc -aes-256-ctr -pass pass:"$(dd if=/dev/urandom bs=128 count=1 </dev/null | base64)" -nosalt </dev/zero | pv --progress --eta --rate --bytes | dd of=/dev/sdX


Replace sdX with the target drive. Make sure pv is installed before executing. OpenSSL is encrypting /dev/zero with a randomized password of /dev/urandom. You should see a progress bar & ETA.