Hacker News new | past | comments | ask | show | jobs | submit login
Backup and Compress a MySQL Database With One Command (longren.io)
2 points by tlongren on Aug 4, 2014 | hide | past | web | favorite | 3 comments

Doing it that way will rape disk I/O on your server, much better use ZFS snapshot then just zfs send & pipe thru xz/bzip2 to send it remotely


   TS=$(date "+%Y%m%d-%H%M%S")
   zfs snapshot $SNAP
   zfs send $SNAP | pv | xz -z | ssh somewhere "cat > /mnt/snapshots/mysql-${TS}.xz"
   zfs destroy -Rv $SNAP
Assumes that your /var/lib/mysql is zpool/mysql ofc

I would say that the biggest risk with mysqldump is restore time.

If a significant portion of the buffer pool is in memory, it may actually dump quite fast. MySQL 5.5 and above also have a modified LRU algorithm so that tablescans (i.e. mysqldump) can only evict up to 3/8ths of the cache contents.

Awesome tip Nanzikambe! I'll update with your more disk I/O friendly solution.

Applications are open for YC Summer 2020

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact