Can I find out what version of MySQL from the data files?

Question

I have a very odd situation going on here. I had a linux box running ubuntu 8.10 and MySQL server with a Wordpress installation on it hosting an internal blog for our company. The machine that this was running on has crapped out, but I was able to put the drive into an enclosure and gain access to the files and recover the MySQL data folder. We are mostly a Windows company and I have very limited linux experience. Before this machine crashed we were experimenting with moving this blog to a Windows Virtual Machine running PHP for IIS; and we have an older copy of the Wordpress database up and running on MySQL 5.1. I am currently trying to retrieve the latest data from the linux MySQL data folder and import it into the current database but I am having problems.

Problems so far: 1) I do not know what version of MySQL was installed on the linux box 2) I see ib_logfile0, ib_logfile1, and ibdata1 on the linux MySQL data folder, but am not sure if it was truly running InnoDB or not as I also have .frm, .myd, and .myi files in the wordpress folder

If I can figure out the version of the linux box MySQL I might be able to create a virtual machine to run linux and MySQL so I can copy the data folder and then do an export dump; unless someone else knows of an easier way to get the data into my current database.

Any help or guidance on this issue would be greatly appreciated.

Thanks, Nathan

Answer 1

The cleanest method I can think of is to examine only one table : mysql.user

If you can login to mysql at all, run this query:

SELECT COUNT(1) column_count FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

Here are the answers you should get:

• If you get 43, MySQL 5.6
• If you get 42, MySQL 5.5
• If you get 39, MySQL 5.1
• If you get 37, MySQL 5.0

If you cannot login to mysql, copy the table

• user.frm
• user.MYD
• user.MYI

to another machine running mysql.

On that other machine run CREATE DATABASE junk;

Go to the datadir in that other mysql server

Change directory to junk

Copy the three files for the user table into it. If the other machine is a Linux box, remember to run chown mysql:mysql user.*

Login to MySQL on that other machine and run

DESC junk.user;

It will tell the same numbers I mentioned before.

However, if you get 28 that's MySQL 4.1. If you get 17, that's MySQL 4.0. Have fun with that one.

Give it a Try !!!

Answer 2

With one exception, MySQL's database files are binary compatible between hosts, even between Linux and Windows, so you can just copy them over. The exception comes in if you happen to have floating point data (declared double or float) and try to export across computers with different floating-point representation. If you're unsure (or don't know what I'm talking about), just try it.

An extra little step is to convert to only lowercase for the database and table names in Windows but you can do that by adding the following to your configuration file (my.cnf or my.ini):

[mysqld]
lower_case_table_names=1

You may wish to check that your data isn't damaged too badly. See the manual for help.