Notes from Online MySQL Backup in 6.0

Here is the notes from MySQL online backup in 6.0 session by Dr. Charles A. Bell, the Author of MySQL Expert book.

• Introduction
  • SQL Driven, run from any client
  • backup to localhost
  • new security privileges for backup/restore
  • blocking restore
  • non-blocking backup for Innodb, Falcon etc
• Overview
  • protection and restore
  • basic functionality of backup and restore available now
  • db level -> table, views, sps, functions, … are supported
  • 6.0.5 has it already, try it
  • source code is another way to look into it
• Design
  • ent level consistency between engines
  • default driver for engines that don’t support backup
  • consistent snapshot
  • storage engine specific backup methods
  • mix logical and physical backup
  • streaming backup data
• Future
  • pluggable, modular architecture
  • versioning for backward compatibility
• State of development
  • ent level consistence
  • default blocking
  • consistent
  • kernel synchronization algorithm
  • backup driver API
  • restore driver API
• Implemented
  • storage on server host
  • dx transfer protocol already built
  • streaming format
  • MyISAM naive
  • Table Space support for Falcon
  • NoData engine backup (blackhole, merge etc)
  • synchronization with binary log for point-in-time recovery
• Compare with existing solutions
  1. non-blocking tools
     • mysqldump with single transaction
     • cluster backup
     • replication
     • InnoDB-hot backup (commercial)
  2. blocking tools
     • mysqldump
     • mysqlhotcopy
     • native file system copy
     • select .. INTO OUTFILE
  3. commercial
     • zmanda
     • bakbone
     • other
• Architecture:
  • backup kernel .. part of kernel
  • backup engine .. backup driver and restore driver
  • default backup or restore driver
  • native backup or restore
• Interfaces
  • sql control interface (simple sql statements)
  • backup engine interface
• Backup Kernel responsibility
  • execute backup and restore sql statements
  • backup / restore metadata
  • initialize and co-ordinate work of backup/restore
  • write/read backup archive
• Capabilities
  • backup database {db_list} TO {image}
  • restore from { image }
  • progress logs (currently tables, online_backup, online_backup_progress)
• Future plans
  • cover all storage engine
  • plugin architecture
  • backup to other media
  • std tool
  • full-server, database and enhanced
  • incremental
  • Limitations
    • single threaded
    • synchronous
    • no optimization at this time, simple go and do it.
    • integrated schema and data (no way to split at this time)
    • NDB cluser is not supported, use native