0

I just started learning about cluster so I don't have much knowledge if question is not appropriate please forgive me.

I have a 3 node InnoDB cluster running .suppose if I want to tune my server by changing the value of the innodb_buffer_pool_size variable or let say I want to add a new variable in configuration file do I need to change the my.cnf file of every server or is there any simple way I can achieve this.

and how do I change the server system variable so that the setting will be replicated to other servers too?

0

Yes, you have to change the variable on every node, and if you want the change to be permanent, then you also have to change the relevant options file (e.g. my.cnf).

innodb_buffer_pool_size is dynamic (since MySQL 5.7), so at least you don't have to restart the nodes for the change to take effect:

SET GLOBAL innodb_buffer_pool_size=402653184; 

(Don't forget to also set the value in the options file so it becomes permanent, i.e. so the change survives restarts.)

EDIT

If you're using MySQL 8.0+ then there's a handy feature that allows you to set a global variable and persist it to a special options file:

SET PERSIST max_connections = 1000;

See the SET syntax documentation for more details.

  • so if i set using SET GLOBAL innodb_buffer_pool_size=402653184; command in one server it will be replicated to other nodes or i just have to do it in every server – dan Mar 8 at 11:10
  • @dan - GLOBAL doesn't actually mean it will be 'global' across the cluster, just 'global' on the node (as opposed to local to the session you have on that node). So yes, you have to do this on each node. – dbdemon Mar 8 at 11:23
  • @dan I've edited the answer as I came across a useful MySQL 8 feature that might be helpful to you. – dbdemon Mar 8 at 11:35
  • 400M is pathetically small; do you have only 2G of RAM? – Rick James Mar 13 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.