MariaDB

PHP/MySQL med failover

• Alle der udvikler PHP til info SKAL læse
  • MySQL native driver
  • MySQL replication and load balancing plugin
  • http://www.slideshare.net/dotmanila/highly-available-mysqlphp-applications-with-mysqlnd - slide 58+ - især slide 70 !!!
  • http://www.slideshare.net/nixnutz/load-mysq-clusterin-balancing-peclmysqlndms-14

Monitorering

Galera SYNC

• https://github.com/fridim/nagios-plugin-check_galera_cluster
• Læs også Galera Status Variables

Følgende targets skal sættes i nagios for alle 3 nodes

• Galera cync
• Intel SSD
• SW Raid
• MySQL connection
• CPU forbrug
• free space:
  • root partition
  • /mnt/data

Ting at være obs på

• LOAD DATA INFILE - forventer lokale filer
  • LOAD DATA LOCAL INFILE er implicit IGNORE, så der kommer ingen fejl hvis at der er fejl i datafilen
• auto_increment tabeller laver ikke længere nummerserien som direkte fortløbende (1,2,3) men springer derimod i numrene (3,7,10)
  • genereringen af SSCC stregkoder samt sms koder forudsætter direkte sekvens - disse skal kodes om.
  • læs mere...
• Galera Limitations Alle skal Læse denne (vær især obs på "Transaction Size" sektionen)

Kilde

Galera

• https://mariadb.org/installing-mariadb-galera-cluster-on-debian-ubuntu/
• https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluster/

husk at sætte

wsrep_node_name="node-name"
wsrep_on=ON

den sidste er yderst vigtigt for at galera vil starte

Links

• Percona Toolkit
  • Generic:
    • pt-heartbeat - Monitor MySQL replication delay.
    • pt-index-usage - Read queries from a log and analyze how they use indexes.
    • pt-variable-advisor - Analyze MySQL variables and advise on possible problems
    • pt-config-diff - Diff MySQL configuration files and server variables.
  • Migration:
    • pt-upgrade - Verify that query results are identical on different servers.
    • pt-table-checksum
    • pt-table-sync - Synchronize MySQL table data efficiently.

• Percona Playback

• ClusterControl

Checksum

Note: Nuværende slave data er bygget op fra mysqldump - som skipper dato stemplede tabeller, så når de er fraværende vil pt-table-checksum brokke sig

Gennemført på

• test
• master
• logs

06-12T21:36:31 Skipping table logs.morgen_rapport because on the master it would be checksummed in one chunk but on these replicas it has too many rows:
  139820 rows on info-backup
  142723 rows on MariaDB-01
The current chunk size limit is 137222 rows (chunk size=68611 * chunk size limit=2.0).

• fulddaeking
• produktion_status

06-12T22:08:55 Skipping table produktion_status.30-5lossalgnyture_tan_frem30052013 because it has problems on these replicas:
Table produktion_status.30-5lossalgnyture_tan_frem30052013 does not exist on replica info-backup
Table produktion_status.30-5lossalgnyture_tan_frem30052013 does not exist on replica MariaDB-01
06-12T22:13:19      0      0 10741793     144       0 260.846 produktion_status.aflo
06-12T22:13:25 Skipping chunk 1 of produktion_status.afregning because MySQL used only 7 bytes of the PRIMARY index instead of 134.  See the --[no]check-plan documentation for more information.

Centos/RHEL

https://github.com/Tunts/bakery/wiki/Setting-up-MariaDB-10.1---Galera-Cluster-on-Centos-7

https://www.percona.com/doc/percona-xtrabackup/LATEST/installation/yum_repo.html

"Due to a bug in socat on CentOS 7, the xtrabackup-v2 method does not work. This can be solved by installing an older version of socat or by using the rsync method for wsrep_sst_method"

remember

• (define your cluster as a set of hostnames and NOT as a list of IP's) ?
• If using xtrabackup transfer
  • socat in centos 7/rehl 7 doesn't work with SST - so install anothter one
  • to set wsrep_sst_method=xtrabackup-v2
  • and set wsrep_sst_auth="user:secret" [1]
  • and create the user
    • mysql@percona1> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cret';
    • mysql@percona1> GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
    • mysql@percona1> FLUSH PRIVILEGES;
• disable selinux !!!!
• disable host firewall totally
  • systemctl stop firewalld
  • systemctl disable firewalld