强制整个MySQL数据库在内存中

For the purposes of running a large number of tests that interact with the database, I want to do two things:

为了运行与数据库交互的大量测试，我想做两件事：

I would like to copy the schema of a database without copying its data. I can do this with a script that grabs the CREATE TABLE statements from each table in the database.

我想复制数据库的模式而不复制其数据。我可以使用一个脚本来执行此操作，该脚本从数据库中的每个表中获取CREATE TABLE语句。
Upon creating this database, I would like to force it to be 100% in memory.

创建此数据库后，我想强制它在内存中100％。

I'm stuck on how to do part 2 - Is there an easier way to do this other than specifying each table's engine? Somehow that seems like a poor way of doing it.

我坚持如何做第2部分 - 除了指定每个表的引擎之外，还有更简单的方法吗？不知何故，这似乎是一种糟糕的做法。

4 个解决方案

#1

Create the database in /dev/shm (ubuntu|debian) and it will be in RAM. It can grow up to 0.5 of available memory.

在/ dev / shm（ubuntu | debian）中创建数据库，它将在RAM中。它可以增长到可用内存的0.5。

#2

As dtmilano said, you can put it on a tmpfs mounted filesystem. It doesn't have to be /dev/shm, but that is one place where tmpfs is usually mounted.

正如dtmilano所说，你可以把它放在一个tmpfs挂载的文件系统上。它不一定是/ dev / shm，但这是通常安装tmpfs的地方。

You can create a new one anywhere, though:

您可以在任何地方创建新的：

mount none -t tmpfs /path/to/dir

If it fills all your available RAM, it will use swap as a backup.

如果它填满了所有可用的RAM，它将使用swap作为备份。

Put it in /etc/fstab to re-mount on boot. Just remember, it's a ram disk, so it starts out empty every time you reboot. See: http://www.howtoforge.com/storing-files-directories-in-memory-with-tmpfs

将它放在/ etc / fstab中以在引导时重新挂载。请记住，它是一个ram磁盘，所以每次重启时它都会空出来。请参阅：http：//www.howtoforge.com/storing-files-directories-in-memory-with-tmpfs

Alternately, as suggested by yuxhuang you can create a table of type MEMORY. It also empties on restart, though the table definition remains. The MEMORY table type has a few restrictions, though. It uses fixed-size rows, for example, so text and blob columns are not allowed, and varchar isn't variable length. See: http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

或者，如yuxhuang所示，您可以创建一个MEMORY类型的表。虽然表定义仍然存在，但它也会在重启时清空。但MEMORY表类型有一些限制。例如，它使用固定大小的行，因此不允许使用text和blob列，并且varchar不是可变长度。请参阅：http：//dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

#3

SET storage_engine=MEMORY;

This is going to set the default storage engine for the current session.

这将设置当前会话的默认存储引擎。

#4

If you are using Windows, in your database creation script you can create the tables adding the MEMORY parameter like this:

如果您使用的是Windows，则可以在数据库创建脚本中创建添加MEMORY参数的表，如下所示：

CREATE TABLE IF NOT EXISTS `user` (
  `id` varchar(23) NOT NULL,
  `username` varchar(250) NOT NULL,
  ...
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

#1