MySQL performs best with all (or at least most) of its data cached in its buffer pool(s). For larger systems, this can often mean that there are many GBs of data cached.
If MySQL is restarted, for example as part of a server maintenance reboot, it can often take considerable time to load that data back into cache, and as a result, it’ll perform slower immediately following the restart.
To help alleviate this problem, MySQL 5.6 and later can be configured to dump and reload its buffer pool data during the shutdown and startup processes.
Step 1) Configure a running MySQL instance to automatically dump its buffer pool data upon shutdown
If MySQL is already running it will not detect and use new/changed settings made to its configuration file until it is restarted.
Login to MySQL Command Line Client with root privileges.
Run the following command:
SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;
Step 2) Configure MySQL to automatically dump and reload its buffer pool data upon shutdown and startup
Open MySQL’s configuration file (my.ini or my.cnf for Windows/Linux respectively) for editing.
Add the following lines to the “#*** INNODB Specific options ***” section:
innodb_buffer_pool_load_at_startup=ON
innodb_buffer_pool_dump_at_shutdown=ON
