John Hoffman John Hoffman - 6 months ago 14
MySQL Question

How do I make a MySQL database run completely in memory?

I noticed that my database server supports the Memory database engine. I want to make a database I have already made running InnoDB run completely in memory for performance.

How do I do that? I explored PHPMyAdmin, and I can't find a "change engine" functionality.

Answer

Assuming you understand the consequences of using the MEMORY engine as mentioned in comments, and here, as well as some others you'll find by searching about (no transaction safety, locking issues, etc) - you can proceed as follows:

MEMORY tables are stored differently than InnoDB, so you'll need to use an export/import strategy. First dump each table separately to a file using SELECT * FROM tablename INTO OUTFILE 'table_filename'. Create the MEMORY database and recreate the tables you'll be using. You can then import your data using LOAD DATA INFILE 'table_filename' INTO TABLE tablename for each table.