Safely changing innodb_buffer_pool_size on MySQL replication master? We have a MySQL server which is a replication master. We need to change the innodb_buffer_pool_size and innodb_flush_method to (hopefully) improve performance as the database has grown.

What is the "safest" method for us to do this? We have 2 slave servers which will need to continue replication after this is done.

The plan so far is:


Stop replication on the 2 slave servers with STOP SLAVE.
Stop MySQL on all 3 servers with service mysql stop.
Make the changes in the my.cnf file on the master server.
Start MySQL on the master again to get the live database back online ASAP with service mysql start.
Make the changes in the my.cnf file in both the slave servers.
Start MySQL again on both slave servers with service mysql start.


The settings are:

innodb_buffer_pool_size = 54G
innodb_flush_method=O_DIRECT


The server has 64GB of memory and is a dedicated MySQL server, the server doesn't really do anything else at all. It's a Dell PowerEdge R720 running Ubuntu 12.04 Server.

Can anyone foresee any problems with this method? Is there anything else we need to be aware of?
What you have seems fine. I would add the following

Run this on the Master.

SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL sync_binlog = 1;
SET GLOBAL sync_master_info = 1;


This will cause everything that has been uncommitted to be committed on shutdown.

Then, it flushes the binlogs to disk.

On the Slaves, run this

SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL sync_binlog = 1;
SET GLOBAL sync_relay_log = 1;
SET GLOBAL sync_relay_log-info = 1;
SET GLOBAL sync_master_info = 1;


This will flush all the binlogs and relay logs. If the Slaves do not have binary logging enabled, then skip SET GLOBAL sync_binlog = 1;.

Your steps look like this now:


Stop replication on the 2 slave servers with STOP SLAVE.
Run SET GLOBAL innodb_fast_shutdown = 0; on all three servers
Run SET GLOBAL sync_binlog = 1; on the Master
Run SET GLOBAL sync_master_info = 1; SET GLOBAL sync_relay_log = 1; SET GLOBAL sync_relay_log-info = 1; SET GLOBAL sync_master_info = 1; on the Slave
Stop MySQL on all 3 servers with service mysql stop.
Make the changes in the my.cnf file on the master server.
Start MySQL on the master again to get the live database back online ASAP with service mysql start.
Make the changes in the my.cnf file in both the slave servers.
Start MySQL again on both slave servers with service mysql start.