mysqlmybackup.sh – multi feature MySQL database backup and restore script with Percona XtraBackup & Mydumper tool support
./mysqlmybackup.sh mydumper
Mydumper is a very fast multi-threaded MySQL data backup and restore tool. How fast you may ask ? I did a quick test of a couple of databases totalling 550MB in size and ran a mysqldump –all-databases gzip piped dump and compared it to a default 4 threads mydumper backup run and mydumper was 3x times faster!. The test was done on virtualised Xeon W3550 3.06Ghz 1.5GB SATA disk based CentOS 5.5 64bit server but with only 2 cpu cores allocated. So it could potentially be much faster with more cpu cores/threads. Mydumper authors claim up to 10x times faster than mysqldump!.
- mydumper = 1,008MB per minute that’s ~59GB per hour!
- mysqldump = 315MB per minute that’s ~18.5GB per hour
I’ve only tested mydumper for a couple of days so still need to do more tests but it looks promising.
Note: for CentOS 5.5 users with mydumper v0.50, mydumper v0.50 has compilation bug and you need to grab the mydumper v0.50 rev 101 and higher trunk release which currently as of now is only available via the trunk branch on their launchpad site which you can grab through bazaar (bzr) at lp:mydumper. Update: mydumper v0.51 was released with various bug fixes – including the compile bug.
Official description:
Mydumper (aka. MySQL Data Dumper) is a high-performance multi-threaded backup (and restore) toolset for MySQL and Drizzle. The main developers originally worked as Support Engineers at MySQL (one has moved to Facebook and another to SkySQL) and this is how they would envisage mysqldump based on years of user feedback.
Mydumper features
- Lightweight C source
- Up to 10x faster dumps compared to mysqldump
- Consistent snapshots for transactional and non-transactional tables (in 0.2.2 onwards)
- File compression on-the-fly
- Binary log dumps
- Multi-threaded restore utility (in 0.2.1 onwards)
- Daemon mode for timed snapshots and continuous binary logs (in 0.5.0 onwards)
- Open Source! (GNU GPLv3)
- Mydumper is still under active development but is well tested/used in production on some large installations.
