On 2021-02-11 12:58 pm, Alex wrote:
> Hi,
>
>> I've had good luck with using mariadb and galera to share the spamassassin
>> database across systems. I run a small 3-node setup for email, 2x servers
>> running dovecot replicating to each other, and a 3rd galera quorum server.
>> Mariadb is master-master across all 3 nodes, so changes on any one are
>> replicated to all the others via vpn. Works well, and for the amount of data
>> in the spamassassin database, it replicates very quickly.
>
> This sounds very interesting to me. Can you share more details about
> your configuration? I haven't worked with galera before, but have some
> experience with mariadb - it's currently set up as a single master
> with the actual mail relays being set up as slaves. I'd imagine the
> first thing is to convert them all to masters...
>
> Any help would be greatly appreciated.
> Thanks,
> Alex
Sure. I have a (fairly complex) ansible playbook that sets up the whole
3-node cluster, but here are the relevant details.
This is the galera portion of _/etc/mysql/my.cnf_
> #
> # * Galera-related settings
> #
> [galera]
> bind-address = 0.0.0.0
> binlog_format = row
> default_storage_engine = InnoDB
> innodb_autoinc_lock_mode = 2
> innodb_flush_log_at_trx_commit = 2
> wsrep_cluster_address = gcomm://master.vpn,dove1.vpn,dove2.vpn
> wsrep_node_name = master.vpn
>
> # Need to specify vpn address here, not public address
> wsrep_node_address = 192.168.100.50
>
> wsrep_cluster_name = my_cluster
> wsrep_on = 1
> wsrep_provider = /usr/lib/galera/libgalera_smm.so
> wsrep_sst_auth = "root:my_sekrit_password"
And this is the ansible role mariadb_restart/tasks/main.yml. This gets
called whenever another mariadb-affecting task sets the DO_RESTART
variable. This will cleanly restart the whole mariadb galera cluster.
> - become: yes
> block:
>
> - name: Check status of mysqld
> command: systemctl status mysql
> ignore_errors: yes
> changed_when: false
> register: mysql_status
>
> - name: Gracefully stop mysql on all nodes to start up cluster
> service:
> name: "mysql"
> state: "stopped"
> register: mysql_stopped
> when: mysql_status is succeeded
>
> - name: Force kill mysqld if stuck in starting state
> command: pkill -9 mysqld
> ignore_errors: yes
> changed_when: false
> when: mysql_stopped is failed
>
> - name: Clear RAM caches to free up space
> command: sysctl -w vm.drop_caches=3
> when: ansible_virtualization_type != "openvz"
> changed_when: false
>
> - name: Check if grastate.dat file exists for bootstrapping node0
> stat:
> path: /var/lib/mysql/grastate.dat
> register: grastate_exists
> when: inventory_hostname == play_hosts[0]
>
> - name: Force node0 to be a new bootstrap node
> lineinfile:
> dest: /var/lib/mysql/grastate.dat
> regexp: 'safe_to_bootstrap: 0'
> line: 'safe_to_bootstrap: 1'
> when:
> - inventory_hostname == play_hosts[0]
> - grastate_exists.stat.exists
>
> - name: bootstrap a new cluster with galera_new_cluster
> shell: /usr/bin/galera_new_cluster
> when: inventory_hostname == play_hosts[0]
>
> - name: add slave nodes to the cluster
> service:
> name: "mysql"
> state: "started"
> when: inventory_hostname != play_hosts[0]
>
> - name: Stop mysql on node0
> service:
> name: "mysql"
> state: "stopped"
> when: inventory_hostname == play_hosts[0]
>
> - name: re-add master node to the cluster
> service:
> name: "mysql"
> state: "started"
> when: inventory_hostname == play_hosts[0]
>
> #
> # block
> when: do_restart | bool
Argh - formatting got messed up. But you get the idea. It can also be
run via a small script that runs ansible-playbook like this
> ansible-playbook -K -i hosts mariadb.yml --extra-vars "{do_restart: true}"
In the list of hosts, node0 is the master or quorum node. The other two
are the dovecot replication nodes (dovecot, exim4, roundcube, etc)
I have a bash alias to check on cluster status ...
> alias cluster='mysql -B -s -N -e "show status like "%wsrep_cluster%";"'
>
> dc@master:~$ cluster
> wsrep_cluster_weight 3
> wsrep_cluster_capabilities
> wsrep_cluster_conf_id 10
> wsrep_cluster_size 3
> wsrep_cluster_state_uuid 480b440d-6643-11eb-94bc-5b47cf0676a8
> wsrep_cluster_status Primary
