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
[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'
xtrabackup muß lokal auf dem DB-Host-System ausgeführt werden!
# innobackupex --help ... NOTICE: 'innobackupex' is deprecated, please switch to 'xtrabackup' ...
# 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
# mkdir -p /backup # xtrabackup --backup --target-dir=/backup
der Vorbereitungsschritt darf nicht unterbrochen werden, sonst können die Daten beschädigt werden:
# xtrabackup --prepare --target-dir=/backup
# 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/'
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.
# 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
# mkdir /backup # innobackupex --rsync --slave-info /backup # innobackupex --apply-log /backup
oder
# innobackupex --user=bekupuser --password=mypassword --no-timestamp /backup
# 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
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
-----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
# # 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
[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"