Friday, 28 December 2012

MySQL Enterprise Backup Fast, Consistent, Online Backups.

The MySQL Enterprise Backup product performs hot backup operations for MySQL databases. The product is architected for efficient and reliable backups of tables created by the InnoDB storage engine. For completeness, it can also back up tables from MyISAM and other storage engines.
Hot backups are performed while the database is running. This type of backup does not block normal database operations, and it captures even changes that occur while the backup is happening. For these reasons, hot backups are desirable when your database “grows up” -- when the data is large enough that the backup takes significant time, and when your data is important enough to your business so that you must capture every last change, without taking your application, web site, or web service offline.
MySQL Enterprise Backup does a hot backup of all tables that use the InnoDB storage engine. For tables using MyISAM or other non-InnoDB storage engines, it does a “warm” backup, where the database continues to run, but those tables cannot be modified while being backed up. For efficient backup operations, you can designate InnoDB as the default storage engine for new tables, or convert existing tables to use the InnoDB storage engine.

Backing up MySQL data

When do you need backups?
What needs to be backed up?
When should backups be performed?
Where will the backups be stored?
How can backups be performed?

When Do You Need Backups?

Hardware failure
A system crash may cause some of the data in the databases to be lost
A hard¬disk failure will most certainly lead to lost data

User/Application failure
Accidental DROP TABLE or malformed DELETE FROM statements
Editing the table files with text editors, usually leading to corrupt tables.

What needs to be backed up? When?

Database content
for full backups
logical or physical backup

Log files
for incremental backups
point-in-time recovery

On a regular basis
Not during high usage peaks (off hours)
Static data can be backed up less frequently

The Data Directory

Databases and most log and status files are stored in the data directory by default

Default directory compiled into the server
/usr/local/mysql/data/ (tarball installation)
/var/lib/mysql (RPM packages)
Data directory location can be specified during server startup with

Find out the location by asking the server

mysql> SHOW VARIABLES like 'data%';

The Binary Log

Contains all SQL commands that change data
Also contains additional information about each query (e.g. query execution time)
Binary log is stored in an efficient binary format
Use mysqlbinlog to decipher the log contents
Log turned on with ¬¬log¬bin[=file_name]

Update logs are created in sequence
e.g. file_name¬bin.001, file_name¬bin.002, etc.

Binary log is transaction-compatible
mysqld creates binary log index file which contains names of the binary log files used

Managing the Binary Log

Purpose of the Binary Log:
Enable replication
Ease crash recovery

SHOW MASTER LOGS shows all binary log files residing on the server
FLUSH LOGS or restarting the server creates a new file
RESET MASTER deletes all binary log files
PURGE MASTER deletes all binary log files up to a certain point
Don't delete logs that slaves still need


•Dumps table structure and data into SQL statements
$ mysqldump mydb > mydb.20090413.sql

•Dumps individual tables or whole databases
•Default output from mysqldump consists of SQL
– CREATE TABLE statements for table structure
– INSERT statements for the data
•Can also be used directly as input into another mysqld server (without creating any files)
$ mysqldump opt world | mysql world

mysqldump hints
Use –single-transaction when backing up InnoDB tables
--lock-all-tables is useful for performing consistent MyISAM backups
But locks all DML statements until backup is done
--flush-logs flushes the binary log file

Recovering from Backups
Restoring tables to the state before a crash requires both the backup files and the binary log
> Restore the tables to the state they were at the time of the backup from the backup files
> Extract the queries issued between the backup and now from synchronized binary logs
If you are recovering data lost due to unwise queries, remember not to issue them again
DB Recovery = Last full backup & binlogs

MySQL Enterprise Backup--Features & Benefits

MySQL Backup Concerns

MySQL Enterprise Backup

Online Backup for InnoDB
Support for MyISAM (Read-only)
High Performance Backup & Restore
Compressed Backup
Full Backup
Incremental Backup
Partial Backups
Point in Time Recovery
Unlimited Database Size
Windows, Linux, Unix

Benefits: ==
Online “Hot” Backup (Non-blocking)
Reads and Writes to InnoDB
Reads for MyISAM tables
High Performance
Backup: >3x faster than mysqldump (export)
Restore: >10x than mysqldump recovery
Consistent Backups
Point in Time Recovery
Multi-level compression
Save 70% or more of the storage required.
Proven for 7+ Years
Scalable for Large Databases
No Database Size Limitations
Easy to automate
Easily integrate within various scheduling systems
Examples: cron, OSB scheduler, others

MySQL Enterprise Backup 3.5: New Features

Incremental backup
Support of InnoDB Barracuda file format 
Backup of compressed tables
Backup of partition files
Backup of in-memory database
with --exec-when-locked option
Adds mysql system tables to keep backup status, progress, and history

Database Backup Types-- Advantages & Disadvantages 

Hot Backup (online)
MySQL Enterprise Backup
Export/Import (portable copies – a logical backup)
Standby Copy (hot swap)
MySQL Replication
Cold Backup (offline)
Simple File Copies when server is shutdown
File System Volume Managers (snapshots)
LVM for example - create snapshot copy


Good for small databases or tables
Good assurance that database files are not corrupt 
Logical Backup – thus flexible and portable
Very slow restore times
Uses database processing cycles and resources
Not Online (requires Transaction or Locks on Tables in the database)
Not Incremental (requires a Full Backup every time)
Not Consistent (unless transaction is used)

MySQL Replication

Rolling “snapshot”
Quick Recovery - via failover
Works well in conjunction with other backup options

Only latest “Point in Time” (point it time keeps moving forward)
Not historical
Not for archival purposes
Doesn’t protect from “oops”   

LVM Snapshots

Feature of Linux
Good to use in conjunction with backups

It’s a snapshot 
Still need to make a backup copy – which is “full” in size
Performance degrades with each concurrent snapshot
Snapshots need to be released
Cross File System Limitations 

MySQL Enterprise Backup

Physical Backup so Fast – esp. restores
Flexible - many options 

Requires some planning

MySQL Enterprise Backup--How it Works

MySQL Enterprise Backup: Terms

•mysqlbackup : backup executable which includes InnoDB, MyISAM and other MySQL Data. mysqlbackup is a compatible replacement for the innobackup post 3.5.1 and includes additional features and capabilites
•ibbackup: finer grained raw innodb backup executable for innodb files alone
•binlog: contains database changes – eg DDL and DML
•LSN: Log Sequence Number – the unique monotonically increasing id for each change in the binlog
•Ibdata: system tablespace files
•.ibd: single table space file

mysqlbackup – Usage Syntax

mysqlbackup [--sleep=MS] [--compress[=LEVEL]] [--include=REGEXP]
            [--user=WORD][--password=WORD] [--port=PORT]
            [--socket=SOCKET] [--no-timestamp]
            [--ibbackup=IBBACKUP-BINARY] [--slave-info]
            [--backup-and-apply-log] [--databases=LIST]
            [--exec-when-locked="utility arg1 arg2 ..."]
            [--incremental --lsn=LSN]
mysqlbackup --apply-log [--use-memory=MB] [--uncompress]
            [--ibbackup=IBBACKUP-BINARY]  MY.CNF BACKUP-DIR
mysqlbackup --apply-log --incremental [--use-memory=MB] [--uncompress]
mysqlbackup --copy-back MY.CNF BACKUP-DIR

mysqlbackup (innobackup) Examples

Full Backup
mysqlbackup --user=dba --password=xyz --compress /etc/my.cnf /backups
Incremental Backup
The backup only contains changed data
mysqlbackup --incremental --lsn  /etc/my.cnf /incr-backup
The backup contains tables in test database that match the .ib.* regular expression.
mysqlbackup --include 'test\.ib.*' /etc/my.cnf /backups

Tips: InnoDB and MyISAM Backup

InnoDB tables are fully accessible during backup
Insert, Update & Delete
MyISAM tables cannot be updated during backup
Uses FLUSH TABLES WITH READ LOCK near the end of the backup
Works best if …
Wait for insert/update/delete transactions during MyISAM backup
Do not run long SELECT queries during the backup
MyISAM tables are small, thus copied quickly

ibbackup – Usage Syntax – strictly innodb

ibbackup [--incremental lsn]
         [--sleep ms] [--suspend-at-end] [--compress [level]]
         [--include regexp] my.cnf backup-my.cnf
ibbackup --apply-log
         [--use-memory mb] [--uncompress]
ibbackup --apply-log --incremental
         [--use-memory mb] [--uncompress]
         incremental-backup-my.cnf full-backup-my.cnf

Typical ibbackup Backup/Restore Steps

Take  Backup'
ibbackup  my.cnf  backup.cnf
Prepare backup for restore
ibbackup –apply-log  backup.cnf
Copy innodb backup files to mysqld datadir
Start mysqld
Performs recovery during startup

Examples : Take Backup details

Example my.cnf
  datadir = /production/var
  innodb_data_home_dir = /production/var
  innodb_log_group_home_dir = /production/var
  innodb_data_file_path =ibdata1:32M;ibdata2:32M:autoextend
  innodb_log_files_in_group = 3
  innodb_log_file_size = 32M
Example backup.cnf
   datadir = /backup
   innodb_log_group_home_dir = /backup
   innodb_data_home_dir = /backup
   innodb_data_file_path = ibdata1:32M;ibdata2:32M:autoextend
   innodb_log_files_in_group = 3
   innodb_log_file_size = 32M

No comments:

Post a Comment