Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am new to MariaDB and interested in using it because I want to use MEMORY storage engine for every table. I read MEMORY storage engine on and it seems like I had to specify storage engine at the creation time.

What I want is to run a single command that enable MEMORY storage engine to every table on an instance. Is there anyway I can achieve that?

share|improve this question

There are two situations you must address


You must add this to my.cnf


Then, restart mysql (MariaDB)

CAVEAT: This will not convert already existing tables to MEMORY tables.

What can done for those existing tables ?


Here is the script to convert all tables to MEMORY tables:

echo "SET SQL_LOG_BIN = 0;" > 
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MEMORY;')"
SQL="${SQL} ConversionSQL FROM information_schema.tables WHERE engine='InnoDB'"
SQL="${SQL} AND table_schema NOT IN ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -AN -e"${SQL}" > ${SQLSCRIPT}

If you are satistied with the contents of the SQL Script, login to MariaDB and run

mysql> source /root/Convert_To_MEMORY.sql


While I gave you the two major things to do to have everything as MEMORY tables, you should read my old posts on the good, the bad, and the ugly of using MEMORY tables:

share|improve this answer

I found two ways to archieve this. The first one is using terminal. (based on Changing default storage engine in MySQL)

  1. edit my.cnf using

    vi /etc/my.cnf
  2. Add these two lines at the end of the file.

  3. Restart MariaDB

    sudo /etc/init.d/mysql stop
    sudo /etc/init.d/mysql start

The second way of doing this is to use a GUI tool which is HeidiSQL.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.