Tuesday, September 11, 2012

MySQL Configuration Wizard - Don't always trust it...

So, the MySQL Configuration Wizard for making MySQL Easier to use on Windows will just plainly aid in setting up a safe and sound MySQL Configuration. Right? Just run it and you are safe. OK? But thing do break, specifically harddrives and stuff, so to keep your database in shape you have a second set of disks in your Windows machine, for all that data that you do not want to loose in RAID configuration. Or you might even connect, say, your D:-drive to an expensive but safe SAN! Then you run the MySQL Configuration Wizard and tell it to place your InnoDB data files in that D: drive. Phew! Now you are OK! That RAID-set was expensive, but better safe than sorry! Now you can place all that important data into MySQL, and what's left on the C:-drive is just some programs, which can be replaced if necessary, but the data is safe!

NO! That's NOT how it works, not at all. There are two things wrong here:
  • Just because you move the InnoDB files, which might well be the storage engine you use, doesn't mean that the other MySQL datafiles are there. Fact is, you CANNOT change where MySQL places the other datafiles, they get into C:\ProgramData\MySQL Server 5.5/data whatever you do! Why this is so I do not know, but if you want to change this location, you have 2 options:
    • Edit the my.ini file generated by the Config Wizard. And change the datadir setting and move the files and directories from the C:\ProgramData\MySQL Server 5.5/data directory to the directory where you want the files on your D:drive.
    •  Instruct Windows beforehand where you want your datafiles, which is real easy, just change the setting in the registry that determines where ProgramData is: HKLM\Software\Microsoft\Windows NT\CurrentVersion\ProfileList\ProgramData. Real easy and safe, right :-)
  • If you are OK with the MySQL other datafiles, and just want the InnoDB files to be safe, you still have to fix one thing: The innodb_log_group_home_dir setting. This follows the datadir setting, which means that if you move the InnoDB datafiles to another drive using the Config Wizard, the transaction log files will not move with it! They remain in C:\ProgramData\MySQL Server 5.5/data! Ouch! See this bug:66769

No comments: