Backing up and recovering MySQL databases

1. Backing up and recovering MySQL databases MySQL performace masterclass, Kyiv 2009 Aleksandr.Kuzminsky@Percona.com Percona Inc. http://MySQLPerformanceBlog.com

2. -2- Agenda • Taking MySQL backups • Offline backups • Online backups • XtraBackup – the tool for backups and not only backups • Recovery lost/corrupted MySQL databases • How MySQL stores data in InnoDB/MyISAM • When recovery is possible • InnoDB recovery tool • MyISAM recovery tool • How make recovery easier

3. 1. Taking MySQL Backups

4. 1.a. Offline backups. Copy from “main” server server# Backup server# /etc/init.d/mysql stop server# cp -R /var/lib/mysql /backups server# /etc/init.d/mysql start Restore server# /etc/init.d/mysql stop server# cp -R /backups/mysql /var/lib/mysql server# /etc/init.d/mysql start Backup and Recovery MySQL databases

5. 1.a. Offline backups. Copy from slave server Master# Slave# • Backup: • Restore: Slave# /etc/init.d/mysql stop Slave# cp -R /var/lib/mysql /backups Slave# /etc/init.d/mysql start Master# scp –r slave:/backups/mysql/* /var/lib/mysql/ Master# /etc/init.d/mysql start Reinstall replication Backup and Recovery MySQL databases

6. 1.b. Online backups. mysqldump Comes with MySQL distribution Supports all storage engines Restore takes ages Backup: server# mysqldump –-opt > /backups/mysql.sql Restore: server# mysql < /backups/mysql.sql Backup and Recovery MySQL databases

7. 1.b. Online backups. mysqlhotcopy Comes with MySQL distribution As fast as disk reads/writes Only MyISAM Backup: server# mysqlhotcopy -u root -p Password test /tmp/mysql Restore: server# cp –R /tmp/mysql/* /var/lib/mysql/ server# /etc/init.d/mysql start Backup and Recovery MySQL databases

8. 1.b. Online backups. InnoDB Hot Backup Handles InnoDB Fast Neither Free nor OpenSource The last major release is Jun 2007 Some platforms aren’t supported (Windows limited support, FreeBSD amd64) http://www.innodb.com/products/hot-backup/ Backup and Recovery MySQL databases

9. 1.b. Online backups. Filesystem Snapshot • Mylvmbackup (http://www.lenzg.net/mylvmbackup/) • Virtual Machines snapshots Backup and Recovery MySQL databases

10. 1.c. XtraBackup. Features(1/2) • Hot Backup of InnoDB, XtraDB tables • Warm Backup of MyISAM • Free and OpenSource • Support of incremental backups • Stream backups Backup and Recovery MySQL databases

11. 1.c. XtraBackup. Features(2/2) • Export InnoDB tables • Index statistics • Partial backups with innodb_file_per_table Backup and Recovery MySQL databases

12. 1.c. XtraBackup. How it works (backup) Takes a snapshot of InnoDB files Writes changes after taking the snapshot to a log_file backup/ibdata backup/xtrabackup_logfile Backup and Recovery MySQL databases

13. 1.c. XtraBackup. How it works (restore) Apply the log file on an InnoDB tablespace Copy InnoDB (MyISAM) files back backup/xtrabackup_logfile backup/ibdata Backup and Recovery MySQL databases

14. 1.c. XtraBackup. Advanced backup actions and Things beyond backup Export of InnoDB tables (moving tables between servers) Statistics Setting up a new slave from a backup in replication Incremental backup Stream backups Backup and Recovery MySQL databases

15. 1.c. XtraBackup. Where it lives Debian packages deb http://repo.percona.com/apt lenny main deb-src http://repo.percona.com/apt lenny main RPMs [percona] name=CentOS-$releasever - Percona baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/ gpgcheck=0 Other OS-es http://www.percona.com/mysql/xtrabackup Development https://launchpad.net/percona-xtrabackup Backup and Recovery MySQL databases

16. 2. Recovery MySQL Tables

17. Backup and Recovery MySQL databases How MySQL stores data in InnoDB/MyISAM • InnoDB A table space (ibdata1) or file per table (.ibd) InnoDB files are split into pages (default 16k) A table data are stored in a clustered index PRIMARY There are other indices. If the key is (f1, f2) it is stored as (f1, f2, PK) Every index is identified by index_id

18. Backup and Recovery MySQL databases How MySQL stores data in InnoDB/MyISAM • Page identifier index_id mysql> CREATE TABLE innodb_table_monitor(x int) engine=innodb Error log:     TABLE: name test/site_folders, id 0 119, columns 9, indexes 1, appr.rows 1      COLUMNS: id: DATA_INT len 4 prec 0; name: type 12 len 765 prec 0; sites_count: DATA_INT len 4 prec 0;                           created_at: DATA_INT len 8 prec 0; updated_at: DATA_INT len 8 prec 0;                    DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0;                    DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0;           INDEX: name PRIMARY, id 0 254, fields 1/7, type 3           root page 271, appr.key vals 1, leaf pages 1, size pages 1           FIELDS:  id DB_TRX_ID DB_ROLL_PTR name sites_count created_at updated_at

19. Backup and Recovery MySQL databases How MySQL stores data in InnoDB/MyISAM • SYS_TABLES and SYS_INDEXES • Always REDUNDANT format! CREATE TABLE `SYS_TABLES` ( `NAME` varchar(255) NOT NULL default '', `ID` bigint(20) unsigned NOT NULL default '0', `N_COLS` int(10) unsigned default NULL, `TYPE` int(10) unsigned default NULL, `MIX_ID` bigint(20) unsigned default NULL, `MIX_LEN` int(10) unsigned default NULL, `CLUSTER_NAME` varchar(255) default NULL, `SPACE` int(10) unsigned default NULL, PRIMARY KEY (`NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `SYS_INDEXES` ( `TABLE_ID` bigint(20) unsigned NOT NULL default '0', `ID` bigint(20) unsigned NOT NULL default '0', `NAME` varchar(120) default NULL, `N_FIELDS` int(10) unsigned default NULL, `TYPE` int(10) unsigned default NULL, `SPACE` int(10) unsigned default NULL, `PAGE_NO` int(10) unsigned default NULL, PRIMARY KEY (`TABLE_ID`,`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 index_id = 0-3 index_id = 0-1

20. Backup and Recovery MySQL databases How MySQL stores data in InnoDB/MyISAM Example: SYS_TABLES SYS_TABLES "archive19/9299_msg_store" 40694 8 1 0 0 NULL 0 SYS_TABLES "archive19/9299_msg_store" 40694 8 1 0 0 NULL 0 SYS_TABLES "archive19/9299_msg_store" 40694 8 1 0 0 NULL 0 SYS_INDEXES SYS_INDEXES 40694 196389 "PRIMARY" 2 3 0 21031026 SYS_INDEXES 40694 196390 "msg_hash" 1 0 0 21031028

21. Backup and Recovery MySQL databases InnoDB page format (REDUNDANT)

22. Backup and Recovery MySQL databases InnoDB page format (REDUNDANT) Fil Header

23. Backup and Recovery MySQL databases InnoDB page format (REDUNDANT) Page Header

24. Backup and Recovery MySQL databases InnoDB page format (REDUNDANT) User record format Records in a page is a unidirectional list, sorted by PK Infinum record is the first dummy record in the list Supremum is the last dummy record in the list

25. Backup and Recovery MySQL databases InnoDB page format (REDUNDANT) Field start offset • When The Size Of Each Offset Is One Byte • 1 bit = NULL, = NULL • 7 bits = the actual offset, a number between 0 and 127 • When The Size Of Each Offset Is Two Bytes • 1 bit = NULL, = NULL • 1 bit = 0 if field is on same page as offset, = 1 if field and offset are on different pages • 14 bits = the actual offset, a number between 0 and 16383

26. Backup and Recovery MySQL databases InnoDB page format (REDUNDANT) Field start offset • When The Size Of Each Offset Is One Byte • 1 bit = NULL, = NULL • 7 bits = the actual offset, a number between 0 and 127 • When The Size Of Each Offset Is Two Bytes • 1 bit = NULL, = NULL • 1 bit = 0 if field is on same page as offset, = 1 if field and offset are on different pages • 14 bits = the actual offset, a number between 0 and 16383

27. Backup and Recovery MySQL databases InnoDB page format (REDUNDANT) Extra bytes

28. Backup and Recovery MySQL databases InnoDB page format Changes to COMPACT format 5 Extra bytes No offsets for fixed size fields

29. Backup and Recovery MySQL databases How MySQL stores data in InnoDB/MyISAM • MyISAM • Table definition is stored in .frm file • Table indices are stored in .MYI file • Table data are stored in .MYD file • Fixed format • Dynamic format • Compressed format

30. Backup and Recovery MySQL databases How MySQL stores data in InnoDB/MyISAM • .MYD file format • Records are stored in frames • Frame types: • 0/00: Deleted block • 1/01: Full small record, full block • 3/03: Full small record, unused space • … • Record length is in rec_len,data_len • Length of variable types precedes field value • Frames can be fragmented • Field values may be packed!

31. Backup and Recovery MySQL databases When recovery is possible • Type of disasters • DROP TABLE or DROP DATABASE • DELETE FROM TABLE • Wrong UPDATE table • Files corruption; When data are lost, stop mysqld as soon as possible killall -9 mysqld – is the best option

32. Backup and Recovery MySQL databases When recovery is possible • DROP TABLE or DROP DATABASE • Respective Records from SYS_TABLES and SYS_INDEXES are removed • Records are untouched

33. Backup and Recovery MySQL databases When recovery is possible • DELETE FROM TABLE • Records are marked as deleted

34. Backup and Recovery MySQL databases When recovery is possible • Wrong UPDATE table • If the field length is the same, the content is replaced bz new value. The old one goes to UNDO segment • If the field length is longer, the old record is “unlinked” , copy goes to the UNDO segment and the new record is created in the pages.

35. Backup and Recovery MySQL databases When recovery is possible • Files corruption • Nothing in InnoDB is touched • Pages are overwritten by junk or shifted

36. Backup and Recovery MySQL databases InnoDB recovery tool • http://code.google.com/p/innodb-tools/ Written in Percona Contributed by Percona and community Supported by Percona • Consists of two major tools • page_parser – splits InnoDB tablespace into 16k pages • constraints_parser – scans a page and finds good records

37. Backup and Recovery MySQL databases InnoDB recovery tool page_parser server# ./page_parser -4 -f /var/lib/mysql/ibdata1 Opening file: /var/lib/mysql/ibdata1 Read data from fn=3... Read page #0.. saving it to pages-1259793800/0-18219008/0-00000000.page Read page #1.. saving it to pages-1259793800/0-0/1-00000001.page Read page #2.. saving it to pages-1259793800/4294967295-65535/2-00000002.page Read page #3.. saving it to pages-1259793800/0-0/3-00000003.page

38. Backup and Recovery MySQL databases InnoDB recovery tool constraints_parser server# ./constraints_parser -4 -f pages-1259793800/0-16/51-00000051.page Table structure is defined in "include/table_defs.h" Filters inside table_defs.h are very important See HOWTO for details http://code.google.com/p/innodb-tools/wiki/InnodbRecoveryHowto

39. Backup and Recovery MySQL databases MyISAM recovery tool https://code.launchpad.net/percona-myisam-recovery-tool • Alfa version is available (with very little functionality) • Supports FIXED and DDYNAMIC formats

40. Backup and Recovery MySQL databases How make recovery easier • REDUNDANT format • Single tablespace, no innodb_file_per_table • Long (> 7k) BLOBs, TEXT is not supported • Interger PRIMARY KEY

41. Backup and Recovery MySQL databases We are hiring • XtraDB / MySQL developer • Serious C/C++ skills • Understanding database internals • Consulting • Serious skills in MySQL optimization • Excellent English • vadim@percona.com

42. -42- Questions ? Thank you for coming! • References • http://www.mysqlperformanceblog.com/ • http://percona.com/ XtraDB for Performance