Automatically Loading MySQL Memory Tables on MySQL Startup or Reboot

Problem Statement:

If you use MySQL memory tables a lot to speed up frequently used but rarely changed data, you are faced with a problem of loading the memory tables manually whenever you start or reboot your MySQL server. You need an automated of loading the memory tables; here we will show you how.

Solution

To load MySQL memory tables with data from existing physical tables, you can do the following:

1. Edit your /etc/my.cnf file and add the following line under the [mysqld] section:

init_file=/etc/my.memory.tables.init.sql

2. Now create a text file called /etc/my.memory.tables.init.sql file to to have a statement such as the following for each memory table to load from a physical table:

INSERT into db_name.memory_table SELECT * FROM db_name.physical_table;


3. Restart your MySQL server
4. You should check of your memory tables and notice that they have data from physical tables

Make sure your SQL statements are prefixed with database name as we show in the above-mentioned sample query; this will make sure the statements load data from the correct database.

Category: Performance | Tags: