1

I have seen many posts about recovering InnoDB files.

The versions I have are:

  • Windows: 5.6.17
  • Linux 5.0.96 (I cannot upgrade this at this time)

If I create a new InnoDB file on the Windows machine, can it be copied to the Linux machine? Are they binary compatible?

Paul White
  • 83,961
  • 28
  • 402
  • 634
MB34
  • 171
  • 2
  • 2
  • 7

1 Answers1

2

It's all in the documentation here. It should be OK but see my final point.

Two important points to note:

  • Copying Data Files (Cold Backup Method)

You can move an InnoDB database simply by copying all the relevant files listed under "Cold Backups" in Section 14.15, “InnoDB Backup and Recovery”. Like MyISAM data files, InnoDB data and log files are binary-compatible on all platforms having the same floating-point number format.

(Should be OK unless you are running Linux on Sparc or ARM! :-) )

and

  • Using Lowercase Names for Cross-Platform Moving or Copying

On Windows, InnoDB always stores database and table names internally in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, create all databases and tables using lowercase names. A convenient way to accomplish this is to add the following line to the [mysqld] section of your my.cnf or my.ini file before creating any databases or tables:

[mysqld]
lower_case_table_names=1

Finally, I think tat this is also of interest, particularly for you.

A lot of water has passed under the bridge between 5.0 and 5.6.

If at all possible I strongly recommend that you do a mysqldump and copy over your data that way.

  • Export and Import (mysqldump)

You can use mysqldump to dump your tables on one machine and then import the dump files on the other machine. Using this method, it does not matter whether the formats differ or if your tables contain floating-point data.

One way to increase the performance of this method is to switch off autocommit mode when importing data, assuming that the tablespace has enough space for the big rollback segment that the import transactions generate. Do the commit only after importing a whole table or a segment of a table.

Vérace
  • 29,825
  • 9
  • 70
  • 84
  • The main problem here is we had a corruption and when I restarted the server, SHOW INNODB STATUS returns ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined. There is no skip-innodb setting in my.cnf. – MB34 Mar 04 '16 at 19:14
  • In MySQL log file: Error: log file /var/lib/mysql/ib_logfile0 is of different size 0 5242880 bytes than specified in the .cnf file 0 536870912 bytes!. Seems that the log files were different size than what was in the my.cnf. I had changed the innodb_log_file_size value before I shut down. Changed it back to 5M and status now shows it is enabled. – MB34 Mar 04 '16 at 19:32
  • What is it, exactly, that you have done to produce these errors? Modify your question and then let me know by putting @Vérace in a reply to this comment. – Vérace Mar 04 '16 at 19:47
  • We had tons of SQL deadlocks on Magento. I dropped the tables that had the deadlocks, recreated them, then restarted the server. That is when the InnoDB file got corrupted and it hosed the engine. I set the logfile size based on some advise from a highly respected MySQL consultant and it cause the engine to not start. – MB34 Mar 04 '16 at 20:45
  • Why was your original question was about moving a file from Windows 5.6 to Linux 5.0? Can you not use the mysqldump strategy that I suggested? – Vérace Mar 04 '16 at 20:52
  • The original question was because we thought we could move the InnoDB files, restore them, then move them back. This has now been rendered moot. – MB34 Mar 04 '16 at 20:55
  • Well, if that's the case, then I think that you should start a new question, since the title bears no relation to what your problem now is. Be sure and Google your problem and if you ask a question, provide OS and MySQL version. Best of luck! :-) – Vérace Mar 04 '16 at 20:59
  • Yep already found some things that will help. – MB34 Mar 04 '16 at 21:41