Mariadb Replication

I have installed mariadb104-server-10.4.6 galera: 25.3.26 FreeBSD 12
This is my.cnf.

Code:
root@freebsd:/usr/local/etc # nano my.cnf
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/local/lib/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://192.168.64.143,192.168.64.144"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="this_node_ip"
wsrep_node_name="this_node_name"

I have this error when trying to start mariadb.
Code:
root@freebsd:/usr/local/etc # service mysql-server start --wsrep-new-cluster
/usr/local/etc/rc.d/mysql-server: ERROR: --wsrep-new-cluster not found in mysql_instances
Please help me. What I have to do?
 
You cannot add that option to the service(8) command. The additional argument is meant to be used to indicate the MySQL/MariaDB instance (the rc(8) scripts allow you to run multiple instances).
 
This error with rc
root@freebsd:/usr/local/etc/rc.d # /usr/local/etc/rc.d/mysql-server start --wsrep-new-cluster
/usr/local/etc/rc.d/mysql-server: ERROR: --wsrep-new-cluster not found in mysql_instances
 
The service(8) command simply starts the appropriate rc(8) script. So it doesn't matter how you start it, it's still an invalid argument.

Add the argument to mysql_args in rc.conf.
 
Dear all,


I'm trying (for testing purposes) to build a cluster of 3 MariaDB 10.5 installs using Galera 26 (in Ports, it's really the 4th version) on FreeBSD 13. I'm hitting some kind of issues I'm unable to understand. If anyone has some detailed insight, or experience with this software, any comments are more than welcome.

First of all, a "tricky" thing that must be done with Galera when starting the cluster. An already running cluster is expected for any node to join, even for the first one. If I haven't explained myself precisely or correctly, anyone can have a read here for better understanding.


Now, to accomplish a much needed start up of Galera one needs to place this argument: --wsrep-new-cluster

As SirDice explains to use the service mechanism in FreeBSD an argument for mysql must be included in /etc/rc.conf. Mine reads as follows:

Code:
mysql_enable="YES"
mysql_args="--wsrep-new-cluster"
mysql_optfile="/usr/local/etc/mysql/my.cnf"

And in this order, otherwise 4 processes are created instead of 2. I have no clue how, why, the whole lot, I'm just an user.

I've tried with different /usr/local/etc/mysql/my.cnf setups such as this:

Code:
#
# This group is read both by the client and the server
# use it for options that affect everything, see
# https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#option-groups
#
[client-server]
port    = 3306
socket  = /var/run/mysql/mysql.sock

#
# include *.cnf from the config directory
#
!includedir /usr/local/etc/mysql/conf.d/

[mysqld]

# (This must be substituted by wsrep_format)
binlog_format=ROW

# Currently only InnoDB storage engine is supported
default-storage-engine=innodb

# to avoid issues with 'bulk mode inserts' using autoinc
innodb_autoinc_lock_mode=2

# Override bind-address
# In some systems bind-address defaults to 127.0.0.1, and with mysqldump SST
# it will have (most likely) disastrous consequences on donor node
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=1
wsrep_provider=/usr/local/lib/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://192.168.1.73,192.168.1.79,192.168.1.80"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.1.73"
wsrep_node_name="Cluster1"

And two variations of this above, by removing these directives at the top.

A removed bit here:

Code:
#
# This group is read both by the client and the server
# use it for options that affect everything, see
# https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#option-groups
#
[client-server]
port    = 3306
socket  = /var/run/mysql/mysql.sock

The second bit removed here:

Code:
#
# include *.cnf from the config directory
#
!includedir /usr/local/etc/mysql/conf.d/

Any of these three "configs" hasn't worked. But nothing I've done so far has.

The actual status is:

Code:
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$ sudo service mysql-server status
mysql is not running.
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$

[albert@Cluster1 /usr/local/etc/mysql/conf.d]$ ps aux | grep mysql
mysql  2625   0.0  0.2  13628  3168  -  Is   00:15    0:00.01 /bin/sh /usr/local/bin/mariadbd-safe --defaults-extra-file=/usr/local/etc/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --
mysql  2922   0.0  2.4 215076 49064  -  I    00:15    0:00.05 /usr/local/libexec/mariadbd --defaults-extra-file=/usr/local/etc/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --p
albert 2944   0.0  0.0    432   244  1  R+   00:16    0:00.00 grep mysql
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$

This is the current content at the /usr/local/etc/mysql/my.cnf file:

Code:
[albert@Cluster1 ~]$ cat /usr/local/etc/mysql/my.cnf
#
# This group is read both by the client and the server
# use it for options that affect everything, see
# https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#option-groups
#
[client-server]
port    = 3306
socket  = /var/run/mysql/mysql.sock

#
# include *.cnf from the config directory
#
!includedir /usr/local/etc/mysql/conf.d/
[albert@Cluster1 ~]$

The file governing the wsrep Galera replication placed at /usr/local/etc/mysql/conf.d/wsrep.cnf now reads as follows:

Code:
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$ cat wsrep.cnf
# This file contains wsrep-related mysqld options. It should be included
# in the main MySQL configuration file.
#
# Options that need to be customized:
#  - wsrep_provider
#  - wsrep_cluster_address
#  - wsrep_sst_auth
# The rest of defaults should work out of the box.

##
## mysqld options _MANDATORY_ for correct opration of the cluster
##
[mysqld]

# (This must be substituted by wsrep_format)
binlog_format=ROW

# Currently only InnoDB storage engine is supported
default-storage-engine=innodb

# to avoid issues with 'bulk mode inserts' using autoinc
innodb_autoinc_lock_mode=2

# Override bind-address
# In some systems bind-address defaults to 127.0.0.1, and with mysqldump SST
# it will have (most likely) disastrous consequences on donor node
bind-address=0.0.0.0

##
## WSREP options
##

# Enable wsrep
wsrep_on=1

# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/local/lib/libgalera_smm.so

# Provider specific configuration options
#wsrep_provider_options=

# Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="test_cluster"

# Group communication system handle
wsrep_cluster_address="gcomm://192.168.1.73,192.168.1.79,192.168.1.80"

# Human-readable node name (non-unique). Hostname by default.
wsrep_node_name=Cluster1

# Base replication <address|hostname>[:port] of the node.
# The values supplied will be used as defaults for state transfer receiving,
# listening ports and so on. Default: address of the first network interface.
wsrep_node_address=192.168.1.73

# Address for incoming client connections. Autodetect by default.
#wsrep_node_incoming_address=

# How many threads will process writesets from other nodes
wsrep_slave_threads=1

# DBUG options for wsrep provider
#wsrep_dbug_option

# Generate fake primary keys for non-PK tables (required for multi-master
# and parallel applying operation)
wsrep_certify_nonPK=1

# Maximum number of rows in write set
wsrep_max_ws_rows=0

# Maximum size of write set
wsrep_max_ws_size=2147483647

# to enable debug level logging, set this to 1
wsrep_debug=0

# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=0

# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1

# change auto_increment_increment and auto_increment_offset automatically
wsrep_auto_increment_control=1

# retry autoinc insert, which failed for duplicate key error
wsrep_drupal_282555_workaround=0

# enable "strictly synchronous" semantics for read operations
wsrep_causal_reads=0

# Command to call when node status or cluster membership changes.
# Will be passed all or some of the following options:
# --status  - new status of this node
# --uuid    - UUID of the cluster
# --primary - whether the component is primary or not ("yes"/"no")
# --members - comma-separated list of members
# --index   - index of this node in the list
wsrep_notify_cmd=

##
## WSREP State Transfer options
##

# State Snapshot Transfer method
wsrep_sst_method=rsync

# Address which donor should send State Snapshot to.
# Should be the address of THIS node. DON'T SET IT TO DONOR ADDRESS!!!
# (SST method dependent. Defaults to the first IP of the first interface)
#wsrep_sst_receive_address=

# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:<root password>
wsrep_sst_auth=root:

# Desired SST donor name.
#wsrep_sst_donor=

# Reject client queries when donating SST (false)
#wsrep_sst_donor_rejects_queries=0

# Protocol version to use
# wsrep_protocol_version=
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$

The error output I see in the logs is:

Code:
2021-12-07  0:15:57 0 [ERROR] WSREP: exception from gcomm, backend must be restarted: failed to set FD_CLOEXEC: 9 (Bad file descriptor)
         at /wrkdirs/usr/ports/databases/galera26/work/galera-release_26.4.9/galerautils/src/gu_asio_socket_util.hpp:set_fd_options():41
2021-12-07  0:15:57 0 [Note] WSREP: gcomm: terminating thread
2021-12-07  0:15:57 0 [Note] WSREP: gcomm: joining thread
2021-12-07  0:15:57 0 [Note] WSREP: gcomm: closing backend
2021-12-07  0:15:57 0 [Note] WSREP: Forced PC close
2021-12-07  0:15:57 0 [Note] WSREP: gcomm: closed
2021-12-07  0:15:57 0 [ERROR] WSREP: Failed to connect to cluster: GCommConn::get_mtu(): backend connection not open (FATAL)
         at /wrkdirs/usr/ports/databases/galera26/work/galera-release_26.4.9/gcs/src/gcs_gcomm.cpp:get_mtu():221
2021-12-07  0:15:57 0 [ERROR] WSREP: wsrep::connect(gcomm://192.168.1.73,192.168.1.79,192.168.1.80) failed: 7
2021-12-07  0:15:57 0 [ERROR] Aborting

I've seen a bug filed a few days ago for the FD_CLOEXEC:9 error but no reply, nothing as of today.


But I'm not sure if that's a bug or a missconfig.

Anyhow, I'm worried the parameters I'm passing to MariaDB (myslqd) at start up time are wrong. I've even tried some of the "tricks" described here, but some don't work and definitely I'm a bit lost in that long configuration setup. To me the key in all this is starting up the cluster and the way FreeBSD handles that isn't the same as in sysv nor systemd. Bug? Maybe. Me missing some configuration bits? Quite possible. Guides on the internet on this particulara on FreeBSD? None that I've found aside from that other one I shared above.

If anyone can shed some light or point out anything, thanks in advance.

Best regards
 
It seems coincidental that you're bug/issue is the same as the one you linked to. I would sign up for bugs and add yourself as a "me too" to the PR, that way it is potentially more noticeable for the port maintainer.

Also, I see wsrep_on=1 where the documentation says wsrep_on=ON but it also says it's a boolean (100% confusing unless they are analogous).
 
It seems coincidental that you're bug/issue is the same as the one you linked to. I would sign up for bugs and add yourself as a "me too" to the PR, that way it is potentially more noticeable for the port maintainer.

Also, I see wsrep_on=1 where the documentation says wsrep_on=ON but it also says it's a boolean (100% confusing unless they are analogous).
Thanks for the tip, but it hasn't worked out as you can read below trying this you mentioned.

Code:
# Enable wsrep
wsrep_on=ON

# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/local/lib/libgalera_smm.so

# Provider specific configuration options
#wsrep_provider_options=

# Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="test_cluster"

# Group communication system handle
wsrep_cluster_address="gcomm://192.168.1.73,192.168.1.79,192.168.1.80"

# Human-readable node name (non-unique). Hostname by default.
wsrep_node_name=Cluster1
wsrep.cnf: 125 lines, 3553 characters.
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$ ps aux | grep mysql
mysql  2299   0.0  0.2  13628  3168  -  Is   18:01    0:00.01 /bin/sh /usr/local/bin/mariadbd-safe --defaults-extra-file=/usr/local/etc/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --
mysql  2596   0.0  2.3 215076 47696  -  I    18:01    0:00.07 /usr/local/libexec/mariadbd --defaults-extra-file=/usr/local/etc/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --p
albert 2653   0.0  0.0    432   244  0  R+   18:27    0:00.00 grep mysql
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$ sudo kill -9 2596
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$ ps aux | grep mysql
albert 2659   0.0  0.0   432  244  0  R+   18:27    0:00.00 grep mysql
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$ sudo service mysql-server status
mysql is not running.
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$ sudo service mysql-server start
Starting mysql.
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$ sudo service mysql-server status
mysql is not running.
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$ ps aux | grep mysql
mysql  2677   0.0  0.2  13628  3232  -  Is   18:28    0:00.01 /bin/sh /usr/local/bin/mariadbd-safe --defaults-extra-file=/usr/local/etc/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --
mysql  2974   0.0  2.3 215076 47760  -  I    18:28    0:00.06 /usr/local/libexec/mariadbd --defaults-extra-file=/usr/local/etc/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --p
albert 2996   0.0  0.0    432   244  0  R+   18:28    0:00.00 grep mysql
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$ sudo mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (61)
[albert@Cluster1 /usr/local/etc/mysql/conf.d]$

I've reported my case in the same bug. There's no maintainer assigned anyways, so I've filed another one in MariaDB's bug tracker for Galera issues.

 
i got my cluster to initialise with:

sysrc mysql_args="--wsrep-on --wsrep-new-cluster --wsrep_cluster_address=gcomm://172.16.255.1"

do not stop this master until you have a second node registered as it's in some sort of staged setup … and doesn’t actually work until the second node is registered.

after the second note has registered, you need to reset mysql_args to ""

while this will get the cluster up, if you shutdown all the nodes then the cluster does not resume on restart.
i don't know if im doing something wrong, but one node must always be up.
 
It's sad that no one has come back to complete this question. Others will need to know these things and the question remains unknown.
 
i got my cluster to initialise with:
...
while this will get the cluster up, if you shutdown all the nodes then the cluster does not resume on restart.
i don't know if im doing something wrong, but one node must always be up.
Apparently, if all three [or more] nodes are down, you have to bring the cluster up starting from the node that has the most recent version of the database. I'd guess this [mostly?] means the node that was shut down the last.

All Three Nodes Are Gracefully Stopped

The cluster is completely stopped and the problem is how to initialize it again. It is important that a node writes its last executed position to the grastate.dat file.

By comparing the seqno number in this file, you can see which is the most advanced node (most likely the last stopped). The cluster must be bootstrapped using this node, otherwise nodes that had a more advanced position will have to perform the full SST to join the cluster initialized from the less advanced one. As a result, some transactions will be lost). To bootstrap the first node, invoke the startup script like this:

 
Back
Top