Processing mysql dumps in hurry (convert single insert to extended insert)

Most time there’s little time, sometime there’s NO TIME!

A few days ago I had no time, and had to manipulate a badly exported database (2million+ single myisam insert statements) tuning mysqld was useless, insert delayed useless, increasing buffers useless… and so on… import was taking hours (many hours) on the target box due to impressively high disk io!

So I just fired up a vmware instance with 32gb of ram, 10gb hdd and 8cpu cores (of a xeon L56xx) and did everything in ram.
What was going to take hours on the target box took just 2minutes on the vmware instance…
Then I did a proper “mysqldump –opt” and imported it back into the target box in just 20seconds 😀

yum upgrade -y
wget -q -O - http://www.atomicorp.com/installers/atomic | sh
mkdir -p /var/lib/mysql && mount -v -t tmpfs -o size=24G none /var/lib/mysql
yum install mysql mysql-server -y
nano -w /etc/my.cnf

tune it up a little, in my case

thread_concurrency=16

was enough 🙂

service mysqld restart
mysql_secure_installation

and you are good to go!

import the bad export and after that export it making use of all the proper settings (extended queries, locking and so on) … –opt handles all of them by default 🙂

So yes… sometime I make use of “the cloud” too :O

PS: I do the same (storage on ramdisk) when I’ve to compile a linux kernel.