MySQL5.7新特性之在线动态修改innodb_buffer_pool_size

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

点击(此处)折叠或打开

1. mysql> select version();
2. +-----------+
3. | version() |
4. +-----------+
5. | 5.6.36 |
6. +-----------+

查看innodb_buffer_pool_size大小

点击(此处)折叠或打开

1. mysql> show variables like 'innodb_buffer_pool_size';
2. +-------------------------+-----------+
3. | Variable_name | Value |
4. +-------------------------+-----------+
5. | innodb_buffer_pool_size | 134217728 |
6. +-------------------------+-----------+
7. 修改innodb_buffer_pool_size大小

8. 点击(此处)折叠或打开

   1. mysql> set global innodb_buffer_pool_size=500*1024*1024;
   2. ERROR 1238 (HY000): Variable 'innodb_buffer_pool_size' is a read only variable
   在MySQL5.7测试环境：

9. 点击(此处)折叠或打开

   1. mysql> select version();
   2. +------------+
   3. | version() |
   4. +------------+
   5. | 5.7.18-log |
   6. +------------+
   7. 查看当前innodb_buffer_pool_size大小。

   8. 点击(此处)折叠或打开

      1. mysql> show variables like 'innodb_buffer_pool_size';
      2. +-------------------------+-----------+
      3. | Variable_name | Value |
      4. +-------------------------+-----------+
      5. | innodb_buffer_pool_size | 536870912 |
      6. +-------------------------+-----------+
      7. 修改innodb_buffer_pool_size大小：

      8. 点击(此处)折叠或打开

         1. mysql> set global innodb_buffer_pool_size=800*1024*1024;
         2. Query OK, 0 rows affected, 1 warning (0.00 sec)
         查看innodb_buffer_pool_size大小：

      9. 点击(此处)折叠或打开

         1. mysql> show variables like 'innodb_buffer_pool_size';
         2. +-------------------------+-----------+
         3. | Variable_name | Value |
         4. +-------------------------+-----------+
         5. | innodb_buffer_pool_size | 939524096 |
         6. +-------------------------+-----------+