Inhaltsverzeichnis

Percona XtraDB Cluster (PXC)

Percona XtraDB Cluster (PXC)

Installing Percona XtraDB Cluster on Debian or Ubuntu → direkt von Percona (mit den Percona-Paketen ist der Admin-Aufwand am geringsten)

PXC-Repository (8.0):

> echo "deb http://repo.percona.com/pxc-80/apt $(lsb_release -cs) main" >> /etc/apt/sources.list.d/percona-ps-80-release.list
> echo "deb http://repo.percona.com/pxb-80/apt $(lsb_release -cs) main" >> /etc/apt/sources.list.d/percona-ps-80-release.list
> echo "deb http://repo.percona.com/tools/apt $(lsb_release -cs) main" >> /etc/apt/sources.list.d/percona-ps-80-release.list
> apt update
> apt install percona-xtradb-cluster-server percona-xtrabackup-80

oder so:

> apt install wget gnupg2 lsb-release curl qpress
> wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
> dpkg -i percona-release_latest.generic_all.deb

> cat /etc/apt/sources.list.d/percona-pxc-80-release.list 
#
# This repo is managed by "percona-release" utility, do not edit!
#
deb http://repo.percona.com/pxc-80/apt focal main
deb-src http://repo.percona.com/pxc-80/apt focal main

> apt update
> apt install percona-release

> percona-release enable pxc-80 release
> percona-release enable pxb-80 release
> percona-release enable tools release

> percona-release show
The following repositories are enabled on your system:
aptrelease
pxb-80 - release
pxc-80 - release
tools - release
<*> All done!

> apt update
> apt install percona-xtradb-cluster-server percona-xtrabackup-80

/etc/mysql/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';
# SHOW VARIABLES LIKE 'innodb_fast_shutdown'; SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct'; SHOW VARIABLES LIKE 'innodb_change_buffering';
innodb_fast_shutdown=0
innodb_max_dirty_pages_pct=0
innodb_change_buffering='none'

Backup mit xtrabackup auf Ubuntu 18.04 LTS

xtrabackup muß lokal auf dem DB-Host-System ausgeführt werden!

# innobackupex --help
...

NOTICE: 'innobackupex' is deprecated, please switch to 'xtrabackup'

...

xtrabackup installieren

# xtrabackup --help
# xtrabackup [--defaults-file=#] --backup --target-dir=/
# xtrabackup [--defaults-file=#] --prepare

# ssh root@pxcserver01

# apt install percona-release
# apt update
# apt install percona-xtradb-cluster-57
# apt install percona-xtrabackup perl rsync qpress

# vi /root/.my.cnf

Mit xtrabackup ein Backup erstellen

# mkdir -p /backup
# xtrabackup --backup --target-dir=/backup

Ein mit xtrabackup erstelltes Backup vorbereiten

der Vorbereitungsschritt darf nicht unterbrochen werden, sonst können die Daten beschädigt werden:

# xtrabackup --prepare --target-dir=/backup

Mit xtrabackup ein Backup zurückspielen

# service mysql stop
# mkdir /var/lib/mysql_alt/
# mv /var/lib/mysql/* /var/lib/mysql_alt/
# xtrabackup --copy-back --target-dir=/backup
# chown -R mysql:mysql /var/lib/mysql
# service mysql start

es ist auch dieses Kommando möglich

# xtrabackup --move-back --target-dir=/backup

oder übers Netz

# xtrabackup --backup --stream=tar -h /var/lib/mysql | ssh root@backupserver01 'mkdir -p /backups/pxcserver01/ ; tar xf - -C /backups/pxcserver01/'

# echo "SHOW DATABASES;" | mysql -t -uroot -p${MYSQL_ROOT_PASSWORD} -hlocalhost -P3306
# xtrabackup --backup --stream=tar -h /var/lib/mysql -uroot -p${MYSQL_ROOT_PASSWORD} -Hlocalhost -P3306 | gzip -1 | ssh root@backupserver01 'cat > /backups/pxcserver01.tgz'
# xtrabackup --backup --stream=tar -h /var/lib/mysql -uroot -p${MYSQL_ROOT_PASSWORD} -Hlocalhost -P3306 | ssh root@backupserver01 'mkdir -p /backups/pxcserver01/ ; tar xf - -C /backups/pxcserver01/'

Backup mit innobackupex auf Ubuntu 18.04 LTS

innobackupex muß lokal auf dem DB-Host-System ausgeführt werden!

# innobackupex --help
...

NOTICE: 'innobackupex' is deprecated, please switch to 'xtrabackup'

...

Das innobackupex-Tool ist ein Perl-Skript, das als Wrapper für das C-Programm xtrabackup fungiert. Es ist eine gepatchte Version des innobackup Perl-Skripts, das Oracle mit dem InnoDB Hot Backup-Tool verteilt. Es ermöglicht mehr Funktionalität durch die Integration von xtrabackup und anderen Funktionen wie Kopieren und Streamen von Dateien sowie zusätzlichen Komfort. Sie können zu einem bestimmten Zeitpunkt Sicherungen von InnoDB / XtraDB-Tabellen zusammen mit den Schemadefinitionen, MyISAM-Tabellen und anderen Teilen des Servers durchführen.

Installation von innobackupex

# ssh root@pxcserver01

# apt install percona-release
# apt update
# apt install percona-xtradb-cluster-57
# apt install percona-xtrabackup perl rsync qpress

# vi /root/.my.cnf

Backup erstellen

# mkdir /backup
# innobackupex --rsync --slave-info /backup
# innobackupex --apply-log /backup

oder

# innobackupex --user=bekupuser --password=mypassword --no-timestamp /backup

Backup zurückspielen

# service mysql stop
# mkdir ~/mysql_old/
# mv /var/lib/mysql/* ~/mysql_old/
# innobackupex --copy-back /backup
# chown -R mysql:mysql /var/lib/mysql
# service mysql start

Ubuntu 18.04.2 LTS - bionic

Galera ist eine zuverlässig funktionierende synchroner Multi-Master-Cluster

# apt-get -y update
# apt-get -y install apt-utils
# apt-get -y install dialog vim screen mc bc facter language-pack-de less bash-completion lsb-release gnupg bind9-host iputils-ping
# apt-get -y autoclean
# apt-get -y autoremove
# dpkg --configure -a
# apt-get -y update
# apt-get -y dist-upgrade
# dpkg --configure -a

percona.gpg

-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: SKS 1.1.6
Comment: Hostname: keyserver.ubuntu.com
 
mQINBFd0veABEADyFa8jPHXhhX1XS9W7Og4p+jLxB0aowElk4Kt6lb/mYjwKmQ779ZKUAvb1
xRYFU1/NEaykEl/jxE7RA/fqlqheZzBblB3WLIPM0sMfh/D4fyFCaKKFk2CSwXtYfhk9DOsB
P2K+ZEg0PoLqMbLIBUxPl61ZIy2tnF3G+gCfGu6pMHK7WTtInnruMKk51s9Itc9vUeUvRGDc
FIiEEq0xJhEX/7J/WAReD5Am/kD4CvkkunSqbhhuB6DV9tAeEFtDppEHdFDzfHfTOwlHLgTv
gVETDgLgTRXzztgBVKl7Gdvc3ulbtowBuBtbuRr49+QIlcBdFZmM6gA4V5P9/qrkUaarvuIk
XWQYs9/8oCd3SRluhdxXs3xX1/gQQXYHUhcdAWrqS56txncXf0cnO2v5kO5rlOX1ovpNQsc6
9R52LJKOLA1KmjcaJNtC+4e+SF2upK14gtXK384z7owXYUA4NRZOEu+UAw7wAoiIWPUfzMEH
Yi8I3RszEtpVyOQC5YyYgwzIdt4YxlVJ0CUoinvtIygies8LkA5GQvaGJHYG1aQ3i9WDddCX
wtoV1uA4EZlEWjTXlSRc92jhSKut/EWbmYHEUhmvcfFErrxUPqirpVZHSaXY5RdhKVFyx9Jc
RuIQ0SJxeHQPlaEkyhKpTDN5Cw7USLwoXfIu2w0w0W06LdXZ7wARAQABtDtQZXJjb25hIERl
dmVsb3BtZW50IFRlYW0gKFBhY2thZ2luZyBrZXkpIDxpbmZvQHBlcmNvbmEuY29tPokCNwQT
AQgAIQUCWwLC+wIbAwULCQgHAgYVCAkKCwIEFgIDAQIeAQIXgAAKCRCTNKJfhQfvpYf+D/oD
7dFS0eXR4OH2g8CACNeTWB2EJ57W0gyLwko42IjBSOSogB4BMm/3vlk8PefikTU5+Z/fYK3O
IJV7kMIEXNfnNzr3QWvafHRRqGUoTmvP29O5Y4s7oGllIUOlr9gwtSGfHnjtF+WZBhko2uH6
KvXBJay28ye4S8sSzDQdk8RULFN4hfIT4duOjo7Clf4iZtoUX7bVN32NRYH8Ss4IvbdDOAjl
zjQa+NgOSEsDvP3DwRoZQcAIMXngOMlPa/SA87pAcOup/8AvX3i7F7ZfWkKys3jpoSRyt0Ol
InpOrlJqJY4ugSxNkCgz+21kb1EVtIjSY8LAMPzZ5OAiiG0MyOTUyKFhzAkE1Mn3Cs9TzNjy
bPlvPGt6CsckjgReL2XQBqITRsmLOwzWguuqduBlPISVoeGUPpEBj7HvCa7p9QbEaXtN5Jml
AFLwPTuM4S5IxG5bEXMFECKL45J8F9G/EGs/qO/HSebQsJ/+i5Ct6gElUwIOaaCUPpWG0qwR
2aP4QAndvLsaGN7v6BmtLYw8+n5vjIueFXh/gRyI8eOIxrCUYhukkdM+YQ0h6Xd+X8FvHdYR
GHmW86Ro2HkBqqKyXbab04+769jpzCdMb0oKzXapU94mKuWZ+fOncshTpUN17neFzb1YIc2k
cwb3rQxDJNd7IR3mq+d3yapkvTYlP7uFk7RGUGVyY29uYSBNeVNRTCBEZXZlbG9wbWVudCBU
ZWFtIChQYWNrYWdpbmcga2V5KSA8bXlzcWwtZGV2QHBlcmNvbmEuY29tPokCNwQTAQoAIQUC
V3S94AIbAwULCQgHAwUVCgkICwUWAgMBAAIeAQIXgAAKCRCTNKJfhQfvpeJKEACpK0T/iv6f
0xpZ6pWzckuYHUiAdYcU94hcCHRfRD2atFPprIfDmO6Jlf/NqyaeE+XCCRb4r7VkKRMKVIyb
agc1a0JRftdg9URT0qF5JpOreKKQJgrAJgLUR2lyM1PVhiRE2h5ee067+SDzL9eV905i55NZ
gzEiJiaMViTMlB8wN+wZT476kGb+ZnrsHiwmy+KiUtNebO1M/tX/xbIgy7K5RNImAgEnHwXk
UiheDEp5OYbiBoJ3GpbSiT5zWKaV1G9sLfuF9naWN/1dAaIhvGAcVeje7AO5QvzqGBevh9wz
JUsRgUBG9YvW+sqcGSGxugpzNmmy6juuw91xseeffxbQIMawwV9X+tzECpbvrUPZjqwr0m7f
mzBk+n6xaKYB+l4kjoTojJo1Gs1UQbF0SvxzOaiJOTCtLRj0oKXOba/TWzA5qefDkGWELHLJ
SocdBAw2B0Mh32BUtqqHbkcEBuAYKWZtsYxjP2rgr3WfqcY6su3ZxF7zFX9ItQcpZMluKfqC
uZxMGNyh5Y7EcbidPoI38o+uIoTTEqg1+ZN/E9hj0l5uVwDfv4ms5SLoqj0Ci2VlI0zsK9vw
AOgM7VP35rF1drMM/FnpKk9n6Yb1ilDiEq97RBhvhxdYiGONwaADB2GDnHUiNWTg2at1onPs
5TdVsz7IQwfnd2nMXleSYbLbz4kCOQQwAQgAIwUCWwLD2RwdIFVzZXIgSUQgaXMgbm8gbG9u
Z2VyIHZhbGlkAAoJEJM0ol+FB++lW4UQALX2/ofmALXhdC0nlh4X1MJLPpmLjyZKTyK3YNOU
JukzGW0LVGIq4SAvPxw4oc4zQ1PCQuUGoj062Fd4sWF1oGFQBOVUAebnyCOcAE1ybcpw9Fhd
B6ZGa0hTx1RD9jg+OT8e1u62XbQyRuLBbbncyIt/lhTcqnCVv14auolAVLuFqiFx5uk2n1x5
Y5bs6ABt9Ka0MhYZm6Qyhm0kGNYn+AiHEwNgdAboe155zp2augVVDmGS+s+tVD60nnWzZLsZ
GCCZh2gJjyxxXNaIeY7OyaMRQFa3gBVGd7UeJZ1d3MR4nR7wlKMUXSC8a0l+bkgi/sgyAJNg
X3bCiEDRIGxGv/Dgg1/ahKVEch/W0Y+0DyifPzAFtnCBH0c2GJUrU8/c2i1iKhYf/r/71113
6Oqd5LDROQGzo4dnzdTs3qEeWdIVkgSwaLUFrw6Kq0tAnZSqHK2WQw3C1oPdlBMimysOhJnw
smYbtlgRF2/rU7QiuJvMHXqBPfOSHKRcy5hoa5S2+PCe/IXBQmod1MlmfsUH6TjwC5SWGFaI
m76+ROsiQKie28fAqRLKqeNvuaMqxTsVpYofQZXEJcSyhwhTcaQxsrYYM+4z8sbdxiIqR7PW
6BthsAKCrOr6U53Pm00+yI16Tt7FNcVcwHl+lRTe/EhDQ93LvbFvB4/Svx/GLdlvdsHaiQQc
BBABCAAGBQJYCWSTAAoJEHpjgJ3lEnYiM40gALkOg65HOAOGkBV6WG9BTpQgnhsmrvC/2ozZ
6dV5577/zYCf6ZB5hMO3mSwcrjTGX5+yD1CyVQEayWuUxoV2By+N9an98660hWAIYTSNiRwS
FITDbLVqXOp7t/B7Bddhj3ZrzA3Eo5bV/QyS/zyKGF1tMkA64IJkQ3292g1L7RYfNG5h1IBB
/xY2xCVcKNT2XcFbAPOct30bqMyT4mdT39WdYg0l4U3zOutemFYs4uyObzrVNOKln0thZpfN
JdRq+OfkE6XwW2UwhTK0/GM5l1Y3NJW64DGPyM7KKcE4FTgq1MRaWepw5sAZr6pTqasWuWUf
20la1M9fIdyxJsAbWn1bhpPIOl3NZ88dRK6XI8Ly36fRa2as/lPeG7ql2ymaOVFDBHqfB+gA
WMzkwF7TS+02er4kg9vnpErPc/aA0lMKmyXHkMANLAnWBA7tx+7sEKck8XcY4e1OiwpUXRxC
+UlSaJYQtE/kmoC2NPQB0FhhvC/VQ0sBOYOAbJ5GukEJVDB7QqqGKjzaKE0LUADCXJFcLY4y
MA9bP9U+Ex/G62YcYn0g1amriKAAkEBRvBOp/qUFSj6b+EqEC5w2my3cLBnATrzskGm32XNO
FdpwR469rOqxomtVedH72vW3sS1etcGw/SHBSplDYTzcnAJQbHvD6LEeOQeWPbA77PD9ASlx
7jGZj3GCq0tc7dndjTLyiL+A4EsRxEUDrH30d8TLaYd1WSD6v5i/xa0r3rXQUmPviBBzRpJx
l0CFB/db2L6a/A2EHkOWjpcL2XSJgcgIVlYZCgM1OEuDGURbLUM9qNiFogdBNCkGTkqjIFES
0iq4lBA4vphcXR8C34OP+7DeT1RthyPjmvi/ErXIQLTpR2Yuwl9/nI2gx6ddZFqkoHFcPSyE
152uJRsYdtL9iIeEIPH//WZ0Fz+h6hhfLiPh6AN1LH3wxKqLW4hAAZ8ytUqANNZT+7o6EVQH
I6VyoigS5TJ34h36jKjRvfUaP4FfkGaPRpfR/cKUiNaCIJRaIFlvlUdbN+biQO3WRxwdyUdg
DSETZnLiym6pKuCpLsic/3+fOyBuWuIxxvGGm3XUt3Lmtvlkey/sSCwInioxn0drYosq+FZP
/ocBQ9aeyxZ5Fqyxqg0BInrusfthXA35WUExVsjwidFPeftz2VbV9gD1Og3JN2Rhd7FzxH0l
rLghxh129R1QVPZiDOiaJQO4QObsC5YXmzF0A/25qJ9Y8UJrsnWrPvjpH41p70Sl6iDWKigd
xi6LD9NrwOnw9qBkIlmjbJL6WKrvjxgVoCo4iP8jtHUx0jwn2qsMkGqO3NM2xWb6MBVzU7nZ
syGpH5OzlrHYoYziw8v6zCLZj8eg3EgFxe+5Ag0EV3S94AEQAJ+4dVt7Lmobk/qtGEBfal13
9/uLAd1xbX56/EJ8JHl8fOw7UtHCUcz0ZGqXO0rODHMAh+BRep0xdSzq9bxqB+S7nneHyAGq
uF2r00frn9h6fNX9K/1z8QbOwFC6tq7VELiB8niOAB527gVApm9Wv//Q1Na4mbd6XeithjPi
surv1q9KAPtD+4rz+PvXOAImLGwXOMLx6FGU60x1609NjfrNzYuNBIxNKkTtK8RuuTrIMqlC
9lpuXd2aQSQG+gWlq3vH6Ldm0ELNEVPHasf/0NYoI75K4ZUFezy+Eu0C8oqNtYYZT0uuYRJl
xqEjp+WIfnDbw2+k64mWvxGf/qNCYkMM8o7nRcozyGlPoMGogT31ipgtTNcAp/hjzwXIe+U7
qSJVtdo5jPU5OoJZWqNoxgVuI9bo2ANfSHIT24bSV80D0/l52rI9IRpM36SkP05WobpHS48E
IVjy7bk2s1GEyogVB28jnh4S03SS0U/QWuUUWSDpL6X7dCyv2wwMoJRVMn8GQrCqR2FO/ldj
gqIgQlCO8wqvS8fmViI8MZf/cqwkv6vEmMD77haHjRYEtgNINZIB8I9KiSDWVGM5owOGcfli
dR4SToyHLrUNBGwf7ESl4v8XUvTq7RaH7SJeopckDiO9ThfAZKTODfJppuWRie6fmbKEhBiz
Ah0LIQfhaXdJABEBAAGJAh8EGAEKAAkFAld0veACGwwACgkQkzSiX4UH76XGqRAAgLuPPUJa
361sqC60tEVzF7E1BmhMAA9OTc6Oqp4ItY7VyYe2aM1JdNzmulfvy88RhCPNCkABFnECmkB1
4kcHOb1Ct+LKjtNbw/QZ/1z2nWY9S2XaDQE29FTvNjOAIXVojAq1L5c7ZR1NPnobLm9rF3UG
JODwn3K2QgZKS5JdI4BJ4YLlGY3dJoPrKiZVrjzeT2RWGFI5TMrBgr1/ZaAaEjXHGlUXkttt
GEKgTPiJr9OomhZ0f9qC6XfgAZY6A9GEy74USlv+eiezvddPBC1xeJkB73PhmW1WxJyKiWBH
M/CRfEyZZUyZ71jKZUI9OvPE+LqdzqelJnMTbvmbTa7zpXaG3APYxtK4aZxN2YA899eBDlcz
nsQsSUNs0DV43WNkCHNgEu/rdf6c07LrKy5pzlDujPIE4ik2SwuV4DT4XOydiY+UarNi2cPq
cWCUOfz3yOT8taTCK0vjvZ+HxFFsNh9+xd5qWLLpbZNgqtCXnZqMtXsPk9RRL3FKUA9x09K5
cDOHsaE4oOiaZbAt8+jS5g3deNr4CRbXfly3Ph68Km9mOQFN+iDTsUaW6Z25Qrl8e8liJLJX
U/lIqvjvbYLyNYKjZhxL4ixmBUUW5jVsboe2Iiak/vkgzQbeDW7J3Y6EX2cYNLGOniQpadSg
Z1XQ/VtRdoBu9dHOUhzHt04Pu1k=
=jpnC
-----END PGP PUBLIC KEY BLOCK-----

# apt-key add percona.gpg
# echo "deb http://repo.percona.com/apt bionic main" >> /etc/apt/sources.list.d/percona.list
# apt-get -y update

# touch ~/.my.cnf
# chmod 0600 ~/.my.cnf
# vi ~/.my.cnf
[client]
host     = localhost
user     = root
password = rootPasswort

Installation der DB

# apt-get -y install libaio1 percona-xtradb-cluster-57

xtrabackup-User (wsrep_sst_method, wsrep_sst_auth) ⇒ Connection and Privileges Needed:

# echo "CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'geheimesPasswort'; GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'; FLUSH PRIVILEGES;" | mysql

# service mysql stop
# echo '!includedir /var/lib/mysql/cnf/conf.d/' >> /etc/mysql/my.cnf
# mv /var/lib/mysql /var/lib/mysql_SiK
# mkdir -p /var/lib/mysql
# mv /var/lib/mysql_SiK /var/lib/mysql/data
# mkdir -p /var/lib/mysql/cnf/conf.d /var/lib/mysql/data /var/lib/mysql/log_bin
# chown -R mysql:mysql /var/lib/mysql/cnf/conf.d /var/lib/mysql/data /var/lib/mysql/log_bin

/var/lib/mysql/cnf/conf.d/basic.cnf

#
# diese Konfigurationsdatei überschreibt ggf. die Standard-Optionen aus der /etc/mysql/my.cnf
# um eigene Werte zu setzen
#
 
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
 
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
 
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
datadir         = /var/lib/mysql/data
lc_messages     = german
 
bind-address    = 0.0.0.0
max_connections         = 400
slow_query_log_file     = /var/log/mysql/mariadb-slow.log
 
log_bin                 = /var/lib/mysql/log_bin/mariadb-bin
log_bin_index           = /var/lib/mysql/log_bin/mariadb-bin.index
expire_logs_days        = 10
max_binlog_size         = 100M

/var/lib/mysql/cnf/conf.d/galera.cnf

[mysqld]
binlog_format           = ROW
default-storage-engine  = innodb
innodb_autoinc_lock_mode= 2
 
### Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
 
### Galera Cluster Configuration
wsrep_cluster_name="notify_cluster"
#wsrep_cluster_address="gcomm://"
wsrep_cluster_address="gcomm://192.168.1.101,192.168.1.102,192.168.1.103"
 
### Galera Synchronization Configuration
### 
wsrep_sst_method=xtrabackup
wsrep_sst_auth = sstuser:geheimesPasswort
 
### Galera Node Configuration
wsrep_node_address="192.168.1.101"
wsrep_node_name="knoten01"