Using tmpfs for mysqldump & mysqlimport

For large database dump & restore, its better to use tmpfs – a filesystem created in memory. Since its a RAM based filesystem, it will be much faster as compared to harddisk-based storage.

Please note that tmpfs is volatile and generally limited in size. If your goal is to create permanent backup, do NOT use tmpfs.

Also check available free space before you proceed using a command like below:

df -H

Sample output:

Filesystem      Size  Used Avail Use% Mounted on
/dev/md0        463G  365G   75G  83% /
udev             17G   13k   17G   1% /dev
tmpfs           6.8G  525M  6.3G   8% /run
none            5.3M     0  5.3M   0% /run/lock
none             17G     0   17G   0% /run/shm
cgroup           17G     0   17G   0% /sys/fs/cgroup

Now…

Lets consider case where we need to copy DB_NAME_OLD to DB_NAME_NEW

Backup

Use following to backup mysql database, if target database is different you

mysqldump --single-transaction --no-autocommit --no-create-db --skip-comments DB_NAME_OLD > /run/db.sql

Restore

For same database

mysql DB_NAME_NEW < /run/db.sql

Usage

We use this to duplicate databases. Also when converting data from one character-set encoding to another.