References: http://www.innodb.com/home/ http://www.innodb.com/products/hot-backup/ http://www.innodb.com/doc/hot_backup/manual.html https://docsrva.mysql.com/docs-confidential/mysql-enterprise-backup-3.5/en/ html/manual.html http://www.innodb.com/doc/innodb_plugin-1.0-doc-single/innodb-plugin.html Peter's Article on InnoDB Logs: http://www.devarticles.com/c/a/MySQL/How-Logs-Work-On-MySQL-With-InnoDB-Tables/1/ InnoDB_Architecture: Heikki's Presentation: http://www.innodb.com/wp/wp-content/uploads/2009/05/innodb-file-formats-and-source-code-structure.pdf Recovery_Of_Corrupted_InnoDB: Percona Presentation in User conf 2010: http://assets.en.oreilly.com/1/event/36/Recovery%20of%20Lost%20or%20Corrupted%20InnoDB%20Tables%20Presentation.pdf Baron Schwartz (From Percona) blog : http://www.xaprb.com/blog/2006/09/26/an-introduction-to-innodb-error-handling/ Heikki's Answers to InnoDB Questions: http://www.mysqlperformanceblog.com/2007/10/26/heikki-tuuri-innodb-answers-part-i/ Contents: Product-Information InnoDB-Variables ibbackup-Usage-Info PTR : Point in Time recovery from a hot backup Innobackup-Usage FilePerTableOption PartialBackup InnoDB_Plugin_Information Enabling_Compression & Barracuda Example_Backup_Sessions For Slides InnoDB_Logs_How_It_Works InnoDB_Redo_Logs_Philosophy InnoDB_Source_Code_Modules Caveats ============================================================================== Product-Information: The InnoDB product family * Embedded InnoDB available. * InnoDB Plugin is also opensource * Embedded Innodb is also opensource and available under GPL v2 ============================================================================== InnoDB-Variables ---------------- mysql> show variables like '%innodb%' ; +---------------------------------------+------------------------+ | Variable_name | Value | +---------------------------------------+------------------------+ | have_innodb | YES | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_log_file_size | 5242880 | | innodb_autoextend_increment | 8 | | innodb_checksums | ON | | innodb_data_home_dir | | | innodb_doublewrite | ON | | innodb_extra_dirty_writes | ON | | innodb_fast_shutdown | 1 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_write_io_threads | 8 | +---------------------------------------+------------------------+ 41 rows in set (0.01 sec) mysql> show variables like '%datadir%' ; +---------------+------------------------------------------------+ | Variable_name | Value | +---------------+------------------------------------------------+ | datadir | /home/thava/repo/mysql-6.0-backup/install/var/ | +---------------+------------------------------------------------+ 1 row in set (0.00 sec) ============================================================================ ibbackup-Usage-Info ------------------- ibbackup --help Usage: ibbackup [--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 Note: --restore is synonym for --apply-log my.cnf -- original mysqld my.cnf file. backup-my.cnf -- specifies parameters to include dest dir for backup Both files should include following options: datadir = /home/heikki/data innodb_data_home_dir = /home/heikki/data innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /home/heikki/data set-variable = innodb_log_files_in_group=2 set-variable = innodb_log_file_size=20M --compress instructs the program to compress the backup copies of data files. Compressed data files are named by adding suffix '.ibz' to the file name. --uncompress is relevant only when --apply-log is specified. --suspend-at-end m Creates 'ibbackup_suspended' to the log group home dir as per backup-my.cnf and waits until the user deletes it. If --include regexp is specified, only those per-table data files which match the given regular expression are included in the backup. ============================================================================== PTR: Point-in-time recovery from a hot backup --------------------------------------------- InnoDB only stores the binlog position information to its tablespace at a transaction commit. To make InnoDB aware of the current binlog position you must run at least one transaction while binlogging is enabled. When you run ibbackup --apply-log on your backup, ibbackup versions >= 1.03 print the latest MySQL binlog position the backup knows of. Also mysqld prints it when you start it on the backup after the --apply-log: $ mysqld --defaults-file=/home/pekka/.backup-my.cnf 040122 15:41:57 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. … InnoDB: Last MySQL binlog file position 0 27183537, file name ./binlog.000005 … mysqld: ready for connections. The MySQL version has to be >= 3.23.48 or >= 4.0.2. The printed position is the MySQL binlog byte position from the moment when InnoDB Hot Backup finished the copying of your data files. Then you can apply the binlog file(s) starting from that position to the restored database: $ mysqlbinlog --position=27183537 /sqldata/binlog.000005 | mysql If you want to recover the database to a specific timepoint, you can direct the output of mysqlbinlog to an output file, instead of piping it directly to mysql. The output file contains timestamps for all SQL statements in the binlog. Use an editor to cut off the end of the output file and redirect the output file to mysql, like this: $ mysql < youroutputfile ============================================================================ Innobackup-Usage ---------------- $ perl innobackup --help Usage: innobackup [--sleep=MS] [--compress[=LEVEL]] [--include=REGEXP] [--user=NAME] [--password=WORD] [--port=PORT] [--socket=SOCKET] [--no-timestamp ] [--ibbackup=IBBACKUP-BINARY] [--slave-info] [--databases=LIST] MY.CNF BACKUP-ROOT-DIR innobackup --apply-log [--use-memory=MB] [--uncompress] MY.CNF BACKUP-DIR innobackup --copy-back MY.CNF BACKUP-DIR Note: innobackup creates backup.cnf, so you never have to create it. Even the apply-log specifies my.cnf only not backup.cnf The first command line above makes a hot backup of a MySQL database. It creates a backup directory (named by the current date and time) in the given backup root directory. This command makes a complete backup of all MyISAM and InnoDB tables and indexes in all databases. The created backup contains .frm, .MYD, .MYI, and InnoDB data and log files. The MY.CNF options file defines the location of the database. This command connects to the MySQL server using mysql client program, and runs ibbackup (InnoDB Hot Backup program) as a child process. The command with --apply-log option prepares a backup for starting a MySQL server on the backup. This command expands InnoDB data files as specified in BACKUP-DIR/backup-my.cnf using BACKUP-DIR/ibbackup_logfile, and creates new InnoDB log files as specified in BACKUP-DIR/backup-my.cnf. The BACKUP-DIR should be a path name of a backup directory created by innobackup. This command runs ibbackup as a child process, but it does not connect to the database server. The command with --copy-back option copies data, index, and log files from backup directory back to their original locations. The MY.CNF options file defines the original location of the database. The BACKUP-DIR is a path name of a backup directory created by innobackup. On success the exit code of innobackup process is 0. A non-zero exit code indicates an error. ============================================================================ FilePerTableOption: File Per Table Tablespaces To enable multiple tablespaces, start the server with the --innodb_file_per_table option. For example, add a line to the [mysqld] section of my.cnf: [mysqld] innodb_file_per_table With multiple tablespaces enabled, InnoDB stores each newly created table into its own tbl_name.ibd file in the database directory where the table belongs. ============================================================================ PartialBackup: ============== ibbackup --include 'test\.ib.*' /home/pekka/.my.cnf /home/pekka/.backup-my.cnf This takes backup of system tablespace + specified tables; The advantage is to be able to exclude certain per-file tables. The *.ibd files are created along side .frm files in db directory. ibbackup --compress --include 'test\.ib.*' /home/pekka/.my.cnf /home/pekka/.backup-my.cnf The *.ibz files are created: $ ls /sqldata-backups/test alex1.ibz alex2.ibz alex3.ibz blobt3.ibz Restore of partial backup: ibbackup --apply-log --uncompress my-backup.cnf Note: The .ibz files will be restored as .ibd files. Online restore of single .ibd file : LOCK TABLES tbl_name WRITE; ALTER TABLE tbl_name DISCARD TABLESPACE; ALTER TABLE tbl_name IMPORT TABLESPACE; UNLOCK TABLES; Note: partial backup of partitioned tables are not supported since "alter table tbl_name discard tablespace" cmd fails. The copying of .ibd files should be "clean". What is a "clean" .ibd file ? * There are no uncommitted modifications by transactions in the .ibd file. * There are no unmerged insert buffer entries in the .ibd file. * Purge has removed all delete-marked index records from the .ibd file. * mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file. How to obtain clean .ibd file ? * Copy it from shutdown mysqld installation * Take it from ibbackup backup, start dummy mysqld server and shutdown. The .ibd copy now is clean after recovery. * do: flush tables; lock tables with read lock; Then copy the .ibd file. ============================================================================ PartialBackupTestCases: ---------------------------------------------------------------------------- Test Cases for Partial Backup : Step 1: file_per_table is turned off. Create following 3 tables in innodb default global table space. db1.default_ts_table[123] tables; Bounce the server with file_per_table on. Create some file-per-table tables. Create 3 databases having 3 tables each: db1_table_[123]; db2_table_[123]; db3_table_[123]; case (A) Take full backup using innobackup to "full-backup" dir. case (B) Take backup of tables in db1 to "db1-backup" using: innobackup --include 'db1.*' case (C) Take backup of tables in db1 & db2 to "db1_2-backup" using: innobackup --include 'db[12].*' case (D) Take backup of only 1 table in db1 to 'db1-backup-1" using: innobackup --include 'db1.db1_table_1' case (E) Take backup of only 2 tables in db1 to 'db1-backup-2" using: innobackup --include 'db1.db1_table_[12]' case (F) Take backup of only global innodb tables to "no-db-backup" using non-matching database expression : innobackup --include 'nodb.*' ... case (G) Take backup of only global innodb tables to "no-tbl-backup" using non-matching table expression : innobackup --include 'db1.void*' ... Shutdown mysql server. Step 2: Initialize new instance with file-per-table=off. Restore every thing. Verify all tables exist. Shutdown. Step 3: Initialize new instance with file-per-table=off. Restore db1-backup. Verify only tables db1.* exist; not db2; not db3; Shutdown. Step 4: Initialize new instance with file-per-table=off. Restore db1_2-backup. Verify only relevant tables exist. Shutdown. Step 5: Initialize new instance with file-per-table=off. Restore db1-backup-1. Verify only relevant tables exist. Shutdown. Step 6: Initialize new instance with file-per-table=off. Restore db1-backup-2. Verify only relevant tables exist. Shutdown. /* Step 7: Repeat Steps 2 to 6 with file-per-table=on */ Step 7: Initialize new instance with file-per-table=OFF Create following 3 tables : db1_table_[123]; Verify partial online restore of these 3 tables from each of these backup directories: full-backup db1-backup db1_2-backup /* Step 7: Repeat step 6 with file-per-table=OFF */ ============================================================================== InnoDB_Plugin_Information ========================= http://www.innodb.com/doc/innodb_plugin-1.0-doc-single/innodb-plugin.html - Data compression, New row format - Barracuda InnoDB file format. - New parameter innodb_file_format - Plugin is upward compatible with built-in InnoDB - Same license for InnoDB plugin like MySQL - Limited support from http://forums.innodb.com Plugin Install/View ------------------- - INSTALL PLUGIN INNODB SONAME 'ha_innodb_plugin.so'; - [mysqld] plugin_dir=/path/to/plugin/dir - Show plugins; select * from information_schema.plugins; - Uninstall plugin innobase; Tips when using plugin ---------------------- - Use loose-* options (e.g. loose-innodb-file-per-table) - my.cnf to contain following: ignore_builtin_innodb default_storage_engine=MyISAM - innodb_strict_mode=1 Makes warnings as errors. - Before 5.1.32, use --skip-innodb option before install plugin. After 5.1.32, it is required to invoke mysqld --ignore-builtin-innodb to be able to install dynamic plugin. See Bug#42610. - Table 9.1. InnoDB Plugin Compatibility Plugin MySQL (Binary) MySQL Release (Source Compatibility) 1.0.0 5.1.23 5.1.23 or newer 1.0.1 5.1.24 5.1.24 or newer 1.0.2 5.1.30 5.1.24 or newer 1.0.3 5.1.30 (not 5.1.31) 5.1.24 or newer 1.0.4 5.1.37 5.1.24 or newer 1.0.5 5.1.41 5.1.24 or newer 1.0.6 5.1.41 5.1.24 or newer 1.0.7 5.1.46 5.1.24 or newer =========================================================================== What InnoDB plugins available ? Edit the option file (my.cnf) to ignore the builtin InnoDB, and load the InnoDB Plugin and all Information Schema tables implemented in the InnoDB Plugin when the server starts: ignore_builtin_innodb plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so; innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so; innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so; innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so Note that all plugins for plugin-load should be on the same line in the option file. Alternatively, you can use the equivalent options on the MySQL command line: mysqld --ignore-builtin-innodb --plugin-load=innodb=ha_innodb_plugin.so; innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so; innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so; innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so; innodb_cmpmem_reset=ha_innodb_plugin.so You can also install the InnoDB Plugin and the new InnoDB Information Schema tables implemented in ha_innodb_plugin.so with INSTALL commands: INSTALL PLUGIN INNODB SONAME 'ha_innodb_plugin.so'; INSTALL PLUGIN INNODB_TRX SONAME 'ha_innodb_plugin.so'; INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb_plugin.so'; INSTALL PLUGIN INNODB_LOCK_WAITS SONAME 'ha_innodb_plugin.so'; INSTALL PLUGIN INNODB_CMP SONAME 'ha_innodb_plugin.so'; INSTALL PLUGIN INNODB_CMP_RESET SONAME 'ha_innodb_plugin.so'; INSTALL PLUGIN INNODB_CMPMEM SONAME 'ha_innodb_plugin.so'; INSTALL PLUGIN INNODB_CMPMEM_RESET SONAME 'ha_innodb_plugin.so'; =========================================================================== Main advantages of InnoDB Plugin ? - Fast index creation of large InnoDB table. - Data compression: innodb_file_format=Barracuda with innodb_file_per_table option enables use of table compression. =========================================================================== Reference: InnoDB Plugin Pointers Talk on InnoDB Plugin on MySQL UC 2008: http://www.innodb.com/wp/wp-content/uploads/2008/04/innodb-plugin-talk-2008-final.pdf http://www.innodb.com/wp/wp-content/uploads/2008/05/venu-anuganti-article-april-29-2008.html ============================================================================ Enabling_Compression & Barracuda ================================ create table name ( ... ) row_format=compressed key_block_size=4; Note: key_block_size is actually just table block size; The keyword for MyISAM is reused here. compressed row format uses 8KB compressed size (orig size=16K) - Compressed tables are stored in new 'Barracuda' data file format. - Compression applies only to file-per-table tables. - What is row_format ? - REDUNDANT - Used by InnoDB - contains redundant info like num cols. (old and obsolete used in prior to 5.0.3 versions). - FIXED - Used by MyISAM; can force to use fixed even if varchar present. - COMPACT - Used by InnoDB; - DYNAMIC - Used by MyISAM; For InnoDB: Index page stores only 20-byte pointer to overflow page (as against 768 byte prefix for COMPACT). requires barracuda format. - COMPRESSED - like DYNAMIC but also compresses data. KEY_BLOCK_SIZE option in create innodb table implies compressed row format. Note: Every (file-per) table is tagged with type Antelope or Barracuda. The row_format property of the table determines this "table type". The innodb_file_format only determines if new table can be created in the enabled (latest) format. It is not a "default" format. Tip: innodb_change_buffering variable: none (do not buffer any operations) inserts (default: buffer only insert operations) - innodb_file_per_table - can be changed dynamically only with plugin - Remember to do 'slow shutdown' during upgrade. Beware of 'bufferred inserts' with fast shutdown. SET GLOBAL innodb_fast_shutdown=0; - Beware of use of compressed tables created before plugin 1.0.2 and upgrading to newer version! - Antelope file format == REDUNDANT or COMPACT row format Barracuda == DYNAMIC or COMPRESSED row format - Downgrading table version : ALTER TABLE table_name ROW_FORMAT=COMPACT; - Permit InnoDB to open a database containing files in a format it does not support : innodb_file_format_check = OFF - Redo Log - Maintains changes since last checkpoint. - Crash recovery scans redo logs after last checkpoint. - Freed after checkpoint - written disk pages may include temporary versions of tables used by uncommitted transactions. - Undo Log - contains changes of active transactions uncommitted. - Entries typically contain ? old-image is not written depending on implementation ?? - processing undo log may free disk pages used by temporary versions of tables used by uncommitted transactions. Database Log - LSN - Log Sequence Number - Checkpoint Record contains: -redoLSN - First update that was not flushed at checkpoint. Can be current LSN or older one. -undoLSN - oldest log record of the oldest in-progress transaction. Note: - If there is uncommitted dirty idle transaction for long time, it may create problem in reusing rotating log files. See following for troubleshooting offline partial backup restore: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html iblog* files are redo log files; ibdata* files contain data + rollback segments http://forge.mysql.com/wiki/MySQL_Internals_Files_In_InnoDB_Sources you can find my online article about the differences between Oracle-style and DB2/SQL-Server-style locks at: http://dbazine.com/gulutzan6.html See Peter Gulutzan article here: http://www.devarticles.com/c/a/MySQL/How-Logs-Work-On-MySQL-With-InnoDB-Tables/ http://www.innodb.com/support/documentation/ http://forge.mysql.com/wiki/MySQL_Internals_InnoDB Always use a primary key otherwise innodb creates one internally. ============================================================================== Example_Backup_Sessions ----------------------- http://www.innodb.com/doc/hot_backup/manual.html 1. Do hotbackup of innodb data using ibbackup with innodb_data_file_path = ibdata1:10M:autoextend innodb_log_files_in_group=2 innodb_log_file_size=5M Generate data for around 30M Do restore using ibbackup. Before Backup: ls -1s ../var (size displayed in KB) 51256 ibdata1 5132 ib_logfile0 5132 ib_logfile1 After backup: ls -1s backup1 51256 ibdata1 4 ibbackup_logfile After ibbackup --apply-log : ls -1s backup1 51256 ibdata1 5132 ib_logfile0 5132 ib_logfile1 4 ibbackup_logfile Note: Backup generates single ibdata1 and single ibbackup_logfile. 2. Repeat above with 2 datafiles and 3 log files in group. 3. Use innobackup for full restore with case (1). Backup and Restore 4. Repeat 1-3 with 2 table spaces ibdata1 and ibdata2 5. Do step 1. Generate 5 tables; Bounce and enable innodb_file_per_table [ Just append "innodb_file_per_table" in my.cnf; Dont use = ON ] Generate 10 tables; Do selective backup and restore. Verify it works. Note: tabname.ibd files are created along with .frm files in same dir. 6. Do step 1 with --compress option : Done 7. Use suspend-at-end option with Case 1. 8. Use sleep=ms option with Case 1. 9. Do point-in-time recovery 10. Use innobackup for backup and restore. Observations : Tip: Use set global storage_engine = InnoDB; create table .... ; Step by Step Session : 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 innodb_file_per_table = ON /production/var $ ls -lh total 185M -rw-rw---- 1 thava thava 32M 2010-07-01 11:31 ibdata1 -rw-rw---- 1 thava thava 56M 2010-07-01 11:31 ibdata2 -rw-rw---- 1 thava thava 32M 2010-07-01 11:31 ib_logfile0 -rw-rw---- 1 thava thava 32M 2010-07-01 11:31 ib_logfile1 -rw-rw---- 1 thava thava 32M 2010-07-01 11:31 ib_logfile2 drwx------ 2 thava thava 4.0K 2010-07-01 11:30 mysql/ -rw-rw---- 1 thava thava 8.2K 2010-07-01 11:31 mysql-bin.000001 -rw-rw---- 1 thava thava 19 2010-07-01 11:30 mysql-bin.index drwx------ 2 thava thava 4.0K 2010-07-01 11:31 test/ -rw-rw---- 1 thava thava 2.0K 2010-07-01 11:30 thava-u810.err -rw-rw---- 1 thava thava 6 2010-07-01 11:30 thava-u810.pid /production $ ibbackup ./etc/my.cnf backup.cnf ... display contents of cnf files ... ibbackup: System tablespace file format is Antelope. ibbackup: Found checkpoint at lsn 81934961. ibbackup: Starting log scan from lsn 81934848. 100701 11:38:13 ibbackup: Copying log... 100701 11:38:13 ibbackup: Log copied, lsn 81934961. ibbackup: We wait 1 second before starting copying the data files... 100701 11:38:14 ibbackup: Copying /production/var/ibdata1 (Antelope file format). 100701 11:38:16 ibbackup: Copying /production/var/ibdata2 (Antelope file format). ibbackup: A copied database page was modified at 81934961. ibbackup: Scanned log up to lsn 81934961. ibbackup: Was able to parse the log up to lsn 81934961. ibbackup: Maximum page number for a log record 0 100701 11:38:18 ibbackup: Full backup completed! Tip: Make a note of this LSN ! /production $ ls -l /backup -rw-r----- 1 thava thava 1024 2010-07-01 11:38 ibbackup_logfile -rw-r----- 1 thava thava 33554432 2010-07-01 11:38 ibdata1 -rw-r----- 1 thava thava 58720256 2010-07-01 11:38 ibdata2 Compression: /production $ ibbackup --compress ./etc/my.cnf backup.cnf .... .... $ ls -lh /backup total 3.2M -rw-r----- 1 thava thava 1.0K 2010-07-01 11:56 ibbackup_logfile -rw-r----- 1 thava thava 1.3M 2010-07-01 11:56 ibdata1.ibz -rw-r----- 1 thava thava 1.9M 2010-07-01 11:56 ibdata2.ibz Tip: You can not use --compress for incremental backup. /production $ ibbackup --incremental 81934961 ./etc/my.cnf backup.cnf ibbackup: System tablespace file format is Antelope. ibbackup: Found checkpoint at lsn 122879461. ibbackup: Starting log scan from lsn 122878976. 100701 12:01:11 ibbackup: Copying log... 100701 12:01:11 ibbackup: Log copied, lsn 122879461. ibbackup: We wait 1 second before starting copying the data files... 100701 12:01:12 ibbackup: Copying /production/var/ibdata1 (Antelope file format). 100701 12:01:14 ibbackup: Copying /production/var/ibdata2 (Antelope file format). ibbackup: A copied database page was modified at 122879461. ibbackup: Scanned log up to lsn 122879461. ibbackup: Was able to parse the log up to lsn 122879461. ibbackup: Maximum page number for a log record 0 ibbackup: Backup contains changes from lsn 81934962 to lsn 122879461 100701 12:01:14 ibbackup: Incremental backup completed! $ ls -lh /backup total 41M -rw-r--r-- 1 thava thava 0 2010-07-01 12:01 ibbackup_ibd_files -rw-r----- 1 thava thava 1.0K 2010-07-01 12:01 ibbackup_logfile -rw-r----- 1 thava thava 31M 2010-07-01 12:01 ibdata1 -rw-r----- 1 thava thava 11M 2010-07-01 12:01 ibdata2 Note: Only changed .ibd files copied. Tip: Use my.cnf parameters: innodb_file_format=1 (0 - Antelope; 1 - Barracuda) innodb_file_per_table 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 Introducing innobackup ... - Used to take consistent backup of innodb and MyISAM - ibbackup --suspend-at-end ...; - After seeing ibbackup_suspended file: FLUSH TABLES WITH READ LOCK; copy all .frm files, MyISAM tables, and other non-InnoDB files; Resume the ibbackup run by deleting the file ibbackup_suspended. UNLOCK TABLES; Applying Log to backup: ibbackup: We were able to parse ibbackup_logfile up to ibbackup: lsn 81934961. bbackup: We were able to parse ibbackup_logfile up to ibbackup: lsn 81934961. ibbackup: Last MySQL binlog file position 0 8316, file name ./mysql-bin.000001 ibbackup: The first data file is '/backup/ibdata1' ibbackup: and the new created log files are at '/backup' ibbackup: System tablespace file format is Antelope. 100701 12:23:15 ibbackup: Full backup prepared for recovery successfully! Tip: Note down LSN and MySQL binlog position! $ls -lh backup2 total 185M -rw-r----- 1 thava thava 1.0K 2010-07-01 11:38 ibbackup_logfile -rw-r----- 1 thava thava 32M 2010-07-01 11:38 ibdata1 -rw-r----- 1 thava thava 56M 2010-07-01 11:38 ibdata2 -rw-r----- 1 thava thava 32M 2010-07-01 12:23 ib_logfile0 -rw-r----- 1 thava thava 32M 2010-07-01 12:23 ib_logfile1 -rw-r----- 1 thava thava 32M 2010-07-01 12:23 ib_logfile2 ibbackup: Last MySQL binlog file position 0 8316, file name ./mysql-bin.000001 ibbackup: The first data file is '/backup/ibdata1' ibbackup: and the new created log files are at '/backup' ibbackup: System tablespace file format is Antelope. 100701 12:23:15 ibbackup: Full backup prepared for recovery successfully! Tip: Note down LSN and MySQL binlog position! $ls -lh backup2 total 185M -rw-r----- 1 thava thava 1.0K 2010-07-01 11:38 ibbackup_logfile -rw-r----- 1 thava thava 32M 2010-07-01 11:38 ibdata1 -rw-r----- 1 thava thava 56M 2010-07-01 11:38 ibdata2 -rw-r----- 1 thava thava 32M 2010-07-01 12:23 ib_logfile0 -rw-r----- 1 thava thava 32M 2010-07-01 12:23 ib_logfile1 -rw-r----- 1 thava thava 32M 2010-07-01 12:23 ib_logfile2 Tip: Backup recovery != database recovery Backup is now ready for database recovery Incremental apply-log : $ ibbackup --apply-log --incremental inc-backup.cnf backup.cnf Start mysqld on restored database: - copy ib* files - simply start mysqld with matching my.cnf - real database recovery happens-- open trxs rolled back. - InnoDB displays Last MySQL binlog pos in .err log during startup; This is for information purpose only; Useful for Point-in-time recovery Point-in-time Recovery $ mysqld --defaults-file=my.cnf 040122 15:41:57 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. … InnoDB: Last MySQL binlog file position 0 27183537, file name ./binlog.000005 … mysqld: ready for connections. $ mysqlbinlog --start-position=27183537 /sqldata/binlog.000005 | mysql perl innobackup --help Usage: innobackup [--sleep=MS] [--compress[=LEVEL]] [--include=REGEXP] [--user=NAME] [--password=WORD] [--port=PORT] [--socket=SOCKET] [--no-timestamp] [--ibbackup=IBBACKUP-BINARY] [--slave-info] [--backup-and-apply-log] [--databases=LIST] [--mysql-extra-args="extra arguments to mysql"] [--exec-when-locked="utility arg1 arg2 ..."] [--incremental --lsn LSN] [--only-known-file-types] MY.CNF BACKUP-ROOT-DIR innobackup --apply-log [--use-memory=MB] [--uncompress] [--ibbackup=IBBACKUP-BINARY] MY.CNF BACKUP-DIR innobackup --apply-log --incremental [--use-memory=MB] [--uncompress] [--ibbackup=IBBACKUP-BINARY] INCREMENTAL-BACKUP-MY.CNF FULL-BACKUP-MY.CNF innobackup --copy-back MY.CNF BACKUP-DIR /production $ perl innobackup ./etc/my.cnf /backups .... $ ls -lh /backups/2010-03-02_12-11-58 total 145M -rw-r--r-- 1 pekka pekka 348 2010-03-02 12:11 backup-my.cnf -rw-r--r-- 1 pekka pekka 1 2010-03-02 12:12 ibbackup_binlog_info -rw-r----- 1 pekka pekka 1.0K 2010-03-02 12:12 ibbackup_logfile -rw-r----- 1 pekka pekka 10M 2010-03-02 12:12 ibdata1 -rw-r----- 1 pekka pekka 20M 2010-03-02 12:12 ibdata2 -rw-r----- 1 pekka pekka 114M 2010-03-02 12:12 ibdata3 drwxr-xr-x 2 pekka pekka 4.0K 2010-03-02 12:12 mysql -rw-r--r-- 1 pekka pekka 0 2010-03-02 12:12 mysql-stderr -rw-r--r-- 1 pekka pekka 466 2010-03-02 12:12 mysql-stdout drwxr-xr-x 2 pekka pekka 32 2010-03-02 12:12 test Tip: ibbackup_binlog_info provides MySQL binlog info; mysql-stdout contains mysql client output. Notedown innodb LSN manually for future incremental backup; If you forget to note down, you can use dummy restore on another machine to obtain this from startup message; innobackup --copy-back my.cnf /backups/2010-03-02_12-11-58 - Do it offline; - Overwrites files; - restart server Partial Backup ibbackup --include 'test\.ib.*' my.cnf backup.cnf - System tablespace + matching tables - Use innodb_file_per_table option - Backs up ib* and */*.ibd files - Can also do compressed partial backup (*.ibz) - Offline restore of partial backup is tricky! - Online Restore Of Partial backup is more robust. LOCK TABLES tbl_name WRITE; ALTER TABLE tbl_name DISCARD TABLESPACE; #Caution: This deletes the current .ibd file. # Put the backup .ibd file back in the proper database directory. ALTER TABLE tbl_name IMPORT TABLESPACE; UNLOCK TABLES; Note: Requires clean .ibd file; ibbackup --databases=LIST Read InnoDB plugin documentation before restoring on different innodb versions; Working Around Corruption Problems - Disk page (or os disk buffer) corrupted: ibbackup: Re-reading page at offset 0 3185082368 in /sqldata/mts/ibdata15 ibbackup: Re-reading page at offset 0 3185082368 in /sqldata/mts/ibdata15 ibbackup: Error: page at offset 0 3185082368 in /sqldata/mts/ibdata15 seems corrupt! ================== Slides ================== Use the following options to enable Plugin-load : ignore-builtin-innodb plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so; loose-innodb_data_home_dir = /home/thava/bzrroot/mysql-5.1-meb/install/var loose-innodb_data_file_path = ibdata1:32M;ibdata2:32M:autoextend loose-innodb_log_group_home_dir = /home/thava/bzrroot/mysql-5.1-meb/install/var loose-innodb_log_files_in_group = 3 loose-innodb_log_file_size = 32M loose-innodb_file_format = Barracuda loose-innodb_file_format_check = Barracuda loose-innodb_file_per_table ============================================================================== InnoDB_Logs_How_It_Works Peter Gulutzan Article: http://www.devarticles.com/c/a/MySQL/How-Logs-Work-On-MySQL-With-InnoDB-Tables/1/ - Usually Logs are written ahead of data buffers. - (Dirty) Data buffers are redundant if Logs are written to disk. - If Logs are about to be overwritten (by circular logic), then dirty buffers should be written out. - Every (dirty) data buffer has LSN field which has made it dirty. The dirty page must be written out before the Log entry is overwritten. (b) The LSN isn't a field in the log record, instead it's an absolute address for a file and byte offset. ============================================================================== InnoDB_Architecture: Heikki's Presentation: file:/home/thava/ref-learn/innodb/ innodb-file-formats-and-source-code-structure.pdf Unique subsystems: Doublewrite Insert buffering Adaptive hash index -Insert Buffer and Undo Logs in System Tablespace OR .ibd file ?? -PageSize=16KB for uncompressed; PageSize=1KB to 16KB in compressed mode TS. -TableSpace << Files << Segments << Extents == 64 Pages. Page << Rows -Row = [Trx id, Roll Pointer, Field Pointers, F1, F2, ... Fn] -Page = [Header, Row...., RowOffsetArray..., Trailer] - InnoDB Compressed Pages: InnoDB keeps a modification log in each page Updates & inserts of small records are written to the log w/o page reconstruction; deletes dont even require uncompression Log also tells InnoDB if the page will compress to fit page size When log space runs out, InnoDB uncompresses the page, applies the changes and recompresses the page -Redo Log Structure: [Space-id, PageNo, OpCode, Data] ============================================================================== Recovery_Of_Corrupted_InnoDB: Percona Presentation in User conf 2010: - InnoDB Page Format: FIL HEADER PAGE_HEADER INFINUM+SUPREMUM RECORDS USER RECORDS FREE SPACE Page Directory Fil Trailer. - Rows in InnoDB Page is a linked list. - First record is "INFIMUM" (marker record), last record is "SUREMUM" (i.e. next record pointer is NIL) - Rows are linked according to primary key. - Records are physically saved in insert order. * Internal system tables SYS_INDEXES and SYS_TABLES * Correspondence table name -> index_id; * Storage for other internal information From: http://mysqlbugs.blogspot.com/2008/09/innodb-index-page-format.html Let's reformat this page into the correct fields as seen by InnoDB: 0000: 35567104 -> FIL_PAGE_SPACE_OR_CHKSUM 0004: 00000034 -> FIL_PAGE_OFFSET 0008: FFFFFFFF -> FIL_PAGE_PREV 0012: FFFFFFFF -> FIL_PAGE_NEXT 0016: 000000000000BD92 -> FIL_PAGE_LSN 0024: 45BF -> FIL_PAGE_TYPE (#define FIL_PAGE_INDEX 17855) 0026: 0000000000000000 -> FIL_PAGE_FILE_FLUSH_LSN 0034: 00000000 -> FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID 0038: 0002 -> PAGE_N_DIR_SLOTS 0040: 00C3 -> PAGE_HEAP_TOP (195 ...) 0042: 8006 -> PAGE_N_HEAP (6 records in heap (remove 15th bit) 0044: 0000 -> PAGE_FREE 0046: 0000 -> PAGE_GARBAGE 0048: 00B5 -> PAGE_LAST_INSERT 0050: 0005 -> PAGE_DIRECTION (PAGE_NO_DIRECTION) 0052: 0000 -> PAGE_N_DIRECTION 0054: 0004 -> PAGE_N_RECS 0056: 0000000000000305 -> PAGE_MAX_TRX_ID (773) 0064: 0000 -> PAGE_LEVEL 0066: 0000000000000011 -> PAGE_INDEX_ID ( Page may be an index page where index id is 0 277385) 0074: 000000000000000215F2 -> PAGE_BTR_SEG_LEAF 0084: 00000000000000021532 -> PAGE_BTR_SEG_TOP infimum: 0094: 01 -> info_bits=0, n_owned=1 (always 1 for the infimum) 0095: 00 -> heap number 0096: 02 -> status bits 0097: 0047 -> next record (71 bytes) 0099: 696E66696D756D00 -> "infimum" supremum: 0107: 05000b -> extra bytes 0110: 0000 -> next record, zero since supremum is always last 0112: 73757072656D756D -> "supremum" index row1: 0120: 040506 -> field offsets, starting with the last field. 0123: 00000010 -> extra bytes 0127: FFEF -> offset to next record (17 bytes back (offset 112)) 0129: 636363636363 -> 'cccccc' (keypart1) 0135: 6262626262 -> 'bbbbb' (keypart2) 0140: 61616161 -> 'aaaa' (primary key appended) index row2: 0144: 0703 -> field lengths, starting with the last field (excluding null!). 0146: 02000018 -> extra bytes 0150: FFE9 -> offset to next record (23 bytes back (offset 129)) 0152: 636363 -> 'ccc' (keypart1) 0155: 61616161616161 -> 'aaaaaaa' (primary key appended) index row3: 0162: 0101 -> field lengths, starting with the last field (excluding null!). 0164: 01000020 -> extra bytes 0168: 000B -> offset to next record (11 bytes (offset 181)) 0170: 62 -> 'b' (keypart2) 0171: 61 -> 'a' (primary key appended) index row4: 0172: 0A0301 -> field lengths 0175: 00000028 -> extra bytes 0179: FFE3 -> offset to next record (29 bytes back (offset 152!)) 0181: 63 -> 'c' (keypart1) 0182: 626262 -> 'bbb' (keypart2) 0185: 61616161616161616161 -> 'aaaaaaaaaa' (primary key appended) ============================================================================== InnoDB_Redo_Logs_Philosophy /* General philosophy of InnoDB redo-logs: 1) Every change to a contents of a data page must be done through mtr, which in mtr_commit() writes log records to the InnoDB redo log. 2) Normally these changes are performed using a mlog_write_ulint() or similar function. 3) In some page level operations only a code number of a c-function and its parameters are written to the log to reduce the size of the log. 3a) You should not add parameters to these kind of functions (e.g. trx_undo_header_create(), trx_undo_insert_header_reuse()) 3b) You should not add such functionality which either change working when compared with the old or are dependent on data outside of the page. These kind of functions should implement self-contained page transformation and it should be unchanged if you don't have very essential reasons to change log semantics or format. */ ============================================================================== InnoDB_Source_Code_Modules: http://mysqlinsights.blogspot.com/2009/02/understanding-innodb-source-code.html INNODB MODULES row Row Abstraction, 19,768 lines sub-modules: Updates, Undo, Undo Modify, Undo Insert, Select, Purge, Mysql Interface The logic for the mysql row formatting and the innodb row formatting is quite lengthy. This module also seems to have a lot of the high-level business logic for Innodb. trx Transactions, 13,138 lines sub-modules: Rollback, Rollback Segment, Undo, Log Records, Purge As Innodb is a transactional storage engine there is a lot of logic to implement this btr Btree data structure, 12,228 lines sub-modules: Btree Cursor, Btree Persistent Cursor, Btree Adaptive Search Btree is the index of Innodb and is core functionality dict Data Dictionary (meta-data), 10,446 lines sub-modules: Boot, Creation, Load, Memory Table names, column names, key names, etc. all in this code handler Mysql Storage Engine Interface, 8498 lines This is the primary interface between Mysql and the innodb storage engine and the entry point for all mysql API calls. log Database Log, 8379 lines sub-modules: Recovery Database logging is core functionality buf Buffer Pool, 7784 lines sub-modules: Buffer Flush Algorithm, Buffer Replacement Algorithm, Buffer Read Abstraction os Operating System Interface, 7659 lines sub-modules: Files, Processes, Threads, Synchronization This is the fun stuff, all the low level OS specific code lock Transaction Lock System, 6224 lines sub-modules: Lock Queue Iterator page Index Page, 5675 lines sub-modules: Page Cursor srv Main Server Driver, 5469 lines sub-modules: Startup, Query Execution Look here for configuration option handling coding and other startup issues sync Synchronization, 5361 lines sub-modules: ReadWrite Lock, Wait Array fil Table Space Memory Cache, 5282 lines rem Records Abstraction, 4965 lines sub-modules: Record Manager, Record Comparison Service fsp File Space Management, 4405 lines ibuf Insert Buffer, 4125 lines ut Utilities, 4113 lines sub-modules: Vector, Random Numbers, Memory, List, Debug, Byte Manipulation, Work Queue mem Memory Management, 3598 lines sub-modules: Memory Debug, Memory Pool data Data Element Abstraction, 2867 lines sub-modules: Data Types que Query Graph, 2255 lines mtr Mini-transaction Buffer, 1967 lines sub-modules: Mini-transaction Log eval SQL evaluator, 1603 lines sub-modules: Stored Procedures ha Hash table, 1422 lines mach Machine Dependent Utilities, 1198 lines fut File Based Utilities, 951 lines sub-modules: File Based List read Cursor Read, 788 lines dyn Dynamically Allocated Array, 560 lines thr Threads, 302 lines sub-modules: Thread Local Storage usr Sessions, 163 lines ============================================================================== Caveats: - The specification of the number of data files and their sizes must match in my.cnf and my2.cnf. If the last data file is specified as auto-extending in my.cnf, it must be specified as auto-extending also in my2.cnf. - The number of log files and their size must be explicitly specified, but their number and size can be different in my.cnf and my2.cnf. - Incremental backups can not be compressed. ==============================================================================