Benutzer-Werkzeuge

Webseiten-Werkzeuge

A PCRE internal error occured. This might be caused by a faulty plugin

mysql_5.7_-_server_mit_gtid

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

mysql_5.7_-_server_mit_gtid.txt · Zuletzt geändert: 2018/10/19 17:37 von manfred