0

On production server(linux machine mysql 5.6.17), I tried to find my.cnf to change the system variables, but my.cnf was not in /etc/my.cnf location. It was in usr/ directory. Also that file contains only comments like -

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

Please tell me where should I copy my.cnf and how I can change variables like -

innodb_buffer_pool_size = 200M
max_connections = 300
Aamir
  • 738
  • 2
  • 17
  • 41

1 Answers1

3

There are various ways you can modify global variables like that.

In your my.cnf file

Add/edit the values under the [mysqld] block, so;

[mysqld]
innodb_buffer_pool_size = 200M
max_connections = 300

Then restart the mysqld daemon for changes to take affect.

Running a query

You can run the following queries to change the values for those variables, but there will be reset to default once mysqld daemon restarts/server is rebooted/etc.

SET GLOBAL innodb_buffer_pool_size = 200M;
SET GLOBAL max_connections = 300;

You can read more about that here: http://dev.mysql.com/doc/refman/5.0/en/using-system-variables.html

ʰᵈˑ
  • 11,279
  • 3
  • 26
  • 49
  • @hd, but in my.cnf everything is commented by #. After adding innodb_buffer_pool_size and max_connections, what will happen to other parameters? Will they remain same(default value)? Or I have to add all? – Aamir Oct 10 '14 at 11:08
  • They should remain as default. I run it within a development environment beforehand, as I've not had *that* much experience with altering global variables. – ʰᵈˑ Oct 10 '14 at 11:26
  • ok and what about location? should I copy my.cnf from /usr to etc/ ? – Aamir Oct 10 '14 at 11:30
  • @Aamir see this: http://stackoverflow.com/questions/580331/determine-which-config-file-is-being-used – ʰᵈˑ Oct 10 '14 at 11:57
  • link is related to mysql 5.0. I am working on 5.6, thats why I am getting trouble for my.cnf setup, any help would be greatly appreciated. – Aamir Oct 11 '14 at 08:53