X

Backup of External Tablespaces in MySQL 8.0

Introduction

We can categorize the InnoDB tablespaces created by users, broadly in following categories.

  1. File-per-table Tablespace - A single, file-per-table tablespace created in the datadir, which contains only one table.
  2. General Tablespace   - A shared tablespace created in the datadir, which may contain multiple tables.
  3. External File-per-table Tablespace - A single, file-per-table tablespace created outside of the datadir, which contains only one table.
  4. External General Tablespace - A shared tablespace created outside of the datadir, which may contain multiple tables.

External tablespaces are sometimes called remote tablespaces, but both refer to a single or shared, file-per-table tablespace outside of the datadir.

Prior to MySQL 8.0, the server creates an InnoDB Symbolic Link (*.isl) file in the datadir to locate the external tablespaces. MEB copies the *.isl files as *.bl files into the backup for those MySQL server versions.  At the time of copy back, MEB copies the *.bl files to the target datadir as the *.isl files. It then copies the corresponding external tablespaces to the location pointed by the *.isl files.

In MySQL 8.0, the use of *.isl files has been removed for InnoDB, as per WL#6416.

This change raises the question: how would MEB find the external tablespaces and copy them back to their respective locations? This article attempts to answer these questions.

tablespaces_tracker file

MEB has introduced a new file in the backup named 'tablespaces_tracker'. It is a JSON file that is created only if at least one external tablespace is included in the backup. The file contains metadata about the backed-up external tablespaces.  The file contains an array named "tablespace_list", which contains a list of the external tablespace objects and the following properties for each external tablespace.

  • "server_file_path" : External tablespace location at the sever
  • "backup_file_path" :  External tablespace location in the backup
  • "space_id”: A tablespace identifier.
  • "Flags”:  As of now these are the possible flag values.
  • GENERAL: If it is a shared tablespace.
  • SINGLE: If it is a file-per-table tablespace.
  • REMOTE: If it is an external tablespace.

The file is present in the datadir of the backup. At the time of copy-back operation, it is copied to the server datadir.

Backup of external tablespaces

In MySQL 8.0, the only source of truth for tablespace information is the transactional data dictionary.  InnoDB keeps the metadata about all tablespaces in the data dictionary tables. Thanks to the new data dictionary, querying of the INFORMATION_SCHEMA has also improved tremendously.

MEB queries the information schema to find out the information about all tablespaces to be backed up.  It then locates all tablespaces on the server and copies them to the backup.

Now, how will MEB be able to copy back the external tablespaces to their respective target locations given that copy-back is an offline operation (except the TTS backups)?  Well, the answer is, with the help of 'tablespaces_tracker' file.

The following is a discussion of a backup of external tablespaces.

  1. Start the MySQL server with an external directory explicitly specified with the --innodb_directories option. Refer to the server manual for information on this option.

 mysqld.exe --no-defaults --datadir=D:\trunk_datadir  --console --innodb_directories='D:\rsisondi\80'

  1. Create the following two external tablespaces:

mysql> CREATE TABLESPACE 80_rem_gen_ddl_ts1  ADD DATAFILE  'D:/rsisondi/80/80_rem_gents1.ibd';

Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE 80_extern_table (c1 INT) ENGINE=InnoDB  DATA DIRECTORY = 'D:/rsisondi/80';

Query OK, 0 rows affected (0.11 sec)

  1. Take a simple directory backup:

> mysqlbackup.exe -ubackup_user --backup-dir=D:\rsisondi\backup backup

MySQL Enterprise Backup version 8.0.12-tr Windows-10.0.14393-AMD64 [2018-04-02  09:01:58]

Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.

A thread created with Id '38700'

Starting with following command line ...

 mysqlbackup.exe –ubackup_user --backup-dir=D:\rsisondi\backup backup

 

<snip>

 

-------------------------------------------------------------

   Parameters Summary        

-------------------------------------------------------------

   Start LSN                  : 19189248

   End LSN                    : 19189656

-------------------------------------------------------------

 

mysqlbackup completed OK!

  1. Navigate to the backup directory and notice the file 'tablespaces_tracker'. It has the metadata about the external tablespaces JSON format as shown below:

{

    "version": 1.0,

    "HowTo": "Update server_file_path if and only if it is needed for each tablespace to restore an external tablespace to a different location; make sure correct database folder is given for each per-table tablespace.",

    "tablespace_list": [

        {

            "server_file_path": "D:/rsisondi/80/80_rem_gents1.ibd",

            "backup_file_path": "D:/rsisondi/backup/datadir/meb#8_80_rem_gents1.ibd",

            "space_id": 8,

            "Flags": [

                "GENERAL",

                "REMOTE"

            ]

        },

        {

            "server_file_path": "D:/rsisondi/80/test_80/80_extern_table.ibd",

            "backup_file_path": "D:/rsisondi/backup/datadir/test_80/80_extern_table.ibd",

            "space_id": 9,

            "Flags": [

                "SINGLE",

                "REMOTE"

            ]

        }

    ]

Notice from above that the file name on the server is 80_remote_gents1.ibd but it has been renamed in the backup. InnoDB allows creation of shared tablespaces with the same name at different locations. This could cause conflicts during directory backups as MEB copies all shared tablespaces into the datadir inside backup. Therefore, a prefix meb#<space_id>_ is appended to the general tablespaces file names in the backup.

  1. Next, drop an existing external tablespace and create a new.

mysql> DROP TABLE 80_extern_table;

Query OK, 0 rows affected (0.17 sec)

 

mysql> CREATE TABLE 80_new_extern_table (c1 INT) ENGINE=InnoDB DATA DIRECTORY = 'D:/rsisondi/80';

Query OK, 0 rows affected (0.07 sec)

 

  1. Take a simple incremental directory backup

>  mysqlbackup.exe -ubackup_user backup --incremental --incremental-backup-dir=D:\rsisondi\incr --incremental-base=history:last_backup

MySQL Enterprise Backup version 8.0.12-tr Windows-10.0.14393-AMD64 [2018-04-02  09:01:58]

Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.

A thread created with Id '36608'

Starting with following command line ...

 mysqlbackup.exe -ubackup  backup --incremental

        --incremental-backup-dir=D:\rsisondi\incr

        --incremental-base=history:last_backup

 

            <snip>

 

-------------------------------------------------------------

   Parameters Summary        

-------------------------------------------------------------

   Start LSN                  : 19189657

   End LSN                    : 19218134

-------------------------------------------------------------

 

           mysqlbackup completed OK! 

  1. Navigate to the datadir in the backup directory and notice the file 'tablespaces_tracker’.  Notice that the information about the dropped table is not present in the tracker file while the information about the newly added file is present.

{

    "version": 1.0,

    "HowTo": "Update server_file_path if and only if it is needed for each tablespace to restore an external tablespace to a different         location; make sure correct database folder is given for each per-table tablespace.",

    "tablespace_list": [

        {

            "server_file_path": "D:/rsisondi/80/80_rem_gents1.ibd",

            "backup_file_path": "D:/rsisondi/incr/datadir/meb#8_80_rem_gents1.ibd",

            "space_id": 8,

            "Flags": [

                "GENERAL",

                "REMOTE"

            ]

        },

        {

            "server_file_path": "D:/rsisondi/80/test_80/80_new_extern_table.ibd",

            "backup_file_path": "D:/rsisondi/incr/datadir/test_80/80_new_extern_table.ibd",

            "space_id": 10,

            "Flags": [

                "SINGLE",

                "REMOTE"

            ]

        }

    ]

}

Copy-back of external tablespaces

The copy-back operation of external tablespaces relies solely on the ‘tablespaces_tracker’ file.  As mentioned before, the ‘tablespaces_tracker’ file is copied into the target datadir as well. It helps MEB to resolve external tablespaces conflicts during the copy-back operation from the incremental backups into the target datadir. For instance, some external tablespaces could have been created/renamed/dropped after the previous backup and before the incremental backup.

Therefore, you should never delete the 'tablesapces_tracker' file from either the backup or datadir after a copy-back operation.

Let us perform a copy-back operation with the backups taken in the previous section.

  1. Stop the server and move/rename the previous external tablespaces directories in case they are still present.

  1. Restore the full backup:

> mysqlbackup.exe -ubackup_user  --backup-dir=D:\rsisondi\backup copy-back-and-apply-log --datadir=D:\rsisondi\datadir

MySQL Enterprise Backup version 8.0.12-tr Windows-10.0.14393-AMD64 [2018-04-02  09:01:58]

Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.

A thread created with Id '27600'

Starting with following command line ...

mysqlbackup.exe –ubackup_user --backup-dir=D:\rsisondi\backup 

        copy-back-and-apply-log --datadir=D:\rsisondi\datadir

 

<snip>

 

180402 20:52:32 MAIN    INFO: Apply-log operation completed successfully.

180402 20:52:32 MAIN    INFO: Full Backup has been restored successfully.

 

mysqlbackup completed OK!

  1. Notice the ‘tablespaces_tracker’ file in the target datadir. It has the same content as it does in the backup.

{

    "version": 1.0,

    "HowTo": "Update server_file_path if and only if it is needed for each tablespace to restore an external tablespace to a different         location; make sure correct database folder is given for each per-table tablespace.",

    "tablespace_list": [

        {

            "server_file_path": "D:/rsisondi/backup/datadir/meb#8_80_rem_gents1.ibd",

            "backup_file_path": "D:/rsisondi/80/80_rem_gents1.ibd",

            "space_id": 8,

            "Flags": [

                "GENERAL",

                "REMOTE"

            ]

        },

        {

            "server_file_path": "D:/rsisondi/backup/datadir/test_80/80_extern_table.ibd",

            "backup_file_path": "D:/rsisondi/80/test_80/80_extern_table.ibd",

            "space_id": 9,

            "Flags": [

                "SINGLE",

                "REMOTE"

            ]

        }

    ]

}

  1. Now, restore the incremental backup that was taken in the above step.

> mysqlbackup.exe --defaults-file=D:\rsisondi\incr\backup-my.cnf -ubackup_user --backup-dir=D:\rsisondi\incr copy-back-and-apply-log --datadir=D:\rsisondi\datadir --incremental

 

MySQL Enterprise Backup version 8.0.12-tr Windows-10.0.14393-AMD64 [2018-04-02  09:01:58]

Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.

 

A thread created with Id '33576'

Starting with following command line ...

 mysqlbackup.exe --defaults-file=D:\rsisondi\incr\backup-my.cnf –ubackup_user

        --backup-dir=D:\rsisondi\incr copy-back-and-apply-log

        --datadir=D:\rsisondi\datadir --incremental

 

<snip>

 

180402 20:52:37 MAIN    INFO: Apply-log operation completed successfully.

180402 20:52:37 MAIN    INFO: Incremental backup applied successfully.

 

mysqlbackup completed OK!

  1. Check the contents of the ‘tablespaces_tracker’ file in the datadir. In this case, it is the same as it was in the incremental backup.  But the contents could be an aggregation of the tracker files that is in the target datadir and tracker file that is in the backup directory. It really depends on what external tablespaces are included in the incremental backup.

{

    "version": 1.0,

    "HowTo": "Update server_file_path if and only if it is needed for each tablespace to restore an external tablespace to a different         location; make sure correct database folder is given for each per-table tablespace.",

    "tablespace_list": [

        {

            "server_file_path": "D:/rsisondi/incr/datadir/meb#8_80_rem_gents1.ibd",

            "backup_file_path": "D:/rsisondi/80/80_rem_gents1.ibd",

            "space_id": 8,

            "Flags": [

                "GENERAL",

                "REMOTE"

            ]

        },

        {

            "server_file_path": "D:/rsisondi/incr/datadir/test_80/80_new_extern_table.ibd",

            "backup_file_path": "D:/rsisondi/80/test_80/80_new_extern_table.ibd",

            "space_id": 10,

            "Flags": [

                "SINGLE",

                "REMOTE"

            ]

        }

    ]

}

Copy-back of external tablespaces to non-default location

There could be a situation in which you want to restore the external tablespace to some other location than its original location. For instance, you are restoring the backup to a new location or preparing a clone. In such cases, you would want to copy the external tablespaces to a different location.

It is possible to achieve the same using the following steps.

  1. Modify the value of "server_file_path" property of the tablespace that you wish to change in the ‘tablespaces_tracker’ file in backup.
  1. Make sure that the proper directory hierarchy exists on the target server for the tablespace to be restored.
  1. Start server with the --innodb-directories option. Specify the directories pertaining to all external tablespaces to this option. For more information refer to the server manual.

Conclusion

With the previous versions of server, the external tablespaces metadata is scattered around the server datadir and also in the backup.  Restoring multiple external tablespaces to the non-default location is cumbersome as you have to modify the .isl files corresponding to the tablespaces.

With 8.0 version, server keeps all tablespaces information in the transactional data dictionary and,

MEB keeps all external tablespaces information in one metadata file. Also, it is easier to modify the restore location of the external tablespaces because it is at one place. We encourage you to give this a try and provide us feedback on how it works for your data. For more details, please see the MySQL Enterprise Backup Guide

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha