Friday, February 26, 2010

How to set the MySQL Default Storage Engine with MAMP

The MySQL server of MAMP and MAMP PRO uses the Default Storage Engine MyISAM. If you want to change this, you have to change the configuration file of the MySQL server. With this tutorial we want to show you how to manage this. As Default Storage Engine we are using InnoDB.

MAMP:
  1. Stop the MAMP Server if it is running.
  2. Use a texteditor to create a file named "my.cnf" and save it to the "/Applications/MAMP/conf/" folder.
  3. Add the following lines into the file:

    [mysqld]
    default-storage-engine = InnoDB

  4. Save the file and close your editor.
  5. Start the MAMP servers.
MAMP PRO:
  1. Start MAMP PRO.
  2. Stop the MAMP PRO server if it is running.
  3. Select File -> Edit Template -> MySQL my.cnf
  4. An editor window appears.
  5. If a warning message appears confirm with OK.
  6. Find the section "[mysqld]"
  7. Beneath the last line of this section add this line:

    default-storage-engine = InnoDB

  8. The section "[mysqld]" now looks like this:



  9. Save (Apple + S) all changes made on "my.cnf"
  10. Close the editor window (Apple + W)
  11. Start the MAMP PRO servers.

How-to check if the modifications succeeded. Servers of MAMP / MAMP PRO must be running.
  • Open Terminal (Applications/Utilities)
  • Enter the following line into the terminal and hit Enter:

    /Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot

  • Enter the following line into the MySQL prompt and hit Enter:

    USE information_schema;

  • Enter the following line into the MySQL prompt and hit Enter:

    SELECT * FROM engines;

  • A table with the storage Engines of MySQL will show up. Inside the Support column "InnoDB" now has the value "DEFAULT". InnoDB now is the default Storage Engine of the MySQL Server.

  • Enter the following line into the MySQL prompt and hit Enter:

    exit;

Share/Save/Bookmark
Labels: , , ,

9 comments:

  1. Adding this command made my whole system crash and now phpmyadmin will not come up at all when I try to select it from my MAMP dashboard. Are you sure the syntax is correct? I also tried editing the cnf doc to un-comment the options located under "If you use InnoDB you can un-comment these options". That made no difference, except that according to the logs it trashed it even worse. Very frustrating. I am using the latest version of MAMP as of this date.

    ReplyDelete

  2. Error: Could not connect to MySQL server!

    Please Help

    ReplyDelete

  3. @Anonymous:

    It sounds like you are using a sample cnf file that comes with MySQL. Try creating a new file with the two lines given above and of course, make sure the servers are off before doing this. You may also need to clear your browser cache. I can attest that the above instructions work. Good luck.

    ReplyDelete

  4. Are these instructions still valid for mamp 2.0.2? I see that in the cnf file there is stuff dealing with innodb that is commented out, should that be uncommented as well?

    ReplyDelete

  5. MAMP PRO 1.7.1
    Added to [mysqld]
    default-storage-engine = InnoDB
    at the bottom

    but bind-address = 127.0.0.1
    is not there which I did *not* add.

    Restarted MAMP - seems to be OK. MySQL log shows:
    InnoDB: Started;

    but when I go into terminal I don't find table:

    engines

    Am I ... 1) running too old a version and about to enter into pain world, or 2) OK and InnoDB is up?

    (BTW kudos to the MAMP crew. Great work and will be updating soon)


    +---------------------------------------+
    | Tables_in_information_schema |
    +---------------------------------------+
    | CHARACTER_SETS |
    | COLLATIONS |
    | COLLATION_CHARACTER_SET_APPLICABILITY |
    | COLUMNS |
    | COLUMN_PRIVILEGES |
    | KEY_COLUMN_USAGE |
    | PROFILING |
    | ROUTINES |
    | SCHEMATA |
    | SCHEMA_PRIVILEGES |
    | STATISTICS |
    | TABLES |
    | TABLE_CONSTRAINTS |
    | TABLE_PRIVILEGES |
    | TRIGGERS |
    | USER_PRIVILEGES |
    | VIEWS |
    +---------------------------------------+
    17 rows in set (0.00 sec)

    ReplyDelete

  6. Working just fine, in Mamp 2.1.1 .. Beautiful solution, thank you! --Jen D.

    ReplyDelete

  7. Works great, thank you!

    ReplyDelete

  8. When following the instructions for checking that the change was successful,
    "/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot"

    I get this message on the terminal,
    "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)"

    What do I need to do differently to get the MySQL command prompt as described above?

    Thanks.

    ReplyDelete

  9. Your skills are very good and you also have a sense of humor of understanding the audiance with in the things you write. Well think discount coupons are distributed on regular basis to consumers via saving sites. Thanks for sharing such useful post with us.

    atlanta roofing

    ReplyDelete

Subscribe to: Post Comments (Atom)