MySQL Dumping and Reloading the InnoDB Buffer Pool

    MySQL’s default storage engine as of version 5.5 is InnoDB . InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. By keeping the frequently-accessed data in memory, related searches are retrieved much faster than reading from disk.

     mysql从5.5版本开始，就把innodb作为默认的存储引擎，innodb 维护着 buffer pool 空间去在内存中缓存数据和索引，通过保持频繁访问的数据在内存中,相关搜索检索比从磁盘读快得多。

    

When you stop or restart MySQL, you lose the cached data stored in the buffer pool. There is a feature in MySQL 5.6 which allows you to dump the contents of the buffer pool before you shutdown the mysqld process. Then, when you start mysqld again, you can reload the contents of the buffer pool back into memory. You may also dump the buffer pool at any time for reloading later.

To see information about the buffer pool, use theSHOW ENGINE INNODB STATUScommand:

    当你停止或重新启动MySQL,您失去了缓存的数据存储在缓冲池中。MySQL 5.6有一个特性,允许您转储的内容缓冲池之前关闭mysqld过程。然后,当你再次mysqld时,你可以重新加载缓冲池的内容回内存。你也可以把在任何时间重新加载后缓冲池。　　　　看到关于缓冲池的信息,使用上引擎INNODB STATUScommand:

 

mysql> SHOW ENGINE INNODB STATUS\G
....
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 274726912; in additional pool allocated 0
Dictionary memory allocated 308740
Buffer pool size   16384
Free buffers       15186
Database pages     1195
Old database pages 421
....SHOW ENGINE INNODB STATUS\G
....
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 274726912; in additional pool allocated 0
Dictionary memory allocated 308740
Buffer pool size   16384
Free buffers       15186
Database pages     1195
Old database pages 421
....

 

This example shows the buffer pool contains 1195 database pages (this example is a very small one from my home server). When you dump the buffer pool to disk, only the database pages are recorded. When you restart mysqld , the data from these pages will be loaded back into memory.

You may dump the buffer pool with this command:

这个例子显示了数据库缓冲池包含1195页(本例中是一个非常小的从我家服务器)。将缓冲池转储到磁盘时,只记录数据库页面。当你重启mysqld,这些页面的数据将被加载到内存中。　　　　可以用这个命令转储缓冲池:

 

mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;
Query OK, 0 rows affected (0.00 sec)SET GLOBAL innodb_buffer_pool_dump_now=ON;
Query OK, 0 rows affected (0.00 sec)

The buffer pool dump file is stored in your MySQL data directory.

 

 

 

缓冲池转储文件存储在您的MySQL数据目录

 

# pwd
/usr/local/mysql/data
# ls -l ib_buffer_pool
-rw-rw----  1 mysql  wheel  7122 Feb 13 13:58 ib_buffer_pool
/usr/local/mysql/data
# ls -l ib_buffer_pool
-rw-rw----  1 mysql  wheel  7122 Feb 13 13:58 ib_buffer_pool

The dump is a plain-text file, and we can see the file is 1195 lines long and contains only the database page references.

 

 

 

转储是一个纯文本文件,我们可以看到该文件是1195线长,仅包含数据库页面引用。

 

# file ib_buffer_pool
ib_buffer_pool: ASCII text
# wc -l ib_buffer_pool
  1195 ib_buffer_pool
# head ib_buffer_pool
0,7
0,1
0,3
0,2
0,4
0,11
0,5
0,6
0,301
0,522
ib_buffer_pool: ASCII text
# wc -l ib_buffer_pool
  1195 ib_buffer_pool
# head ib_buffer_pool
0,7
0,1
0,3
0,2
0,4
0,11
0,5
0,6
0,301
0,522

If you have a large buffer pool, you can check on the status of the dump with this command:

 

 

 

如果你有一个大的缓冲池,您可以检查这个命令转储的状态:

 

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'; STATUS LIKE 'Innodb_buffer_pool_dump_status';

If you want to save the buffer pool when MySQL is shutdown or restarted, use this command:

 

 

 

如果你想保存缓冲池MySQL关机或重启时,使用这个命令:

 

SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON; GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

To restore the buffer pool when starting MySQL, append this statement to your mysqld command:

 

 

 

 

恢复缓冲池启动MySQL时,添加这句话mysqld命令:

 

--innodb_buffer_pool_load_at_startup=ON;ON;

Or, to load the buffer pool file while mysqld is running, use this command:

 

 

 

或者,加载缓冲池文件mysqld正在运行时,使用这个命令：

 

SET GLOBAL innodb_buffer_pool_load_now=ON; GLOBAL innodb_buffer_pool_load_now=ON;

Reloading the buffer pool is very fast, and is performed in the background so the users will not be effected. More information about preloading the buffer pools may be found at

 

 

 

重新加载缓冲池是非常快的,在后台执行,用户将不会影响。关于预加载缓冲池的更多信息可以发现