I’m absolutely 100% not a DBA. i’m more the ops side of devops. I can install mysql/maria/oracle/vertica/couchbase/cockroach/cassandra … and I can create a database, partition it, back it up etc but that’s about as far as it goes. I don’t know the intricate SQL or NOSQL or even CQL to do super complex things, that’s where DBAs come in.
Recently, I setup a galera cluster (i’ve set them up before for OpenStack, but that’s a very automated process using ansible) and I noticed that setting it up and getting it all running was as easy as, if not easier, than regular replication between database hosts.
Galera is a cluster, meaning you can connect to any node in the cluster, and you can read and write to any of them. I’m not going to go into all the benefits and disadvantages of Galera but just to show you how easy it is, I set it up on three machines in a scenario where the client wanted to go from a regular master/slave replicated setup to a full cluster being able to read and write to any node.
We have three nodes, let’s call them
db0 = 10.0.0.1 db1 = 10.0.0.2 db2 = 10.0.0.3
For the purposes of this blog post, they are running on CentOS 7, but besides the package names, the configuration will be the same no matter what linux distro you installed, so long as you are using an up-to-date version. I will also be using mariadb (10.5) for this post.
First, to make sure we’re getting the latest mariadb, setup the repo. Create the file /etc/yum.repos.d/maria.repo and paste this in:
[mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.4/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
Now rebuild the yum cache.
sudo yum makecache
Install mariadb on all your database hosts
yum install mariadb mariadb-server
add / uncomment / change the following lines:
[galera] # Mandatory settings wsrep_on=ON wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so wsrep_cluster_address="gcomm://10.0.0.1,10.0.0.2,10.0.0.3" wsrep_cluster_name="production" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="10.0.0.1" wsrep_node_name="db0" binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 # # Allow server to accept connections on all interfaces. # bind-address=0.0.0.0
Remember to change
for each node in the cluster, put in it’s IP and it’s hostname for each node. So for instance, db2 will be
Now you need to open up some firewall ports
tcp/3306 tcp & udp/4567 tcp/4568 tcp/4444
Now make sure that mariadb is stopped on db0
systemctl stop mariadb
Now start a new cluster on db0
Now start the other nodes in the cluster in sequence (and let’s set the service to start on boot)
[email protected]: systemctl enable mariadb [db0 should already be running] [email protected]: systemctl enable mariadb && systemctl start mariadb [email protected]: systemctl enable mariadb && systemctl start mariadb
After just a moment, you should find you’ve got 3 nodes, in sync, and you can read and write to any of them.
You can see how many nodes are in your cluster using
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.002 sec)
You can take one node down if it’s in sync just by using systemctl stop mariadb and then start it again afterwards, it will rejoin the cluster once it updates itself.
Wasn’t that easier than setting up replication?