Mysql – shrink ibdata1 in Galera Cluster

The InnoDB buffer pool caches queries; if you have less usage on one of the nodes then you're going to have less cache used.

If you're not properly load-balancing queries then the amount of cache in use on each node would be different. If you just turned on a node and added it to the cluster the amount of cache would be different than the others one that have been long running.

It doesn't matter if you use Galera in this case as this is a specific InnoDB question; sure you could be using Galera, but it has nothing to do with this caching question you've asked.

I have been asking the same question for several months and have also been unable to find a clear answer on the internet. Perhaps that means that I'm more concerned than I should be; perhaps I should not be doing database server maintenance...

So I was forced to do this yesterday, and everything worked well. This is how I went about it:

I upgraded the servers one at a time, using a round-robin technique. So these steps would be repeated for each server in the cluster, one at a time.

Fail the node out

Shut down MySQL. (service mysql stop on CentOS)

Uninstall Galera server

yum remove MariaDB-Galera-server on CentOS. For me an uninstall and reinstall is necessary because the package manager gave me fits on upgrade. Your mileage may vary.

Update the repository if necessary

You will need to install or configure your system to point to the repository with the later version of Galera. See the Galera repository configuration page

Install Galera server 10.0

yum install MariaDB-Galera-server on CentOS. After completing the installation, I also upgraded all other packages on the system to help ensure, the necessary client libraries and such were up to date.

Also, if your package manager renames central configuration files when software is uninstalled, ensure that your undo this after installing Galera again. Otherwise, for me, /etc/my.cnf.d/server.cnf will not have the correct information.

Startup the new version of Galera

This seemed to be the most dangerous step for me, but goes well. The server logs will likely include a few lines about columns in some internal tables. Don't worry

Upgrade the internal schemas

This is the tricky part. You want to make sure that the updates applied to the internal tables are not replicated to the other nodes in the cluster. There are two wsrep settings to help with this. One is called wsrep_desync, which is new to version 10. It does something I don't quite understand that will help prevent this node from slowing down the rest of the cluster while the upgrade is underway. The other is wsrep_on which is used to configure if the local node should replicate changes to the other nodes in the cluster.

Adjust the settings by logging in to the database locally as root.

set global wsrep_desync = on;

set global wsrep_on = off;

Now you can drop back the command line and safely upgrade the internal schemas.

mysql_upgrade --skip-write-binlog -u root -p

I use the skip-write-binlog option to further assist in keeping from replicating the updates.

(Optionally) rebuild the database

I did this because of failed past upgrades and such. It doesn't take too much longer and helps ensure that all your data is consistent and in the new formats.

mysqlcheck --optimize --all-databases -u root -p

Bring the node back into the cluster

Just reverse the wsrep_desync and wsrep_on settings and the node will resynchronize with the cluster. Log back into the MySQL command line client and set those global variables back.

set global wsrep_desync = off;

set global wsrep_on = on;