Bug #73180 | TTS and selective restore | ||
---|---|---|---|
Submitted: | 2 Jul 2014 20:19 | Modified: | 22 Oct 2014 19:03 |
Reporter: | Daniël van Eeden (OCA) | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Enterprise Backup Documentation | Severity: | S3 (Non-critical) |
Version: | 3.10, 3.10.1 | OS: | Any |
Assigned to: | Daniel So | CPU Architecture: | Any |
Tags: | tts |
[2 Jul 2014 20:19]
Daniël van Eeden
[3 Jul 2014 3:56]
MySQL Verification Team
Hello Daniel, Thank you for the report and test case. Verified as described. Thanks, Umesh
[3 Jul 2014 3:58]
MySQL Verification Team
// Schema setup etc [root@cluster-repo mysql-advanced-5.6.20]# bin/mysql -u root -p -S /tmp/72720M/72720M.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE DATABASE test2; Query OK, 1 row affected (0.00 sec) mysql> USE test2; Database changed mysql> CREATE TABLE t1 (id int); Query OK, 0 rows affected (0.04 sec) mysql> CREATE TABLE t2 (id int); Query OK, 0 rows affected (0.03 sec) // Backup [root@cluster-repo meb-3.10.1-el6-x86-64bit]# bin/mysqlbackup --socket=/tmp/72720M/72720M.sock --backup-dir=/tmp/backuptest --include-tables="test2.*" --use-tts=with-full-locking backup MySQL Enterprise Backup version 3.10.1 Linux-2.6.39-200.24.1.el6uek.x86_64-x86_64 [2014/04/11] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... bin/mysqlbackup --socket=/tmp/72720M/72720M.sock --backup-dir=/tmp/backuptest --include-tables=test2.* --use-tts=with-full-locking backup mysqlbackup: INFO: mysqlbackup: INFO: MySQL server version is '5.6.20-enterprise-commercial-advanced'. 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!". 140705 00:05:21 mysqlbackup: INFO: MEB logfile created at /tmp/backuptest/meta/MEB_2014-07-05.00-05-21_backup.log -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /tmp/72720M/ innodb_data_home_dir = innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /tmp/72720M/ innodb_log_files_in_group = 2 innodb_log_file_size = 50331648 innodb_page_size = 16384 innodb_checksum_algorithm = innodb innodb_undo_directory = /tmp/72720M/ innodb_undo_tablespaces = 0 innodb_undo_logs = 128 -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /tmp/backuptest/datadir innodb_data_home_dir = /tmp/backuptest/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /tmp/backuptest/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 50331648 innodb_page_size = 16384 innodb_checksum_algorithm = innodb innodb_undo_directory = /tmp/backuptest/datadir innodb_undo_tablespaces = 0 innodb_undo_logs = 128 mysqlbackup: INFO: Unique generated backup id for this is 14044989211844997 mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 140705 00:05:23 mysqlbackup: INFO: Full Backup operation starts with following threads 1 read-threads 6 process-threads 1 write-threads 140705 00:05:23 mysqlbackup: INFO: System tablespace file format is Antelope. 140705 00:05:23 mysqlbackup: INFO: Starting to copy all innodb files... 140705 00:05:23 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server. 140705 00:05:23 mysqlbackup: INFO: Starting to lock all the tables... 140705 00:05:23 mysqlbackup: INFO: All tables are locked and flushed to disk 140705 00:05:23 mysqlbackup: INFO: Copying /tmp/72720M/test2/t1.ibd (Antelope file format). 140705 00:05:23 mysqlbackup: INFO: Copying /tmp/72720M/test2/t2.ibd (Antelope file format). 140705 00:05:23 mysqlbackup: INFO: Completing the copy of innodb files. 140705 00:05:24 mysqlbackup: INFO: Opening backup source directory '/tmp/72720M/' 140705 00:05:24 mysqlbackup: INFO: Starting to backup .frm files in the subdirectories of /tmp/72720M/ 140705 00:05:24 mysqlbackup: INFO: Copying the database directory 'test2' 140705 00:05:24 mysqlbackup: INFO: Completing the copy of all non-innodb files. 140705 00:05:25 mysqlbackup: INFO: All tables unlocked 140705 00:05:25 mysqlbackup: INFO: All MySQL tables were locked for 2.029 seconds. 140705 00:05:25 mysqlbackup: INFO: Reading all global variables from the server. 140705 00:05:25 mysqlbackup: INFO: Completed reading of all global variables from the server. 140705 00:05:25 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /tmp/backuptest 140705 00:05:25 mysqlbackup: INFO: Full Backup operation completed successfully. 140705 00:05:25 mysqlbackup: INFO: Backup created in directory '/tmp/backuptest' mysqlbackup completed OK!
[3 Jul 2014 3:58]
MySQL Verification Team
// Cont from earlier note.. // drop tables mysql> show tables; +-----------------+ | Tables_in_test2 | +-----------------+ | t1 | | t2 | +-----------------+ 2 rows in set (0.00 sec) mysql> drop table t1,t2; Query OK, 0 rows affected (0.02 sec) mysql> show tables; Empty set (0.00 sec) // restore [root@cluster-repo meb-3.10.1-el6-x86-64bit]# bin/mysqlbackup --socket=/tmp/72720M/72720M.sock --backup-dir=/tmp/backuptest --include-tables=test2.t1 --datadir=/tmp/72720M copy-back-and-apply-log MySQL Enterprise Backup version 3.10.1 Linux-2.6.39-200.24.1.el6uek.x86_64-x86_64 [2014/04/11] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... bin/mysqlbackup --socket=/tmp/72720M/72720M.sock --backup-dir=/tmp/backuptest --include-tables=test2.t1 --datadir=/tmp/72720M 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!". 140705 00:09:17 mysqlbackup: INFO: MEB logfile created at /tmp/backuptest/meta/MEB_2014-07-05.00-09-17_copy_back_dir_to_datadir.log mysqlbackup: INFO: MySQL server version is '5.6.20-enterprise-commercial-advanced'. mysqlbackup: INFO: Got some server configuration information from running server. -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /tmp/72720M/ innodb_data_home_dir = innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = ./ innodb_log_files_in_group = 2 innodb_log_file_size = 50331648 innodb_page_size = 16384 innodb_checksum_algorithm = innodb innodb_undo_directory = . innodb_undo_tablespaces = 0 innodb_undo_logs = 128 -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /tmp/backuptest/datadir innodb_data_home_dir = /tmp/backuptest/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /tmp/backuptest/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 50331648 innodb_page_size = 16384 innodb_checksum_algorithm = innodb mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 140705 00:09:17 mysqlbackup: INFO: Copy-back-and-apply-log operation starts with following threads 1 read-threads 1 write-threads 140705 00:09:17 mysqlbackup: INFO: Creating table: test2.t1. 140705 00:09:17 mysqlbackup: INFO: Copying /tmp/backuptest/datadir/test2/t1.ibd. 140705 00:09:17 mysqlbackup: INFO: Completing the copy of all non-innodb files. 140705 00:09:18 mysqlbackup: INFO: Importing table: test2.t1. 140705 00:09:18 mysqlbackup: INFO: Copy-back operation completed successfully. mysqlbackup completed OK! // Confirm just test2.t1 table restored mysql> show tables; +-----------------+ | Tables_in_test2 | +-----------------+ | t1 | +-----------------+ 1 row in set (0.00 sec)
[22 Oct 2014 19:03]
Daniel So
The MySQL Enterprise Backup 3,10 Manual is indeed correct: selective restore of tables backed up using TTS is not officially supported before version 3.11. Even that might sometimes work, the feature is not reliable with version 3.10, and we advise against using it with MySQL Enterprise Backup 3.10.