Wednesday, July 12, 2006

Relocating MySQL data

After an interesting question I'm posting here a simple description of how to place your MySQL data on another drive, if you got a server with multiple disks this can really benefit performance.
The picture is this, you have installed MySQL server in the usual C:\Program Files\MySQL\MySQL Server 5.0\ folder, but you have another drive D: and you want to place your data there, you'll create three folders there:

D:\MySQL DataDir\ which will contain MyISAM tables (.frm, .MYD, .MYI files) and also InnoDB tables definitions (.frm files)

D:\MySQL InnoDBDatafiles\ which will contain InnoDB data (ibdata1 etc. files)

D:\MySQL InnoDBLogfiles\ which will contain InnoDB logs (ib_logfile0, ib_logfile1 etc.)

this will be accomplished by:

1. creating those folders
2. stopping the server
3. editing my.ini file this way:

#Path to the database root
datadir="D:/MySQL DataDir/"

#*** INNODB Specific options ***
innodb_data_home_dir="D:/MySQL InnoDBDatafiles/"

innodb_log_group_home_dir="D:/MySQL InnoDBLogfiles/"

leave unchanged the following line

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Programmi/MySQL/MySQL Server 5.0/"

because you are not relocating the binaries.

4. restart the server.

Note that you .pid and your .err files will also be in D:\MySQL DataDir

Hope this helps!

Note: as you can see from the forum thread linked above, the most common problem is due to the user under which mysql is running not having proper permissions on the target folders.

No comments: