Wissen

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: Start » mysql-mmm
Zuletzt angesehen: • security_key • zfs • ffmpeg • iptables • mysql-dump
mysql-mmm

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
mysql-mmm [2017-07-19 09:20:15] – [Manager / MMM-Monitor] manfredmysql-mmm [2017-07-19 10:12:03] (aktuell) – [Use of uninitialized value $old_state in string ne at /usr/local/lib/perl5/5.18.0/MMM/Monitor/Agent.pm line 42.] manfred
Zeile 1: Zeile 1:
 +====== MySQL-MMM ======
 +
 +**MMM** -> //Multi-Master Replication Manager for MySQL// -> is a set of flexible scripts to perform monitoring/failover and management of MySQL master-master replication configurations (with only one node writable at any time).
 +
 +  * [[http://mysql-mmm.org/doku.php]]
 +    * [[http://mysql-mmm.org/mmm2:guide]]
 +  * //MMM software is deprecated//
 +    * [[https://code.google.com/p/mysql-master-ha/|mysql-master-ha]] //(MHA for MySQL: Master High Availability Manager and tools for MySQL)// -> **''MHA'' ist eine Alternative zu MySQL-MMM**
 +    * [[https://severalnines.com/resources/tutorials/mysql-load-balancing-haproxy-tutorial]] -> **''haproxy'' ist eine Alternative zu MySQL-MMM**
 +      * ''3.1.2. mysqlchk for MySQL Replication''
 +        * [[https://github.com/ashraf-s9s/mysqlchk/blob/master/mysqlchk.mysql]] -> ''/usr/share/cmon/templates/mysqlchk.mysql''
 +    * [[https://mariadb.com/products/mariadb-maxscale]] -> **''MariaDB MaxScale'' ist ein sehr vielseitiger MySQL-Proxy**
 +      * [[https://www.youtube.com/watch?v=YFk31z5pwfc|MariaDB MaxScale]]
 +        * MaxScale kann __noch__ (2015-07-09) kein HA (siehe Video ab Position 45:30), noch wird dafür Zusatzsoftware wie z.B. Pacemaker+Corosync benötigt
 +
 +
 +===== DB-Knoten / MMM-Agent =====
 +
 +
 +==== auf allen Knoten ====
 +
 +diese Benutzer müssen auf allen Datenbanken angelegt werden (das Passwort vom 'replication'-User darf nicht länger als 32 Zeichen sein):
 +  GRANT REPLICATION CLIENT                 ON *.* TO 'mmm_monitor'@'%' IDENTIFIED BY 'monitor_password';
 +  GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'%'   IDENTIFIED BY 'agent_password';
 +  GRANT REPLICATION SLAVE                  ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication_password';
 +
 +MMM-Paket installieren:
 +  > apt install mysql-mmm-agent
 +  > apt install libproc-daemon-perl libalgorithm-diff-perl libdbd-mysql-perl libdbi-perl liblog-log4perl-perl libmailtools-perl liblog-dispatch-perl iproute libnet-arp-perl
 +  > apt install libproc-daemon-perl libalgorithm-diff-perl libdbd-mysql-perl libnet0-dev
 +
 +In dem Paket ''mysql-mmm-agent'' ist die NetzMaske für die virtuellen IPs fest im Kode auf ''255.255.255.255'' eingetargen.
 +Benötigt man für die virtuellen IPs beispielsweise eine NetzMaske von ''255.255.0.0'', dann muß auf allen Knoten ein Perl-Modul angepasst werden.\\
 +Siehe Zeilen ''70/71'' und ''107/108'':
 +  > vi /usr/share/perl5/MMM/Agent/Helpers/Network.pm
 +  ...
 +   69         if ($OSNAME eq 'linux') {
 +   70                 #$output = `/sbin/ip addr add $ip/32 dev $if`;
 +   71                 $output = `/sbin/ip addr add $ip/16 dev $if`;
 +   72                 _exit_error("Could not configure ip $ip on interface $if: $output") if ($? >> 8 == 255);
 +   73         }
 +  ...
 +  106         if ($OSNAME eq 'linux') {
 +  107                 #$output = `/sbin/ip addr del $ip/32 dev $if`;
 +  108                 $output = `/sbin/ip addr del $ip/16 dev $if`;
 +  109                 _exit_error("Could not remove ip $ip from interface $if: $output") if ($? >> 8 == 255);
 +  110         }
 +  ...
 +
 +
 +  > vi /etc/default/mysql-mmm-agent
 +  ...
 +  ENABLED=1
 +  ...
 +
 +die ''/etc/mysql-mmm/mmm_common.conf'' muss auf allen Systemen (MMM+DB-Knoten) gleich sein,
 +sie wird auf dem ''MMM'' (siehe unten) konfiguriert und dann auf alle Cluster-Knoten verteilt:
 +  [root@mmm ~]# scp /etc/mysql-mmm/mmm_common.conf db01:/etc/mysql-mmm/
 +  [root@mmm ~]# scp /etc/mysql-mmm/mmm_common.conf db02:/etc/mysql-mmm/
 +  [root@mmm ~]# scp /etc/mysql-mmm/mmm_common.conf db03:/etc/mysql-mmm/
 +
 +  > chmod 0600 /etc/mysql-mmm/mmm_*
 +
 +auf jedem Knoten muss hier der Hostname eingetragen werden:
 +  > vi /etc/mysql-mmm/mmm_agent.conf
 +  include mmm_common.conf
 +  this db01
 +
 +  > service mysql-mmm-agent start
 +
 +
 +==== auf db01 (1. Master) ====
 +
 +zum 2. Master:
 +  > echo "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='db02', MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='replication_password', MASTER_AUTO_POSITION=1, MASTER_SSL=0; START SLAVE;" | mysql
 +
 +==== auf db02 (2. Master) ====
 +
 +zum 1. Master:
 +  > echo "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='db01', MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='replication_password', MASTER_AUTO_POSITION=1, MASTER_SSL=0; START SLAVE;" | mysql
 +
 +==== auf db03 (Reader) ====
 +
 +Leider kann MMM in der Version 2.2.1 noch nicht mit "''CHANNEL''" umgehen, deshalb muss man sich beim letzten SLAVE für einen der beiden MASTER entscheiden!
 +
 +__entweder__ zum ''1. Master'':
 +  > echo "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='db01', MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='replication_password', MASTER_AUTO_POSITION=1, MASTER_SSL=0; START SLAVE;" | mysql
 +
 +__oder__ zum ''2. Master'':
 +  > echo "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='db02', MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='replication_password', MASTER_AUTO_POSITION=1, MASTER_SSL=0; START SLAVE;" | mysql
 +
 +
 +===== Manager / MMM-Monitor =====
 +
 +MMM-Paket installieren:
 +  > apt install mysql-mmm-monitor
 +  > find /etc/mysql-mmm/
 +  /etc/mysql-mmm/
 +  /etc/mysql-mmm/mmm_mon.conf
 +  /etc/mysql-mmm/mmm_common.conf
 +
 +  > apt install libproc-daemon-perl libalgorithm-diff-perl libdbd-mysql-perl libdbi-perl liblog-log4perl-perl libmailtools-perl liblog-dispatch-perl libclass-singleton-perl
 +  > apt install libproc-daemon-perl libalgorithm-diff-perl libdbd-mysql-perl libnet0-dev
 +
 +
 +Wichtig zu wissen ist, dass die ''/etc/mysql-mmm/mmm_common.conf'' am Anfang von ''/etc/mysql-mmm/mmm_mon.conf'' importiert wird.
 +
 +  > useradd -c "MMM Script owner" -s /sbin/nologin mmmd
 +
 +
 +=== Use of uninitialized value $old_state in string ne at /usr/local/lib/perl5/5.18.0/MMM/Monitor/Agent.pm line 42. ===
 +
 +**Dieser Fehler besteht bereits seit langer Zeit in MMM und wird offensichtlich nicht behoben.**
 +
 +  * [[https://answers.launchpad.net/mysql-mmm/+question/233645]]
 +    * MMM-Alternative => [[https://code.google.com/p/mysql-master-ha/|mysql-master-ha]]
 +
 +<file bash>
 +Daniel Black (daniel-black) said on 2014-10-15: #4
 +
 +I added the following code to /usr/share/perl5/MMM/Monitor/Agent.pm on line 41.
 +
 +if (! defined($old_state)) { $old_state = 'certinally not new_state'; }
 +
 +Looking forward to migrating off mmm to corosync/pacemaker.
 +</file>
 +
 +Den richtigen Ort der Datei findet man so:
 +  > dpkg -L mysql-mmm-monitor | fgrep Agent.pm
 +  /usr/share/perl5/MMM/Monitor/Agent.pm
 +
 +
 +==== MMM konfigurieren ====
 +
 +  * [[http://mysql-mmm.org/mmm2:guide#configure_mmm]]
 +
 +Bedeutung der TAGs in den Konfigurationsdateien:
 +  * ''cluster_interface'' (in der ''/etc/mysql-mmm/mmm_common.conf'') - hier muss der Name der Netzwerkkarte angegeben werden, an das die virtuellen (Cluster-) IPs gebunden werden sollen
 +  * ''<role writer>'' (in der ''/etc/mysql-mmm/mmm_common.conf'') - hier werden die Datenbanken aufgeführt, in die geschrieben werden darf, dafür wird hier eine virtuelle (Cluster-) IP angegeben
 +    * **Master**
 +  * ''<role reader>'' (in der ''/etc/mysql-mmm/mmm_common.conf'') - hier werden die Datenbanken aufgeführt, aus denen gelesen werden soll, zB. um das Backup zu erstellen, dafür wird hier eine virtuelle (Cluster-) IP angegeben
 +    * **Slaves**
 +    * es können zusätzlich auch die **Master** angegeben werden
 +  * ''ping_ips'' (in der ''/etc/mysql-mmm/mmm_mon.conf'') - hier werden die IPs aufgeführt, über die der Status der Datenbank ermittelt werden soll (bei mehreren Netzen, das //Datenbank-Netz//)
 +    * **Master** + **Slaves**
 +
 +dort stehen Passwörter drin, deshalb dürfen diese Dateien nicht von jedem gelesen werden dürfen:
 +  > chmod 0600 /etc/mysql-mmm/mmm_*
 +
 +<file bash /etc/mysql-mmm/mmm_common.conf>
 +active_master_role                              writer
 +
 +<host default>
 +        cluster_interface                       br-db
 +
 +        pid_path                                /var/run/mmm_agentd.pid
 +        bin_path                                /usr/lib/mysql-mmm/
 +
 +        replication_user                        replication
 +        replication_password                    replication_password
 +
 +        agent_user                              mmm_agent
 +        agent_password                          agent_password
 +</host>
 +
 +<host db01>
 +        ip                                      192.168.0.30
 +        mode                                    master
 +        peer                                    db02
 +</host>
 +
 +<host db02>
 +        ip                                      192.168.0.30
 +        mode                                    master
 +        peer                                    db01
 +</host>
 +
 +<host db03>
 +        ip                                      192.168.0.30
 +        mode                                    slave
 +</host>
 +
 +<role writer>
 +        hosts                                   db01, db02
 +        ips                                     192.168.3.100
 +        mode                                    exclusive
 +</role>
 +
 +<role reader>
 +        hosts                                   db01, db02, db03
 +        ips                                     192.168.3.200
 +        mode                                    exclusive
 +        prefer                                  db03
 +</role>
 +
 +debug                                           0
 +</file>
 +
 +<file bash /etc/mysql-mmm/mmm_mon.conf>
 +include mmm_common.conf
 +
 +<monitor>
 +        ip                                      127.0.0.1
 +        pid_path                                /var/run/mmm_mond.pid
 +        bin_path                                /usr/lib/mysql-mmm/
 +        status_path                             /var/lib/misc/mmm_mond.status
 +        ping_ips                                192.168.3.11, 192.168.3.12, 192.168.3.13
 +</monitor>
 +
 +<host default>
 +        monitor_user                            mmm_monitor
 +        monitor_password                        monitor_password
 +</host>
 +
 +debug                                           0
 +</file>
 +
 +<file bash /etc/mysql-mmm/mmm_agent.conf>
 +include mmm_common.conf
 +this db01
 +</file>
 +
 +
 +==== MMM starten ====
 +
 +
 +=== Die Aktivierung ===
 +
 +hier wird der MMM-Monitor aktiviert:
 +  > vi /etc/default/mysql-mmm-monitor
 +  ...
 +  ENABLED=1
 +  ...
 +
 +
 +=== Der Start ===
 +
 +  > /etc/init.d/mysql-mmm-monitor start
 +  > /etc/init.d/mysql-mmm-monitor status
 +   * mmm_mond is running
 +
 +  > mmm_control show
 +    db01(192.168.0.30) master/ADMIN_OFFLINE. Roles: 
 +    db02(192.168.0.31) master/ADMIN_OFFLINE. Roles: 
 +    db03(192.168.0.33) slave/ADMIN_OFFLINE. Roles: 
 +  # Role reader has tcdb03 configured as it's preferred host.
 +
 +  > mmm_control set_online db01
 +  OK: State of 'db01' changed to ONLINE. Now you can wait some time and check its new roles!
 +  
 +  > mmm_control set_online db02
 +  OK: State of 'db02' changed to ONLINE. Now you can wait some time and check its new roles!
 +  
 +  > mmm_control set_online db03
 +  OK: State of 'db03' changed to ONLINE. Now you can wait some time and check its new roles!
 +
 +  > mmm_control show
 +    tcdb01(192.168.0.30) master/ONLINE. Roles: writer(192.168.0.100)
 +    tcdb02(192.168.0.31) master/ONLINE. Roles: 
 +    tcdb03(192.168.0.33) slave/ONLINE. Roles: reader(192.168.0.200)
 +  # Role reader is assigned to it's preferred host tcdb03.
 +
 +  > mmm_control checks
 +  db03  ping         [last change: 2016/04/27 12:56:15]  OK
 +  db03  mysql        [last change: 2016/04/27 12:56:15]  OK
 +  db03  rep_threads  [last change: 2016/04/27 12:56:15]  OK
 +  db03  rep_backlog  [last change: 2016/04/27 12:56:15]  OK: Backlog is null
 +  db02  ping         [last change: 2016/04/27 12:56:15]  OK
 +  db02  mysql        [last change: 2016/04/27 12:56:15]  OK
 +  db02  rep_threads  [last change: 2016/04/27 12:56:15]  OK
 +  db02  rep_backlog  [last change: 2016/04/27 12:56:15]  OK: Backlog is null
 +  db01  ping         [last change: 2016/04/27 12:56:15]  OK
 +  db01  mysql        [last change: 2016/04/27 12:56:15]  OK
 +  db01  rep_threads  [last change: 2016/04/27 12:56:15]  OK
 +  db01  rep_backlog  [last change: 2016/04/27 12:56:15]  OK: Backlog is null
 +
 +die virtuelle (Cluster-IP) auf den anderen Master zu verschieben:
 +  > mmm_control move_role writer db02
 +
 +[[http://mysql-mmm.org/mysql-mmm.html]]
 +
 +
 +=== Der Monitor ===
 +
 +  > watch 'mmm_control show;mmm_control checks'
 +<file>
 +Alle 2,0s: mmm_control show;mmm_control checks                                                            Wed Apr 27 13:13:49 2016
 +
 +  db01(192.168.0.30) master/ONLINE. Roles: writer(10.32.0.216)
 +  db02(192.168.0.31) master/ONLINE. Roles:
 +  db03(192.168.0.33) slave/ONLINE. Roles: reader(10.32.0.241)
 +# Role reader is assigned to it's preferred host tcdb03.
 +
 +db03  ping         [last change: 2016/04/27 12:56:15]  OK
 +db03  mysql        [last change: 2016/04/27 12:56:15]  OK
 +db03  rep_threads  [last change: 2016/04/27 13:09:08]  OK
 +db03  rep_backlog  [last change: 2016/04/27 12:56:15]  OK: Backlog is null
 +db02  ping         [last change: 2016/04/27 12:56:15]  OK
 +db02  mysql        [last change: 2016/04/27 12:56:15]  OK
 +db02  rep_threads  [last change: 2016/04/27 13:12:58]  OK
 +db02  rep_backlog  [last change: 2016/04/27 12:56:15]  OK: Backlog is null
 +db01  ping         [last change: 2016/04/27 12:56:15]  OK
 +db01  mysql        [last change: 2016/04/27 12:56:15]  OK
 +db01  rep_threads  [last change: 2016/04/27 13:09:08]  OK
 +db01  rep_backlog  [last change: 2016/04/27 12:56:15]  OK: Backlog is null
 +</file>
 +
 +
 +=== manuelle Verschiebung der virtuellen IP ===
 +
 +  > mmm_control move_role writer db01
 +  > mmm_control move_role reader db03
 +
 +
 +==== MMM mit mehreren Clustern ====
 +
 +für jeden Cluster müssen die beiden Konfigurationsdateien angelegt werden:
 +  > find /etc/mysql-mmm/
 +  /etc/mysql-mmm/
 +  /etc/mysql-mmm/mmm_mon_cl01.conf
 +  /etc/mysql-mmm/mmm_common_cl01.conf
 +  /etc/mysql-mmm/mmm_mon_cl02.conf
 +  /etc/mysql-mmm/mmm_common_cl02.conf
 +
 +bei mehreren Clustern muss man den Cluster-Namen mit angeben:
 +  > mmm_control @cl01 show
 +  
 +  > mmm_control @cl01 set_online db01
 +  > mmm_control @cl01 set_online db02
 +  > mmm_control @cl01 set_online db03
 +  
 +  > mmm_control @cl02 show
 +  
 +  > mmm_control @cl02 set_online db01
 +  > mmm_control @cl02 set_online db02
 +  > mmm_control @cl02 set_online db03
 +
 +
 +===== troubleshoot =====
 +
 +[[http://www.planetlarg.net/add-customer-services/prepare-mysql-cluster/complete-mysql-mmm#tro]]
 +