Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Alison Mercer
Alison Mercer
46 Points
Posted by Alison Mercer
Alison Mercer
46 Points

Unable to change innodb_buffer_pool_size

I am trying to change my database's innodb_buffer_pool_size but when I try it tells me it's read-only. I tried to change it in my.cnf file but corrupted the database and it ended in me having to restore everything from a backup. Does anyone know a way to change the innodb_buffer_pool_size safely? I also don't by default have any InnoDB setting in my my.cnf file they only appear in my phpmyadmin variables settings -- I am not sure why this is.

2 Answers

Alison Mercer
Alison Mercer
46 Points
Alison Mercer
Alison Mercer
46 Points

For some reason with the command SET GLOBAL innodb_buffer_pool_size=3072mib; I get the responce "#1238 - Variable 'innodb_buffer_pool_size' is a read only variable".

Steven Parker
Steven Parker
231,236 Points
Steven Parker
Steven Parker
231,236 Points

What MySQL version are you using? If it is earlier than 5.7.5, this value can only be set in the my.cnf file or on the command line when restarting the server.

Alison Mercer
Alison Mercer
46 Points
Alison Mercer
Alison Mercer
46 Points

5.5.54-0ubuntu0.12.04.1. The last time I tried to set the innodb_buffer_pool_size in the my.cnf file it ended up corrupting the database and I had to wipe my vps and restore it all from a backup.

Steven Parker
Steven Parker
231,236 Points
Steven Parker
Steven Parker
231,236 Points

Sounds like you might be ready to upgrade. It looks like the current version for your platform is mysql-server_5.7.20-1ubuntu17.04_i386 or mysql-server_5.7.20-1ubuntu17.04_amd64. Other packages available from the Download page.

Alison Mercer
Alison Mercer
46 Points
Alison Mercer
Alison Mercer
46 Points

Do you know how I can update MySQL and ubuntu over SSH, if that's possible? I was also wondering do you know if updating ubuntu will affect my PHP files, Plesk and Nginx setup, and other files. Thanks.

Steven Parker
Steven Parker
231,236 Points
Steven Parker
Steven Parker
231,236 Points

The type of connection should not be a factor in updating MySQL. Updating the operating system might be a different matter and could require physical presence at the machine. Check the OS documentation.

I'm not personally familiar with updating that OS so I'm not sure what side effects may occur.