mysql - How to set global innodb_buffer_pool_size?

Question

How to set the global innodb_buffer_pool_size mySQL variable? When I set it to system display I get this error:

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

Answer 1

In the earlier versions of MySQL ( < 5.7.5 ) the only way to set

'innodb_buffer_pool_size'

variable was by writing it to my.cnf (for linux) and my.ini (for windows) under [mysqld] section :

[mysqld]

innodb_buffer_pool_size = 2147483648

You need to restart your mysql server to have it's effect in action.

UPDATE :

As of MySQL 5.7.5, the innodb_buffer_pool_size configuration option can be set dynamically using a SET statement, allowing you to resize the buffer pool without restarting the server. For example:

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

Reference : http://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-online-resize.html

Note

Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you configure innodb_buffer_pool_size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances that is not less than the specified buffer pool

By @FlyingAtom