MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
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.

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 the SHOW ENGINE INNODB STATUS command:

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:

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

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

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

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

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

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

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 http://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.html.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.