如何在 Mac OS 上的 MySQL 中更改 innodb_buffer_pool_size 的值？

How to change value for innodb_buffer_pool_size in MySQL on Mac OS?

提问by analyticsPierce

I am trying to increase the size of the innodb_buffer_pool_size in MySQL 5.1 as I keep running into the following error indicating I have run out of space for the table locks.

我正在尝试增加 MySQL 5.1 中 innodb_buffer_pool_size 的大小，因为我一直遇到以下错误，表明我的表锁空间不足。

ERROR: The total number of locks exceeds the lock table size
Error
Code: 1206

I have gone through the documentation and from what I gather, I need to update innodb_buffer_pool_size in the /etc/my.cnf file. My current value is 8M. However, even after creating that file and adding the following line to set the value it is not updating in MySQL.

我已经阅读了文档并根据我收集的内容，我需要更新 /etc/my.cnf 文件中的 innodb_buffer_pool_size。我现在的值是8M。但是，即使在创建该文件并添加以下行来设置值后，它也不会在 MySQL 中更新。

set-variable=innodb_buffer_pool_size=256M

Does have any advice on how I can adjust this value in MySQL on my mac? Any other advice or suggestions?

关于如何在 Mac 上的 MySQL 中调整此值有什么建议吗？任何其他意见或建议？

回答by neocanable

add this to your my.cnf

将此添加到您的 my.cnf

innodb_buffer_pool_size=1G

restart your mysql to make it effect

重启你的mysql使其生效

回答by Jorge Walters

I had to put the statement under the [mysqld] block to make it work. Otherwise the change was not reflected. I have a REL distribution.

我必须将语句放在 [mysqld] 块下才能使其工作。否则不会反映更改。我有一个 REL 发行版。

回答by 1000111

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

在早期版本的MySQL ( < 5.7.5 )中设置的唯一方法

'innodb_buffer_pool_size'

'innodb_buffer_pool_size'

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

变量是通过将其写入[mysqld] 部分下的my.cnf（对于 linux）和my.ini（对于 Windows）：

[mysqld]

innodb_buffer_pool_size = 2147483648

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

您需要重新启动 mysql 服务器才能使其生效。

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;

从 MySQL 5.7.5 开始，可以使用 SET 语句动态设置 innodb_buffer_pool_size 配置选项，允许您在不重新启动服务器的情况下调整缓冲池的大小。例如：

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

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

参考：https: //dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

回答by exceed

For standard OS X installations of MySQL you will find my.cnf located in the /etc/ folder.

对于 MySQL 的标准 OS X 安装，您会在 /etc/ 文件夹中找到 my.cnf。

Steps to update this variable:

更新此变量的步骤：

1. Load Terminal.
2. Type cd /etc/.
3. sudo vi my.cnf.
4. This file should already exist (if not please use sudo find / -name 'my.cnf' 2>1- this will hide the errors and only report the successfile file location).
5. Using vi(m) find the line innodb_buffer_pool_size, press ito start making changes.
6. When finished, press esc, shift+colon and type wq.
7. Profit (done).

1. 负载终端。
2. 键入cd /etc/。
3. sudo vi my.cnf.
4. 该文件应该已经存在（如果不存在，请使用sudo find / -name 'my.cnf' 2>1- 这将隐藏错误并仅报告成功文件的文件位置）。
5. 使用 vi(m) 找到该行innodb_buffer_pool_size，按i开始进行更改。
6. 完成后，按 esc、shift+冒号并键入wq。
7. 利润（完成）。

回答by Luke Madhanga

As stated,

就像声明的那样，

innodb_buffer_pool_size=50M

Following the convention on the other predefined variables, make sure there is no space either side of the equals sign.

按照其他预定义变量的约定，确保等号两侧没有空格。

Then run

然后运行

sudo service mysqld stop
sudo service mysqld start

Note

笔记

Sometimes, e.g. on Ubuntu, the MySQL daemon is named mysqlas opposed to mysqld

有时，例如在 Ubuntu 上，MySQL 守护进程被命名mysql为mysqld

I find that running /etc/init.d/mysqld restartdoesn't always work and you may get an error like

我发现运行/etc/init.d/mysqld restart并不总是有效，您可能会收到类似的错误

Stopping mysqld:                                           [FAILED]
Starting mysqld:                                           [  OK  ]

To see if the variable has been set, run show variablesand see if the value has been updated.

要查看变量是否已设置，请运行show variables并查看值是否已更新。