3

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.

BenMorel
  • 734
  • 1
  • 11
  • 36
Nathan
  • 31
  • 1
  • 2

3 Answers3

7

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

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

Old question, but I stumbled upon it while experiencing a similar issue.

First check if you have one of these files in your data dir:

  • mysql_upgrade_info
  • mysql_upgrade_history

They can give you the MySQL server version that was running.

If you don't, the ib_logfile0 seems to contain the MySQL server version as well:

hexdump -C /var/lib/mysql/ib_logfile0 | head -n 3

00000000  00 00 00 04 00 00 00 00  00 00 07 56 2e 6f 70 00  |...........V.op.|
00000010  4d 79 53 51 4c 20 38 2e  30 2e 32 35 00 00 00 00  |MySQL 8.0.25....|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
BenMorel
  • 734
  • 1
  • 11
  • 36
0

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.

igelkott
  • 161
  • 2