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

Answer 1

You can move files and check with mysql tools when you have same version of MySql Server. I dont think there is any issue with OS. But version should be same for mysql server.

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).

Answer 3

If the versions are different, your best bet is to just mysqldump tables and then use mysql to import them.

Answer 4

Maybe. I'll elaborate.

YES if they are the same minor version and have identical my.cnf versions.

YES if you are going from 5.0 or higher to another 5.0 or higher, and have identical my.cnf

NO if you use fulltext indexing and have any of its parameters set differently including stop-words (HINT: don't use full-text indexing).

---

But in the general case, no, and I'd say you can only go to another machine if it's running the exact same mysql binary as the original one. If you plan to do anything else, test it extensively. Changes in the format may be subtle and appear to work (example: Collation subtleties causing i18n problems on index range scans).

The contents of your my.cnf do affect the behaviour and may make an existing table invalid, full-text indexing in particular.

I have had difficulties even moving to a newer version, because although it claims and appears to work, older MyISAM versions cause unpredictable behaviour and crashes in the long term (We're talking several days of sustained simulated load on big data sets here). This meant that for 4.1 - 5 I had to migrate the entire data set using REPAIR TABLE.

If any differences in the myisam format will cause incompatibilities, REPAIR TABLE always fixes them (provided mysql can read the table AT ALL - not 5.0 to 4.1 for example!)

Always do a soak / stress test when planning an upgrade, it may expose problems that MySQL didn't know about.

Answer 5

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 --