6

I have read lot of posts & manuals to try to find out how to increase memory in MySQL 5.6 on Ubuntu, which say there are 3 methods:

  1. By editing innodb_buffer_pool_size in the my.cnf
  2. By command line option on starting MySQL
  3. Dynamically with SQL commands.

I assume 2 and 3 will be lost when MySQL is restarted, which leaves 1.

The problem is, there are no such settings in my /etc/mysql/my.conf.

The string "inno" does not exist in the conf file, nor does "buffer_size" or "buffer-size" (but "key_buffer" is in there).

I installed it via:

# apt-get install mysql-server-5.6

Any ideas where I can change the value?

Did I somehow install MySQL without the InnoDB engine?

Am I looking in the wrong file?

There are some other .cnf files under /etc/mysql, but they have nothing like innodb in them either.

mysql> show variables like 'inno%'
:
innodb_buffer_pool_size                  | 134217728              |

So it looks like InnoDB is installed, I just can't find where its config file is.

Any help appreciated.

Ola Ström
  • 177
  • 1
  • 1
  • 6
eos
  • 497
  • 3
  • 9
  • 25

4 Answers4

7

Here's what worked for me (ubuntu 19, mysql 8):

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

add in this at the bottom of the file 

innodb_buffer_pool_size        = 8G

save and exit. restart MySQL.

sudo systemctl restart mysql
Community
  • 1
FlyingZebra1
  • 186
  • 1
  • 1
2

The problem is, there are no such settings in my /etc/mysql/my.conf.

So add the lines needed to the [mysqld] section of the config file. The default my.cnf file only contains a tiny fraction of the configuration values that are available.

EEAA
  • 108,414
  • 18
  • 172
  • 242
  • there is strangeness in the syntax of my.cnf.... mostly due to the backwards compatibility of "-" vs "_" and other depricated varnames.... mysql has been around since forever – nandoP Jan 15 '15 at 03:52
  • THanks EEAA. I actually tried this, although it was guesswork as I cant find a complete example. I added the line "innodb_buffer_pool_size=512M" directly below the section [mysql], and restarted mysql (service mysql restart). Unfortunately, it made no difference, "show variables" still showed it to be 134217728 = 128MB. Any ideas? – eos Jan 17 '15 at 12:11
  • Ok, found it, you have to put the settings under [mysqld]. Not sure why only my install is missing these settings. – eos Jan 17 '15 at 12:15
  • Right, which I mentioned in my answer. :) – EEAA Jan 17 '15 at 15:27
1

On Ubuntu 20.04 LTS this work for me:

Go to /etc/mysql/conf.d/mysql.cnf

add this under [mysql]:

[mysqld]

innodb_buffer_pool_size=1G

then restart mysql:

sudo systemctl restart mysql

Go to mysql to check result:

sudo mysql

SELECT @@innodb_buffer_pool_size/1024/1024/1024;
Gerald Schneider
  • 19,757
  • 8
  • 52
  • 79
hassan b.
  • 11
  • 1
1

Write these commands in the terminal :

root:$cd /etc/mysql/
root:/etc/mysql$ sudo gedit my.cnf

Add this at the end of the file (2 GiB)

[mysqld]
innodb_buffer_pool_size = 2147483648

restart apache / mysql

root:~$ sudo service mysql restart
root:~$ sudo service apache2 restart
Swisstone
  • 6,357
  • 7
  • 21
  • 32
Ashraf CK
  • 11
  • 2