Search code examples
mysqldatabaseimportmariadb

Import database MySQL fastest way

I'm looking for the fastest way to import database. The problem is that I use mysqldump export database. My database is over 2 GB on disk.

mysqldump is quite fast, but the import takes a long time. Is there a quick way to import a database?

We tried to implement hot backup, but MySQL not loaded. Are there options how to do a quick export-import? That is a simple copy of the database files in the data folder?

Info server: OS: MS Windows Server 2008
MySQL Server: MySQL 5.5 or MariaDB
Tables Type: InnoDB (if MariaDB - InnoDB plugin)

Solution

  • Yes, it is possible to copy the InnoDB files to from one server to another. If you have "innodb file per table" enabled, it'll be a lot easier. Following the instructions in the manual you can do:

    1. On the source server, create a table if one does not already exist:

      mysql> use test;
      mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

    2. On the destination server, create a table if one does not exist:

      mysql> use test;
      mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

    3. On the destination server, discard the existing tablespace. (Before a tablespace can be imported, InnoDB must discard the tablespace that is attached to the receiving table.)

      mysql> ALTER TABLE t DISCARD TABLESPACE;

    4. On the source server, run FLUSH TABLES ... FOR EXPORT to quiesce the table and create the .cfg metadata file:

      mysql> use test;
      mysql> FLUSH TABLES t FOR EXPORT;

      The metadata (.cfg) file is created in the InnoDB data directory. Note

      FLUSH TABLES ... FOR EXPORT is available as of MySQL 5.6.6. The statement ensures that changes to the named tables have been flushed to disk so that binary table copies can be made while the server is running. When FLUSH TABLES ... FOR EXPORT is run, InnoDB produces a .cfg file in the same database directory as the table. The .cfg file contains metadata used for schema verification when importing the tablespace file.

    5. Copy the .ibd file and .cfg metadata file from the source server to the destination server. For example:

      shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test

      Note

      The .ibd file and .cfg file must be copied before releasing the shared locks, as described in the next step.

    6. On the source server, use UNLOCK TABLES to release the locks acquired by FLUSH TABLES ... FOR EXPORT:

      mysql> use test;
      mysql> UNLOCK TABLES;

    7. On the destination server, import the tablespace:

      mysql> use test; mysql> ALTER TABLE t IMPORT TABLESPACE;

      Note

      The ALTER TABLE ... IMPORT TABLESPACE feature does not enforce foreign key constraints on imported data. If there are foreign key constraints between tables, all tables should be exported at the same (logical) point in time. In this case you would stop updating the tables, commit all transactions, acquire shared locks on the tables, and then perform the export operation.