Blazing Fast MySQL Backup :: I've been trying to backup a 400 GB database over network (#), and I kept getting abysmal speed. Even after using compression (mysqldump -C), gzip trick (mysqldump | gzip -c), etc; I can only get 20 Mbps transfer rate at best.

So I looked around, and found out this article about using "tab" option on mysqldump, to make the backup process much faster. 

Apparently, mysqldump spend much of its effort in formatting its output. 
The "tab" option will make mysqldump to save its output in a very sparse format (tab delimited). 

Anyway, since the "tab" option can only be used on disk (not over network), I mounted the destination over network using NFS. Then I pointed mysqldump to use that.

I was shocked to see that I'm getting 200 Mbps transfer speed ! 🙂 instant 10x speed increase. Amazing. 

To be honest, I was sceptical at first. I thought I'd just be getting a bit of performance increase.
But 10x speed increase ? That's just awesome 😀

Listed below is the script to do the fast mysql backup, and its restore. I've also fixed the original script, nothing major, but might be confusing for newbies.

Here you go :

—- fast-mysql-backup.sh —————–
#!/bin/bash
dir=$(date "+%Y-%m-%d_%Hh")
mkdir -m 777 -p /tmp/$dir
for db in $(mysql -BNe "show databases" | grep -v information_schema ) ; do 
  mkdir -m 777 /tmp/$dir/$db
  mysqldump –tab=/tmp/$dir/$db –opt –single-transaction –quick  $db
done
tar czf dump.tgz /tmp/$dir
——————————

—- fast-mysql-restore.sh —————–
#!/bin/bash
### NOTE
# change "dir" with the correct location
# change –use-threads with the number of processors in your computer

dir=/mnt/nfs
cores=16

cd $dir
tar xzf dump.tgz

for db in * ; do
  mysql -e "drop database $db; create database $db default charset utf8"
# create the database structure
cat $db/*.sql | mysql $db
# import the database contents
mysqlimport –use-threads=$cores –local $db $db/*.txt
done
——————————

TIP : to find out network throughput on a server, use "iftop" on linux.

(#) Can not backup to the server itself because it's running out of space…hence the backup (to move its data out, into another server)

Credit : http://matthew.mceachen.us/blog/faster-mysql-dumps-and-loads-with-tab-and-use-threads-1047.html