Wissen

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: Start » mysql_5.7_-_server_mit_gtid
Zuletzt angesehen: • ffmpeg • mysql-dump • zfs • archlinux-installation • iptables • security_key
mysql_5.7_-_server_mit_gtid

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
mysql_5.7_-_server_mit_gtid [2016-10-13 11:20:39] – manfredmysql_5.7_-_server_mit_gtid [2018-10-19 15:37:32] (aktuell) – manfred
Zeile 1: Zeile 1:
 +====== MySQL 5.7 - Server mit GTID ======
 +
 +
 +==== MySQL 5.7 ====
 +
 +
 +=== Ubuntu 16.04.5 LTS - Xenial ===
 +
 +als erstes "Update":
 +  > time (locale-gen ; apt-get autoclean ; dpkg --configure -a ; apt-get update ; aptitude update ; apt update ; apt-get dist-upgrade ; aptitude autoclean ; apt-get autoremove) && echo OK ; dpkg --configure -a ; apt purge $(dpkg -l | sed "s/^rc[ ]*//;s/ .*//" | egrep ^linux-) ; dpkg -l | sed "s/^ii[ ]*//;s/ .*//" | egrep ^linux- ; uname -a
 +
 +...ggf. jetzt ein Reboot durchführen.
 +
 +__Leider kann MySQL auf Ubuntu 16.04 installiert werden, wenn das "''datadir''" ein Mount-Point ist.__
 +  > df -h
 +  Filesystem                    Size  Used Avail Use% Mounted on
 +  ...
 +  /dev/mapper/xenial--vg-mysql   96G  194M   91G   1% /var/lib/mysql
 +  ...
 +
 +work-around:
 +  > umount /var/lib/mysql
 +  > rm -fr /var/lib/mysql
 +  > mkdir /var/lib/mysql
 +  > chown mysql:mysql /var/lib/mysql
 +  > apt install mysql-server
 +  > service mysql stop
 +  > mv /var/lib/mysql /var/lib/data
 +  > mkdir /var/lib/mysql
 +  > chown mysql:mysql /var/lib/mysql
 +  > mount /var/lib/mysql
 +  > mv /var/lib/data /var/lib/mysql/
 +  > chown -R mysql:mysql /var/lib/mysql
 +  > sed -ie 's|^datadir[ \t][ \t]*=[ ]/var/lib/mysql.*|datadir              = /var/lib/mysql/data|' /etc/mysql/mysql.conf.d/mysqld.cnf && rm -f /etc/mysql/mysql.conf.d/mysqld.cnfe
 +  > sed -ie 's|^bind-address[ \t].*|bind-address            = 0.0.0.0|' /etc/mysql/mysql.conf.d/mysqld.cnf && rm -f /etc/mysql/mysql.conf.d/mysqld.cnfe
 +  > dpkg --configure -a
 +  > service mysql start
 +  > service mysql status
 +  > mysqlshow
 +
 +  > echo "SELECT Host,User,plugin,authentication_string,password_last_changed,account_locked FROM user; CREATE USER 'borg' IDENTIFIED BY 'XXXXXXXX'; GRANT REPLICATION CLIENT,RELOAD,REPLICATION SLAVE ON *.* TO 'borg'@'%'; FLUSH PRIVILEGES; SELECT Host,User,plugin,authentication_string,password_last_changed,account_locked FROM user;" | mysql -t mysql
 +  > echo "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='10.10.10.11', MASTER_PORT=3306, MASTER_USER='borg', MASTER_PASSWORD='XXXXXXXX', MASTER_AUTO_POSITION=1 FOR CHANNEL 'dbserver01'; START SLAVE;" | mysql
 +  > echo "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='10.10.10.12', MASTER_PORT=3306, MASTER_USER='borg', MASTER_PASSWORD='XXXXXXXX', MASTER_AUTO_POSITION=1 FOR CHANNEL 'dbserver02'; START SLAVE;" | mysql
 +  > echo "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='10.10.10.13', MASTER_PORT=3306, MASTER_USER='borg', MASTER_PASSWORD='XXXXXXXX', MASTER_AUTO_POSITION=1 FOR CHANNEL 'dbserver03'; START SLAVE;" | mysql
 +
 +
 +==== Csync2 ====
 +
 +siehe auch [[Csync2]]
 +
 +  > apt install csync2
 +  > openssl req -rand /dev/urandom -sha512 -new -x509 -newkey rsa:4096 -nodes -days 7000 -keyout /etc/csync2_ssl_key.pem -keyform PEM -out /etc/csync2_ssl_cert.pem -outform PEM -subj "/C=DE/ST=Bundesland/L=Stadt/O=Organisation/OU=Abteilung/emailAddress=default@domain.de"
 +
 +  > vi /etc/inetd.conf
 +  ...
 +  csync2          stream  tcp     nowait  root    /usr/sbin/csync2        csync2 -i -l
 +
 +  > vi /etc/hosts
 +  ...
 +  10.10.20.11     dbserver01
 +  10.10.20.12     dbserver02
 +  10.10.20.13     dbserver03
 +  ...
 +
 +<file bash /etc/csync2.cfg>
 +group Loadbalancer #group name, we can have multiple groups
 +{   
 +        host dbserver01; #master server
 +        host dbserver02; #slave server
 +        host dbserver03; #slave server
 +
 +        key /etc/csync2_ssl.key;
 +
 +        include /etc/csync2.cfg;
 +        include /etc/hosts.allow;
 +        include /etc/hosts;
 +        include /root/bin/;
 +        include /root/.ssh;
 +        include /root/.my.cnf;
 +
 +        backup-directory /var/backups/csync2;
 +        backup-generations 10;
 +        auto none; #no automatic sync
 +}   
 +</file>
 +
 +
 +==== KeepaliveD ====
 +
 +siehe auch: [[KeepaliveD]]
 +
 +  > echo "net.ipv4.ip_nonlocal_bind = 1" >> /etc/sysctl.conf
 +  > sysctl -p
 +  > apt install keepalived
 +  > update-rc.d keepalived defaults
 +
 +<hidden /etc/keepalived/keepalived.conf>
 +<file bash /etc/keepalived/keepalived.conf>
 +#
 +
 +vrrp_script chk_dienst {
 +   script "/root/bin/check_db.sh $(hostname -s) 3306"   # einfachste Form einen Dienst zu prüfen
 +   interval 2                                           # Alle 2 Sekunden prüfen
 +   weight 2                                             # 2 Punkte hinzufügen wenn OK
 +}
 +
 +vrrp_instance Netz1 {
 +   interface ens1                                       # Zu überwachendes Interface
 +   state EQUAL
 +   virtual_router_id 71                                 # ID der Route
 +   priority 100                                         # 102 - Master, 101 - Backup
 +   virtual_ipaddress {
 +       10.10.20.10                                      # Die virtuelle IP Adresse
 +   }
 +   track_script {
 +       chk_dienst
 +   }
 +}
 +
 +vrrp_instance Netz2 {
 +   interface ens2                                       # Zu überwachendes Interface
 +   state EQUAL
 +   virtual_router_id 72                                 # ID der Route
 +   priority 100                                         # 102 - Master, 101 - Backup
 +   virtual_ipaddress {
 +       10.10.10.10                                      # Die virtuelle IP Adresse
 +   }
 +   track_script {
 +       chk_dienst
 +   }
 +}
 +</file>
 +</hidden>
 +
 +<hidden /root/bin/check_db.sh>
 +<file bash /root/bin/check_db.sh>
 +#!/bin/bash
 + 
 +#------------------------------------------------------------------------------#
 +#
 +# DB-Check
 +#
 +# /root/bin/check_db.sh $(hostname -s) 3306
 +#
 +#------------------------------------------------------------------------------#
 +
 +VERSION="v2018101900"
 + 
 +if [ "x${2}" = x ] ; then
 + echo "${0} [local-hostname] [DB-Port]"
 + echo "${0} dbserver01 3306"
 + exit 1
 +else
 + mysql_host="${1}";
 + mysql_port="${2}";
 +fi
 + 
 + 
 +node_response=$(echo "SHOW GLOBAL VARIABLES LIKE 'hostname';" | mysql --defaults-file=/root/.my.cnf -hlocalhost -P${mysql_port} -N | awk '{ print $2 }');
 + 
 +echo "${mysql_host} ? ${node_response}"
 +if [ "${node_response}" == "${mysql_host}" ]
 +then
 + echo "Hostname matched"
 + exit 0;
 +else
 + echo "Hostname not matched"
 + exit 1;
 +fi
 +</file>
 +</hidden>
 +
 +  > service keepalived restart
 +