How to Enable MySQL InnoDB Storage Engine on XAMPP

InnoDB is one of MySQL storage engines, it supports transactions, row-level locking, and foreign-keys. However, by default, InnoDB is not enabled by XAMPP. To enable it, locate the my.cnf configuration file (normally in C:/xampp/mysql/bin directory) and search for the following lines:

# Comment the following if you are using InnoDB tables
skip-innodb
#innodb_data_home_dir = “C:/xampp/mysql/”
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = “C:/xampp/mysql/”
#innodb_log_arch_dir = “C:/xampp/mysql/”
## You can set .._buffer_pool_size up to 50 – 80 %
## of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

Modify the lines and it should look like this:

# Comment the following if you are using InnoDB tables
#skip-innodb
innodb_data_home_dir = “C:/xampp/mysql/”
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = “C:/xampp/mysql/”
innodb_log_arch_dir = “C:/xampp/mysql/”
## You can set .._buffer_pool_size up to 50 – 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

You can change the paremeters as you want, save it and then restart mysql service. To check wheter InnoDB is already enabled, go to Phpmyadmin page, point to Storage Engines menu and it should look like this:

innodb.jpg

Note:

If you have made changes as mentioned above but it still won’t work, try to locate my.ini file on WINDOWS directory, make the same changes on it and restart mysql. Sometimes during installation default my.cnf file will be copied into WINDOWS directory as my.ini and mysql uses it as default configuration file.

facebooktwittergoogle_plusredditpinterestlinkedinmailby feather

Comments

  1. nugroho says

    I’ve tried it but still not works.. InnoDB still disabled although I’ve restart the xampp. Can u help me?

  2. says

    I’ve never experienced like that,it always work. What xampp version do you use?The main line to comment is “skip-innodb”, make sure you’ve commented it and try to stop mysql service and start again.

  3. Dele Agagu says

    I tried the above but whatever changes i made to my.cnf file wasnt reflective on restarting mysql instance. The following worked for me:

    1. If you have MYSQL SYSTEM TRAY MONITOR, launch it – you will a little icon on the notification task bar on your windows machine.

    2. Right click it and choose Configure instance.

    3. Select Startup Variables on the left item listings.

    4. Toggle to InnoDB Parameters tab.

    5. At the footer of the window you will see a label:
    Options file: C:\WINDOWS\my.ini
    Section: mysqld

    6. On the further right of this label click on the

  4. says

    I tried the above but whatever changes i made to my.cnf file in apachefriends folder, nothing was working. The following worked for me:

    1. If you have MYSQL SYSTEM TRAY MONITOR installed on your machine (it is bundled with MYSQL QUERY BROWSER), launch it – you will see a little icon on the notification task bar on your windows machine.

    2. Right click it and choose “Configure instance”.

    3. Select “Startup Variables” on the left item listings.

    4. Toggle to “InnoDB Parameters” tab. Select “Activate InnoDB”. You will notice all your settings are disabled.( to change your settings/uncomment as mentioned above, follow the next steps)

    5. At the footer of the InnoDB tab window you will see a label:
    Options file: C:\WINDOWS\my.ini
    Section: mysqld

    So this is where my configuration file was located. This ini file took precedent over the apachefriends folder > my.cnf. Locate this file and simply follow the uncommenting procedure of this post and save.

    6. Stop the mysql service by right clicking on the Monitor tray again. Select “Shutdown Instance”.

    7. Right click again and Select “Start Instance”.

    8. Now you will see all your InnoDB variables settings. :)

  5. says

    Thank you for your information. It works for me. But I’d installed XAMPP on C:/Program Files/xampp/ so all folders begin with C:/Program Files/xampp/ instead of C:/xampp/

  6. says

    It was difficult to find the start options of mysql in my installation.

    Finally I found this information: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

    There are several lines that help me to understand:
    “The appropriate –defaults-file setting can be found using the Services Manager:

    Start Menu -> Control Panel -> Administrative Tools -> Services

    Find the MySQL service in the list, right-click on it, and choose the Properties option. The Path to executable field contains the –defaults-file setting. ”

    The right settings worked when I changed no the my.ini file but the my.cfg located in: C:\xampp\mysql\bin\my.cnf

    C:\xampp\mysql\bin\mysqld-nt –defaults-file=C:\xampp\mysql\bin\my.cnf mysql

  7. sachin says

    In my localhost i found my.ini in windows folder(C:WINDOWSmy.ini)

    But in that file only two lines are there
    [WinMySQLAdmin]
    Server=C:/Program Files/xampp/mysql/bin/mysqld-nt.exe

    Please help me how to enable INNODB in xampp

  8. says

    Hi sachin,

    Add the following lines to your my.ini

    innodb_data_home_dir = “C:/Program Files/xampp/mysq”
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = “C:/Program Files/xampp/mysq”
    innodb_log_arch_dir = “C:/Program Files/xampp/mysq”

    innodb_buffer_pool_size = 16M
    innodb_additional_mem_pool_size = 2M

    innodb_log_file_size = 5M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50

    Don’t forget to restart mysql server after doing the modification.

    • paulrajj says

      @ Lorenz,
      Thanks for sharing this.
      I am using Xampp version 1.7.2 and mysql version 5.1.37
      I did all the corresponding changes to my.cnf file under mysql/bin folder and restarted mysql server.
      Still InnoDB feature is not been enabled and i dont find my.ini file under WINDOWS folder.
      Is there any other way or option to enable this ?

  9. says

    Thank you for the info. Thanks to your hint to look into the engines section I found that “InnoDB is the default storage engine on this MySQL server. ”

    So thank you very much :D

Leave a Reply

Your email address will not be published. Required fields are marked *