Mysql – Accessing MySql Data on local machine

MySQL

I have never worked with MySql.

I have some old/crude software running on my machine. It holds a lot of data that I would like access to since the UI is garbage. After searching around the file directories I found what appears to be a MySql database.

C:\Users\Me\AppData\Roaming\Program Name\mdb\bin

Inside of this directory is a my.ini file along with several others. Also several folders including data.

Inside C:\Users\Me\AppData\Roaming\Program Name\mdb\bin\data are ib_logfile0, ibdata1, and other folders/files including frm.

In Sql Server I'd attach it to SSMS, but I can't seem to do that here. I tried copying all the files to the local MySQL ProgramData folder then opening MySQL workbench. But I never see any tables in the Schema section.

My question is: Is there a way for me to access these tables? I have installed MySql and attempted to connect to this database with no luck. Is there a way to access usernames or can I just use a root account?

Thanks in advance.

Best Answer

Look in your my.ini file where the variable "datadir" points. Install another mysql (same version or sup better) and copy everything from "datadir" to the new "datadir" in the new installation, start the mysql service. Look in the log maybe you have to run mysql_upgrade script or face another issue, then connect to the database via the workbench.

Related Solutions

Mysql – find out what version of MySQL from the data files

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

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

Mysql – Recover MySQL database from data folder without ibdata1 from ibd files

MyISAM

For a MyISAM table mydb.mytable, you should have three files

\bin\mysql\mysql5.6.12\data\mydb\mytable.frm
\bin\mysql\mysql5.6.12\data\mydb\mytable.MYD
\bin\mysql\mysql5.6.12\data\mydb\mytable.MYI

They should already be accessible as a table since each file contains needed data, metadata, and index info. Collectively, they form the table. There are no external storage engine mecahnisms to access.

InnoDB

Take a look at this Pictorial Representation of InnoDB

InnoDB Architecture

The only thing that attaches ibdata1 to the .ibd files is the data dictionary.

Your mission, should you decide to accept it, is to create each table and swap in the .ibd

Before you do anything, make a full copy of "\bin\mysql\mysql5.6.12\data" to another

Here is a sample

Suppose you have a database mydb with the table mytable. This means

You have the folder \bin\mysql\mysql5.6.12\data\mydb
Inside that folder, you have
- mytable.frm
- mytable.ibd

You need the .frm. If you look at my post How can extract the table schema from just the .frm file?, you can download a MySQL utility that can generate the SQL needed to create the table.

You should now do the following

Move mytable.ibd to \bin\mysql\mysql5.6.12\data
Run the SQL to create the InnoDB table
Login to mysql and run ALTER TABLE mydb.mytable DISCARD TABLESPACE; (This will delete \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd)
Copy \bin\mysql\mysql5.6.12\data\mytable.ibd into \bin\mysql\mysql5.6.12\data\mydb
Login to mysql and run ALTER TABLE mydb.mytable IMPORT TABLESPACE; (This will register \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd into the data dictionary)

After this, the table mydb.mytable should be fully accessible. You can test that accessibility by simply running:

SELECT * FROM mydb.mytable LIMIT 10;