MySQL Backup And Recovery Essentials

MySQL

Backup & Recovery

Essentials

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Ronald Bradford

http://ronaldbradford.com

RMOUG QEW

Denver, Colorado - 2012.05

EffectiveMySQL.com - Performance, Scalability & Business Continuity

"No one cares if you can

backup, only that you

can restore."

Adapted from W. Curtis Preston - Backup & Recovery (O'Reilly 2009)

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Agenda

Backup Options

Tools available

Restore Options

Necessary Nomenclature

Business Requirements

Slides at http://j.mp/EM-BandR

EffectiveMySQL.com - Performance, Scalability & Business Continuity

ABOUT THE AUTHOR

Provide independent consulting - Available NOW

Top invited MySQL speaker

All time top MySQL blogger

Published Author (3++ books)

Oracle ACE Director

MySQL community member of the year

23 years of RDBMS experience,13 years with MySQL

MySQL Inc (2006-2008)

Oracle Corporation (1996-1999)

Ronald Bradford

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Four Step

Overview

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Basics

Static Consistent Backup

Master Binary Logs

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Recovery Basics

Static Restore

Point in Time Recovery

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Verification

Necessary at EVERY step

Commands complete without error

No errors in logs

Results match expectations

Approximate

Precise

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Testing

“Testing is about trying to

break your software, not

checking that it works!”

Ronald Bradford, circa 2006

EffectiveMySQL.com - Performance, Scalability & Business Continuity

"MySQL has no single

unbreakable backup

solution [yet]."

EffectiveMySQL.com - Performance, Scalability & Business Continuity

B&R Options

EffectiveMySQL.com - Performance, Scalability & Business Continuity

B&R Options

Included

mysqldump

OS ﬁlecopy

OS Speciﬁc

ﬁlesystem snapshot

Open Source

XtraBackup

mydumper

Commercial

MEB

EffectiveMySQL.com - Performance, Scalability & Business Continuity

EXAMPLE DB

SELECT SUM(data_length+index_length)/1024/1024 AS total_mb,

SUM(data_length)/1024/1024 AS data_mb,

SUM(index_length)/1024/1024 AS index_mb,

COUNT(DISTINCT table_schema) AS schema_cnt,

COUNT(*) AS tables,

CURDATE() AS today,

VERSION()

FROM information_schema.tables\G

*************************** 1. row ***************************

total_mb: 5344.63

data_mb: 4545.49

index_mb: 799.13

schema_cnt: 7

tables: 103

today: 2012-04-03

VERSION(): 5.1.61-0ubuntu0.11.10.1-log

Available on GitHub with Effective

MySQL: Backup and Recovery Book

TIP: Your daily veriﬁcation

step should include this

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

Pros

Included with MySQL server

ASCII output

Remote capabilities

mysqldump

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

Cons

Single threaded

Locking by default (*)

Slow restore for large DBs

mysqldump

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

Recommendations

Great for 5-10GB

No locking with InnoDB (*)

Essential for schema objects

mysqldump

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

$ time mysqldump --all-databases > /mysql/backup/

dump1.sql

real 1m31.631s

user 1m12.533s

sys 0m10.893s

$ echo $?

$ ls -lh /mysql/backup/dump1.sql

-rw-rw-r-- 1 uid gid 2.9G 2012-04-03 03:04 /mysql/

backup/dump1.sql

mysqldump USAGE

TIP: Always time and record long

running processes for veriﬁcation

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Restore Options

$ time mysql –u[user] -p < dump1.sql > dump1.out 2>&1

real 14m13.817s

user 1m6.960s

sys 0m1.516s

$ echo $?

$ ls -l dump1.out

-rw-rw-r-- 1 uid gid 0 2012-04-08 04:07 dump1.out

mysqldump USAGE

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

$ mysqldump --all-databases --no-data \

> /mysql/backup/schema.sql

$ mysqldump --all-databases --no-data --no-create-info \

--events --routines > /mysql/backup/objects.sql

mysqldump USAGE

TIP: Include daily dumps of

database objects

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

$ mysqldump --all-databases --no-data --no-create-info \

--events --routines > /mysql/backup/objects.sql

mysqldump: Got error: 1142: SELECT,LOCK TABL command

denied to user 'root'@'localhost' for table

'cond_instances' when using LOCK TABLES

$ echo $?

VERIFICATION

TIP: Error checking is

essential and easy to implement

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

Pros

No software needed

Consistent

File Copy

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

Cons

MySQL unavailable

Not consistent (**)

Time to warm server caches

Restore must match conﬁguration

Must backup right ﬁles

File Copy

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

Recommendations

Great for slaves

When access disabled

File Copy

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

$ sudo service mysql stop # Ubuntu

$ sudo /etc/init.d/mysqld stop # RHEL

$ mkdir /mysql/backup/cp1

$ cp -r /var/lib/mysql /mysql/backup/cp1

$ echo $?

$ cp /etc/mysql/my.cnf /mysql/backup/cp1

# Other directories?

$ sudo service mysql start

File Copy USAGE

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

Pros

Fastest

Database agnostic (*)

SNAPSHOT

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

Cons

Must be pre-conﬁgured (LVM)

Extra Disk I/O

Inconsistent (*)

FLUSH TABLES duration

SNAPSHOT

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

$ sudo su -

$ sync ; lvcreate -L1G -s -n dbsnapshot /dev/db/p0

$ mkdir -p /mnt/dbsnapshot

$ mount -o ro /dev/db/dbsnapshot /mnt/dbsnapshot

$ du -sh /mnt/dbsnapshot

$ ls -al /mnt/dbsnapshot

$ mkdir /mysql/backup/snapshot1

$ cp -r /mnt/dbsnapshot/* /mysql/backup/snapshot1

$ sudo su -

$ mylvmbackup

SNAPSHOT USAGE

http://effectiveMySQL.com/article/conﬁguring-a-new-hard-drive-for-lvm

http://effectiveMySQL.com/article/using-mysql-with-lvm

http://www.lenzg.net/mylvmbackup/

TIP: mylvmbackup does all the

hard work

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

Pros

Non-blocking (InnoDB)

Open Source

Supports incremental, compression etc

XtraBACKUP

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

Cons

Blocking for non-InnoDB tables

XtraBACKUP

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

$ time innobackupex --defaults-file=/etc/mysql/my.cnf \

--user=root --password=passwd --no-timestamp \

/mysql/backup/xtrabackup/first

real 2m30.667s

user 0m21.933s

sys 0m14.713s

$ echo $?

XtraBACKUP USAGE

EffectiveMySQL.com - Performance, Scalability & Business Continuity

restore Options

$ sudo su – mysql

$ sudo service mysql stop # Ubuntu

$ sudo rm -rf /var/lib/mysql # data directory

$ sudo mkdir -m /var/lib/mysql

$ sudo chown mysql:mysql /var/lib/mysql

$ time innobackupex --copy-back \

/mysql/backup/xtrabackup/first/

$ echo $?

XtraBACKUP USAGE

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

Pros

Non Blocking (InnoDB)

Commercial Support

Supports incremental, compression etc

media management software (MMS)

Oracle Secure Backup (OSB) - SBT

MySQL ENTERPRISE BACKUP (MEB)

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

Cons

Warm for non-InnoDB

Cost

MySQL ENTERPRISE BACKUP (MEB)

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

$ sudo su - mysql

$ time /opt/meb/bin/mysqlbackup --user=root \

--password=passwd \

--backup-dir=/mysql/backup/meb/first \

backup-and-apply-log

real 3m30.879s

user 0m17.081s

sys 0m14.565s

$ echo $?

$ du -sh /mysql/backup/meb/first

5.6G /mysql/backup/meb/first

$ ls -lh /mysql/backup/meb/first/datadir/ibd*

-rw-rw-r-- 1 uid gid 5.4G 2012-04-03 03:25 /mysql/

backup/meb/first/datadir/ibdata1

MEB USAGE

EffectiveMySQL.com - Performance, Scalability & Business Continuity

RESTORE Options

$ sudo su – mysql

$ sudo service mysql stop # Ubuntu

$ sudo rm -rf /var/lib/mysql # data directory

$ sudo mkdir -m /var/lib/mysql

$ sudo chown mysql:mysql /var/lib/mysql

$ time /opt/meb/bin/mysqlbackup \

--defaults-file=/etc/mysql/my.cnf \

--backup-dir=/mysql/backup/meb/first \

--innodb-log-files-in-group=2 copy-back

$ echo $?

MEB USAGE

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Point in Time

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Binary Logs

Possible with Binary Logs

#my.cnf

[mysqld]

log-bin=mysql-bin

expire-logs-days=5

WARNING: If you care

about your data, enable

binary logging

mysql> SHOW BINARY LOGS;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.019662 | 104857736 |

| mysql-bin.019663 | 104857699 |

| mysql-bin.019664 | 104857850 |

$ ls -ltr /var/log/mysql | tail

-rw-rw---- 1 mysql adm 104857848 2011-09-04 22:00 mysql-bin.020607

-rw-rw---- 1 mysql adm 104857953 2011-09-04 22:08 mysql-bin.020608

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Options

rsync

Slave --log-slave-updates

DRBD

mysqlbinlog --read-from-remote-server

(New in 5.6)

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup needs

Static Backup

Useless without Binary Log position

mysql> SHOW MASTER STATUS\G

File: mysql-bin.020616

Position: 63395562

Binlog_Do_DB:

Binlog_Ignore_DB:

WARNING: Can work on

slave and provide the wrong

information

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Backup Needs

$ mysqldump --master-data (or --dump-slave)

CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER

='repl',MASTER_PASSWORD='******', MASTER_LOG_FILE=

'mysql-bin.000146', MASTER_LOG_POS=810715371;

$ cat xtrabackup_binlog_info

mysql-bin.000001 37522

$ grep binlog meta/backup_variables.txt

binlog_position=mysql-bin.000017:5555

MEB

Xtrabackup

$ cat export-20120407-230027/metadata

Log: mysql-bin.000017 Pos: 8328

mydumper

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Restore Options

mysqlbinlog

MySQL replication

$ mysqlbinlog /path/to/mysql-bin.000146 \

--start-position=810715371 | mysql -uroot -p

$ mysqlbinlog /path/to/mysql-bin.000147 \

/path/to/mysql-bin.00148 ... etc | mysql -uroot -p

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Advanced

Features

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Advanced

Compression

Incremental

Remote

Parallel

Partial

For another presentation

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Compression

Utility

Comp (s)

Dec (s)

Saving

lzo (-3)

48%

pigz (-1)

64%

pigz [-6]

105

69%

gzip [-6]

232

69%

bzip2

540

175

74%

lzo (-9)

20m

58%

lzma

58m

180

78%

59m

160

78%

Depends greatly on data types

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Terminology

MTTD

MTTR

RPO

RDO

SLA

Determining business

priorities is important for any

EffectiveMySQL.com - Performance, Scalability & Business Continuity

Conclusion

EffectiveMySQL.com - Performance, Scalability & Business Continuity

CONCLUsiON

This is an introduction

Advance features are important

Best option depends

Replication is important in your strategy

Test, Test, Test. - “Chaos Monkey”

Slides at http://j.mp/EM-BandR

EffectiveMySQL.com - Performance, Scalability & Business Continuity

PRESENTATIONS

http://ronaldbradford.com/mysql-presentations/

http://effectivemysql.com/presentation/