Difference between revisions of "MariaDB"

From HoerupWiki
Jump to: navigation, search
(→‎PHP/MySQL med failover)
(→‎Centos/RHEL)
 
(42 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
=ToDo=
+
<!--
−
 
 
 
== Infrastruktur ==  
 
== Infrastruktur ==  
  
Line 8: Line 7:
 
** Ingen failover link - ingen Spanning Tree
 
** Ingen failover link - ingen Spanning Tree
 
** Risiko for split-brain scenarios ?
 
** Risiko for split-brain scenarios ?
 +
-->
  
 
== PHP/MySQL med failover==
 
== PHP/MySQL med failover==
Line 17: Line 17:
  
 
== Monitorering ==
 
== Monitorering ==
 +
 +
Galera SYNC
 +
* https://github.com/fridim/nagios-plugin-check_galera_cluster
 +
* Læs også [http://galeracluster.com/documentation-webpages/galerastatusvariables.html#wsrep-connected Galera Status Variables]
  
 
Følgende targets skal sættes i nagios for alle 3 nodes
 
Følgende targets skal sættes i nagios for alle 3 nodes
  
 
* Galera cync
 
* Galera cync
−
* Intel SSD
+
* <strike>Intel SSD</strike>
−
* SW Raid
+
* <strike>SW Raid</strike>
−
* MySQL connection
+
* <strike>MySQL connection</strike>
 +
* CPU forbrug
 
* free space:
 
* free space:
−
** root partition
+
** <strike>root partition</strike>
−
** /mnt/data
+
** <strike>/mnt/data</strike>
−
* CPU forbrug
 
  
 
== Ting at være obs på ==
 
== Ting at være obs på ==
Line 33: Line 37:
 
* LOAD DATA INFILE - forventer lokale filer
 
* 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
 
** 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.
 +
** [https://mariadb.org/auto-increments-in-galera/ læs mere...]
 +
* [http://galeracluster.com/documentation-webpages/limitations.html Galera Limitations] Alle skal Læse denne (vær især obs på "Transaction Size" sektionen)
 +
 +
<!--
 +
== Stop Windows Scheduler ==
 +
# Hent [https://technet.microsoft.com/en-us/sysinternals/pxexec.aspx SysInternals psexec]
 +
# start en cmd som SYSTEM brugeren: psexec -i -s cmd
 +
# fra den nye CMD stop scheduler service med: sc stop schedule
 +
# når migrering er på plads, så start scheduler med: sc start schedule
 +
-->
 +
 +
[https://social.technet.microsoft.com/Forums/windowsserver/en-US/b9e5a4c0-073c-42fd-a390-8b2aee407a4f/task-scheduler-service-schedule-cannot-be-stopped-in-windows-2008-r1-?forum=winserversecurity Kilde]
  
−
== Ting der skal undersøges ==
+
== 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==
 
==Links==
−
* https://www.percona.com/doc/percona-toolkit/2.2/index.html
+
* [https://www.percona.com/doc/percona-toolkit/2.2/index.html Percona Toolkit]
−
** [https://www.percona.com/doc/percona-toolkit/2.2/pt-heartbeat.html pt-heartbeat - Monitor MySQL replication delay.]
+
** Generic:
−
** [https://www.percona.com/doc/percona-toolkit/2.2/pt-index-usage.html pt-index-usage - Read queries from a log and analyze how they use indexes.]
+
*** [https://www.percona.com/doc/percona-toolkit/2.2/pt-heartbeat.html pt-heartbeat - Monitor MySQL replication delay.]
 +
*** [https://www.percona.com/doc/percona-toolkit/2.2/pt-index-usage.html pt-index-usage - Read queries from a log and analyze how they use indexes.]
 +
*** [https://www.percona.com/doc/percona-toolkit/2.2/pt-variable-advisor.html pt-variable-advisor - Analyze MySQL variables and advise on possible problems]
 +
*** [https://www.percona.com/doc/percona-toolkit/2.2/pt-config-diff.html pt-config-diff - Diff MySQL configuration files and server variables.]
 +
** Migration:
 +
*** [https://www.percona.com/doc/percona-toolkit/2.2/pt-upgrade.html pt-upgrade - Verify that query results are identical on different servers.]
 +
*** [https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html pt-table-checksum]
 +
*** [https://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html pt-table-sync - Synchronize MySQL table data efficiently.]
 +
 
 +
* [https://www.percona.com/doc/percona-playback/index.html Percona Playback]
 +
 
 +
* [http://severalnines.com/product/clustercontrol/deploy 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
 +
<pre>
 +
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).
 +
 
 +
</pre>
 +
* fulddaeking
 +
* produktion_status
 +
<pre>
 +
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.
 +
 
 +
</pre>
 +
 
 +
==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 <code>wsrep_sst_method=xtrabackup-v2</code>
 +
** and set wsrep_sst_auth="user:secret" [https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/centos_howto.html]
 +
** 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

Latest revision as of 16:43, 25 February 2018


PHP/MySQL med failover

Monitorering

Galera SYNC

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

husk at sætte 

wsrep_node_name="node-name"
wsrep_on=ON

den sidste er yderst vigtigt for at galera vil starte

Links

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
Retrieved from "https://wiki.t-hoerup.dk/index.php?title=MariaDB&oldid=11941"