Difference between revisions of "MariaDB"
(→Centos/RHEL) |
|||
(31 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
− | + | <!-- | |
== Infrastruktur == | == Infrastruktur == | ||
Line 7: | 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 16: | 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> |
− | |||
== Ting at være obs på == | == Ting at være obs på == | ||
Line 32: | 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] | |
+ | == 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] |
** Generic: | ** Generic: | ||
− | ** [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-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-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-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: | ** Migration: | ||
− | ** [https://www.percona.com/doc/percona-toolkit/2.2/pt-upgrade.html | + | *** [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-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-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] | * [http://severalnines.com/product/clustercontrol/deploy ClusterControl] | ||
Line 80: | Line 102: | ||
</pre> | </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 15:43, 25 February 2018
Contents
PHP/MySQL med failover
- Alle der udvikler PHP til info SKAL læse
Monitorering
Galera SYNC
Følgende targets skal sættes i nagios for alle 3 nodes
- Galera cync
Intel SSDSW RaidMySQL 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)
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:
- Migration:
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