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
¬¬datadir=/path/to/datadir/
• 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
Mysqldump
•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
• statements:
– 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 hwork.mysql.com 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
(checkpointing)
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
• Cross-Platform
– 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
• Compression
– Multi-level compression
– Save 70% or more of the storage required.
• Reliable
– 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)
– mysqldump
• 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
Mysqldump
• Advantages
– Good for small databases or tables
– Good assurance that database files are not corrupt
– Logical Backup – thus flexible and portable
• Disadvantages
– 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
• Advantages
– Rolling “snapshot”
– Quick Recovery - via failover
– Non-Blocking
– Works well in conjunction with other backup options
• Disadvantages
– Only latest “Point in Time” (point it time keeps moving forward)
– Not historical
– Not for archival purposes
– Doesn’t protect from “oops”
LVM Snapshots
• Advantages
– Quick
– Feature of Linux
– Good to use in conjunction with backups
• Disadvantages
– 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
• Advantages
– Physical Backup so Fast – esp. restores
– Flexible - many options
– Archival
– Scalable
– Consistent
– Supported
• Disadvantages
• 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]
[--only-known-file-types]
MY.CNF BACKUP-ROOT-DIR
mysqlbackup --apply-log [--use-memory=MB] [--uncompress]
[--ibbackup=IBBACKUP-BINARY] MY.CNF BACKUP-DIR
mysqlbackup --apply-log --incremental [--use-memory=MB] [--uncompress]
[--ibbackup=IBBACKUP-BINARY]
INCREMENTAL-BACKUP-MY.CNF FULL-BACKUP-MY.CNF
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
• Partial
– 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
Usage:
ibbackup [--incremental lsn]
[--sleep ms] [--suspend-at-end] [--compress [level]]
[--include regexp] my.cnf backup-my.cnf
or
ibbackup --apply-log
[--use-memory mb] [--uncompress]
backup-my.cnf
or
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
[mysqld]
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