====== 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