Is there a way to enable MariaDB MEMORY storage engine for every table?

Question

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 mariadb.com 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?

Answer 1

There are two situations you must address

SITUATION #1 : TABLES TO CREATE

You must add this to my.cnf

[mysqld]
default-storage-engine=MEMORY

Then, restart mysql (MariaDB)

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

What can done for those existing tables ?

SITUATION #2 : TABLES ALREADY EXISTING

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

SQLSCRIPT=/root/Convert_To_MEMORY.sql
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}
less ${SQLSCRIPT}

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

mysql> source /root/Convert_To_MEMORY.sql

EPILOGUE

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:

• May 22, 2011 : I am using the MEMORY storage engine but MySQL still writes to my disk...Why?
• Sep 26, 2011 : Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?
• Jan 17, 2012 : Mysql Memory table getting many locks
• Jan 21, 2012 : Is InnoDB Engine up to speed against Memory Engine?
• Jan 30, 2012 : How much memory will a MEMORY table take up?

Answer 2

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.

   [mysqld]
   default-storage-engine=MEMORY
   

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.