Wissen

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: Start » datenbanken » galera
Zuletzt angesehen: • freebsd-14.0-stable-image • srpp • mysql_tuning • freebsd_-_paket-management • freebsd_-_formatieren_mit_ufs • ssl-schluessel_generieren • festplatten_partitionen_mounten • galera
datenbanken:galera

Inhaltsverzeichnis

Galera

mehrere PXC-Instanzen auf dem selben Rechner betreiben

Best Practices for Configuring Optimal MySQL Memory Usage

Wenn man auf einem Rechner mehr als eine PXC-Instanz betreiben möchte, ist zu beachten, dass es dann zunehmend den Fall geben kann, dass eine Instanzen nicht starten kann, weil einer der Ports, die sie öffne muß, bereits belegt ist. Um das zu verhindern, kann man dem Kernel sagen (net.ipv4.ip_local_port_range), welchen Port-Bereich er nutzen soll, damit der von Galera verwendete Bereich, frei bleibt.

/etc/sysctl.conf
### damit Galera alle Ports unterhalb von 40000 frei nutzen kann
### https://www.cyberciti.biz/tips/linux-increase-outgoing-network-sockets-range.html
#net.ipv4.ip_local_port_range = 40000 65535
net.ipv4.ip_local_port_range = 40000 60999

MySQL 8.0

Zuerst wird ein globaler ZFS-Pool angelegt, indem dann jede einzelne MySQL-Instanz sein eigenes ZFS-Dataset (Volumen) bekommt.

ZFS-RAID-10
> zpool create -m /var/lib/mysql mysql_datadir mirror /dev/nvme0n1 /dev/nvme1n1 mirror /dev/nvme2n1 /dev/nvme3n1
> chown -R mysql:mysql /var/lib/mysql/
 
> zfs get mountpoint,compression,encryption
NAME           PROPERTY     VALUE           SOURCE
mysql_datadir  mountpoint   /var/lib/mysql  local
mysql_datadir  compression  on              default
mysql_datadir  encryption   off             default
/etc/mysql/meine.conf.d/max_flushing.cnf
[mysqld]
# https://www.percona.com/blog/2020/05/07/prepare-mysql-for-a-safe-shutdown/
# 4. Configure InnoDB for Max Flushing. 
# SET GLOBAL innodb_fast_shutdown=0; SET GLOBAL innodb_max_dirty_pages_pct=0; SET GLOBAL innodb_change_buffering='none';
innodb_fast_shutdown			= 0
innodb_max_dirty_pages_pct		= 0
innodb_max_dirty_pages_pct_lwm		= 0
innodb_change_buffering			= 'none'
/etc/mysql/meine.conf.d/ssl.cnf
[mysqld]
#
# * SSL
#
# ssl-mode = DISABLED PREFERRED REQUIRED VERIFY_CA VERIFY_IDENTITY
#ssl-mode			= PREFERRED		# unknown variable 'ssl-mode=PREFERRED'
#ssl-mode			= VERIFY_CA		# nur CA muß passen
#ssl-mode			= VERIFY_IDENTITY	# CA + Hostnamen müssen passen
#ssl				= ON			# Deprecated, use --ssl-mode.
ssl-ca				= /etc/mysql/ca.pem
ssl-cert			= /etc/mysql/server-cert.pem
ssl-key  			= /etc/mysql/server-key.pem
#
### TLS-Version
tls-version			= TLSv1.3,TLSv1.2
#
### nur noch verschlüsselte Verbindungen möglich
require_secure_transport	= ON
 
[sst]
# https://galeracluster.com/library/documentation/xtrabackup-options.html
compressor                      = "gzip -1"
decompressor                    = "gzip -dc"
rebuild                         = ON
compact                         = ON
encrypt				= 4
ssl-ca				= /etc/mysql/ca.pem
ssl-cert			= /etc/mysql/server-cert.pem
ssl-key  			= /etc/mysql/server-key.pem
/root/bin/Neue_MySQL-Instanz_mysqld_cnf.txt
#==============================================================================#
 
VERSION="v2025043000"           # Konfiguration von max_connections + max_connect_errors ausgelagert
 
#------------------------------------------------------------------------------#
### die unterschiedlichen MySQL-DBMS-Instanz werden auch etwas unterschiedlich konfiguriert
### hier muß man vorsichtig sein, weil der RAM auch mal ein voll ist...
 
RICHTIGE_PORT_NUMMER_01="$(for PN in 13320 13322 13330 13336 13337 13341 13356 13359 13366 13367; do echo "${PN}"; done | grep -F "${PORTNUMMER}" >/dev/null && echo "Ja")"
RICHTIGE_PORT_NUMMER_02="$(for PN in 13329 13355; do echo "${PN}"; done | grep -F "${PORTNUMMER}" >/dev/null && echo "Ja")"
RICHTIGE_PORT_NUMMER_03="$(for PN in 13324 13357; do echo "${PN}"; done | grep -F "${PORTNUMMER}" >/dev/null && echo "Ja")"
RICHTIGE_PORT_NUMMER_04="$(for PN in 13319 13354; do echo "${PN}"; done | grep -F "${PORTNUMMER}" >/dev/null && echo "Ja")"
RICHTIGE_PORT_NUMMER_05="$(for PN in 13353; do echo "${PN}"; done | grep -F "${PORTNUMMER}" >/dev/null && echo "Ja")"
if [ Ja = "${RICHTIGE_PORT_NUMMER_01}" ] ; then
        echo "max_connections                           = 200"
        echo "max_connect_errors                        = 210"
elif [ Ja = "${RICHTIGE_PORT_NUMMER_02}" ] ; then
        echo "max_connections                           = 300"
        echo "max_connect_errors                        = 310"
elif [ Ja = "${RICHTIGE_PORT_NUMMER_03}" ] ; then
        echo "max_connections                           = 400"
        echo "max_connect_errors                        = 410"
elif [ Ja = "${RICHTIGE_PORT_NUMMER_04}" ] ; then
        echo "max_connections                           = 600"
        echo "max_connect_errors                        = 610"
elif [ Ja = "${RICHTIGE_PORT_NUMMER_05}" ] ; then
        echo "max_connections                           = 3000"
        echo "max_connect_errors                        = 3010"
else
        echo "max_connections                           = 100"
        echo "max_connect_errors                        = 110"
fi >> /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/mysqld.cnf
#------------------------------------------------------------------------------#
/root/bin/Neue_MySQL-Instanz_generieren.sh
#!/bin/bash
 
#==============================================================================#
#
# Mir diesem Skript kann man, mit einem einzigen Kommando,
# eine voll funktionstüchtige MySQL-DBMS-Instanz erstellen.
#
#==============================================================================#
 
#VERSION="v2025042500"		# initiale Erstellung
#VERSION="v2025050600"          # ZFS-Tuning: compression=lz4 recordsize=1M
VERSION="v2025051400"           # Fehler behoben
 
#------------------------------------------------------------------------------#
#### Sicherheitsüberprüfungen
 
# Prüfen, ob die übergebene Option leer ist.
if [ x = "x${1}" ] ; then
	echo 'Es muß eine Portnummer für die neue MySQL-DBMS-Instanz übergeben werden!'
	echo "    ${0} [Portnummer]"
	echo "zum Beispiel:"
	echo "    ${0} 13309"
	echo "Abbruch"
	exit 1
fi
 
# Prüfen, ob die übergebene Option eine Integer-Zahl, mit fünf Stellen, ist.
I_FUENF="$(echo "${1}" | grep -E '^[0-9]{5}$')"
if [ x = "x${I_FUENF}" ] ; then
	echo 'Bitte übergeben sie diesem Skript die gewünschte Portnummer.'
	echo 'Die übergebene Option muß eine Integer-Zahl, mit fünf Stellen, sein!'
	echo 'Abbruch'
	exit 1
else
	PORTNUMMER="${1}"
fi
 
if [ -e "/var/lib/mysql/port_${PORTNUMMER}" ] ; then
	echo 'Das gesuchte Verzeichnis existiert schon!'
	ls -d "/var/lib/mysql/port_${PORTNUMMER}"
	echo "Abbruch"
	exit 1
fi
 
#------------------------------------------------------------------------------#
### Aktion: SystemD-Dateien erstellen
 
DATEI_01="/etc/default/mysql.${PORTNUMMER}"
if [ -e "${DATEI_01}" ] ; then
	echo "${DATEI_01} existiert schon"
else
	echo "EXTRA_ARGS=\" --defaults-file=/var/lib/mysql/port_${PORTNUMMER}/etc/my.cnf \"" > ${DATEI_01}
	echo "MY_CNF_ARGS=\" --defaults-file=/var/lib/mysql/port_${PORTNUMMER}/etc/my.cnf \"" >> ${DATEI_01}
fi
ls -lha ${DATEI_01}
 
DATEI_02="/etc/default/mysql.${PORTNUMMER}.bootstrap"
if [ -e "${DATEI_02}" ] ; then
	echo "${DATEI_02} existiert schon"
else
	echo "EXTRA_ARGS=\" --defaults-file=/var/lib/mysql/port_${PORTNUMMER}/etc/my.cnf --wsrep-new-cluster \"" > ${DATEI_02}
	echo "MY_CNF_ARGS=\" --defaults-file=/var/lib/mysql/port_${PORTNUMMER}/etc/my.cnf \"" >> ${DATEI_02}
fi
ls -lha ${DATEI_02}
 
### diese Datei ist nicht speziell für eine Portnummer
### und gehört deshalb zur Allgemeinen/Globalen Konfiguration
DATEI_03="/usr/bin/mysql-systemd_port"
if [ -x "${DATEI_03}" ] ; then
	echo "${DATEI_03} existiert schon"
else
	cat /usr/bin/mysql-systemd | sed 's/my_print_defaults /&${MY_CNF_ARGS} /;' > ${DATEI_03}
	chmod 0755 ${DATEI_03}
fi
ls -lha ${DATEI_03}
 
### diese Datei ist nicht speziell für eine Portnummer
### und gehört deshalb zur Allgemeinen/Globalen Konfiguration
DATEI_04="/usr/lib/systemd/system/mysql_port@.service"
if [ -x "${DATEI_04}" ] ; then
	echo "${DATEI_04} existiert schon"
else
	cat /usr/lib/systemd/system/mysql@.service | sed 's/^Alias=.*/Alias=mysqld_port.service/;s/mysql-systemd /mysql-systemd_port /' > ${DATEI_04}
fi
ls -lha ${DATEI_04}
 
#------------------------------------------------------------------------------#
### Aktion: ZFS-Volumen mit Verzeichnissen erstellen
#
# Es wird empfohlen, für DataDir und Log-Bin unterschiedliche ZFS-Volumen (DataSets)
# zu verwenden und das ZFS-Volumen für DataDir mit "recordsize=16K" zu konfigurieren.
#
 
zfs create -o mountpoint=/var/lib/mysql/port_${PORTNUMMER} mysql_datadir/port_${PORTNUMMER}
chown -R mysql:mysql /var/lib/mysql/port_${PORTNUMMER}
 
# https://www.zfshandbook.com/docs/advanced-zfs/performance-tuning/
# https://openzfs.readthedocs.io/en/latest/performance-tuning.html
zfs set compression=lz4 mysql_datadir/port_${PORTNUMMER}
zfs set recordsize=16K mysql_datadir/port_${PORTNUMMER}
zfs set logbias=throughput mysql_datadir/port_${PORTNUMMER}
 
mkdir -p /var/lib/mysql/port_${PORTNUMMER}/datadir/
mkdir -p /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/
mkdir -p /var/lib/mysql/port_${PORTNUMMER}/XtraBackup/
mkdir -p /var/lib/mysql/port_${PORTNUMMER}/log_bin/
mkdir -p /var/lib/mysql/port_${PORTNUMMER}/relay_log/
mkdir -p /var/lib/mysql/port_${PORTNUMMER}/log
mkdir -p /var/lib/mysql/port_${PORTNUMMER}/tmpdir
 
# Insecure configuration for --secure-log-path: Location is accessible to all OS users. Consider choosing a different directory.
chmod 0750 /var/lib/mysql/port_${PORTNUMMER}/log
chmod 0777 /var/lib/mysql/port_${PORTNUMMER}/tmpdir
 
chown -R mysql:mysql /var/lib/mysql/port_${PORTNUMMER}
 
#------------------------------------------------------------------------------#
### Aktion: MySQL-CNF-Dateien erstellen
 
mkdir -p /var/run/mysqld/
chown mysql:mysql /var/run/mysqld/
 
#==========================================#
# my.cnf
echo "# /var/lib/mysql/port_${PORTNUMMER}/etc/my.cnf
!includedir /etc/mysql/meine.conf.d/
!includedir /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/
" > /var/lib/mysql/port_${PORTNUMMER}/etc/my.cnf
 
#==========================================#
# client.cnf
echo "# /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/client.cnf
[client]
port                                    = ${PORTNUMMER}
socket                                  = /var/run/mysqld/mysqld_${PORTNUMMER}.sock
" > /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/client.cnf
 
#==========================================#
# mysqld.cnf
SERVER_ID="$(hostname -f | sed 's/[0-9][a-zA-Z][-].*[0-9][0-9]/"&/; s/^[^"]*["]//; s/^.//; s/^./&"/; s/["][^"]*$//; s/[aA]/1/;s/[bB]/2/;s/[cC]/3/;')"   # 3
REPORTNUMMER="$(echo "${PORTNUMMER}" | awk '{print $1 - 10000}')"
REPORTKENNUNG="$(hostname -f | sed 's/[0-9][a-zA-Z][-].*[0-9][0-9]/"&"/; s/^[^"]*["]//; s/["][^"]*$//; s/[-][^0-9]*//;')"       # 1C01
MYSQLX_PORT="$(echo "${PORTNUMMER}" | awk '{print $1 + 100}')"
ADMIN_PORT="$(echo "${PORTNUMMER}" | awk '{print $1 + 200}')"
echo "# /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/mysqld.cnf
#------------------------------------------------------------------------------#
[mysqld]
server_id                               = ${SERVER_ID}
report_host                             = mysql_${REPORTNUMMER}-${REPORTKENNUNG}
port                                    = ${PORTNUMMER}
mysqlx_port                             = ${MYSQLX_PORT}
admin_port                              = ${ADMIN_PORT}
datadir                                 = /var/lib/mysql/port_${PORTNUMMER}/datadir
socket                                  = /var/run/mysqld/mysqld_${PORTNUMMER}.sock
mysqlx_socket                           = /var/run/mysqld/mysqlx_${PORTNUMMER}.sock
secure_log_path                         = /var/lib/mysql/port_${PORTNUMMER}/log
log-error                               = /var/lib/mysql/port_${PORTNUMMER}/log/mysqld_error.log
general_log_file                        = /var/lib/mysql/port_${PORTNUMMER}/log/mysqld_general.log
slow_query_log_file                     = /var/lib/mysql/port_${PORTNUMMER}/log/mysqld_slow_query.log
 
tmpdir                                  = /var/lib/mysql/port_${PORTNUMMER}/tmpdir
# Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds              = 604800
log_bin                                 = /var/lib/mysql/port_${PORTNUMMER}/log_bin/mysql-bin
log_bin_index                           = /var/lib/mysql/port_${PORTNUMMER}/log_bin/mysql-bin.index
relay_log                               = /var/lib/mysql/port_${PORTNUMMER}/relay_log/mysql-relay-bin
relay_log_index                         = /var/lib/mysql/port_${PORTNUMMER}/relay_log/mysql-relay-bin.index
# The syntax '--relay-log-info-file' is deprecated and will be removed in a future release.
relay_log_info_file                     = /var/lib/mysql/port_${PORTNUMMER}/relay_log/relay-log.info
 
#------------------------------------------------------------------------------#
#innodb_flush_method                    = O_DIRECT
# innodb_autoinc_lock_mode changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode                = 2
innodb_file_per_table                   = ON
innodb_flush_log_at_trx_commit          = 2
innodb_thread_concurrency               = 0
#
# siehe: mysqltuner.pl
#
innodb_log_buffer_size                  = 16MB
innodb_redo_log_capacity                = 256M
innodb_buffer_pool_instances            = 1
innodb_buffer_pool_size                 = 1G
binlog_cache_size                       = 2M
#
# siehe: SHOW STATUS LIKE 'Max_used_connections%';
#" > /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/mysqld.cnf
 
. /root/bin/Neue_MySQL-Instanz_mysqld_cnf.txt
 
#==========================================#
# mysqld_safe.cnf
echo "# /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/mysqld_safe.cnf
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
 
[mysqld_safe]
pid-file                                = mysqld.pid
socket                                  = /var/run/mysqld/mysqld_${PORTNUMMER}.sock
nice                                    = 0
" > /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/mysqld_safe.cnf
 
#==========================================#
# wsrep.cnf
NODE_ADDRESS="$(ip a | grep -F 'inet 192.168.1.' | awk '{gsub("/"," "); print $2}')"
SST_PORT="$(echo "${PORTNUMMER}" | awk '{print ($1 - 10000) * 10 + 4}')"
INTERCOM_PORT="$(echo "${PORTNUMMER}" | awk '{print ($1 - 10000) * 10 + 7}')"
echo "# /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/wsrep.cnf
[mysqld]
# Path to Galera library
wsrep_provider				= /usr/lib/libgalera_smm.so
#wsrep_provider				= /usr/lib/galera3/libgalera_smm.so
#wsrep_provider				= /usr/lib/galera4/libgalera_smm.so
 
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
### Standard für InterCom: 4567 -> 33XX7
wsrep_cluster_address			= gcomm://
#wsrep_cluster_address			= gcomm://192.168.1.61:${INTERCOM_PORT},192.168.1.62:${INTERCOM_PORT},192.168.1.63:${INTERCOM_PORT}
 
# Slave thread to use
wsrep_applier_threads			= 8
 
wsrep_log_conflicts
 
# Node IP address               
#wsrep_node_address			= 192.168.70.63 
wsrep_node_address			= ${NODE_ADDRESS}
# Cluster name
#wsrep_cluster_name			= pxc-cluster
wsrep_cluster_name			= pxc-cluster_${REPORTNUMMER}	# kompatibel zur alten Umgebung im 2.O.G.
 
#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name 			= pxc-${REPORTNUMMER}-${REPORTKENNUNG}
 
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode				= ENFORCING
 
# SST method
wsrep_sst_method			= xtrabackup-v2
 
#------------------------------------------------------------------------------#
### Standard für SST: 4444 -> 33XX4 (das , am Zeilenende muss unbedingt bleiben)
wsrep_sst_donor  			= 192.168.1.61:${SST_PORT},192.168.1.62:${SST_PORT},192.168.1.63:${SST_PORT}
 
### Standard für IST / replication: 4568 -> 33XX8 (<wsrep_node_address>:<Port +1>)
### Standard für GCache: 128 MB -> 4G
### IST bezieht seine Daten aus dem GCache, vergleichbar mit der BIN-LOG-Historie bei GTID-Replikation
### Je größer der GCache ist, desto länger dürfen Wartungsarbeiten sein.
wsrep_provider_options			= \"gcache.recover=yes;gcache.size=4G;\"
 
#------------------------------------------------------------------------------#
 
pxc-encrypt-cluster-traffic		= OFF
" > /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/wsrep.cnf
 
#==========================================#
# wsrep_knoten.cnf
echo "# /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/wsrep_knoten.cnf
[mysqld]
### Standard für SST: 4444 -> 33XX4
wsrep_sst_receive_address		= ${NODE_ADDRESS}:${SST_PORT}
#
### Standard: 4567 (InterCom) -> 33XX7 (Referenzadresse für viele andere Variablen)
wsrep_node_address			= ${NODE_ADDRESS}:${INTERCOM_PORT}
" > /var/lib/mysql/port_${PORTNUMMER}/etc/conf.d/wsrep_knoten.cnf
 
#------------------------------------------------------------------------------#
### Aktion: DataDir mit Daten füllen / DBMS initialisieren
 
# https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html
# Bei der Initialisierung des Datenverzeichnisses sollten Sie keine anderen Optionen als diese angeben:
# --basedir oder --datadir
# --user
# --initialize oder --initialize-insecure
#
# https://www.middlewareinventory.com/blog/mysql-error-unable-lock-ibdata1/
#
#mysqld --datadir=/var/lib/mysql/port_${PORTNUMMER}/datadir/ --user=mysql --initialize
#grep -F 'A temporary password is generated for root@localhost:' /var/lib/mysql/port_${PORTNUMMER}/log/mysqld_error.log
#
#mysqld --datadir=/var/lib/mysql/port_${PORTNUMMER}/datadir/ --user=mysql --initialize-insecure
echo "mysqld --no-defaults --datadir=/var/lib/mysql/port_${PORTNUMMER}/datadir/ --user=mysql --initialize-insecure"
mysqld --no-defaults --datadir=/var/lib/mysql/port_${PORTNUMMER}/datadir/ --user=mysql --initialize-insecure
echo "rm -f /var/lib/mysql/port_${PORTNUMMER}/datadir/auto.cnf /var/lib/mysql/port_${PORTNUMMER}/datadir/*.pem"
rm -f /var/lib/mysql/port_${PORTNUMMER}/datadir/auto.cnf /var/lib/mysql/port_${PORTNUMMER}/datadir/*.pem
 
#------------------------------------------------------------------------------#
### Aktion: DataDir mit Daten füllen / DBMS initialisieren
 
# Erster Start nach der Initialisierung
# mit '--skip-grant-tables' kann man seit MySQL 8.0 das root-Passwort nicht mehr ändern, nur noch löschen
echo "(mysqld --defaults-file=/var/lib/mysql/port_${PORTNUMMER}/etc/my.cnf --datadir=/var/lib/mysql/port_${PORTNUMMER}/datadir/ --user=mysql) &"
(mysqld --defaults-file=/var/lib/mysql/port_${PORTNUMMER}/etc/my.cnf --datadir=/var/lib/mysql/port_${PORTNUMMER}/datadir/ --user=mysql) &
echo
#
# root ein Passwort geben
ls -lha /root/.my.cnf
PWT="$(grep -E '^password' /root/.my.cnf 2>/dev/null | awk '{gsub("="," "); print $NF}')"
if [ x = "x${PWT}" ] ; then
  passwortabfrage()
  {
	echo
	echo "Bitte geben Sie das root-Passwort für das neue DBMS ein:"
	read PWT1
	echo
	echo "Bitte das neue root-Passwort einmal wiederholen:"
	read PWT2
	echo
  }
  passwort_abfrage()
  {
	passwortabfrage
	if [ "${PWT1}" != "${PWT2}" ] ; then
		passwort_abfrage
	fi
  }
  passwort_abfrage}
fi
sleep 2
echo
echo "Kein Passwort eingeben, einfach nur ENTER drücken!"
echo "echo \"ALTER USER 'root'@'localhost' IDENTIFIED BY '${PWT}'; FLUSH PRIVILEGES;\" | mysql -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -p"
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY '${PWT}'; FLUSH PRIVILEGES;" | mysql -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -p
#
# root auf machen
echo "echo \"UPDATE mysql.user SET host='%' WHERE user='root'; FLUSH PRIVILEGES;\" | mysql -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -uroot"
echo "UPDATE mysql.user SET host='%' WHERE user='root'; FLUSH PRIVILEGES;" | mysql -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -uroot -p"${PWT}"
#
# Benutzer anzeigen
#echo "SELECT host,user,plugin,password_last_changed,authentication_string FROM mysql.user;" | mysql -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -t
echo "echo 'SELECT host,user,plugin,password_last_changed FROM mysql.user;' | mysql -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -uroot -t"
echo 'SELECT host,user,plugin,password_last_changed FROM mysql.user;' | mysql -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -uroot -p"${PWT}" -t
#
# einfacher Zugriff per root funktioniert jetzt
echo "mysqlshow -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -uroot"
mysqlshow -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -uroot -p"${PWT}"
#
#------------------------------------------------------------------------------#
# MySQL-Dienst wieder beenden
 
#MY_PID_02="$(ps alxwww | grep -Fv grep | grep -F mysqld | grep -F /var/lib/mysql/port_${PORTNUMMER}/etc/my.cnf | awk '{print $3}')"
MY_PID_02="$(ps alxwww | grep -Fv grep | grep -F mysqld | grep -F "datadir=/var/lib/mysql/port_${PORTNUMMER}/datadir/" | awk '{print $3}')"
if [ x != "x${MY_PID_02}" ] ; then
	echo "kill -15 ${MY_PID_02}"
	kill -15 ${MY_PID_02}
	sleep 20
fi
 
#------------------------------------------------------------------------------#
# DBMS regulär starten
 
# ab jetzt kann man diesen MySQL-Dienst auch per SystemD starten und stoppen
rm -f /var/lib/mysql/port_${PORTNUMMER}/datadir/auto.cnf /var/lib/mysql/port_${PORTNUMMER}/datadir/*.pem
service mysql_port@${PORTNUMMER} restart
sleep 1
 
# Probeaufruf
echo "mysqlshow -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -uroot"
mysqlshow -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -uroot -p"${PWT}"
 
echo "################################################################################"
grep -F ERROR /var/lib/mysql/port_${PORTNUMMER}/log/mysqld_error.log
echo "################################################################################"
 
#------------------------------------------------------------------------------#
### Zum guten Schluß gibt es noch ein paar Hinweise.
 
systemctl daemon-reload
JETZT="$(date +'%F_%H-%M-%S')"
echo "
# So wird das BDMS behandelt:
 
> service mysql_port@${PORTNUMMER}.bootstrap start
> service mysql_port@${PORTNUMMER}.bootstrap status
> service mysql_port@${PORTNUMMER}.bootstrap stop
 
> service mysql_port@${PORTNUMMER} start
> service mysql_port@${PORTNUMMER} status
> service mysql_port@${PORTNUMMER} stop
 
> mysqlshow -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock
> echo 'SELECT host,user,plugin,password_last_changed,authentication_string FROM mysql.user;' | mysql -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -t
> echo \"SHOW VARIABLES LIKE '%ssl%';\" | mysql -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -t
> echo \"SHOW VARIABLES WHERE variable_name IN ('hostname','wsrep_node_name','wsrep_cluster_name','port','ssl_ca','ssl_cert','ssl_key'); SHOW STATUS LIKE '%Ssl_server_not_%';\" | mysql -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock -t
> mysqlshow --ssl-ca=/etc/mysql/ca.pem --ssl-mode=VERIFY_CA -S /var/run/mysqld/mysqld_${PORTNUMMER}.sock
 
zum kompletten und unwiederbringlichen entfernen dieser MySQL-DBMS-Instanz,
ist nur dieses Kommando nötig:
	/root/bin/Neue_MySQL-Instanz_löschen.sh ${PORTNUMMER}
"
 
#------------------------------------------------------------------------------#
# damit keiner etwas illegales unten anhängen kann
exit
/root/bin/Neue_MySQL-Instanz_löschen.sh
#!/bin/bash
 
#==============================================================================#
#
# Mir diesem Skript kann man, mit einem einzigen Kommando,
# eine komplette MySQL-DBMS-Instanz restlos löschen.
#
#==============================================================================#
# https://wiki.victorvox.net/doku.php?id=dokumentationen:datenbank-design_2025
#==============================================================================#
 
#VERSION="v2025042900"		# initiale Erstellung
VERSION="v2025043000"		# zfs destroy -r [pool]/[dataset]
 
#------------------------------------------------------------------------------#
#### Sicherheitsüberprüfungen
 
# Prüfen, ob die übergebene Option leer ist.
if [ x = "x${1}" ] ; then
	echo 'Es muß eine Portnummer für die neue MySQL-DBMS-Instanz übergeben werden!'
	echo "    ${0} [Portnummer]"
	echo "zum Beispiel:"
	echo "    ${0} 13309"
	echo "Abbruch"
	exit 1
fi
 
# Prüfen, ob die übergebene Option eine Integer-Zahl, mit fünf Stellen, ist.
I_FUENF="$(echo "${1}" | grep -E '^[0-9]{5}$')"
if [ x = "x${I_FUENF}" ] ; then
	echo 'Bitte übergeben sie diesem Skript die gewünschte Portnummer.'
	echo 'Die übergebene Option muß eine Integer-Zahl, mit fünf Stellen, sein!'
	echo 'Abbruch'
	exit 1
else
	PORTNUMMER="${1}"
fi
 
#------------------------------------------------------------------------------#
### stoppen
 
STATUS="$(service mysql_port@${PORTNUMMER} status | grep -F 'Active:' | grep -F running)"
if [ x != "x${STATUS}" ] ; then
	echo "service mysql_port@${PORTNUMMER} stop"
	service mysql_port@${PORTNUMMER} stop
	sleep 2
fi
STATUS="$(service mysql_port@${PORTNUMMER} status | grep -F 'Active:' | grep -F dead)"
if [ x = "x${STATUS}" ] ; then
	echo "service mysql_port@${PORTNUMMER} stop"
	service mysql_port@${PORTNUMMER} stop
	sleep 2
fi
if [ -e /var/lib/mysql/port_${PORTNUMMER}/datadir/mysqld.pid ] ; then
	MY_PID_01="$(cat /var/lib/mysql/port_${PORTNUMMER}/datadir/mysqld.pid)"
	if [ x != "x${MY_PID_01}" ] ; then
		echo "kill -9 ${MY_PID_01}"
		kill -9 ${MY_PID_01}
		sleep 2
	fi
fi
MY_PID_02="$(ps alxwww | grep -Fv grep | grep -F mysqld | grep -F /var/lib/mysql/port_${PORTNUMMER}/etc/my.cnf)"
if [ x != "x${MY_PID_02}" ] ; then
	echo "kill -9 ${MY_PID_02}"
	kill -9 ${MY_PID_02}
	sleep 2
fi
 
#------------------------------------------------------------------------------#
### löschen
 
# ZFS-Volumen zerstören
echo "zfs destroy -r mysql_datadir/port_${PORTNUMMER}"
zfs destroy -r mysql_datadir/port_${PORTNUMMER}
 
# ZFS-Volumen-Mount-Point und zwei spezielle Dateien löschen
echo "rm -fr /var/lib/mysql/port_${PORTNUMMER} /etc/default/mysql.${PORTNUMMER} /etc/default/mysql.${PORTNUMMER}.bootstrap"
rm -fr /var/lib/mysql/port_${PORTNUMMER} /etc/default/mysql.${PORTNUMMER} /etc/default/mysql.${PORTNUMMER}.bootstrap
 
# SystemD-Start-Stop-Datei-Reload
echo "systemctl daemon-reload"
systemctl daemon-reload
1. /root/bin/Neue_MySQL-Instanz_generieren.sh 13307
root@host61:~# time /root/bin/Neue_MySQL-Instanz_generieren.sh 13307
-rw-r--r-- 1 root root 137 Apr 29 18:00 /etc/default/mysql.13307
-rw-r--r-- 1 root root 157 Apr 29 18:00 /etc/default/mysql.13307.bootstrap
/usr/bin/mysql-systemd_port existiert schon
-rwxr-xr-x 1 root root 12K Apr 28 12:52 /usr/bin/mysql-systemd_port
-rw-r--r-- 1 root root 4.9K Apr 29 18:00 /usr/lib/systemd/system/mysql_port@.service
mysqld --no-defaults --datadir=/var/lib/mysql/port_13307/datadir/ --user=mysql --initialize-insecure
2025-04-29T18:00:31.403643Z 0 [Warning] [MY-000000] [WSREP] Node is running in bootstrap/initialize mode. Disabling pxc_strict_mode checks
2025-04-29T18:00:31.403958Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.41-32.1) initializing of server in progress as process 317465
2025-04-29T18:00:31.406631Z 0 [Note] [MY-000000] [Galera] Loading provider none initial position: 00000000-0000-0000-0000-000000000000:-1
2025-04-29T18:00:31.406646Z 0 [Note] [MY-000000] [Galera] wsrep_load(): loading provider library 'none'
2025-04-29T18:00:31.406656Z 0 [Note] [MY-000000] [Galera] Symbol 'wsrep_node_isolation_mode_set_v1' not found from provider
2025-04-29T18:00:31.415028Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-04-29T18:00:31.528044Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-04-29T18:00:32.654790Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
rm -f /var/lib/mysql/port_13307/datadir/auto.cnf /var/lib/mysql/port_13307/datadir/*.pem
(mysqld --defaults-file=/var/lib/mysql/port_13307/etc/my.cnf --datadir=/var/lib/mysql/port_13307/datadir/ --user=mysql) &
 
-rw------- 1 root root 259 Apr 29 08:49 /root/.my.cnf
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'Vi1doqui'; FLUSH PRIVILEGES;" | mysql -S /var/run/mysqld/mysqld_13307.sock -p
 
Kein Passwort eingeben, einfach nur ENTER drücken!
Enter password:

Kein Passwort eingeben!!! …einfach nur ENTER drücken!

2. /root/bin/Neue_MySQL-Instanz_generieren.sh 13307
...
echo "UPDATE mysql.user SET host='%' WHERE user='root'; FLUSH PRIVILEGES;" | mysql -S /var/run/mysqld/mysqld_13307.sock -uroot
mysql: [Warning] Using a password on the command line interface can be insecure.
echo 'SELECT host,user,plugin,password_last_changed FROM mysql.user;' | mysql -S /var/run/mysqld/mysqld_13307.sock -uroot -t
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+----------------------------+-----------------------+-----------------------+
| host      | user                       | plugin                | password_last_changed |
+-----------+----------------------------+-----------------------+-----------------------+
| %         | root                       | caching_sha2_password | 2025-04-30 10:19:17   |
| localhost | mysql.infoschema           | caching_sha2_password | 2025-04-30 10:19:13   |
| localhost | mysql.pxc.internal.session | caching_sha2_password | 2025-04-30 10:19:13   |
| localhost | mysql.pxc.sst.role         | caching_sha2_password | 2025-04-30 10:19:13   |
| localhost | mysql.session              | caching_sha2_password | 2025-04-30 10:19:13   |
| localhost | mysql.sys                  | caching_sha2_password | 2025-04-30 10:19:13   |
| localhost | percona.telemetry          | caching_sha2_password | 2025-04-30 10:19:15   |
+-----------+----------------------------+-----------------------+-----------------------+
mysqlshow -S /var/run/mysqld/mysqld_13307.sock -uroot
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
kill -15 332245
################################################################################
################################################################################
 
# So wird das BDMS behandelt:
 
> service mysql_port@13307.bootstrap start
> service mysql_port@13307.bootstrap status
> service mysql_port@13307.bootstrap stop
 
> service mysql_port@13307 start
> service mysql_port@13307 status
> service mysql_port@13307 stop
 
> mysqlshow -S /var/run/mysqld/mysqld_13307.sock
> echo 'SELECT host,user,plugin,password_last_changed,authentication_string FROM mysql.user;' | mysql -S /var/run/mysqld/mysqld_13307.sock -t
> echo "SHOW VARIABLES LIKE '%ssl%';" | mysql -S /var/run/mysqld/mysqld_13307.sock -t
> echo "SHOW VARIABLES WHERE variable_name IN ('hostname','wsrep_node_name','wsrep_cluster_name','port','ssl_ca','ssl_cert','ssl_key'); SHOW STATUS LIKE '%Ssl_server_not_%';" | mysql -S /var/run/mysqld/mysqld_13307.sock -t
> mysqlshow --ssl-ca=/etc/mysql/ca.pem --ssl-mode=VERIFY_CA -S /var/run/mysqld/mysqld_13307.sock
 
zum kompletten und unwiederbringlichen entfernen dieser MySQL-DBMS-Instanz,
ist nur dieses Kommando nötig:
	/root/bin/Neue_MySQL-Instanz_löschen.sh 13307
 
 
real	0m32.173s
user	0m4.630s
sys	0m1.172s
/root/bin/Neue_MySQL-Instanz_löschen.sh
root@host61:~# /root/bin/Neue_MySQL-Instanz_löschen.sh 13307
service mysql_port@13307 stop
zfs destroy mysql_datadir/port_13307
rm -fr /var/lib/mysql/port_13307 /etc/default/mysql.13307 /etc/default/mysql.13307.bootstrap
systemctl daemon-reload
 
real	0m16.575s
user	0m0.029s
sys	0m0.085s

ZFS

https://de.linux-console.net/?p=17625

/etc/cron.d/zfs-snapshot
0 1 * * * root /root/bin/SnapShot_von_allen_ZFS-Volumen.sh >/tmp/zfs-snapshot.log 2>&1
/root/bin/SnapShot_von_allen_ZFS-Volumen.sh
#!/bin/bash
 
#------------------------------------------------------------------------------#
# Dieses Skript macht von allen ZFS-Volumen auf diesem Host einen SnapShot.
#
# Es kann maximal EINER pro Stunde angelegt werden,
# sollte für diesen Zeitraum bereits einer existieren,
# dann wird er vorher gelöscht.
#------------------------------------------------------------------------------#
# zfs snapshot mysql_datadir/port_13307@4-16
# zfs list -t snapshot mysql_datadir/port_13307
# zfs destroy mysql_datadir/port_13307@4-16
#------------------------------------------------------------------------------#
# Bedeutung von "mysql_datadir/port_13307@4-16"
# mysql_datadir - ZFS-Pool (Tank)
# port_13307    - ZFS-Volumen (DataSet)
# 4             - 4. Wochentag -> am Donnerstag aufgenommen
# 16            - zwischen 16:00 und 16:59 Uhr aufgenommen
#------------------------------------------------------------------------------#
 
SNAP_ZEIT="$(date +'%u-%H')"
zfs list -Hp | awk '/mysql_datadir[/]/{print $1}' | while read SNAP
do
        echo "################################################################################"
        echo "snapshot ${SNAP}@${SNAP_ZEIT} erneuern"
        zfs destroy ${SNAP}@${SNAP_ZEIT}
        zfs snapshot ${SNAP}@${SNAP_ZEIT}
done
echo
 
# alle SnapShots anzeigen
zfs list -t snapshot
  • https://docs.oracle.com/cd/E24841_01/html/820-2313/gaynp.html
    • Beispiele
      • zpool-Kommandos
        • man zpool
        • zpool version
        • zpool status (Den ausführlichen Status aller Pools anzeigen)
        • zpool status -x (kurzer Status)
          • all pools are healthy
        • zpool status -v (Den sehr ausführlichen Status aller Pools anzeigen)
        • zpool status -v mysql_datadir (Den sehr ausführlichen Status eines Pools anzeigen)
        • zpool list
        • zpool iostat (I/O aller Pools anzeigen)
        • zpool iostat -v (I/O der einzelnen pysikalischen Laufwerke aller Pools anzeigen)
        • zpool scrub mysql_datadir (check zfs for errors and other stuff)
        • zpool scrub -s mysql_datadir (scrub stoppen)
        • zpool resilver mysql_datadir (automatic dynamic consistency restoration)
        • zpool sync (forces all in-core dirty data to be written to the primary pool storage and not the ZIL)
      • zfs-Kommandos
        • zfs version
        • zfs list
        • zfs list -Hp | awk '/mysql_datadir[/]/{print $1}'
        • zfs list mysql_datadir/port_13307
        • zfs get used
        • zfs userspace mysql_datadir/port_13307
        • zfs snapshot mysql_datadir/port_13307@Montag (einen SnapShot anlegen)
        • zfs list -t snapshot (alle SnapShots anzeigen)
        • zfs rollback mysql_datadir/port_13307@Montag
        • zfs rename mysql_datadir/port_13307@Montag mysql_datadir/port_13307@version1
        • zfs destroy mysql_datadir/port_13307@Montag

MySQL 5.7

percona-xtradb-cluster-server-5.7 (5.7.31-31.45-3.focal)

> echo "SHOW VARIABLES WHERE variable_name IN ('auto_increment_increment', 'auto_increment_offset', 'max_connections', 'wsrep_slave_threads');" | mysql -tS /var/run/mysqld/mysqld_23321.sock
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 3     |
| auto_increment_offset    | 1     |
| max_connections          | 1000  |
| wsrep_slave_threads      | 8     |
+--------------------------+-------+
> echo "SHOW STATUS WHERE variable_name IN ('wsrep_cert_deps_distance', 'wsrep_cluster_size', 'wsrep_cluster_status', 'wsrep_connected', 'wsrep_flow_control_paused', 'wsrep_incoming_addresses', 'wsrep_last_committed', 'wsrep_local_recv_queue_avg', 'wsrep_local_send_queue_avg', 'wsrep_local_state_comment', 'wsrep_local_bf_aborts');" | mysql -tS /var/run/mysqld/mysqld_23321.sock
+----------------------------+----------------------------------------------------------+
| Variable_name              | Value                                                    |
+----------------------------+----------------------------------------------------------+
| wsrep_last_committed       | 19                                                       |
| wsrep_local_send_queue_avg | 0.000000                                                 |
| wsrep_local_recv_queue_avg | 0.000000                                                 |
| wsrep_flow_control_paused  | 0.000000                                                 |
| wsrep_cert_deps_distance   | 0.000000                                                 |
| wsrep_local_state_comment  | Synced                                                   |
| wsrep_incoming_addresses   | galera01.lan:23321,galera02.lan:23321,galera03.lan:23321 |
| wsrep_cluster_size         | 3                                                        |
| wsrep_cluster_status       | Primary                                                  |
| wsrep_connected            | ON                                                       |
| wsrep_local_bf_aborts      | 0                                                        |
+----------------------------+----------------------------------------------------------+

Index of wsrep status variables / Galera Status Variables

  1. auto_increment_increment - sollte so groß sein, wie die max. mögliche Knotenzahl im Cluster
  2. auto_increment_offset - sollte der laufenden Nummer des Knotens im Cluster entsprechen
  3. max_connections - max. gleichzeitig erlaubte Verbindungen
  4. wsrep_slave_threads - aktuelle Parallelisierung
  5. wsrep_cert_deps_distance - max. mögliche Parallelisierung (max. wsrep_slave_threads)
  6. wsrep_cluster_size - Anzahl der Knoten im Cluster
  7. wsrep_cluster_status - Status des Knotens im Cluster
  8. wsrep_connected - Galera-Modul wurde geladen (ON) oder nicht (OFF)
  9. wsrep_flow_control_paused - Pausezeiten (z.B. LOCK)
  10. wsrep_incoming_addresses - IP-Adressen aller Knoten im Cluster
  11. wsrep_last_committed - letzte Log-Position
  12. wsrep_local_recv_queue_avg - Wartezeit auf die Festplatte
  13. wsrep_local_send_queue_avg - Wartezeit auf das Netzwerk, kann aber auch an anderer Hardware oder OS liegen
  14. wsrep_local_state_comment - Status der Replikation des Knotens
  15. wsrep_local_bf_aborts - Gesamtzahl der lokalen Transaktionen, die während der Ausführung von Slave-Transaktionen abgebrochen wurden.

bekannte Probleme mit Percona XtraDB Cluster (PXC)

eine von mehreren PXC-Instanzen startet nicht

Manchmal kommt es vor, dass sich einige Datenbank-Instanzen einfach nicht starten lassen. Dieses Problem konnte jetzt analysiert und Gegenmaßnahmen erarbeitet werden.

In diesem Beispiel will der dritte Knoten von dem Cluster DB23348 einfach nicht starten, auch nicht, wenn das DataDir geleert wurde.

Datenbank starten: 

# /usr/local/sbin/mysql-iq_23348 start
# /etc/init.d/mysql-iq_23348 start

Log von einem Knoten, bei dem der SST funktioniert hat: 

2021-07-06T11:26:35.404416Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2021-07-06T11:26:35.423960Z 0 [Warning] WSREP: Fail to access the file (/var/lib/mysql/port_23348/data/gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
2021-07-06T11:26:35.934747Z 2 [Warning] WSREP: Gap in state sequence. Need state transfer.
        2021-07-06T11:26:36.033036Z WSREP_SST: [INFO] Waiting for data-dir through rsync................
        2021-07-06T11:31:20.412774Z WSREP_SST: [INFO] ..............rsync completed
InnoDB: Progress in percent: 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
2021-07-06T11:31:27.213552Z 0 [Warning] CA certificate ca.pem is self signed.
2021-07-06T11:34:23.346418Z 0 [Warning] WSREP: 2.0 (drdkvm04): State transfer to 1.0 (drdkvm03) failed: -10 (No child processes)

Log von diesem Knoten, bei dem der SST abgebrochen ist: 

2021-07-06T11:34:25.136850Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2021-07-06T11:34:25.810030Z 0 [Warning] WSREP: Fail to access the file (/var/lib/mysql/port_23348/data/gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
2021-07-06T11:34:26.814708Z 2 [Warning] WSREP: Gap in state sequence. Need state transfer.
        2021-07-06T11:34:26.910410Z WSREP_SST: [INFO] Waiting for data-dir through rsync................
2021-07-06T11:34:27.173436Z 0 [Warning] WSREP: 2.0 (drdkvm04): State transfer to 1.0 (drdkvm03) failed: -10 (No child processes)
2021-07-06T11:34:27.173550Z 0 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():807: Will never receive state. Need to abort.
        2021-07-06T11:34:28.212895Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
        2021-07-06T11:34:28.214900Z WSREP_SST: [ERROR] Parent mysqld process (PID:2973175) terminated unexpectedly.
        2021-07-06T11:34:28.216573Z WSREP_SST: [ERROR] ******************************************************

nachsehen, welche Ports offen sind: 

# netstat -antp | fgrep ESTABLISHED | fgrep 3348
tcp        0      0 10.232.0.63:33484       10.232.0.61:33207       ESTABLISHED 3169001/mysqld

nachsehen, welcher Prozess den Port offen hält: 

# ps alxwww | fgrep 3169001
4   112 3169001 3160912  20   0 9791328 819388 poll_s Sl ?         15:32 /usr/sbin/mysqld --defaults-file=/etc/mysql/conf.d_23320/my.cnf --basedir=/usr --datadir=/var/lib/mysql/port_23320/data --plugin-dir=/usr/lib/mysql/plugin --user=mysql --wsrep-provider=/usr/lib/galera3/libgalera_smm.so --log-error=/var/log/DB/mysqld_23320.log --open-files-limit=65000 --pid-file=/var/lib/mysql/port_23320/data/mysqld.pid --socket=/var/run/mysqld/mysqld_23320.sock --port=23320

Zur Überraschung, wird der Port von einer ganz anderen DB offen gehalten. Von der DB23320.

Um das Problem zu lösen, restarten wir die DB23320 einmal: 

# /etc/init.d/mysql-iq_23320 stop
Stopping mysql-iq_23320 (via systemctl): mysql-iq_23320.service.

prüfen, ob der Prozess wirklich beendet wurde: 

# ps alxwww | fgrep -v grep | fgrep 3320

neu starten: 

# /etc/init.d/mysql-iq_23320 start
Starting mysql-iq_23320 (via systemctl): mysql-iq_23320.service.

der Port sollte jetzt wieder frei sein: 

# netstat -antp | fgrep ESTABLISHED | fgrep 3348

Und nun startet auch die DB23348: 

# /etc/init.d/mysql-iq_23348 start
Starting mysql-iq_23348 (via systemctl): mysql-iq_23348.service.

Und nun ist alles gut!

einzelne Galera-Cluster-Knoten lassen sich nicht mehr starten

Wenn alles überprüft wurde und in Ordnung ist und doch diese Galera-Cluster-Knoten einfach nicht starten, dann kann es sein, dass Netzwerkports, die von Galera benötigt werden bereits offen sind. Das kann in seltenen Fällen ein im Hintergrund hängender MySQL-Prozess sein. In unserem Fall hatte Ceph/RBD diese Ports in Nutzung.

Um dieses Problem zu beheben, muß man Ceph in seiner zu nutzenden Port-Range einschränken. 

> sysctl net.ipv4.ip_local_port_range
> echo "net.ipv4.ip_local_port_range = 40000    60999" >> /etc/sysctl.conf
> sysctl -p
> sysctl net.ipv4.ip_local_port_range
Aus einem Galera-Cluster sterben spontan einzelne Knoten

In einer Galera-Datenbank (synchroner DB-Cluster) muß immer mit expliziten Transaktionen gearbeitet werden!

Wenn in unregelmäßigen Abständen einzelne Galera-Cluster-Knoten sterben, dann kann es daran liegen, dass die ändernden SQL-Querries nicht in einer expliziten Transaktion ausgeführt werden. Auch Trigger und Events müssen in expliziten Transaktionen ausgeführt werden.

The table is full
> mysqlcheck -o --all-databases
...
simsysnetwork.IccidEntries
note     : Table does not support optimize, doing recreate + analyze instead
error    : The table 'IccidEntries' is full
status   : Operation failed
...

Dafür soll es zwei Ursachen bzw. Lösungen geben:

  1. running the query below with incorrect foreign key: set foreign_key_checks=0
  2. You seem to have a too low maximum size for your innodb_data_file_path in your my.cnf
    1. bis 512MB: innodb_data_file_path = ibdata1:10M:autoextend:max:512M
    2. ohne Limit: innodb_data_file_path = ibdata1:10M:autoextend

Konfigurationsdateien

percona-xtradb-cluster-server-5.7

/etc/mysql/conf.d_23321/data/client.cnf
[client]
port                                    = 23321
socket                                  = /var/run/mysqld/mysqld_23321.sock
/etc/mysql/conf.d_23321/data/innodb.cnf
[mysqld]
innodb_autoinc_lock_mode                = 2
innodb_buffer_pool_size                 = 2G
innodb_file_per_table                   = ON
innodb_flush_log_at_trx_commit          = 2
innodb_flush_method                     = O_DIRECT
innodb_log_file_size                    = 1G
innodb_log_buffer_size                  = 32MB
innodb_thread_concurrency               = 0
innodb_use_native_aio                   = OFF
/etc/mysql/conf.d_23321/data/iq.cnf
[server]
sql_mode                                = ""
 
[mysqld]
report_host                             = db23321k1
report_port                             = 23321
bind_address                            = 0.0.0.0
socket                                  = /var/run/mysqld/mysqld_23321.sock
datadir                                 = /var/lib/mysql/port_23321/data
default_storage_engine                  = InnoDB
pid-file                                = /var/lib/mysql/port_23321/data/mysqld.pid
log_error                               = mysqld_error.log
# Error messages                           -> log_error_verbosity = 1
# Error and warning messages               -> log_error_verbosity = 2
# Error, warning, and information messages -> log_error_verbosity = 3
log_error_verbosity                     = 2
log_slave_updates                       = ON
log_syslog                              = ON
log_syslog_include_pid                  = ON
log_syslog_tag                          = mysql_23321
# relay_log_recovery=OFF, denn diese Option macht keinen Sinn mehr, seit es die GTID gibt.
#relay_log_recovery                     = OFF
relay_log                               = mysql-relay-bin
relay_log_index                         = mysql-relay-bin.index
relay_log_info_repository               = TABLE
relay_log_space_limit                   = 4G
character_set_server                    = utf8
# 1M = 1048576
collation_server                        = utf8_unicode_ci
expire_logs_days                        = 3
explicit_defaults_for_timestamp         = ON
interactive_timeout                     = 28800
master_info_repository                  = TABLE
master_verify_checksum                  = ON
max_allowed_packet                      = 64M
max_binlog_size                         = 100M
max_connect_errors                      = 1000
max_connections                         = 1000
max_relay_log_size                      = 100M
net_read_timeout                        = 90
net_write_timeout                       = 90
port                                    = 23321
query_cache_limit                       = 0
query_cache_min_res_unit                = 0
query_cache_size                        = 0
query_cache_type                        = OFF
skip_external_locking                   = ON
skip_name_resolve                       = ON
sync_binlog                             = 1
sync_master_info                        = 1
sync_relay_log_info                     = 1
ignore-db-dir                           = lost+found
log-error                               = mysqld.log
symbolic-links                          = 0
server-id                               = 1
 
log_bin                                 = mysql-bin
log_bin_index                           = mysql-bin.index
 
#gtid_mode                               = ON
#enforce_gtid_consistency                = ON
 
open_files_limit                        = 65000
connect_timeout                         = 90
/etc/mysql/conf.d_23321/data/mysqld_safe.cnf
#
# The Percona XtraDB Cluster 5.7 configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see                  
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
 
[mysqld_safe]
pid-file                                = mysqld.pid
socket                                  = /var/run/mysqld/mysqld_23321.sock
nice                                    = 0
/etc/mysql/conf.d_23321/data/wsrep.cnf
[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib/galera3/libgalera_smm.so
 
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
#wsrep_cluster_address=gcomm://
 
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW                       
 
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
 
# Slave thread to use
wsrep_slave_threads= 8
 
wsrep_log_conflicts
 
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
 
# Node IP address
#wsrep_node_address=192.168.70.63
# Cluster name
#wsrep_cluster_name=pxc-cluster
 
#If wsrep_node_name is not specified,  then system hostname will be used
#wsrep_node_name=pxc-cluster-node-1
 
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
 
# SST method
#wsrep_sst_method=xtrabackup-v2
 
#Authentication for SST method
#wsrep_sst_auth="sstuser:s3cretPass"
 
#------------------------------------------------------------------------------#
 
wsrep_cluster_name              = pxc-cluster_3321
wsrep_sst_method                = rsync
wsrep_sst_auth                  = "repliuser:geheimesPasswort"
 
### Standard für DB: 3306 -> 233XX
port                            = 23321
bind-address                    = 0.0.0.0
 
### Standard für SST: 4444 -> 33XX4 (das , am Zeilenende muss unbedingt bleiben)
### Standard für IST / replication: 4568 -> 33XX8 (<wsrep_node_address>:<Port +1>)
wsrep_sst_donor                 = galera01.lan:33214,galera02.lan:33214,galera03.lan:33214,
 
### Standard für InterCom: 4567 -> 33XX7
#wsrep_cluster_address          = gcomm://
wsrep_cluster_address           = "gcomm://galera01.lan:33217,galera02.lan:33217,galera03.lan:33217"
 
### Standard für GCache: 128 MB -> 4G
### IST bezieht seine Daten aus dem GCache, vergleichbar mit der BIN-LOG-Historie bei GTID-Replikation
### Je größer der GCache ist, desto länger dürfen Wartungsarbeiten sein.
wsrep_provider_options          = "gcache.size=4G;"
 
#------------------------------------------------------------------------------#
/etc/mysql/conf.d_23321/data/wsrep_knoten.cnf
[mysqld]
### Standard für SST: 4444 -> 33XX4
wsrep_sst_receive_address       = galera01.lan:33214
#
### Standard: 4567 (InterCom) -> 33XX7 (Referenzadresse für viele andere Variablen)
wsrep_node_address              =  galera01.lan:33217

implizite vs. explizite Transaktionen

Wenn man viele DELETE-Kommandos (tausende) in kurzer Folge auf einen Galera-Cluster absetzt, dann kann er komplett abstürzen: 

DELETE FROM tabelle WHERE id=120340243;
DELETE FROM tabelle WHERE id=120340244;
DELETE FROM tabelle WHERE id=120340245;
...

…wenn diese DELETE-Kommandos nicht explizit in einer Transaktion zusammengefasst werden.

Dabei werden dann im Log die Meldungen "lock_mode X l ocks rec but not gap" und "error 1032" geschrieben.

Die Log-Meldung "lock_mode X l ocks rec but not gap" besagt, dass während der vielen kurz aufeinander folgenden impliziten Transaktionen, die Tabelle und ihr Index nicht synchron sind. Das scheint in so einem Fall normal zu sein und muß gezielte durch weitere Maßnahmen verhindert werden. Im Index stehen dann für kurze Zeit noch ältere Daten drin, als in der Tabelle. Das verhindert die implizite Transaktion nicht, sondern nur eine explizite Transaktion.

Diese Tatsache führt dann zu dem Folgefehler: "error 1032". Der Fehler "error 1032" besagt, das ein Löschvorgang nicht durchgeführt werden kann, weil das zu löschende Objekt nicht da ist. Daraus schließt Galera, dass der Cluster nicht mehr synchron ist und versucht sich den kompletten Datenbestand per SST von einem anderen Knoten zu holen. Damit beginnt der Zusammenbruch des Clusters.

Dieses Problem kann durch das gezielte einleiten einer Transaktion verhindert werden: 

START TRANSACTION;
  DELETE FROM tabelle WHERE id=120340243;
  DELETE FROM tabelle WHERE id=120340244;
  DELETE FROM tabelle WHERE id=120340245;
  ...
COMMIT;
/home/http/wiki/data/pages/datenbanken/galera.txt · Zuletzt geändert: von manfred