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.