MySQL5.7 在线调整Innodb_Buffer_Pool_size不用重启mysql进程

在之前的版本，调整Innodb_Buffer_Pool_size大小必须重启mysql进程才可以生效，如今在MySQL5.7里，可以直接动态设置，方便了很多。这个功能应用的场景：
一、机器增加内存，DBA粗心大意忘记调大Innodb_Buffer_Pool_size了
二、工作交接，新来的DBA发现前任DBA设置的Innodb_Buffer_Pool_size不合理需要注意的地方，在调整Buffer_Pool期间，用户的请求将会阻塞，直到调整完毕，所以请勿在白天调整，在凌晨3-4点低峰期调整。调整时，内部把数据页移动到一个新的位置，单位是块。如果想增加移动的速度，需要调整innodb_buffer_pool_chunk_size参数的大小，默认是128M。例（把BP 128M增大为384M）：mysql> SELECT @@innodb_buffer_pool_size;+---------------------------+| @@innodb_buffer_pool_size |+---------------------------+| 134217728 |+---------------------------+1 row in set （0.00 sec）mysql> SELECT @@innodb_buffer_pool_chunk_size;+---------------------------------+| @@innodb_buffer_pool_chunk_size |+---------------------------------+| 134217728 |+---------------------------------+1 row in set （0.00 sec）mysql> SET GLOBAL innodb_buffer_pool_size=402653184;Query OK, 0 rows affected （0.01 sec）mysql> SELECT @@innodb_buffer_pool_size;+---------------------------+| @@innodb_buffer_pool_size |+---------------------------+| 402653184 |+---------------------------+1 row in set （0.00 sec）
innodb_buffer_pool_chunk_size的大小，计算公式是innodb_buffer_pool_size / innodb_buffer_pool_instances比如现在初始化innodb_buffer_pool_size为2G，innodb_buffer_pool_instances实例为4，innodb_buffer_pool_chunk_size设置为1G，那么会自动把innodb_buffer_pool_chunk_size 1G调整为512M，例：
./mysqld --innodb_buffer_pool_size=2147483648 --innodb_buffer_pool_instances=4
--innodb_buffer_pool_chunk_size=1073741824;mysql> SELECT @@innodb_buffer_pool_size;+---------------------------+| @@innodb_buffer_pool_size |+---------------------------+| 2147483648 |+---------------------------+1 row in set （0.00 sec） mysql> SELECT @@innodb_buffer_pool_instances;+--------------------------------+| @@innodb_buffer_pool_instances |+--------------------------------+| 4 |+--------------------------------+1 row in set （0.00 sec） # Chunk size was set to 1GB （1073741824 bytes） on startup but was# truncated to innodb_buffer_pool_size / innodb_buffer_pool_instancesmysql> SELECT @@innodb_buffer_pool_chunk_size;+---------------------------------+| @@innodb_buffer_pool_chunk_size |+---------------------------------+| 536870912 |+---------------------------------+1 row in set （0.00 sec）
监控Buffer Pool调整进程mysql> SHOW STATUS WHERE Variable_name="InnoDB_buffer_pool_resize_status";+----------------------------------+----------------------------------+| Variable_name | Value |+----------------------------------+----------------------------------+| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |+----------------------------------+----------------------------------+1 row in set （0.00 sec）
查看错误日志：
（增大）[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. （unit=134217728）[Note] InnoDB: disabled adaptive hash index.[Note] InnoDB: buffer pool 0 : 31 chunks （253952 blocks） was added.[Note] InnoDB: buffer pool 0 : hash tables were resized.[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.[Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.[Note] InnoDB: re-enabled adaptive hash index.
（减少）[Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. （unit=134217728）[Note] InnoDB: disabled adaptive hash index.[Note] InnoDB: buffer pool 0 : start to withdraw the last 253952 blocks.[Note] InnoDB: buffer pool 0 : withdrew 253952 blocks from free list. tried to relocate 0 pages. （253952/253952）[Note] InnoDB: buffer pool 0 : withdrawn target 253952 blocks.[Note] InnoDB: buffer pool 0 : 31 chunks （253952 blocks） was freed.[Note] InnoDB: buffer pool 0 : hash tables were resized.[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.[Note] InnoDB: completed to resize buffer pool from 4294967296 to 134217728.[Note] InnoDB: re-enabled adaptive hash index.--------------------------------------分割线 --------------------------------------Ubuntu 14.04下安装MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm《MySQL权威指南（原书第2版）》清晰中文扫描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htmUbuntu 14.04 LTS 安装 LNMP NginxPHP5 （PHP-FPM）MySQL http://www.linuxidc.com/Linux/2014-05/102351.htmUbuntu 14.04下搭建MySQL主从服务器 http://www.linuxidc.com/Linux/2014-05/101599.htmUbuntu 12.04 LTS 构建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htmUbuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htmMySQL-5.5.38通用二进制安装 http://www.linuxidc.com/Linux/2014-07/104509.htm--------------------------------------分割线 --------------------------------------本文永久更新链接地址