MySQL Bugs: #73180: TTS and selective restore

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:	None
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

Description:
Page: http://dev.mysql.com/doc/mysql-enterprise-backup/3.10/en/backup-partial-options.html#optio...

"However, the option has the following limitations:
…
 - Cannot restore tables selectively from the backup"

This doesn't seem to be correct as I can backup 2 tables with TTS and restore just 1.

How to repeat:
CREATE DATABASE test2;
USE test2;
CREATE TABLE t1 (id int);
CREATE TABLE t2 (id int);

Backup on source instance:
./mysqlbackup --socket=/tmp/mysql1.sock --backup-dir=/tmp/backuptest --include-tables="test2.*" --use-tts=with-full-locking backup

Restore on destination instance:
./mysqlbackup --socket=/tmp/mysql2.sock --backup-dir=/tmp/backuptest --include-tables=test2.t1 --datadir=/mysql2/data copy-back-and-apply-log

Hello Daniel,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

// 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!

// 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)

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.