Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 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.

share|improve this answer

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:

share|improve this answer

Your Answer

 
discard

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.