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