Faster Logical Backup and Restores Using mydumpe

r

By

MySQL ConsultingTeam

About MyDBOPS

•MySQL Consulting. •MySQL Support.•Remote DBA Service.•Expert MySQL Solutions•MariaDB, Percona, Galera, TokuDB are supported too.

Agenda

• Logical Backups

• Backup Tools available

• Drawbacks

• Mydumper

• Myloader

Logical backups

• Plain text files - SQL Statements

• Can be remote

• Can be restored on different versions

• Sometimes is the only option

• Partial backup and restores

• Feed ETL processes

Backup Tools

mysqldump

• Native MySQL Client Program

• Can be piped to mysql client

• One Big Output File

• Lack of features for modern day problems

Drawbacks

• Single Threaded

• Lack of native compression

• Not possible to chunk the output file

• Consistent Online backup

mydumper

Maintained by Percona Currently

Launchpad Project

https://launchpad.net/mydumper

Release Notes

http://tinyurl.com/mydumper-091

mydumper - features

• Faster as is multithread

• Different files per table

• One row per line

• Can able to chunk based on rows, size

• Support to MariDB multi source replication

• TokuDB transactional support

mydumper - important options

• --threads

• --outputdir

• --rows, --chunk-filesize

• --compress

• --less-locking

• --kill-long-queries

mydumper - selective backups

• -B, --database

• -T, --tables-list

• -B db1

• -B db1 -T t1,t2

• -T db1.t1,db2.t1

• --regex=‘^((db1.|db2.))’

mydumper - improving times

--rows• Chunk tables by rows• Only works with INT fields indexes

--file-size-chunks• Chunk tables by data size• 1 dump thread but different files allowing parallel

restore• works with any table

mydumper - improving times

mydumper - improving times

Myloader

• Helps in restoring the backup for mydumper.

Restore

myloader -h x.x.x.x -d /backup/dir -o

Examples

• Full backup

mydumper -h x.x.x.x -–less-locking –-compress

-o /backup/dir

• Restore

myloader -h x.x.x.x -d /backup/dir -o

Examples

• Partial restore (version >= 0.9)

myloader -h x.x.x.x -d /backup/dir/restore/ -o

-B db

• Partial restore diff DB name (version >= 0.9)

myloader -h x.x.x.x -d /backup/dir/restore/ -o

-B db_new -s db

DEMO

How MyDBOPS can Help You ?

• MySQL Consulting

• MySQL Support

• Remote DBA services

• 24*7 MySQL Monitoring

Contact : mysqlsupport@mydbops.com

Thank You