Galera Cluster: Difference between revisions

From Lolly's Wiki
Jump to navigationJump to search
m (Text replacement - "<source" to "<syntaxhighlight")
Line 6: Line 6:
On each node do as root:
On each node do as root:
* Add sources
* Add sources
<source lang=bash>
<syntaxhighlight lang=bash>
# cat > /etc/apt/sources.list.d/mariadb.list << EOF
# cat > /etc/apt/sources.list.d/mariadb.list << EOF
# MariaDB Server
# MariaDB Server
Line 24: Line 24:
</source>
</source>
* Install the packages
* Install the packages
<source lang=bash>
<syntaxhighlight lang=bash>
# apt update
# apt update
# apt install mariadb-server mariadb-backup galera-4
# apt install mariadb-server mariadb-backup galera-4
Line 32: Line 32:
===Make a CA certificate===
===Make a CA certificate===
Make a CA certificate with a very long lifetime as you dont want to make normal certificate updates at this point.
Make a CA certificate with a very long lifetime as you dont want to make normal certificate updates at this point.
<source lang=bash>
<syntaxhighlight lang=bash>
$ subject='/C=DE/ST=Hamburg/L=Hamburg/O=Organisation/OU=Databases/CN=Galera Cluster'
$ subject='/C=DE/ST=Hamburg/L=Hamburg/O=Organisation/OU=Databases/CN=Galera Cluster'
$ openssl req -new -x509 -nodes -days 365000 -newkey rsa:4096 -sha256 -keyout ca-key.pem -out ca-cert.pem -batch -subj "${subject}"
$ openssl req -new -x509 -nodes -days 365000 -newkey rsa:4096 -sha256 -keyout ca-key.pem -out ca-cert.pem -batch -subj "${subject}"
Line 38: Line 38:


===Create a certificate for each cluster node===
===Create a certificate for each cluster node===
<source lang=bash>
<syntaxhighlight lang=bash>
$ for node in {1..4}
$ for node in {1..4}
do
do
Line 51: Line 51:
===Copy keys and certificates to the nodes===
===Copy keys and certificates to the nodes===
Copy the specific keys and certs to each node:
Copy the specific keys and certs to each node:
<source lang=bash>
<syntaxhighlight lang=bash>
$ sudo mkdir --mode=0700 /etc/mysql/priv # put in here: maria-${node}.server.de-key.pem
$ sudo mkdir --mode=0700 /etc/mysql/priv # put in here: maria-${node}.server.de-key.pem
$ sudo mkdir --mode=0750 /etc/mysql/cert # put in here: maria-${node}.server.de-cert.pem , ca-cert.pem
$ sudo mkdir --mode=0750 /etc/mysql/cert # put in here: maria-${node}.server.de-cert.pem , ca-cert.pem
Line 59: Line 59:


=== Create a mariabackup user on each node ===
=== Create a mariabackup user on each node ===
<source lang=bash>
<syntaxhighlight lang=bash>
# mariadb
# mariadb
MariaDB [(none)]> grant reload, process, lock tables, replication client on *.* to 'mariabackup'@'localhost'        identified by 'the_very_secret_mariabackup_password';
MariaDB [(none)]> grant reload, process, lock tables, replication client on *.* to 'mariabackup'@'localhost'        identified by 'the_very_secret_mariabackup_password';
Line 73: Line 73:


/etc/mysql/mariadb.conf.d/zz-galera.cnf
/etc/mysql/mariadb.conf.d/zz-galera.cnf
<source lang=ini>
<syntaxhighlight lang=ini>
[galera]
[galera]
# Cluster Configuration
# Cluster Configuration
Line 96: Line 96:
== Get knowledge about your Cluster ==
== Get knowledge about your Cluster ==
=== Show wsrep_provider_options ===
=== Show wsrep_provider_options ===
<source lang=bash>
<syntaxhighlight lang=bash>
$ mariadb -NBABe 'show variables like "wsrep_provider_options"' | awk '{gsub(/$/,":\n",$1); gsub(/(;|$)/,";\n"); printf $0; }'
$ mariadb -NBABe 'show variables like "wsrep_provider_options"' | awk '{gsub(/$/,":\n",$1); gsub(/(;|$)/,";\n"); printf $0; }'
</source>
</source>

Revision as of 21:35, 25 November 2021


Setup the Cluster

Install the packages

On each node do as root:

  • Add sources

<syntaxhighlight lang=bash>

  1. cat > /etc/apt/sources.list.d/mariadb.list << EOF
  2. MariaDB Server
  3. To use a different major version of the server, or to pin to a specific minor version, change URI below.

deb [arch=amd64] http://downloads.mariadb.com/MariaDB/mariadb-10.5/repo/ubuntu $(lsb_release -cs) main

deb [arch=amd64] http://downloads.mariadb.com/MariaDB/mariadb-10.5/repo/ubuntu $(lsb_release -cs) main/debug

  1. MariaDB MaxScale
  2. To use the latest stable release of MaxScale, use "latest" as the version
  3. To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version

deb [arch=amd64] https://dlm.mariadb.com/repo/maxscale/latest/apt $(lsb_release -cs) main

  1. MariaDB Tools

deb [arch=amd64] http://downloads.mariadb.com/Tools/ubuntu $(lsb_release -cs) main EOF </source>

  • Install the packages

<syntaxhighlight lang=bash>

  1. apt update
  2. apt install mariadb-server mariadb-backup galera-4

</source>

Setup certificates for the cluster comunication

Make a CA certificate

Make a CA certificate with a very long lifetime as you dont want to make normal certificate updates at this point. <syntaxhighlight lang=bash> $ subject='/C=DE/ST=Hamburg/L=Hamburg/O=Organisation/OU=Databases/CN=Galera Cluster' $ openssl req -new -x509 -nodes -days 365000 -newkey rsa:4096 -sha256 -keyout ca-key.pem -out ca-cert.pem -batch -subj "${subject}" </source>

Create a certificate for each cluster node

<syntaxhighlight lang=bash> $ for node in {1..4} do 

 emailAddress="dbadmin@server.de"
 servername="maria-${node}.server.de"
 subject="/C=DE/ST=Hamburg/L=Hamburg/O=Organisation/OU=Databases/CN=${servername}/emailAddress=${emailAddress}"
 openssl req  -newkey rsa:4096 -nodes -keyout ${servername}-key.pem    -out ${servername}-req.pem -batch -subj "${subject}"
 openssl x509 -req -days 365000 -set_serial $(printf "%02d" "${node}") -in  ${servername}-req.pem -out ${servername}-cert.pem -CA ca-cert.pem -CAkey ca-key.pem

done </source>

Copy keys and certificates to the nodes

Copy the specific keys and certs to each node: <syntaxhighlight lang=bash> $ sudo mkdir --mode=0700 /etc/mysql/priv # put in here: maria-${node}.server.de-key.pem $ sudo mkdir --mode=0750 /etc/mysql/cert # put in here: maria-${node}.server.de-cert.pem , ca-cert.pem </source>

Configure the MariaDB Galera Cluster

Create a mariabackup user on each node

<syntaxhighlight lang=bash>

  1. mariadb

MariaDB [(none)]> grant reload, process, lock tables, replication client on *.* to 'mariabackup'@'localhost' identified by 'the_very_secret_mariabackup_password'; MariaDB [(none)]> grant reload, process, lock tables, binlog monitor on *.* to 'mariabackup'@'maria-1.server.de' identified by 'the_very_secret_mariabackup_password'; MariaDB [(none)]> grant reload, process, lock tables, binlog monitor on *.* to 'mariabackup'@'maria-2.server.de' identified by 'the_very_secret_mariabackup_password'; MariaDB [(none)]> grant reload, process, lock tables, binlog monitor on *.* to 'mariabackup'@'maria-3.server.de' identified by 'the_very_secret_mariabackup_password'; MariaDB [(none)]> grant reload, process, lock tables, binlog monitor on *.* to 'mariabackup'@'maria-4.server.de' identified by 'the_very_secret_mariabackup_password'; MariaDB [(none)]> flush privileges; MariaDB [(none)]> </source>

Galera settings

/etc/mysql/mariadb.conf.d/zz-galera.cnf <syntaxhighlight lang=ini> [galera]

  1. Cluster Configuration

wsrep_provider = /usr/lib/galera/libgalera_smm.so

  1. gcomm://{ comma seperated list of all cluster node IPs }

wsrep_cluster_address = gcomm://10.33.6.1,10.33.6.2,10.33.6.3,10.33.6.4 wsrep_cluster_name = MariaDB Galera Cluster wsrep_on = ON

  1. Snapshot state transfer (SST): copy entire database, when new node joins

wsrep_sst_method = mariabackup

  1. set the the_very_secret_mariabackup_password to your real mariabackup password

wsrep_sst_auth = mariabackup:the_very_secret_mariabackup_password

[mariadb] binlog_format = ROW innodb_autoinc_lock_mode = 2 </source>


Get knowledge about your Cluster

Show wsrep_provider_options

<syntaxhighlight lang=bash> $ mariadb -NBABe 'show variables like "wsrep_provider_options"' | awk '{gsub(/$/,":\n",$1); gsub(/(;|$)/,";\n"); printf $0; }' </source>

Retrieved from "https://lars.timmann.de/wiki/index.php?title=Galera_Cluster&oldid=2365"