How to updated my.cnf file to increase innodb-buffer-pool-size?

Question

I am trying to updated the my.cnf file to increase the buffer pool size for a query I am running in python via mysql because I keep getting the error:

OperationalError: 2013 (HY000): Lost connection to MySQL server during query

Now I have located the file and get:

/opt/homebrew/etc/my.cnf
/opt/homebrew/Cellar/mysql/8.0.30/.bottle/etc/my.cnf

Now I am trying to figure out how update this file. I have read using sudo command then enter the password, which I do but I get command not found. I am assuming I am doing this wrong.

sudo: /opt/homebrew/etc/my.cnf: command not found

Any and all help will be much appreciated.

---

After attempting to do the nano command my screen gives me this. So leads me to believe that this is not where the file is.

This is what is seen when doing this command:

sudo /opt/homebrew/etc/my.cnf

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1

Answer 1

Change the my.cnf file as follows:

Launch a terminal and go to the directory containing the my.cnf file. In your instance, it's at /opt/homebrew/etc/my.cnf or /opt/homebrew/Cellar/mysql/8.0.30/.bottle/etc/my.cnf.

To open the file with the nano text editor, use sudo nano my.cnf. If you are log in as sudo try to use nano my.cnf without sudo.

After opening the file, go to the [mysqld] section and add the following line: value innodb buffer pool size (replace value with the desired buffer pool size in bytes)

To save the modifications, use Ctrl + O, and to quit nano, press Ctrl + X.

To make the modifications take effect, restart the MySQL server. Depending on your system, you may do this by typing'sudo service mysql restart' or'sudo systemctl restart mysql'.

If you do not want to use nano, you can use any text editor of your choice.

Answer 2

The command you posted attempts to run the my.cnf file like it was a program:

# NO!Do not do this.
sudo /opt/homebrew/etc/my.cnf

If you want to edit a file, you can use a program like nano to edit this file:

sudo nano /opt/homebrew/etc/my.cnf

That will show you the contents of the my.cnf file and you can use the cursor keys to move around and make changes to the file. BE CAREFUL. You should probably make a backup of the file before attempting any changes.

When you are satisfied, hit ctrl-o to output the changes and then ctrl-x to exit nano, then restart mysql for the changes to take effect.

If you are not sure where your my.cnf file is located, it can be sorta tricky to find. Evidently MySQL will sometimes load more than one my.cnf file, each overriding the last. More info here.

You may find this command helpful to locate all the files named 'my.cnf' on your machine's file system:

sudo find / -name "my.cnf" 2>/dev/null

There's also a pretty cool tip here which works on my ubuntu workstation but may or may not for your macos situation. First, find out where mysqld is:

which mysqld

In my case, this returns /usr/sbin/mysqld which I use to create this command:

/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

The output of that command lists 3 my.cnf file locations. Yours may be different.

AFTER MAKING CHANGES TO MY.CNF DON'T FORGET TO RESTART MYSQL.