Mysql – I have multiple ibdata files : How to do with them

ibdatainnodbMySQLwindows

I have 3 different ibdata files on disk. What is the difference between them? Can I remove some of them? The goal is to migrate the files from C: to E:…

I'm on MySQL 5.0.x…

Directory of C:\MySQL Datafiles

11/19/2012  12:05 PM       287,309,824 ibdata1
               1 File(s)    287,309,824 bytes

Directory of C:\Program Files\MySQL\Enterprise\Monitor\mysql\data

11/19/2012  12:05 PM     6,561,988,608 ibdata1
               1 File(s)  6,561,988,608 bytes

Directory of C:\Program Files\MySQL\MySQL Server 5.0\data

02/08/2008  09:00 AM        10,485,760 ibdata1
               1 File(s)     10,485,760 bytes

Best Answer

Disclaimer : Never Used MySQL Enterprise Monitor

In reality, you do not need the first one as it has not been written in years. However, the default datadir for a MSI-installed mysql instance is "C:\Program Files\MySQL\MySQL Server 5.0\data". You should leave it alone in case you want to create another local instance of MySQL or use it as a template datadir for another local instance.

As for the other two ibdata1 files, one may be needed by MySQL Enterprise Monitor and the other for your actual data.

To be sure, you need to check if you are running Multiple Instances of MySQL.

There are two methods

METHOD #1 Check via the Services List

  • Start
  • Control Panel
  • Administrative Tools
  • Services
  • Scroll through the List of Services
  • Look for any user-defined MySQL service names

If you see multiple MySQL services defined in Services, you have multiple MySQL instances running.

METHOD #2 : Check via the Task Manager

  • Right Click the Taskbar
  • Click Start Task Manager
  • Click Processes Tab
  • Scroll through the Process List
  • Count how many mysqld.exe entries you see

If you see multiple mysqld.exe entries in the Task Manager, you have multiple MySQL instances running.

NEXT STEP

Judging from the date and time of each ibdata1 file and their respective folders, my guess is that you have MySQL Enterprise running and it has its own MySQL instance. You need to check the MySQL Enterprise Documentation on moving Enterprise Monitoring Information to anther disk location.

As for C:\MySQL Datafiles, you need to locate the my.ini being used. You should be able to have MySQL Enterprise Monitor tell you. In the event MySQL Enterprise Monitor does not any mechanisms for editing the datadir or migrating the MySQL data folder, you could also get it from the Services entry:

  • Start
  • Control Panel
  • Administrative Tools
  • Services
  • Scroll through the List of Services
  • Right click on the MySQL Service
  • Choose Properties
  • Scroll left-to-right through the command for the how the Service is started. You should see the location of the my.ini

Once you find the my.ini

  • open it with Notepad or Wordpad
  • edit the datadir setting to new folder location on Drive E:\
  • save the file

Migrate the Data from C: to E: with something like this:

mkdir "E:\MySQL Datafiles"
xcopy "C:\MySQL Datafiles" "E:\MySQL Datafiles" /s

Then startup MySQL. If the MySQL Enterprise Monitor cannot be used to start MySQL then do net start mysql.

While you could move the whole folder C:\Program Files\MySQL\Enterprise\Monitor\mysql\data over to Drive E:\ with

mkdir "E:\MySQL_EMD"
xcopy "C:\Program Files\MySQL\Enterprise\Monitor\mysql\data" "E:\MySQLEMD" /s

you need to make sure MySQL Enterprise Monitor is full shut down. You also need to check for configuration settings for MySQL Enterprise Monitor to make sure it knows where its mysql data is located. Please don't move anything until you find this out. Consult either the Documentation or the Enterprise Forums on this aspect.

Sorry, I am not a user of MySQL Enterprise Monitor, but I hope I gave you a starting point.

Related Question