I would recommend that you set up separate clusters for different database instances. You should be able to set up multiple clusters sharing some or all of the hosts, just make sure each cluster has its own ports defined.
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.frmuser.MYDuser.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 !!!
Best Answer
I recommend that the database server ip/name is put in some config file and that all connection strings reference this variable. If both machines are on the same network then use some local name (e.g. mysql.network.local). Then if the machine will change again you will just update your hosts file or update dns records.
Both cases require that every connection string will need to be changed - but it seems inevitable.