2

I'm new to clustering and I'm trying to implement a Galera cluster. I'm transitioning from a standalone MySQL instance to a MariaDB Galera Cluster consisting of (at the moment) 2 nodes.

I have installed MariaDB-Galera-server.x86_64 10.0.13-1.el6 & MariaDB-client.x86_64 10.0.14-1.el6 on a CentOS 6 server.

I have also converted all the MyISAM tables to InnoDB.

My question is how do I make the switch? I was reading the (old?) codership notes on how to set up the my.cnf (https://web.archive.org/web/20131021065420/http://www.codership.com/wiki/doku.php?id=mysql_galera_configuration) and they say that these parameters are Required:

binlog_format=ROW
innodb_autoinc_lock_mode=2

My current log file (even though it's a standalone instance and not a cluster) is statement based and my database has a lot of data. How does making this change affect my current logs and current data?

Should I just make changes as per the my.cnf listed in the codership example file and play around until I get it right? Any other advice before I get started?

1 Answer 1

1

If you have a one-node cluster, add those settings to my.cnf

[mysqld]
binlog_format=ROW
innodb_autoinc_lock_mode=2

Login in a run

mysql> reset master;
mysql> set global innodb_fast_shutdown = 0;

Shutdown mysql

service mysql stop

Go to the folder where the binary logs are located and manually delete the binary logs (should only be one at this point) and also the binary log index file.

Start up MySQL

service mysql start

That's all you need to do.

5
  • Thanks, I'll give this a shot and let you know how it goes. Commented Feb 11, 2015 at 21:19
  • Thanks Rolando, the two nodes in the cluster are synced. I had to do some additional things to get it working and I need to do a lot of testing before I can deploy on production but at the very least, the replication part is working. I had to add this extra line to my my.cnf: innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend Commented Feb 18, 2015 at 20:24
  • Is this answer specific to Galera, or is it applicable to any kind of replication? Does the slave need to be reconfigured in any way? The documentation seems to suggest that the slave will fail if it's configured to use STATEMENT replication but the master's events come in as ROW, but I think that is for versions older than 10.0.22. Commented Jan 8, 2019 at 15:01
  • It's been a few years, now, so I figured I'd check: is it still necessary to delete the old binary logs before re-starting the MySQL instance? Is there a difference between MySQL and MariaDB in this regard? Commented Mar 29, 2021 at 13:13
  • Is it at all possible to ensure that any replicas have definitely fetched the latest writes to the binary log before stopping the primary server? Deleting those binary logs on the primary may end up losing data on the replicas if one isn't careful. Is the only option to stop all clients first (to ensure no new writes), then check the primary's binlog position, verify on the replicas, and then restart the primary (including possible deleting the binary logs)? Commented Mar 29, 2021 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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