MySQL5.7中如何进行在线动态修改innodb_buffer_pool_size

在MySQL5.7之前的版本中，innodb_buffer_pool_size不能在线修改，只能重启MySQL进程进行修改。MySQL5.7开始可以在线动态修改其大小。
在MySQL5.6测试环境：

点击(此处)折叠或打开

mysql> select version();

| version() |

| 5.6.36 |

查看innodb_buffer_pool_size大小

点击(此处)折叠或打开

mysql> show variables like 'innodb_buffer_pool_size';

| Variable_name | Value |

| innodb_buffer_pool_size | 134217728 |

修改innodb_buffer_pool_size大小

点击(此处)折叠或打开

在MySQL5.7测试环境：

mysql> set global innodb_buffer_pool_size=500*1024*1024;

ERROR 1238 (HY000): Variable 'innodb_buffer_pool_size' is a read only variable

点击(此处)折叠或打开




mysql> show variables like 'innodb_buffer_pool_size';

+-------------------------+-----------+

| Variable_name | Value |

+-------------------------+-----------+

| innodb_buffer_pool_size | 536870912 |

+-------------------------+-----------+

修改innodb_buffer_pool_size大小：

点击(此处)折叠或打开

查看innodb_buffer_pool_size大小：

点击(此处)折叠或打开




mysql> set global innodb_buffer_pool_size=800*1024*1024;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'innodb_buffer_pool_size';

+-------------------------+-----------+

| Variable_name | Value |

+-------------------------+-----------+

| innodb_buffer_pool_size | 939524096 |

+-------------------------+-----------+

mysql> select version();

+------------+

| version() |

+------------+

| 5.7.18-log |

+------------+

查看当前innodb_buffer_pool_size大小。