As part of this, we should also handle tables that use the DATA DIRECTORY attribute. Based on a discussion with the same user, I was wondering if there is a bug that we refuse IMPORT TABLESPACE if you import a table whose DATA DIRECTORY attribute in the .cfg file disagrees with the current table definition, but it appears to be OK in 10.2:

--source include/have_innodb.inc

eval CREATE TABLE t2(a INT) ENGINE=InnoDB, DATA DIRECTORY='$MYSQL_TMP_DIR';

ALTER TABLE t2 DISCARD TABLESPACE;

CREATE TABLE t1(a INT) ENGINE=InnoDB;

FLUSH TABLES t1 FOR EXPORT;

--let $MYSQLD_DATADIR= `select @@datadir`

--move_file $MYSQLD_DATADIR/test/t1.cfg $MYSQL_TMP_DIR/test/t2.cfg

--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQL_TMP_DIR/test/t2.ibd

UNLOCK TABLES;

ALTER TABLE t2 IMPORT TABLESPACE;

ALTER TABLE t1 DISCARD TABLESPACE;

FLUSH TABLES t2 FOR EXPORT;

--let $MYSQLD_DATADIR= `select @@datadir`

--move_file $MYSQL_TMP_DIR/test/t2.cfg $MYSQLD_DATADIR/test/t1.cfg

--copy_file $MYSQL_TMP_DIR/test/t2.ibd $MYSQLD_DATADIR/test/t1.ibd

UNLOCK TABLES;

ALTER TABLE t1 IMPORT TABLESPACE;

DROP TABLE t1,t2;

The only surprise here is that the .cfg file must be located in the directory specified by DATA DIRECTORY, plus the name corresponding to the schema name (USE test was implied in this case).

I would like to suggest that we create the "create.sql" file whether it is a "partial" or "full" backup. Oftentimes, folks have a full backup too, where they only need 1 schema or even one table. It is a lot of extra work to restore the full backup, then dump/extract what they need, then re-restore just those table(s).

ccalender The thing with full backup is that the "export" step does not run, thus create.sql is not that useful, as one still needs to start the database and do the "export"

wlad,

I think ccalender is probably thinking about the cases where users want to do a partial restore when they are starting with a full backup, rather than a partial backup. But I don't think this is an issue at all because Mariabackup's --export option doesn't care whether the backup being prepared is a full backup or a partial backup, so I think this should already work.

ccalender,

I believe the --export option should already work with full backups too. It should even be safe to use with backups that have already been prepared, since Mariabackup zeroes out the redo log during the prepare process.

e.g. take a full backup:

$ sudo mariabackup --backup \

>    --target-dir=/home/ec2-user/backup/ \

>    --user=mariabackup --password=mypassword

[00] 2019-03-15 17:29:41 Connecting to MySQL server host: localhost, user: mariabackup, password: set, port: not set, socket: not set

[00] 2019-03-15 17:29:41 Using server version 10.3.13-MariaDB-log

[00] 2019-03-15 17:29:41 mariabackup based on MariaDB server 10.3.13-MariaDB Linux (x86_64)

[00] 2019-03-15 17:29:41 uses posix_fadvise().

[00] 2019-03-15 17:29:41 cd to /var/lib/mysql/

...

[00] 2019-03-15 17:29:43 completed OK!

prepare it:

]$ sudo mariabackup --prepare \

>    --target-dir=/home/ec2-user/backup/

[00] 2019-03-15 17:30:36 mariabackup based on MariaDB server 10.3.13-MariaDB Linux (x86_64)

[00] 2019-03-15 17:30:36 cd to /home/ec2-user/backup/

...

[00] 2019-03-15 17:30:36 completed OK!

and then try --prepare --export:

$ sudo mariabackup --prepare --export \

>    --target-dir=/home/ec2-user/backup/

[00] 2019-03-15 17:32:00 mariabackup based on MariaDB server 10.3.13-MariaDB Linux (x86_64)

[00] 2019-03-15 17:32:00 mariabackup: auto-enabling --innodb-file-per-table due to the --export option

[00] 2019-03-15 17:32:00 cd to /home/ec2-user/backup/

...

[00] 2019-03-15 17:32:01 Prepare export : executing "/usr/bin/mariabackup" --mysqld --defaults-file=./backup-my.cnf --defaults-group-suffix= --datadir=. --innodb --innodb-fast-shutdown=0 --loose-partition --innodb_purge_rseg_truncate_frequency=1 --innodb-buffer-pool-size=104857600 --console  --log-error= --bootstrap  < mariabackup_prepare_for_export.sql

2019-03-15 17:32:01 0 [Note] mysqld (mysqld 10.3.13-MariaDB) starting as process 1255 ...

[00] 2019-03-15 17:32:03 completed OK!

We can see that the .cfg files were exported, even for the full backup:

$ sudo ls -l /home/ec2-user/backup/db1/ | grep test1

-rw-rw---- 1 root root    419 Mar 15 17:32 test1.cfg

-rw-r----- 1 root root   1095 Mar 15 17:29 test1.frm

-rw-r----- 1 root root  98304 Mar 15 17:29 test1.ibd

I think that instead of generating shell scripts and batch files, it is better to have an extra option for copy-back , e.g --copy-back --import , which restores the tables that were backed up with
--prepare --export.

The create.sql and import. sql files will still be generated, in case someone wants to manually one or several tables from backup.

Is "In Progress" still the correct status for this task? Is this task actively being worked on?

The generated SQL CREATE script should also take care to set things like mysql56_temporal_format correctly before doing the actual CREATE, as this option has an effect on how SQL column types like DATETIME are represented internally in InnoDB.

See also MDEV-15225

We have several bugs that affect IMPORT TABLESPACE:
(10.1 and later): MDEV-21407
(10.3 and later): MDEV-21549
(10.4 and later): MDEV-18543
Those are effectively blocking this task.

I think that implementing MDEV-26137 will simplify this.

So with MDEV-26137 closed, were are we with this one now?

We had yet another customer ask about

"Need STEPS to restore single database from mariabackup"

just today.

So nine related support cases, of which five are still active, and I'm pretty sure that there are more that are not even linked to this MDEV yet, esp. from pre-2019 ...

With MDEV-26137 now allowing import from .ibd/.cfg/.frm file with no prior existing table things have become much easier, and creating proper CREATE TABLE statements is no longer needed.

With that restoring a single database, or a specific subset of tables, now has become as easy as "just copy the related table files from a backup directory prepared with "--export" option, and ALTER...IMPORT them into the desired target datbase(s).

With that we can probably also close this one as "fixed in 11.2.1", too, same as MDEV-26137

okay, thanks. I'm tentatively closing it then. Can be reopened, if people will think that MDEV-26137 wasn't enough

How it works, tested using mtr:

1. start an mtr server:
mtr --mysqld=--innodb --mysqld=--partition --start-and-exit && ./client/mysql -uroot -S./mysql-test/var/tmp/mysqld.1.sock test
2. create a table and insert some values:

MariaDB [test]> create table t1 (c int) engine=innodb;

Query OK, 0 rows affected (0.012 sec)

MariaDB [test]> insert into t1 values (42), (32);

Query OK, 2 rows affected (0.014 sec)

Records: 2  Duplicates: 0  Warnings: 0

3. run mariabackup (is the first command needed?)

./extra/mariabackup/mariabackup --backup --target-dir=/tmp/mb/ --databases=test --tables=t1 --socket=./mysql-test/var/tmp/mysqld.1.sock --user=root

./extra/mariabackup/mariabackup --prepare --export --target-dir=/tmp/mb/ --databases=test --tables=t1 --socket=./mysql-test/var/tmp/mysqld.1.sock --user=root

4. start a fresh mtr server, with an empty test database:
mtr --mysqld=--innodb --mysqld=--partition --start-and-exit && ./client/mysql -uroot -S./mysql-test/var/tmp/mysqld.1.sock test

MariaDB [test]> show tables;

Empty set (0.001 sec)

5. copy the tablespace files

cp /tmp/mb/test/t1.* ./mysql-test/var/mysqld.1/data/test/

6. import the table and check it worked:

MariaDB [test]> alter table t1 import tablespace;

Query OK, 0 rows affected (0.016 sec)

MariaDB [test]> show create table t1;

+-------+-----------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                    |

+-------+-----------------------------------------------------------------------------------------------------------------+

| t1    | CREATE TABLE `t1` (

  `c` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |

+-------+-----------------------------------------------------------------------------------------------------------------+

1 row in set (0.001 sec)

MariaDB [test]> select * from t1;

+------+

| c    |

+------+

|   42 |

|   32 |

+------+

2 rows in set (0.003 sec)