Home / Questions / 131435
Accepted
Marc
Asked: 2020-01-17 08:02:10 +0800 CST 2020-01-17 08:02:10 +0800 CST

How to backup a database with very large tables? InnoDB

  • 772

I have a database MySQLwith the engine InnoDB, which, some tables are very large (approximately 30GB each).

When making one Backupor one Restoreof those tables from the Base de Datos, I have the problem that it takes a long time to restore the copy using Dump.

Is there any way to be able to perform the Backupsand Restoresof those tables very quickly?

NOTE:

This question is referenced from the next one, but with a different database storage engine.

mysql

2 Answers

  1. Best Answer

    InnoDBUnlike other engines, it has an option to activate or deactivate the feature of having a file per table ( File-Per-Table ), which makes it easier for us to make Backupsspecific Restorestables.

    To activate File-Per-Tableyou have to modify the configuration file MySQLand add the following in the label [mysqld]:

    innodb_file_per_table=1

    To activate the option, the MySQL service must be restarted once the configuration file has been modified.

    To make a Backupquick one Restore, we are going to take advantage of the functionality that it has InnoDBto be able to discard and import the different ones Tablespacesfrom the database.

    For the examples below we are going to assume that my Database files are in 'C:\BD\'and the folder where I leave the backup is 'C:\Copia\'.

    Backup procedure:

    The first step we must do to perform a Backupis to lock the table to be able to manipulate the files correctly:

    FLUSH TABLES DB1.Tabla1 FOR EXPORT

    Now we will go to look for the files of the table that we have locked ( 'C:\BD\Tabla1.*') and we will copy them to the folder where we left the backup ( 'C:\Copia\').

    Once the files are copied, we will unlock the table that we previously locked:

    UNLOCK TABLES;

    Restore procedure:

    The first step we need to do is drop the Tablespaceone from the table we are going to restore:

    ALTER TABLE DB1.Tabla1 DISCARD TABLESPACE;

    This way the table is no longer linked to Base de datosand you can now manipulate the files related to that table.

    Now we will go to look for the files of Backupthe table that we have discarded ( 'C:\Copia\Tabla1.*') and we will copy them in the folder where the files of the table of the Base de datos( 'C:\BD\') are, replacing all the files.

    Once the files have been copied, we will import the one Tablespacefrom the table and, in this way, we will have restored the copy we had to the Database:

    ALTER TABLE DB1.Tabla1 IMPORT TABLESPACE;

    • 16

  2. I recommend you use either of these two tools:

    • Percona XtraBackup : It is a Percona tool, it does full and incremental backups without blocking, it is consistent and restores very fast, only compatible with InnoDB. It basically makes a copy of the tables directly from the file system and it's not a .sql so it's faster.
    • mydumper : This tool allows backup and restore using parallelism (multiple cores) to increase speed, it is consistent and easy to use, this type of backup generates many .sql.
    • 2