Wissen

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: Start » percona_xtradb_cluster_galera
Zuletzt angesehen: • verschluesselung_mit_cryptsetup
percona_xtradb_cluster_galera

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
percona_xtradb_cluster_galera [2020-12-10 13:49:22] – manfredpercona_xtradb_cluster_galera [2021-10-26 16:21:01] (aktuell) – [DB-Basis-Installation] manfred
Zeile 1: Zeile 1:
 +====== Percona XtraDB Cluster + Galera ======
 +
 +  * [[http://www.percona.com/doc/percona-xtradb-cluster/]]
 +    * **[[http://www.severalnines.com/galera-configurator/|Configurator for Galera Replication]]**
 +
 +[[https://ask.openstack.org/en/question/4432/service-mysql-start-fail-on-galera-2nd-node/|Galera/Wsrep required following ports to be opened and available]]:
 +  * Port **3306 TCP** - mysql
 +  * Port **4444 TCP** - used for SST (State Snapshot Transfer)
 +  * Port **4567 UDP+TCP** - used by Galera for inter-node communication
 +  * Port **4568 TCP** (<wsrep_node_address>:<Port +1>) - used for IST (Incremental State Transfer)
 +  * zusätzliches:
 +    * ''SST'' benötigt einen Benutzer im DBMS, mit dem die Datenübertragung eingeleitet wird:
 +      * ''echo "GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost' IDENTIFIED BY 'geheim';FLUSH PRIVILEGES;" | mysql''
 +    * damit längere SST-Übertragungen nicht von SystemD unterbrochen werden:
 +      * ''vi /etc/systemd/system/mysql.service.d/timeoutsec.conf''
 +      * ''[Service]''
 +      * ''TimeoutStartSec=0''
 +      * ''TimeoutStopSec=0''
 +      * ''systemctl daemon-reload''
 +
 +
 +===== DB-Basis-Installation =====
 +
 +  * [[https://www.percona.com/doc/percona-repo-config/percona-release.html#deb-based-gnu-linux-distributions|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
 +
 +
 +===== DB-Konfiguration =====
 +
 +<code>
 +#------------------------------------------------------------------------------#
 +# https://www.percona.com/doc/percona-xtradb-cluster/8.0/wsrep-system-index.html
 +#
 +# Bei "wsrep_sst_donor" dürfen nur Hostnamen, keine IPs eingetragen werden,
 +# https://www.percona.com/doc/percona-xtradb-cluster/8.0/wsrep-system-index.html#wsrep_sst_donor
 +# https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-sst-donor
 +# sonst kommt es zu dem Fehler:
 +# [ERROR] WSREP: Requesting state transfer failed: -113(No route to host)
 +# Most likely it is due to inability to communicate with the cluster primary component. Restart required.
 +# Ein Komma an Ender der Zeile besagt, dass auch dynamisch nach anderen Hostnamen gesucht werden darf
 +#
 +#------------------------------------------------------------------------------#
 +#
 +### Standard für DB: 3306
 +port                            = 3306
 +bind-address                    = 0.0.0.0
 +#
 +### Standard für SST: 4444
 +wsrep_sst_receive_address       = dbserver01.datenbank:4444
 +wsrep_sst_donor                 = dbserver01.datenbank:4444,dbserver02.datenbank:4444,dbserver03.datenbank:4444,dbserver04.datenbank:4444,dbserver05.datenbank:4444,
 +#
 +### Standard für InterCom: 4567
 +#wsrep_cluster_address          = gcomm://
 +wsrep_cluster_address           = "gcomm://dbserver01.datenbank:4567,dbserver02.datenbank:4567,dbserver03.datenbank:4567?gmcast.listen_addr=tcp://0.0.0.0:4567"
 +wsrep_node_address              = dbserver01.datenbank:4567
 +#
 +### Standard für IST / replication: 4568  (<wsrep_node_address>:<Port +1>)
 +### https://galeracluster.com/library/documentation/glossary.html#term-incremental-state-transfer
 +### https://galeracluster.com/library/documentation/galera-parameters.html#ist-recv-addr
 +wsrep_provider_options          = "ist.recv_addr=dbserver01.datenbank:4568;"
 +#
 +#------------------------------------------------------------------------------#
 +</code>
 +
 +
 +===== Ubuntu-Installation =====
 +
 +//Als erstes muss ein frisches "Ubuntu 10.04.4 LTS server" (ohne weitere Pakete) installiert werden.//
 +
 +erstmal alle neuen Updates installieren:
 +  > aptitude update && aptitude -y safe-upgrade && aptitude autoclean
 +
 +diese Software brauche ich:
 +  > aptitude install ssh vim mc screen sysstat links
 +
 +diese Software beschleunigt die DB:
 +  > aptitude install libaio1
 +
 +damit wir weitere GPG-Schlüssel importieren können:
 +  > aptitude install python-software-properties
 +
 +
 +===== Galera =====
 +
 +  * [[http://linsenraum.de/mt/mt-search.cgi?blog_id=2&tag=galera&limit=20]]
 +  * [[http://linsenraum.de/erkules/mysql/]]
 +
 +von der [[https://www.percona.com/doc/percona-xtradb-cluster/5.7/install/apt.html#apt|Galera-DB]] benötigte Ports:
 +  * [[https://galeracluster.com/library/documentation/firewall-settings.html|Firewall Settings]]
 +    * ''3306'' - is the default port for **MySQL client connections** and State Snapshot Transfer using mysqldump for backups.
 +    * ''4444'' - is reserved for Galera **Cluster replication** traffic. Multicast replication uses both **TCP** and **UDP** transport on this port.
 +    * ''4567'' - **IST**: is the port for Incremental State Transfer.
 +    * ''4568'' - **SST**: is used for all other State Snapshot Transfer.
 +
 +
 +===== Percona =====
 +
 +    * [[http://www.percona.com/downloads/Percona-XtraDB-Cluster/LATEST/|Downloads]]
 +    * [[http://www.percona.com/doc/percona-server/5.5/installation.html#using-percona-software-repositories?id=repositories:start|Repositories - Version 5.5]]
 +
 +GPG-Schlüssel holen:
 +  * [[http://keys.gnupg.net/]] - Key-Server
 +    * [[http://pgpkeys.mallos.nl:11371/]] - Interface
 +
 +  apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
 +
 +
 +=== Percona-GPG-Schlüssel ===
 +
 +  * [[http://www.techques.com/question/2-252355/Installing-Percona-Server-error:-keyserver]]
 +
 +<file>
 +-----BEGIN PGP PUBLIC KEY BLOCK-----
 +Version: GnuPG v1.4.11 (GNU/Linux)
 +
 +mQGiBEsm3aERBACyB1E9ixebIMRGtmD45c6c/wi2IVIa6O3G1f6cyHH4ump6ejOi
 +AX63hhEs4MUCGO7KnON1hpjuNN7MQZtGTJC0iX97X2Mk+IwB1KmBYN9sS/OqhA5C
 +itj2RAkug4PFHR9dy21v0flj66KjBS3GpuOadpcrZ/k0g7Zi6t7kDWV0hwCgxCa2
 +f/ESC2MN3q3j9hfMTBhhDCsD/3+iOxtDAUlPMIH50MdK5yqagdj8V/sxaHJ5u/zw
 +YQunRlhB9f9QUFfhfnjRn8wjeYasMARDctCde5nbx3Pc+nRIXoB4D1Z1ZxRzR/lb
 +7S4i8KRr9xhommFnDv/egkx+7X1aFp1f2wN2DQ4ecGF4EAAVHwFz8H4eQgsbLsa6
 +7DV3BACj1cBwCf8tckWsvFtQfCP4CiBB50Ku49MU2Nfwq7durfIiePF4IIYRDZgg
 +kHKSfP3oUZBGJx00BujtTobERraaV7lIRIwETZao76MqGt9K1uIqw4NT/jAbi9ce
 +rFaOmAkaujbcB11HYIyjtkAGq9mXxaVqCC3RPWGr+fqAx/akBLQ2UGVyY29uYSBN
 +eVNRTCBEZXZlbG9wbWVudCBUZWFtIDxteXNxbC1kZXZAcGVyY29uYS5jb20+iGAE
 +ExECACAFAksm3aECGwMGCwkIBwMCBBUCCAMEFgIDAQIeAQIXgAAKCRAcTL3NzS79
 +Kpk/AKCQKSEgwX9r8jR+6tAnCVpzyUFOQwCfX+fw3OAoYeFZB3eu2oT8OBTiVYu5
 +Ag0ESybdoRAIAKKUV8rbqlB8qwZdWlmrwQqg3o7OpoAJ53/QOIySDmqy5TmNEPLm
 +lHkwGqEqfbFYoTbOCEEJi2yFLg9UJCSBM/sfPaqb2jGP7fc0nZBgUBnFuA9USX72
 +O0PzVAF7rCnWaIz76iY+AMI6xKeRy91TxYo/yenF1nRSJ+rExwlPcHgI685GNuFG
 +chAExMTgbnoPx1ka1Vqbe6iza+FnJq3f4p9luGbZdSParGdlKhGqvVUJ3FLeLTqt
 +caOn5cN2ZsdakE07GzdSktVtdYPT5BNMKgOAxhXKy11IPLj2Z5C33iVYSXjpTelJ
 +b2qHvcg9XDMhmYJyE3O4AWFh2no3Jf4ypIcABA0IAJO8ms9ov6bFqFTqA0UW2gWQ
 +cKFN4Q6NPV6IW0rV61ONLUc0VFXvYDtwsRbUmUYkB/L/R9fHj4lRUDbGEQrLCoE+
 +/HyYvr2rxP94PT6Bkjk/aiCCPAKZRj5CFUKRpShfDIiow9qxtqv7yVd514Qqmjb4
 +eEihtcjltGAoS54+6C3lbjrHUQhLwPGqlAh8uZKzfSZq0C06kTxiEqsG6VDDYWy6
 +L7qaMwOqWdQtdekKiCk8w/FoovsMYED2qlWEt0i52G+0CjoRFx2zNsN3v4dWiIhk
 +ZSL00Mx+g3NA7pQ1Yo5Vhok034mP8L2fBLhhWaK3LG63jYvd0HLkUFhNG+xjkpeI
 +SQQYEQIACQUCSybdoQIbDAAKCRAcTL3NzS79KlacAJ9H6emL/8dsoquhE9PNnKCI
 +eMTmmQCfXRLIoNjJa20VEwJDzR7YVdBEiQI=
 +=AD5m
 +-----END PGP PUBLIC KEY BLOCK-----
 +</file>
 +
 +
 +==== Installation des Percona-Repository's =====
 +
 +[[https://www.percona.com/doc/percona-server/LATEST/installation.html#installing-from-binaries]]
 +
 +
 +=== Percona GPG-Key ====
 +
 +Um die Authentizität der Percona-Pakete zu verifizieren benötig aptitude deren öffentlichen GPG-Schlüssel.
 +\\ [[http://www.percona.com/downloads/RPM-GPG-KEY-percona]]
 +
 +
 +==== Percona XtraDB Cluster + Galera - Grundinstallation - Ubuntu 16.04 ====
 +
 +  > apt install percona-xtradb-cluster-57
 +
 +  > dpkg -L percona-xtradb-cluster-server-5.7 | fgrep libgalera_smm.so
 +  /usr/lib/galera3/libgalera_smm.so
 +  /usr/lib/libgalera_smm.so
 +  
 +  > vi /etc/mysql/conf.d/galera.cnf
 +  ...
 +  wsrep_provider                  = /usr/lib/libgalera_smm.so
 +  ...
 +
 +
 +==== "Percona XtraDB Cluster 5.5.33" und "Galera 23.7.6" auf "Ubuntu 10.04.4 LTS server" installieren ====
 +
 +  * [[http://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html]]
 +  * [[https://extremeshok.com/2013/07/18/ubuntu-12-04-lts-3-node-percona-xtradb-cluster-xtrabackup-glb-load-balancer-aka-mysql-galera/]]
 +
 +
 +=== Vorbereitungen ===
 +
 +Repository einrichten:
 +  > echo 'deb http://repo.percona.com/apt lucid main' >> /etc/apt/sources.list
 +  > aptitude update
 +
 +die XtraDB hat nach der Installation keine ''my.cnf'', deshalb müssen wir hier unsere gewünschten Grundeinstellungen vor der Installation festlegen:
 +  > echo '!includedir /etc/mysql/conf.d/' >> /etc/mysql/my.cnf
 +
 +  > vi /etc/mysql/conf.d/grundeinstellungen.cnf
 +<file>
 +#------------------------------------------------------------------------------#
 +[client]
 +
 +# This will be passed to all mysql clients
 +# It has been reported that passwords should be enclosed with ticks/quotes
 +# escpecially if they contain "#" chars...
 +# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
 +port                            = 3306
 +socket                          = /var/run/mysqld/mysqld.sock
 +default_character_set           = utf8
 +
 +#------------------------------------------------------------------------------#
 +[mysql]
 +default_character_set           = utf8
 +
 +#------------------------------------------------------------------------------#
 +[mysqld_safe]
 +socket                          = /var/run/mysqld/mysqld.sock
 +nice                            = 0
 +
 +#------------------------------------------------------------------------------#
 +[server]
 +sql-mode=""
 +
 +#------------------------------------------------------------------------------#
 +[mysqld]
 +pid_file                        = mysql.pid
 +log_bin                         = mysql-bin
 +relay_log                       = mysql-relay-bin
 +log_error                       = /var/log/mysql/error.log
 +log_warnings                    = 1
 +#log_warnings                    = 2
 +
 +character_set_server            = utf8
 +collation_server                = utf8_unicode_ci
 +#init_connect                    = 'SET NAMES utf8';
 +skip_character_set_client_handshake
 +### http://dev.mysql.com/doc/refman/5.1/de/blocked-host.html
 +max_connect_errors              = 1000
 +max_connections                 = 1000
 +connect_timeout                 = 10
 +interactive_timeout             = 60
 +wait_timeout                    = 60
 +expire_logs_days                = 7
 +sync_binlog                     = 1
 +log_slave_updates               = 1
 +local_infile                    = 1
 +auto_increment_offset           = 1
 +auto_increment_increment        = 1
 +
 +#------------------------------------------------------------------------------#
 +###
 +### muss ein vielfaches von 256 sein 
 +### Standard vor MySQL 5.6 ist 1024
 +### Standard ab MySQL 5.6 ist 8192
 +binlog_row_event_max_size       = 8192
 +###
 +### http://dev.mysql.com/doc/refman/5.1/de/replication-row-based.html
 +###
 +### statement: anweisungsbasierte Replikation (Statement-Based Replication, SBR)
 +### row: datensatzbasierte Replikation (Row-Based Replication, RBR, gibt es seit Version 5.1.5)
 +### Standard vor MySQL 5.1.12 ist STATEMENT
 +### Standard ab MySQL 5.1.12 ist MIXED
 +###
 +### http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html
 +### Bei nichtdeterministischer Datenmodifikation ist nur "RAW" zu verwenden!
 +# Standard in "Percona XtraDB Cluster 5.5.33": STATEMENT
 +binlog_format                   = ROW
 +#default_storage_engine          = InnoDB
 +# Standard seit MySQL 5.5.7: 0
 +innodb_file_per_table           = 1
 +#innodb_fast_shutdown            = 1
 +#innodb_flush_log_at_trx_commit  = 1
 +# Standard in MySQL 5.5: 0
 +innodb_locks_unsafe_for_binlog  = 1
 +# Standard in MySQL 5.5: 1
 +innodb_autoinc_lock_mode        = 2
 +# Standard in MySQL 5.5: 8M
 +#innodb_log_buffer_size          = 1M
 +# Standard in MySQL 5.5: 0
 +innodb_thread_concurrency       = 8
 +
 +#------------------------------------------------------------------------------#
 +### Einstellungen von der alten Dialing-DB (MySQL 5.1)
 +
 +### key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory
 +key_buffer_size                 = 256M
 +# [Warning] The syntax '--max_long_data_size' is deprecated and will be removed in a future release. Please use '--max_allowed_packet' instead.
 +#max_long_data_size              = 16M
 +max_allowed_packet              = 16M
 +max_heap_table_size             = 256M
 +# query_cache_limit               = 256M
 +# query_cache_size                = 256M
 +# query_cache_type                = 1
 +slow_query_log_file             = /var/lib/mysql/mysql-slow.log
 +# sql_auto_is_null                = ON
 +table_open_cache                = 512
 +thread_cache_size               = 8
 +tmp_table_size                  = 256M
 +
 +#------------------------------------------------------------------------------#
 +</file>
 +
 +
 +Percona XtraDB Cluster installieren:
 +  > aptitude install percona-xtradb-cluster-galera percona-xtradb-cluster-client-5.5 percona-xtradb-cluster-server-5.5 percona-xtradb-cluster-common-5.5 libmysqlclient18 tinyca
 +  
 +  ...
 +  
 +   * Percona XtraDB Cluster is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
 +   * Run the following commands to create these functions:
 +  
 +          mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
 +          mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
 +          mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
 +  
 +   * See http://www.percona.com/doc/percona-server/5.5/management/udf_percona_toolkit.html for more details
 +  
 +  
 +   * Starting MySQL (Percona XtraDB Cluster) database server mysqld         [ OK ]
 +  ...
 +  
 +  > update-rc.d mysql enable 2345
 +  > echo "x" | awk '{print "[client]\nhost     = localhost\nuser     = root\npassword =",$1"\nsocket   = /var/run/mysqld/mysqld.sock"}' > ~/.my.cnf
 +  > chmod 0600 ~/.my.cnf
 +  
 +  > mysqlshow
 +
 +den [[http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/privileges.html#permissions-and-privileges-needed|State Snapshot Transfer]]-User (SST-User) anlegen:
 +  > echo "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bodo'@'localhost' IDENTIFIED BY 'qwertz';FLUSH PRIVILEGES;" | mysql
 +
 +root-Zugriff soll auch von außen funktionieren
 +  > echo "SELECT host,user,password FROM user;" | mysql -t mysql
 +  > echo "UPDATE user set host='%' WHERE host='$(hostname)' AND user='root';" | mysql mysql
 +  > echo "SELECT host,user,password FROM user;" | mysql -t mysql
 +  > mysqladmin reload
 +
 +so verhindert man (bei Bedarf) das automatische updaten der DB (siehe auch [[http://wiki.debian.org/AptPreferences]]):
 +  > echo 'Package: *' >> /etc/apt/preferences.d/00percona.pref
 +  > echo 'Pin: release o=Percona Development Team' >> /etc/apt/preferences.d/00percona.pref
 +  > echo 'Pin-Priority: 1001' >> /etc/apt/preferences.d/00percona.pref
 +
 +bei Startproblemen stehen die Log-Daten in der letzten tmp-Datei im "/tmp"-Verzeichnis:
 +  > ls -rtlh /tmp/tmp.*
 +  -rw------- 1 mysql root 18K 2013-09-23 17:39 /tmp/tmp.u3hCB4aJAB
 +  -rw------- 1 mysql root 18K 2013-09-24 11:26 /tmp/tmp.6i9tfHMuT7
 +  
 +  > cat /tmp/tmp.6i9tfHMuT7
 +  InnoDB: using the same InnoDB data or log files.
 +  InnoDB: Unable to lock ./ibdata1, error: 11
 +  InnoDB: Check that you do not already have another mysqld process
 +  InnoDB: using the same InnoDB data or log files.
 +  InnoDB: Unable to lock ./ibdata1, error: 11
 +  InnoDB: Check that you do not already have another mysqld process
 +  InnoDB: using the same InnoDB data or log files.
 +  InnoDB: Unable to lock ./ibdata1, error: 11
 +  InnoDB: Check that you do not already have another mysqld process
 +  InnoDB: using the same InnoDB data or log files.
 +  130924 13:58:36  InnoDB: Unable to open the first data file
 +  InnoDB: Error in opening ./ibdata1
 +  130924 13:58:36  InnoDB: Operating system error number 11 in a file operation.
 +  InnoDB: Error number 11 means 'Resource temporarily unavailable'.
 +  InnoDB: Some operating system error numbers are described at
 +  InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
 +  130924 13:58:36 InnoDB: Could not open or create data files.
 +  130924 13:58:36 InnoDB: If you tried to add new data files, and it failed here,
 +  130924 13:58:36 InnoDB: you should now edit innodb_data_file_path in my.cnf back
 +  130924 13:58:36 InnoDB: to what it was, and remove the new ibdata files InnoDB created
 +  130924 13:58:36 InnoDB: in this failed attempt. InnoDB only wrote those files full of
 +  130924 13:58:36 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
 +  130924 13:58:36 InnoDB: remove old data files which contain your precious data!
 +  130924 13:58:36 [ERROR] Plugin 'InnoDB' init function returned error.
 +  130924 13:58:36 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
 +  130924 13:58:36 [ERROR] Unknown/unsupported storage engine: InnoDB
 +  130924 13:58:36 [ERROR] Aborting
 +  
 +  130924 13:58:36 [Note] /usr/sbin/mysqld: Shutdown complete
 +
 +Bei diesem Fehler (''error: 11'') ist die InnoDB-Datei bereits offen,
 +d.h. es läuft noch ein MySQL-Prozess:
 +  > ps alx | fgrep -v grep | fgrep mysql
 +  4     0 12123     1  20   0   4404   756 wait   S    ?          0:00 /bin/sh /usr/bin/mysqld_safe
 +  4   106 12754 12123  20   0 1855536 133468 poll_s Sl ?          0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/lib/mysql/mysql.pid --socket=/var/run/mysqld/mysqld.sock --wsrep_start_position=19f00d60-301c-11e3-a082-06f5d05d0c45:310504
 +
 +den kann man z.B. so beenden:
 +  > killall /usr/sbin/mysqld
 +
 +Jetzt noch ein paar Sekunden warten und dann sollte kein mysql-Prozess mehr laufen und die DB wieder starten können:
 +  > ps alx | fgrep -v grep | fgrep mysql
 +  > service mysql start
 +   * Stopping MySQL (Percona XtraDB Cluster) mysqld                         [ OK ]
 +   * Starting MySQL (Percona XtraDB Cluster) database server mysqld
 +
 +
 +=== debian-sys-maint ===
 +
 +**An dieser Stelle sollte sicher gestellt werden, dass alle zukünftigen Cluster-Knoten für den Benutzer "debian-sys-maint" das gleiche Passwort verwenden!**
 +  > cat /etc/mysql/debian.cnf
 +  # Automatically generated for Debian scripts. DO NOT TOUCH!
 +  [client]
 +  host     = localhost
 +  user     = debian-sys-maint
 +  password = 455uoHrKFLHFNwgP
 +  socket   = /var/run/mysqld/mysqld.sock
 +  [mysql_upgrade]
 +  host     = localhost
 +  user     = debian-sys-maint
 +  password = 455uoHrKFLHFNwgP
 +  socket   = /var/run/mysqld/mysqld.sock
 +  basedir  = /usr
 +  
 +  > mysqlshow -udebian-sys-maint -p455uoHrKFLHFNwgP
 +  +--------------------+
 +  |     Databases      |
 +  +--------------------+
 +  | information_schema |
 +  | dialing            |
 +  | mysql              |
 +  | performance_schema |
 +  +--------------------+
 +
 +Um diesen Benutzer auch auf den anderen Knoten dieses Passwort zu geben, muss das ausgewählte Passwort in der Datei //"/etc/mysql/debian.cnf"// geändert werden sowie in der "User"-Tabelle in der DB auf dem neuen Knoten:
 +  > echo "update user set password=password('455uoHrKFLHFNwgP') where User='debian-sys-maint';" | mysql mysql
 +  > mysqladmin reload
 +
 +
 +==== Konfiguration ====
 +
 +  * [[http://www.percona.com/doc/percona-xtradb-cluster/installation.html]]
 +    * [[http://www.percona.com/doc/percona-xtradb-cluster/wsrep-system-index.html#wsrep-system-index]]
 +    * [[http://www.percona.com/doc/percona-xtradb-cluster/wsrep-status-index.html#wsrep-status-index]]
 +
 +Tuning:
 +  * [[http://www.severalnines.com/blog/understanding-gcache-galera]]: '' echo "SHOW VARIABLES LIKE 'wsrep_provider_options' \G" | mysql | tr -s ';' '\n' ''
 +  * [[http://www.codership.com/wiki/doku.php?id=galera_parameters]]
 +  * [[http://www.codership.com/wiki/doku.php?id=gcache]]
 +  * [[http://stackoverflow.com/questions/20363540/problems-with-node-joining-the-cluster-when-using-sstxtrabackup-galera]]: '' wsrep_provider_options = "gcache.size=4G; gcache.page_size=1G" ''
 +  * [[https://groups.google.com/forum/#!topic/codership-team/8m9uDSjKed0]]:
 +    - '' wsrep_node_address = 192.168.1.101 ''
 +    - '' wsrep_node_name = galera01 ''
 +    - '' wsrep_provider_options = "evs.keepalive_period = PT3S; evs.inactive_check_period = PT10S; evs.suspect_timeout = PT30S; evs.inactive_timeout = PT1M; evs.install_timeout = PT1M" ''
 +  * [[http://www.percona.com/doc/percona-xtradb-cluster/5.5/wsrep-system-index.html]]: '' wsrep_certify_nonPK = ON ''
 +
 +  * [[Galera-Beispiel für clustereinstellungen.cnf]]
 +
 +  > vi /etc/mysql/conf.d/clustereinstellungen.cnf
 +<file>
 +#------------------------------------------------------------------------------#
 +[mysqld]
 +server_id                       = 1
 +report_host                     = galera01
 +
 +#slave_skip_errors               = 1032,1062
 +#replicate_wild_ignore_table     = memorydb.%
 +
 +#
 +### Galera-Optionen
 +#
 +wsrep_node_address              = 192.168.1.101
 +wsrep_node_name                 = galera01
 +wsrep_cluster_name              = galeracluster
 +#
 +# Replikations-Benutzer und -Passwort
 +wsrep_sst_auth                  = bodo:qwertz
 +#
 +# IPs der Clustermitglieder, ohne die eigene IP
 +# beim Start des ersten Node muss diese Variable leer sein (nach dem ergänzen: service mysql bootstrap-pxc)
 +wsrep_cluster_address           = gcomm://
 +#wsrep_cluster_address           = gcomm://192.168.1.102,192.168.1.103
 +#
 +wsrep_slave_threads             = 4
 +wsrep_sst_method                = xtrabackup
 +wsrep_sst_auth                  = sstuser:geheimesPasswort
 +#
 +### Ubuntu 10.04 LTS
 +#wsrep_provider                  = /usr/lib64/libgalera_smm.so
 +#
 +### Ubuntu 12.04 LTS
 +wsrep_provider                  = /usr/lib/libgalera_smm.so
 +#
 +wsrep_provider_options          = "evs.keepalive_period = PT3S; evs.inactive_check_period = PT10S; evs.suspect_timeout = PT30S; evs.inactive_timeout = PT1M; evs.install_timeout = PT1M; gcache.size=4G; gcache.page_size=1G"
 +wsrep_certify_nonPK             = ON
 +#------------------------------------------------------------------------------#
 +</file>
 +
 +''xtrabackup''-User (''wsrep_sst_method'', ''wsrep_sst_auth''):
 +  # echo "CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'geheimesPasswort'; GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'; FLUSH PRIVILEGES;" | mysql
 +
 +**Bei meinen Tests hat es noch nie funktioniert, wenn die IPs mit Port-Nummer versehen waren. Laut Doku soll es aber gehen.**
 +
 +weitere Tips:
 +  * [[http://www.codership.com/wiki/doku.php?id=configuration_tips]]
 +  * [[http://www.codership.com/wiki/doku.php?id=galera_parameters]]
 +  * [[http://www.codership.com/wiki/doku.php?id=mysql_galera_configuration]]
 +
 +
 +=== die drei Knoten nacheinander hochfahren ===
 +
 +  * [[http://www.percona.com/doc/percona-xtradb-cluster/manual/bootstrap.html#bootstrap]]
 +
 +Kurzfassung der Arbeitsschritte:
 +  - __auf Knoten 1__ MySQL hochfahren
 +  - __auf Knoten 2__ MySQL hochfahren
 +  - __auf Knoten 1__ den 2. Knoten zur MySQL-CFG hinzufühgen
 +  - __auf Knoten 1__ Cluster-Reload ausführen: ''service mysql bootstrap-pxc''
 +  - __auf Knoten 3__ MySQL hochfahren
 +  - __auf Knoten 1__ den 3. Knoten zur MySQL-CFG hinzufühgen
 +  - __auf Knoten 1__ Cluster-Reload ausführen: ''service mysql bootstrap-pxc''
 +  - __auf Knoten 2__ den 3. Knoten zur MySQL-CFG hinzufühgen
 +  - __auf Knoten 2__ Cluster-Reload ausführen: ''service mysql bootstrap-pxc''
 +
 +
 +== Knoten 1 hochfahren ==
 +
 +  > vi /etc/mysql/conf.d/grundeinstellungen.cnf
 +  ...
 +  wsrep_cluster_address          = gcomm://
 +  ...
 +
 +MySQL starten:
 +  > service mysql start
 +   * Stopping MySQL (Percona XtraDB Cluster) mysqld                         [ OK ]
 +   * Starting MySQL (Percona XtraDB Cluster) database server mysqld
 +
 +
 +== Knoten 2 hochfahren ==
 +
 +  > vi /etc/mysql/conf.d/grundeinstellungen.cnf
 +  ...
 +  wsrep_cluster_address           = gcomm://192.168.1.67,192.168.1.68
 +  ...
 +
 +MySQL starten:
 +  > service mysql start
 +   * Stopping MySQL (Percona XtraDB Cluster) mysqld                         [ OK ]
 +   * Starting MySQL (Percona XtraDB Cluster) database server mysqld
 +   * SST in progress, setting sleep higher mysqld
 +
 +__Wenn in der DB von //Knoten 1// viele Daten drin sind, dann wird es länger dauern, bis der Startvorgang durchgeführt ist, weil in diesem Schritt automatisch eine Syncronisation inbegriffen ist!__
 +
 +
 +== Knoten 1 bootstrap-pxc ==
 +
 +damit der erste Knoten starten kann, muss "''safe_to_bootstrap: 0''" gesetzt sein; ist das nicht der Fall, dann ist sicherlich ein anderer Knoten der letzte Master gewesen:
 +  > vi /var/lib/mysql/data/grastate.dat
 +  ...
 +  safe_to_bootstrap: 0
 +
 +  > vi /etc/mysql/conf.d/grundeinstellungen.cnf
 +  ...
 +  wsrep_cluster_address           = gcomm://192.168.1.67,192.168.1.68
 +  ...
 +
 +Cluster-Reload ausführen:
 +  > service mysql bootstrap-pxc
 +
 +
 +== Knoten 3 hochfahren ==
 +
 +  > vi /etc/mysql/conf.d/grundeinstellungen.cnf
 +  ...
 +  wsrep_cluster_address           = gcomm://192.168.1.67,192.168.1.68,192.168.1.69
 +  ...
 +
 +MySQL starten:
 +  > service mysql start
 +   * Stopping MySQL (Percona XtraDB Cluster) mysqld                         [ OK ]
 +   * Starting MySQL (Percona XtraDB Cluster) database server mysqld
 +   * SST in progress, setting sleep higher mysqld
 +
 +__Wenn in der DB von //Knoten 1// viele Daten drin sind, dann wird es länger dauern, bis der Startvorgang durchgeführt ist, weil in diesem Schritt automatisch eine Syncronisation inbegriffen ist!__
 +
 +
 +== Knoten 1 bootstrap-pxc ==
 +
 +vi /etc/mysql/conf.d/grundeinstellungen.cnf
 +  ...
 +  wsrep_cluster_address           = gcomm://192.168.1.67,192.168.1.68,192.168.1.69
 +  ...
 +
 +Cluster-Reload ausführen:
 +  > service mysql bootstrap-pxc
 +
 +
 +== Knoten 2 bootstrap-pxc ==
 +
 +vi /etc/mysql/conf.d/grundeinstellungen.cnf
 +  ...
 +  wsrep_cluster_address           = gcomm://192.168.1.67,192.168.1.68,192.168.1.69
 +  ...
 +
 +Cluster-Reload ausführen:
 +  > service mysql bootstrap-pxc
 +
 +
 +===== MySQL-Fehlermeldungen im Galera-Cluster =====
 +
 +
 +=== Fehlermeldung "1" ===
 +
 +  ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
 +
 +Dieser Fehler besagt, dass das Passwort in der Datei "/etc/mysql/debian.cnf" nicht stimmt.
 +Diese Datei muss auf allen Knoten des Clusters identisch sein.
 +Um das Problem zu lösen, sucht man sich einen Knoten, auf dem diesen Kommando fehlerfrei ausgeführt werden kann und übernimmt von ihm diese Datei:
 +  > mysqlshow -u$(awk '/^user/{print $3}' /etc/mysql/debian.cnf | head -n1) -p$(awk '/^password/{print $3}' /etc/mysql/debian.cnf | head -n1)
 +  +--------------------+
 +  |     Databases      |
 +  +--------------------+
 +  | information_schema |
 +  | mysql              |
 +  | performance_schema |
 +  +--------------------+
 +
 +
 +=== Fehlermeldung "2" ===
 +
 +  ERROR 1047 (08S01) at line 1: Unknown command
 +
 +Diese Fehlermeldung besagt, dass dieser Knoten nicht mit den anderen Knoten synchron ist.
 +Das kann verschiedene Ursachen haben:
 +  - Verbindungsprobleme (Abrisse bzw. Unterbrechungen);
 +  - in einen anderen Knoten wird so intensiev reingeschrieben, dass die Replikation nicht so schnell mitkommt, das sollte sich spätestens nach dem beenden des ursächlichen Schreibvorganges von selber geben;
 +
 +Ob eine Verbindung zwischen den Knoten besteht, kann mit diesem Kommando erkannt werden:
 +  > echo "SHOW STATUS LIKE 'wsrep_cluster_size';SHOW STATUS LIKE 'wsrep_incoming_addresses';SHOW STATUS LIKE 'wsrep_connected';SHOW STATUS LIKE 'wsrep_last_committed';SHOW STATUS LIKE 'wsrep_local_state_comment';" | mysql -t
 +
 +
 +=== Fehlermeldung "3" ===
 +
 +  ERROR 1548 (HY000) at line 1: Cannot load from mysql.proc. The table is probably corrupted
 +
 +Diese Fehlermeldung deutet auf eine veraltete Tabellen-Struktur hin und konnte bei mir durch dieses Kommando beseitigt werden:
 +  > mysql_upgrade --force
 +
 +Es kann aber auch an einer defekten Tabelle liegen, dann sollte man dieses Kommado ausprobieren:
 +  > mysqlcheck -Ag --auto-repair
 +
 +
 +===== ein paar Kontrollkommandos =====
 +
 +<file>
 +#!/bin/bash
 +
 +P="t"
 +#P="N"
 +
 +#echo "SHOW STATUS like '%wsrep%';" | mysql -${P}
 +
 +echo "
 +SHOW STATUS LIKE 'wsrep_cluster_size';
 +SHOW STATUS LIKE 'wsrep_incoming_addresses';
 +SHOW STATUS LIKE 'wsrep_connected';
 +SHOW STATUS LIKE 'wsrep_last_committed';
 +SHOW STATUS LIKE 'wsrep_local_state_comment';
 +SHOW VARIABLES LIKE 'max_connection%';
 +SHOW FULL PROCESSLIST;
 +" | mysql -${P}
 +</file>
 +
 +  > echo "SHOW STATUS LIKE 'wsrep_cluster_size';SHOW STATUS LIKE 'wsrep_incoming_addresses';SHOW STATUS LIKE 'wsrep_connected';SHOW STATUS LIKE 'wsrep_last_committed';SHOW STATUS LIKE 'wsrep_local_state_comment';" | mysql -t
 +  +--------------------+-------+
 +  | Variable_name      | Value |
 +  +--------------------+-------+
 +  | wsrep_cluster_size | 3     |
 +  +--------------------+-------+
 +  +--------------------------+----------------------------------------------------+
 +  | Variable_name            | Value                                              |
 +  +--------------------------+----------------------------------------------------+
 +  | wsrep_incoming_addresses | 10.30.3.153:3306,10.30.2.190:3306,10.30.2.199:3306 |
 +  +--------------------------+----------------------------------------------------+
 +  +-----------------+-------+
 +  | Variable_name   | Value |
 +  +-----------------+-------+
 +  | wsrep_connected | ON    |
 +  +-----------------+-------+
 +  +----------------------+-------+
 +  | Variable_name        | Value |
 +  +----------------------+-------+
 +  | wsrep_last_committed | 8628  |
 +  +----------------------+-------+
 +  +---------------------------+--------+
 +  | Variable_name             | Value  |
 +  +---------------------------+--------+
 +  | wsrep_local_state_comment | Synced |
 +  +---------------------------+--------+
 +
 +Anzahl der Spalten und der Zeilen jeder einzelnen Tabelle ausgeben:
 +  > mysqlshow --count Datenbank_X
 +  Database: Datenbank_X
 +  +---------------------+----------+------------+
 +  |       Tables        | Columns  | Total Rows |
 +  +---------------------+----------+------------+
 +
 +weitere Infos über die einzelnen Tabellen aus der DB ausgeben:
 +  > mysqlshow -i Datenbank_X
 +  Database: Datenbank_X
 +  +---------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+--------------------------------+
 +  | Name                | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment                        |
 +  +---------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+--------------------------------+
 +
 +
 +===== DB komplett neu initialisieren =====
 +
 +Um ein unerwünschtes DB-System komplett neu zu initialisieren, kann man das folgende Skript verwenden.\\
 +Es müssen nur die beiden eigenen CNF-Dateien bereitgelegt werden:
 +  > vi /root/mysql_conf.d/grundeinstellungen.cnf
 +  > vi /root/mysql_conf.d/clustereinstellungen.cnf
 +
 +Dann muss man nur noch wissen, welches "root"-Passwort man verwenden möchte und los gehts.
 +
 +<file>
 +#!/bin/bash
 +
 +#
 +# ein Percona-XTRADB-Cluster-Knoten wird komplett neu reinitialisiert
 +# alle Daten von diesem Knoten werden komplett gelöscht
 +# und sollten sich nach Abschluss der Prozedur aus dem Cluster wieder
 +# replizieren
 +#
 +
 +PERCONAXTRADBCLUSTERSERVER="$(dpkg -l | awk '/percona-xtradb-cluster-server/{print $2}')"
 +if [ -z "${PERCONAXTRADBCLUSTERSERVER}" ] ; then
 +        echo "hier ist kein Percona-XTRADB-Cluster installiert"
 +        exit 0
 +fi
 +
 +service mysql stop
 +sleep 1
 +killall mysqld 2>/dev/null
 +sleep 1
 +killall -9 mysqld 2>/dev/null
 +sleep 1
 +
 +tar czf /var/tmp/etc_mysql.tgz /etc/mysql/
 +dpkg -P "${PERCONAXTRADBCLUSTERSERVER}"
 +rm -fr /etc/mysql/ /var/log/mysql* /usr/lib/mysql/
 +tar xzf /var/tmp/etc_mysql.tgz -C /
 +mv -v /etc/mysql/conf.d/clustereinstellungen.cnf /var/tmp/
 +aptitude install "${PERCONAXTRADBCLUSTERSERVER}"
 +#dpkg-reconfigure "${PERCONAXTRADBCLUSTERSERVER}"
 +
 +#DEBIANSYSMAINTUSR="$(awk '/^user/{print $3}' /etc/mysql/debian.cnf | head -n1)"
 +#DEBIANSYSMAINTPW="$(awk '/^password/{print $3}' /etc/mysql/debian.cnf | head -n1)"
 +#echo "update user set password=password('${DEBIANSYSMAINTPW}') where User='${DEBIANSYSMAINTUSR}';" | mysql mysql
 +#echo "UPDATE user set host='%' WHERE host='$(hostname)' AND user='root';" | mysql mysql
 +#echo "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bodo'@'localhost' IDENTIFIED BY 'qwertz';FLUSH PRIVILEGES;" | mysql
 +echo "SELECT host,user,password FROM user;" | mysql -t mysql
 +
 +mv -v /var/tmp/clustereinstellungen.cnf /etc/mysql/conf.d/
 +service mysql stop
 +sleep 1
 +killall mysqld 2>/dev/null
 +sleep 1
 +killall -9 mysqld 2>/dev/null
 +sleep 2
 +service mysql start
 +
 +echo "SHOW STATUS like '%wsrep%';" | mysql -t
 +echo "SELECT host,user,password FROM user;" | mysql -t mysql
 +</file>
 +