MariaDB starting with 10.0.8

FLUSH TABLES ... FOR EXPORT was introduced in MariaDB 10.0.8.

Syntax

FLUSH TABLES table_name [, table_name] FOR EXPORT

Description

FLUSH TABLES ... FOR EXPORT flushes changes to the specified tables to disk so that binary copies can be made while the server is still running. This works for Archive, Aria, CSV, InnoDB, MyISAM, MERGE, and XtraDB tables.

If a storage engine does not support FLUSH TABLES FOR EXPORT, a 1031 error (SQLSTATE 'HY000') is produced.

If FLUSH TABLES ... FOR EXPORT is in effect in the session, the following statements will produce an error if attempted:

  • FLUSH TABLES WITH READ LOCK
  • FLUSH TABLES ... WITH READ LOCK
  • FLUSH TABLES ... FOR EXPORT

If any of the following statements is in effect in the session, attempting FLUSH TABLES ... FOR EXPORT will produce an error.

  • FLUSH TABLES ... WITH READ LOCK
  • FLUSH TABLES ... FOR EXPORT
  • LOCK TABLES ... READ
  • LOCK TABLES ... WRITE

FLUSH FOR EXPORT is not written to the binary log.

This statement requires the RELOAD and the LOCK TABLES privileges.

If one of the specified tables cannot be locked, none of the tables will be locked.

If a table does not exist, an error like the following will be produced:

ERROR 1146 (42S02): Table 'test.xxx' doesn't exist

If a table is a view, an error like the following will be produced:

ERROR 1347 (HY000): 'test.v' is not BASE TABLE

Copying tables when the MariaDB server is down

The following storage engines support export without FLUSH TABLES ... FOR EXPORT, assuming the source server is down and the receiving server is not accessing the files during the copy.

EngineComment
Archive
AriaRequires clean shutdown. Table will be automatically fixed on receiving server if ariachk --zerofill was not run.
CSV
MyISAM
MERGE  .MRG files can be copied even while server is running as the file only contains a list of tables that are part of merge.

When copying the files, you should copy all files with the same table_name + various extensions. For example, for an Aria table of name foo, you will have files foo.frm, foo.MAI, foo.MAD and possible foo.TRG if you have triggers.

Copying tables live from a running MariaDB server.

For all of the above storage engines, one can copy tables even from a live server under the following circumstances:

  • You have done a FLUSH TABLES or FLUSH TABLE table_name for the specific table.
  • The server is not accessing the tables during the copy process.

Warning: If you do the above live copy, you are doing this on your own risk as if you do something wrong, the copied table is very likely to be corrupted. The original table will of course be fine.

Comments

Comments loading...
Loading