Monday, July 6, 2015

MySQL innodb hotbackup (mysqlbackup) with table filtering

C:\Program Files\MySQL\MySQL Enterprise Backup 3.12>mysqlbackup.exe -u root -p --include-tables="^sakila.(actor|address)$" --with-timestamp --backup-dir=c:\mysql\backup backup

MySQL Enterprise Backup version 3.12.0 Windows-6.0-x86 [10.03.2015 ]
Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 mysqlbackup.exe -u root -p --include-tables=^sakila.(actor|address)
        --with-timestamp --backup-dir=c:\mysql\backup backup

 mysqlbackup: INFO:
Enter password: ********
 mysqlbackup: INFO: MySQL server version is '5.6.25-enterprise-commercial-advanced-log'.
 mysqlbackup: 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!".

150706 22:39:31 mysqlbackup: INFO: MEB logfile created at c:\mysql\backup\2015-07-06_22-39-31\meta\MEB_2015-07-06.22-39-31_backup.log

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir = C:\ProgramData\MySQL\MySQL Server 5.6\Data\
  innodb_data_home_dir =
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = C:\ProgramData\MySQL\MySQL Server 5.6\Data\
  innodb_log_files_in_group = 2
  innodb_log_file_size = 50331648
  innodb_page_size = 16384
  innodb_checksum_algorithm = crc32
  innodb_undo_directory = C:\ProgramData\MySQL\MySQL Server 5.6\Data\
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = c:\mysql\backup\2015-07-06_22-39-31\datadir
  innodb_data_home_dir = c:\mysql\backup\2015-07-06_22-39-31\datadir
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = c:\mysql\backup\2015-07-06_22-39-31\datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 50331648
  innodb_page_size = 16384
  innodb_checksum_algorithm = crc32
  innodb_undo_directory = c:\mysql\backup\2015-07-06_22-39-31\datadir
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

 mysqlbackup: INFO: Unique generated backup id for this is 14361935712403634

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
150706 22:39:33 mysqlbackup: INFO: Full Backup operation starts with following threads
                1 read-threads    6 process-threads    1 write-threads
150706 22:39:33 mysqlbackup: INFO: System tablespace file format is Antelope.
150706 22:39:33 mysqlbackup: INFO: Starting to copy all innodb files...
150706 22:39:33 mysqlbackup: INFO: Found checkpoint at lsn 8424992.
150706 22:39:33 mysqlbackup: INFO: Copying C:\ProgramData\MySQL\MySQL Server 5.6\Data\ibdata1 (Antelope file format).
150706 22:39:33 mysqlbackup: INFO: Starting log scan from lsn 8424960.
150706 22:39:33 mysqlbackup: INFO: Copying log...
150706 22:39:33 mysqlbackup: INFO: Log copied, lsn 8424992.
150706 22:39:33 mysqlbackup: INFO: Copying C:\ProgramData\MySQL\MySQL Server 5.6\Data\sakila\actor.ibd (Antelope file format).
150706 22:39:33 mysqlbackup: INFO: Copying C:\ProgramData\MySQL\MySQL Server 5.6\Data\sakila\address.ibd (Antelope file format).
150706 22:39:33 mysqlbackup: INFO: Completing the copy of innodb files.
150706 22:39:33 mysqlbackup: INFO: Starting to copy Binlog files...
150706 22:39:33 mysqlbackup: INFO: Copying C:\ProgramData\MySQL\MySQL Server 5.6\Data\VMMDB01-bin.000001.
150706 22:39:33 mysqlbackup: INFO: Copying C:\ProgramData\MySQL\MySQL Server 5.6\Data\VMMDB01-bin.000003.
150706 22:39:33 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
150706 22:39:33 mysqlbackup: INFO: Starting to lock all the tables...
150706 22:39:33 mysqlbackup: INFO: All tables are locked and flushed to disk
150706 22:39:33 mysqlbackup: INFO: Copying C:\ProgramData\MySQL\MySQL Server 5.6\Data\VMMDB01-bin.000004.
150706 22:39:34 mysqlbackup: INFO: Completed the copy of binlog files...
150706 22:39:34 mysqlbackup: INFO: Opening backup source directory 'C:\ProgramData\MySQL\MySQL Server 5.6\Data\'
150706 22:39:34 mysqlbackup: INFO: Starting to backup all non-innodb files in
        subdirectories of 'C:\ProgramData\MySQL\MySQL Server 5.6\Data\'
150706 22:39:34 mysqlbackup: INFO: Copying the database directory 'sakila'
150706 22:39:34 mysqlbackup: INFO: Completing the copy of all non-innodb files.
150706 22:39:35 mysqlbackup: INFO: A copied database page was modified at 8424992.
          (This is the highest lsn found on page)
          Scanned log up to lsn 8424992.
          Was able to parse the log up to lsn 8424992.
          Maximum page number for a log record 0
150706 22:39:35 mysqlbackup: INFO: All tables unlocked
150706 22:39:35 mysqlbackup: INFO: All MySQL tables were locked for 1.783 seconds.
150706 22:39:35 mysqlbackup: INFO: Reading all global variables from the server.
150706 22:39:35 mysqlbackup: INFO: Completed reading of all global variables from the server.
150706 22:39:35 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in c:\mysql\backup\2015-07-06_22-39-31
150706 22:39:35 mysqlbackup: INFO: Full Backup operation completed successfully.
150706 22:39:35 mysqlbackup: INFO: Backup created in directory 'c:\mysql\backup\2015-07-06_22-39-31'
150706 22:39:35 mysqlbackup: INFO: MySQL binlog position: filename VMMDB01-bin.000004, position 120

-------------------------------------------------------------
   Parameters Summary
-------------------------------------------------------------
   Start LSN                  : 8424960
   End LSN                    : 8424992
-------------------------------------------------------------

mysqlbackup completed OK!


## Before restoration, shutdown the MySQL instance.

C:\Program Files\MySQL\MySQL Enterprise Backup 3.12>mysqlbackup.exe --defaults-file="C:\MySQL\Backup\2015-07-06_22-40-55\server-my.cnf" --backup-dir=c:\mysql\backup\2015-07-06_22-40-55 copy-back-and-apply-log

MySQL Enterprise Backup version 3.12.0 Windows-6.0-x86 [10.03.2015 ]
Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 mysqlbackup.exe
        --defaults-file=C:\MySQL\Backup\2015-07-06_22-40-55\server-my.cnf
        --backup-dir=c:\mysql\backup\2015-07-06_22-40-55
        copy-back-and-apply-log

 mysqlbackup: INFO:
IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'copy-back-and-apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".

150706 22:48:47 mysqlbackup: INFO: MEB logfile created at c:\mysql\backup\2015-07-06_22-40-55\meta\MEB_2015-07-06.22-48-47_copy_back_dir_to_datadir.log

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir = C:\ProgramData\MySQL\MySQL Server 5.6\Data\
  innodb_data_home_dir = C:\ProgramData\MySQL\MySQL Server 5.6\Data\
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = C:\ProgramData\MySQL\MySQL Server 5.6\Data\.\
  innodb_log_files_in_group = 2
  innodb_log_file_size = 50331648
  innodb_page_size = 16384
  innodb_checksum_algorithm = crc32
  innodb_undo_directory = C:\ProgramData\MySQL\MySQL Server 5.6\Data\
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = c:\mysql\backup\2015-07-06_22-40-55\datadir
  innodb_data_home_dir = c:\mysql\backup\2015-07-06_22-40-55\datadir
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = c:\mysql\backup\2015-07-06_22-40-55\datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 50331648
  innodb_page_size = 16384
  innodb_checksum_algorithm = crc32

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
150706 22:48:47 mysqlbackup: INFO: Copy-back-and-apply-log operation starts with following threads
                1 read-threads    1 write-threads
150706 22:48:47 mysqlbackup: INFO: Copying c:\mysql\backup\2015-07-06_22-40-55\datadir\ibdata1.
150706 22:48:47 mysqlbackup: INFO: Copying c:\mysql\backup\2015-07-06_22-40-55\datadir\sakila\actor.ibd.
150706 22:48:47 mysqlbackup: INFO: Copying c:\mysql\backup\2015-07-06_22-40-55\datadir\sakila\address.ibd.
150706 22:48:47 mysqlbackup: INFO: Starting to copy Binlog files...
150706 22:48:47 mysqlbackup: INFO: Copying c:\mysql\backup\2015-07-06_22-40-55\datadir\VMMDB01-bin.000001.
150706 22:48:47 mysqlbackup: INFO: Copying c:\mysql\backup\2015-07-06_22-40-55\datadir\VMMDB01-bin.000003.
150706 22:48:47 mysqlbackup: INFO: Copying c:\mysql\backup\2015-07-06_22-40-55\datadir\VMMDB01-bin.000004.
150706 22:48:48 mysqlbackup: INFO: Completed the copy of binlog files...
150706 22:48:48 mysqlbackup: INFO: Copying the database directory 'sakila'
150706 22:48:48 mysqlbackup: INFO: Completing the copy of all non-innodb files.
150706 22:48:48 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in C:\ProgramData\MySQL\MySQL Server 5.6\Data\
150706 22:48:48 mysqlbackup: INFO: Copy-back operation completed successfully.


 mysqlbackup: INFO: Creating 14 buffers each of size 65536.
150706 22:48:48 mysqlbackup: INFO: Apply-log operation starts with following threads
                1 read-threads    1 process-threads
 mysqlbackup: INFO: Using up to 100 MB of memory.
150706 22:48:48 mysqlbackup: INFO: ibbackup_logfile's creation parameters:
          start lsn 8424960, end lsn 8424992,
          start checkpoint 8424992.
 mysqlbackup: INFO: Backup was originally taken with the --include regexp option
InnoDB: Doing recovery: scanned up to log sequence number 8424992
 mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 5
0 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
 mysqlbackup: INFO: InnoDB: Setting log file size to 50331648
 mysqlbackup: INFO: InnoDB: Setting log file size to 50331648
150706 22:48:50 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
          lsn 8424992.
 mysqlbackup: INFO: Last MySQL binlog file position 0 120, file name VMMDB01-bin.000004:120
150706 22:48:50 mysqlbackup: INFO: The first data file is 'C:\ProgramData\MySQL\MySQL Server 5.6\Data\ibdata1'
          and the new created log files are at 'C:/ProgramData/MySQL/MySQL Server 5.6/Data/./'
150706 22:48:50 mysqlbackup: INFO: Apply-log operation completed successfully.
150706 22:48:50 mysqlbackup: INFO: Full Backup has been restored successfully.

mysqlbackup completed OK!