Working with Tables

Portability Characterstics

Any table is portable to another server in the sense that you can dump it into a text file with mysqldump, move the file to the machine where the other server runs, and load the file to recreate the table.

Portability as described in this section means that you can directly copy the files that represent the table on disk to another machine, install them into a database directory, and expect the MySQL server there to be able to use the table.

Of course, HEAP tables do not satisfy this definition because their contents are stored in memory, not on disk. Of the other table types, some are portable and some are not:

. ISAM tables are stored in a machine-dependent format, so they are portable only between machines that have identical hardware characteristics.

. BDB tables are not portable because the location of the table is encoded into the table's .db file. This makes a BDB table location-specific within the file system of the machine on which the table was created. (That's the conservative view of BDB portability.

I have experimented with BDB files in various ways, such as by moving them between database directories, renaming the files to use a different basename, and so on. I have not observed ill effects. But presumably it's better to play it safe and move BDB tables by dumping them with mysqldump and re-creating them on the destination machine by reloading the dump file.)

. MyISAM and InnoDB tables are stored in machine-independent format and are portable, assuming that your processor uses two's-complement integer arithmetic and IEEE floating-point format. Unless you have some kind of oddball machine, neither of these conditions should present any real issues.

In practice, you're probably most likely to see portability-compromising variation in hardware if you're using an embedded server built for a special-purpose device, as these sometimes will use processors that have non-standard operating characteristics.

. MERGE tables are portable as long as their constituent MyISAM files are portable.

In essence, the portability requirements for MyISAM and InnoDB tables are that they either contain no floating-point columns or that both machines use the same floating-point storage format.

"Floating-point" means FLOAT and DOUBLE here. DECIMAL columns are stored as strings, which are portable.

Note that for InnoDB, portability must be assessed at the tablespace level, not at the table level. The InnoDB handler stores the contents of all InnoDB tables within the tablespace rather than within table-specific files.

Consequently, it's the InnoDB tablespace files that are or are not portable, not individual InnoDB tables. This means that the floating-point portability constraint applies if any InnoDB table uses floating-point columns.

Regardless of a table type's general portability characteristics, you should not attempt to copy table or tablespace files to another machine unless the server has been shut down cleanly.

You cannot assume the integrity of your tables if you perform a copy after an unclean shutdown; they may be in need of repair or there may be transaction information still stored in a table handler's log files that needs to be applied or rolled back to bring tables up to date.

Similarly, if the server is running and actively updating tables, the table contents on disk will be in flux and the associated files will not yield usable table copies. In the case of a running server, you may be able to tell it to leave the tables alone while you copy them.