Backup & Recovery for Web Applications

Paddy Sreenivasan

paddy@zmanda.com

Feb 2007

Agenda

Where does the data reside?

Backup & Recovery requirements

Open source data protection tools

File system backup

Database backups

Solutions for web applications

Common Web Application components

PHP, Perl, Python, Ruby, JBOSS

MySQL

PostgreSQL

Apache

Linux/BSD

LAMP application

Example: Sugar CRM, MediaWiki, Blogs

Database

Web server

Operating System

Web Application Data

PHP, Perl, Python, Ruby, JBOSS

MySQL

PostgreSQL

Apache

Linux/BSD

Web application and configuration data

Example: Sugar CRM

Sugar CRM database in MySQL

Sugar CRM files in /var/www/html

Perl/PHP/Python/Ruby/JBOSS configuration files

MySQL/PostgreSQL configuration files

Apache configuration files

Web Application: Where does data reside?

Apache

Linux/BSD

MySQL/PostgreSQL

Database

File systems

Logical

Volumes

Disks

MySQL

PostgreSQL

PHP, Perl, Python,

Ruby, JBOSS

Web Application: Where does data reside?

PHP, Perl, Python, Ruby, JBOSS

MySQL

PostgreSQL

Apache

Linux/BSD

MySQL/PostgreSQL

Database

File systems

Logical

Volumes

Disks

Database

Backups

Filesystem

Backups

Web application characteristics

Web applications can have minimal down time

24 x 7 x 365 applications

Run on multiple servers and likely to be distributed over multiple sites

Web applications usually run at managed hosted service providers

Application data is stored in a database that might be replicated or clustered for load balancing/HA

Filesystem data usually contains configuration files

Backup & Recovery is a “Must Have” for Administrators

Loss of data can be catastrophic, resulting in lost revenues, lost customers and lost reputations

Common reasons for loss of data

Hardware and software failures

User error Example: accidental dropping of a table

Damaged databases because of improper shutdown or power failure

Common ways to protect against data loss

Transactional logging supported by databases

Replication (including snapshots)

Backup

Backup requirements

Consistent backup of live applications with minimal impact to users

Versatile

support for a variety of web application configurations

scale up and scale out

backup of local or remote application servers

Granular Recovery Point Objective

System administrators should be able to recover to a particular point back in time

Often time an administrator wants to go back to a particular database event

Enterprise management functionality

Scheduler

Backup policy

Monitoring and reporting

Flexible, easy to use and secure

Open and defined data formats for backup images

Open source data protection methods

Use RAID or Volume manager mirroring such as LVM

Snapshots (Hardware, Software)

Application consistency

Crash consistency

Replication (synchronous, asynchronous) such as DRBD

Backups of file system and database for recovery and archival needs

Data protection comparison

Application downtime

Implementation cost

Synchronous

replication

Tape backup

Snapshots

Network based replication (DRBD)

Block device synchronous replication using LAN

Requires cluster membership

R/W access on the primary node only.

Disconnect DRBD on secondary node

Mount read only

Backup

Connect DRBD

DRBD+ provides asynchronous replication.

Expensive solution for Backup only.

Operator errors/security flaws also get replicated!!!

Logical Volume Manager (LVM 2)

Read/Write snapshots

File system consistent snapshots with 2.6 kernel

Creating a snapshot volume for backups of size 50MB

# lvcreate -L50M -s -n mysqlbackup /dev/mysqlvolume

Remove logical volume after backup

# lvremove /dev/mysqlvolume

File system snapshots

Journaled file systems (ext3fs, XFS, JFS, ReiserFS, GFS)

Data and meta-data journaling does not require snapshots

File system snapshots

XFS (xfs_freeze -f and -u options)

GFS (gfs_tool freeze/unfreeze)

Backup steps

Freeze file system

Create a volume manager snapshot

Thaw file system

Filesystem backups

Filesystem archival tools such tar, star, dump

rsync based tools

rsnapshot

rdiff-backup

Open source network backup tools

Amanda

Backuppc

Bacula

Commercial network backup tools

Amanda features

Consistent backup window

Tries to backup same amount of data every run

Supports multiple backup levels

Uses native platform tools

GNU tar/dump/star for backup

gzip/bzip2 for compression

gpg/aespipe tools for data encryption

Media format – IEEE standard pax format

Supports tapes, disks, changers, optical media, RAIT

Network load balancing

Amanda data recovery

Amanda stores all media, file information in a database

The database can be browsed using Amanda commands

Data in the media is stored in native format and can be recovered using OS tools without Amanda commands

Backup images spanning multiple media can also be restored using OS tools

Logical backup is good for portability

Logical backup contains statements that can re-construct the database schema and contents

Example: mysqldump, pg_dump, pg_dumpall

Advantages

Disadvantages

Works for all MySQL storage engines except NDB (clustering) and PostgreSQL

Allows remote backups

Can be restored to any platform architecture and even to another database

Warm backups

Slow, especially for restores

Backup size can be larger than the database

MySQL full backup - mysqldump

Online logical backup of the database.

Obtains READ LOCK on the tables followed by FLUSH TABLES to flush active indexes to the disk.

mysqldump [options] db_name [tables]

mysqldump [options] --databases db_name1 [db_name2 db_name3...]

mysqldump [options] --all-databases

Raw backup is fast

Raw backup provides a binary copy of the database

Example: mysqlhotcopy, MySQL cluster backup,

PostgreSQL online backup

Advantages

Disadvantages

Faster than logical backups

Size of the copy is the same as the database size

Better database size scalability compared to logical backups

Warm backups (PostgreSQL online backup is hot backup)

Can be restored ONLY to the same version of database server on the same platform as the original data

PostgreSQL Online Backup

Archiving Write Ahead Logs (WAL)

Set up archiving in postgresql.conf

Any archive command can be specified

archive_command = \

'cp -i %p /archivedir/%f</dev/null'

WAL archives may be removed or recycled by PostgreSQL

Backup process

SELECT pg_start_backup('backup_label');

Do backup of cluster data directory and tablespaces

SELECT pg_stop_backup('backup_label');

PostgreSQL Online Backup Recovery

Stop postmaster (PostgreSQL database server)

Restore all database files

Create recovery.conf in the cluster data directory

recovery.conf should contain restore_command to restore archived WAL files.

Point in time recovery or recovery to a transaction id or to a timeline can be specified.

Start postmaster

LVM snapshots are almost instantaneous

Advantages

Disadvantages

Database files must reside on LVM2 logical volume

Works best for MySQL transactional engines such as InnoDB and PostgreSQL DB.

Provides hot backup for InnoDB and PostgreSQL DB. Warm backups for others.

Scalable

Almost instantaneous implementation of raw backup

Works best for filesystems that support freeze operations such as XFS and VxFS

Disk space for logical volume snapshots

Advantages

Disadvantages

Replication files – master.info, relay logs, relay index have to be saved

Can be used for disaster recovery also

Replication slave can be used for load balancing and HA

Hot backup

Not scalable as replication slave is always behind the master

Expensive (additional server, more network bandwidth)

MySQL replication provides hot backup

Incremental database backups

All changes to database data files are recorded in the log.

MySQL binary logs

Required for MySQL replication

PostgreSQL Write ahead logs (WALs)

Required for database crash recovery

Log files should on a different disk from database data.

Improved performance

More availability

Zmanda Recovery Manager for MySQL

Schedule full and incremental backups of MySQL database

Logical backups for portability and raw backups for faster restores

Perform backup that is the best match for used storage engine and MySQL configuration

Monitor and report about backups, e-mail or RSS feed reports

Backup compression and encryption

Enforcement of site or application specific backup policy

Plug-in architecture

Example: ZRM for MySQL Point in time recovery

Weekly full backups

Daily Incremental backups

Local backups of MySQL database

How do you recover from operator errors?

Example: Browsing incremental dumps

------------------------------------------------------------

Log filename | Log Position | Timestamp | Event Type | Event

------------------------------------------------------------

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 98 | 07-01-12 17:55:56 | |

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 507 | 07-01-12 17:55:56 | Execute_load_query | use netflix; LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-12-1' INTO TABLE `MovieID` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 728 | 07-01-12 17:55:56 | Xid = 691 | COMMIT;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 755 | 07-01-12 18:56:16 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID‘= 17771;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 876 | 07-01-12 18:56:16 | Xid = 695 | COMMIT;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 903 | 07-01-12 18:56:16 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17772;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 1024 | 07-01-12 18:56:16 | Xid = 696 | COMMIT;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 1051 | 07-01-12 18:56:16 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17773;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 1791 | 07-01-12 18:56:16 | Query | drop table MovieID;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 1875 | 07-01-12 18:56:28 | Rotate to mysql-bin.000006 pos: 4 |

------------------------------------------------------------

Example: Operator error!

------------------------------------------------------------

Log filename | Log Position | Timestamp | Event Type | Event

------------------------------------------------------------

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 98 | 07-01-12 17:55:56 | |

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 507 | 07-01-12 17:55:56 | Execute_load_query | use netflix; LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-12-1' INTO TABLE `MovieID` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 728 | 07-01-12 17:55:56 | Xid = 691 | COMMIT;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 755 | 07-01-12 18:56:16 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID‘= 17771;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 876 | 07-01-12 18:56:16 | Xid = 695 | COMMIT;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 903 | 07-01-12 18:56:16 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17772;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 1024 | 07-01-12 18:56:16 | Xid = 696 | COMMIT;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 1051 | 07-01-12 18:56:16 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17773;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 1791 | 07-01-12 18:56:16 | Query | drop table MovieID;

/var/lib/mysql-zrm/netflix/20070111175628/mysql-bin.000005 | 1875 | 07-01-12 18:56:28 | Rotate to mysql-bin.000006 pos: 4 |

------------------------------------------------------------

Example: Point in time recovery

MySQL transactions

Row deletion

x x x x x x x

Jan 12 2007

Jan 11 2007

Full
backup

Incremental
backup

Example: Recovery

Restore the database from Jan 11 full backup.

Selective restoration to Jan 12 18:50 from Jan 12 incremental backup.

# mysql-zrm --action restore --backup-set netflix –stop-\ datetime “20070112180000” --source-directory /var/lib/mysql-\ zrm/netflix/20070112175628/

INFO: Incremental restore done for database netflix

INFO: Shutting down MySQL

INFO: Restore done in 3 seconds.

MySQL server has been shutdown. Please restart after verification.

LAMP application backup – single server

MySQL

Filesystem

Volumes

Disks

Filesystem

Amanda Server

M

Linux

A

M

P/J

MySQL backup using ZRM

Amanda

client

LAMP application data backup – multiple servers

ZRM for MySQL

Amanda Server

M

Linux

A

M

P

M

Linux

A

M

P

M

Linux

A

M

P

M

Linux

A

M

P

Summary

Backup and recovery plan for web application is critical for the business

Consistent regular backups with minimal impact to users

Open backup image format that can be restored using platform tools

Secure backup communication and data at rest security

Recovery to any point in time

All these requirements can be accomplished with open source tools!

More information

Amanda backups http://amanda.zmanda.com

Rsync based backups http://www.rsnapshot.org/

MySQL backup http://forge.mysql.com/wiki/OnlineBackup

PostgreSQL backup http://www.postgresql.org/

ZRM for MySQL http://www.zmanda.com/backup-mysql.html

“Data protection for LAMP applications” white paper http://network.zmanda.com/documentation.php

Slide 1

Dmitri Joukovski

2005-10-29T18:17:26