MySQL Backup: Can I copying individual MyISAM table files to another server with different MySQL version and different OS?

Question

I means copying individual MyISAM table files is:
(shut down mysqld and copy the .frm, .myd, and .myi files from one database folder to another)


Question:
(a) can I use this way to backup MySQL database folder from one server to another server with different MySQL version?

(b) can this backup files moved to different OS? (example: debian to centos)

Answer 1

Only file-level copy MyISAM tables between versions of servers with the same:
- CPU 'endian' ( SPARC != x86 )
- MySQL versions are upgrade-able without conversion (5.0.48 copy to 5.0.52 is bad because of index structure changes, but 5.0.52 copy to 5.1.45 is valid).

Beware of race conditions... You may be accessing the files using FTP or some other tool, while the database is reading the table. There are table 'counters' updated within the .MYI for even the most benign of table reads.

I found the following will ensure integrity of MyISAM tables for any file-level manipulation:

LOCK TABLE x WRITE;
FLUSH TABLE x; -- closes all file handles into table by mysql.
   < perform file-level manipulations >
FLUSH TABLE x; -- A 'stat' of the table occurs and info-schema is updated appropriately.
UNLOCK TABLES;

If you do NOT lock-write your table, mysql may access it (reads or writes) while you are doing your file-level copy/manipulation.

This is also the same mechanism used to allow 'myisampack', 'myisamchk' for tables on an HOT database that even has external locking disabled, without worries of corruption.

-- J Jorgenson --

Answer 2

a) As long you make sure to shut down mysql, moving myisam files across servers of the same version works just fine (this is NOT true for other engines however). In general, you could probably even switch between different minor versions (although its not as guaranteed).

b) Debian to CentOS should work just fine. In general, the only potential problems you would have crossing OSes would be different folder structures (not a problem for most linux distros) or OSes with different endian-ness (also a very rare problem these days).