Optimistic non-locking copy of InnoDB .frm files
- by jothir
MySQL Enterprise Backup(MEB) does hot backup of innodb data and log files. Till MEB 3.6.1, the user backs up the only innodb tables in a 3 step process:
STEP 1. Take backup using --only-innodb option
STEP 2. Temporarily make the table read only by executing “FLUSH TABLES WITH READ LOCK”
MEB 3.7.0 has an enhancement to innodb file copying. The .frm files gets copied along with the hot backup done for innodb files. I would like to make the blog a little interactive by explaining the feature as answers:
1. What are these .frm files?
The files containing the metadata, such as the table definition, of a MySQL table.
For backups, the full set of .frm files are always required along with the backup data, to be able to restore tables that are altered or dropped after the backup.
2. Can the .frm files not be copied by MEB itself?
--only-innodb-with-frm is the new option introduced in MEB 3.7.1 to do a copy of .frm files without locking the tables during backup operation itself. This is to reduce the pain of manually copying the .frm files. The option is intended for backups where you can ensure that no ALTER TABLE, CREATE TABLE, DROP TABLE, or other DDL statements modify the .frm files for InnoDB tables during the backup operation.
3. How is data consistency ensured?
MEB does validation of the .frm files after copying by comparing with the server directory to see if the timestamps of any of the .frm files is greater than the saved system time (check .frm time). This change in timestamp of the .frm files will show if a table is altered during the process of backup.
The total number of frm files in the server directory is also verified against the copied contents. If the number of .frm files is less compared to server directory, it shows that table/tables have been dropped during the process of backup. If the number of .frm files is more compared to server directory, it shows that new table/tables have been created during backup operation.
4. How does MEB handle data inconsistency?
MEB copies the .frm files through several iterations, does the validation and throws a WARNING if there is any inconsistency found in .frm files at the end of backup operation. This means the user is warned of some DDL operations that had occurred during backup operation, and has to manually copy the .frm files or do a backup again.
5. What is the option and explain its usage?
The option introduced is --only-innodb-with-frm which does optimistic copy of .frm files without locking. This can be used when the user wants to backup only innodb tables along with .frm files.
The option can take one of the 2 values: all | related.
--only-innodb-with-frm=all does copy of all .frm files of all innodb tables.
--only-innodb-with-frm=related works in conjunction with --include option.This is to allow partial backup of .frm files corresponding to the tables specified in --include.
Let me show the usage with example output:
./mysqlbackup -uroot --backup-dir=/logs/backupWithFrmAll --only-innodb-with-frm=all backup
MySQL Enterprise Backup version 3.7.1 [2012/06/05]
Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.
INFO: Starting with following command line ...
./mysqlbackup -uroot --backup-dir=/logs/backupWithFrmAll
--only-innodb-with-frm=all backup
INFO: Got some server configuration information from running server.
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'backup' run mysqlbackup
prints "mysqlbackup completed OK!".
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /mysql/trydb/
innodb_data_home_dir =
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /mysql/trydb/
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /logs/backupWithFrmAll/datadir
innodb_data_home_dir = /logs/backupWithFrmAll/datadir
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /logs/backupWithFrmAll/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880
mysqlbackup: INFO: Unique generated backup id for this is 13451979804504860
mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
mysqlbackup: INFO: System tablespace file format is Antelope.
mysqlbackup: INFO: Found checkpoint at lsn 1656792.
mysqlbackup: INFO: Starting log scan from lsn 1656320.
120817 15:36:22 mysqlbackup: INFO: Copying log...
120817 15:36:22 mysqlbackup: INFO: Log copied, lsn 1656792.
We wait 1 second before starting copying the data files...
120817 15:36:23 mysqlbackup: INFO: Copying /mysql/trydb/ibdata1 (Antelope file format).
120817 15:36:23 mysqlbackup: INFO: Copying /mysql/trydb/innodb1/table2.ibd (Antelope file format).
120817 15:36:23 mysqlbackup: INFO: Copying /mysql/trydb/innodb1/table3.ibd (Antelope file format).
120817 15:36:23 mysqlbackup: INFO: Copying /mysql/trydb/innodb1/table1.ibd (Antelope file format).
mysqlbackup: INFO: Opening backup source directory '/mysql/trydb/'
120817 15:36:23 mysqlbackup: INFO: Starting to backup .frm files in the subdirectories of /mysql/trydb/
mysqlbackup: INFO: Copying innodb data and logs during final stage ...
mysqlbackup: INFO: A copied database page was modified at 1656792.
(This is the highest lsn found on page)
Scanned log up to lsn 1656792.
Was able to parse the log up to lsn 1656792.
Maximum page number for a log record 0
mysqlbackup: INFO: Copying non-innodb files took 2.000 seconds
120817 15:36:25 mysqlbackup: INFO: Full backup completed!
mysqlbackup: INFO: Backup created in directory '/logs/backupWithFrmAll'
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 1656320
End LSN : 1656792
-------------------------------------------------------------
mysqlbackup completed OK!
bash$ ls /logs/backupWithFrmAll/datadir/innodb1/
table1.frm table1.ibd table2.frm table2.ibd table3.frm table3.ibd
Here the backup directory contains all the .frm files of all the innodb tables.
./mysqlbackup -uroot --backup-dir=/logs/backupWithFrm --include="innodb1.table3.*" --only-innodb-with-frm=related backup
MySQL Enterprise Backup version 3.7.1 [2012/06/05]
Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.
INFO: Starting with following command line ...
./mysqlbackup -uroot --backup-dir=/logs/backup371frm
--include=innodb1.table3.* --only-innodb-with-frm=related backup
INFO: Got some server configuration information from running server.
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'backup' run mysqlbackup
prints "mysqlbackup completed OK!".
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /mysql/trydb/
innodb_data_home_dir =
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /mysql/trydb
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /logs/backupWithFrm/datadir
innodb_data_home_dir = /logs/backupWithFrm/datadir
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /logs/backupWithFrm/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880
mysqlbackup: INFO: Unique generated backup id for this is 13451973458118162
mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
mysqlbackup: INFO: The --include option specified: innodb1.table3.*
mysqlbackup: INFO: System tablespace file format is Antelope.
mysqlbackup: INFO: Found checkpoint at lsn 1656792.
mysqlbackup: INFO: Starting log scan from lsn 1656320.
120817 15:25:47 mysqlbackup: INFO: Copying log...
120817 15:25:47 mysqlbackup: INFO: Log copied, lsn 1656792.
We wait 1 second before starting copying the data files...
120817 15:25:48 mysqlbackup: INFO: Copying /mysql/trydbibdata1 (Antelope file format).
120817 15:25:49 mysqlbackup: INFO: Copying /mysql/trydbinnodb1/table3.ibd (Antelope file format).
mysqlbackup: INFO: Opening backup source directory '/mysql/trydb'
120817 15:25:49 mysqlbackup: INFO: Starting to backup .frm files in the subdirectories of /mysql/trydb
mysqlbackup: INFO: Copying innodb data and logs during final stage ...
mysqlbackup: INFO: A copied database page was modified at 1656792.
(This is the highest lsn found on page)
Scanned log up to lsn 1656792.
Was able to parse the log up to lsn 1656792.
Maximum page number for a log record 0
mysqlbackup: INFO: Copying non-innodb files took 2.000 seconds
120817 15:25:51 mysqlbackup: INFO: Full backup completed!
mysqlbackup: INFO: Backup created in directory '/logs/backupWithFrm'
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 1656320
End LSN : 1656792
-------------------------------------------------------------
mysqlbackup completed OK!
bash$ ls /logs/backupWithFrm/datadir/innodb1/
table3.frm table3.ibd
Thus the backup directory contains only the .frm file matching the innodb table name specified in --include option.
In a nutshell, we present our great new option --only-innodb-with-frm which is a true hot InnoDB-only backup with .frm files, but with an additional check, if any DDL happened during the backup. If a DDL has happened, the DBA can decide if to repeat the backup, or to live with the potential inconsistency. This is the ideal solution for users that have all their "real" data in InnoDB and seldom change their schemas.
You may also like:
http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/backup-partial-options.html
STEP 3. Manually copy the .frm files of innodb tables to the destination directory where backup is stored.
