1

mybdd:~# CREATE USER 'haproxy'@'192.168.1.10';

1

# tail /etc/haproxy/haproxy.cfg

listen galera 192.168.1.10:3306
     balance source
     mode tcp
     option tcpka
     option mysql-check user haproxy
     server node1 192.168.1.1:3306 check weight 1
     server node2 192.168.1.2:3306 check weight 1
     server node2 192.168.1.3:3306 check weight 1

http://php.net/manual/en/mysqlnd-ms.supportedclusters.php ?

la bonne doc

Cluster Galera

sources.list

https://downloads.mariadb.org/mariadb/repositories/#mirror=urbach&distro=Debian

Bootstrapping a new cluster

1

$ mysqld --wsrep-new-cluster &

Adding node to a cluster

1

$ mysqld --wsrep_cluster_address=gcomm://192.168.0.1  # DNS names work as well

Cluster state

1

mysql> show global status like '%wsrep%';

Cluster conf

[mysqld]

#mysql settings
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

#galera settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name=”my_wsrep_cluster”
wsrep_cluster_address=”gcomm://172.16.8.5,172.16.8.6,172.16.8.4”
wsrep_sst_method=rsync

more conf

• choose your donor
  wsrep_sst_donor=”10.0.100.10,10.0.100.8”

• obsolete query cache was disable prior to “5.5.40-galera”, “10.0.14-galera” and “10.1.2”.
  query_cache_size=0
  query_cache_type=0

• (http://www.ovaistariq.net/597/understanding-innodb-transaction-isolation-levels/)
  MariaDB> select @@global.tx_isolation;
  transaction-isolation = REPEATABLE-READ

• (https://mariadb.com/kb/en/mariadb/wsrep_provider_options/)
  wsrep_provider_options =”gcs.fc_limit=256 ; gcache.size=2G”

• (https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_causal_reads) (default OFF)
  wsrep_causal_reads = OFF

• (https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_slave_threads)
  wsrep_slave_threads = 8

• (https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_sst_method)
  wsrep_sst_auth = debian-sys-maint:C1WXqAscHm9mTi8r
  wsrep_sst_method = xtrabackup-v2

Doc

MariaDB

Troubleshooting

• En cas de :

/usr//bin/wsrep_sst_rsync: 269: /usr//bin/wsrep_sst_rsync: [[: not found

remplacer /bin/sh par /bin/bash dans le shebang

• Si le start timeout à cause d’une sync trop long

ajouter TimeoutStartSec=0 au [Service] systemd

• Si tous les noeuds tombent en même temps.

~# mysqld –wsrep-cluster-address=”gcomm://“

• Si aucun des noeud ne veut boostrap starter :

dans
/var/lib/mysql/grastate.dat

passer “safe_to_bootstrap: 0” à 1

un outil percona

Boot en “single mode”

1
2

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &

Reset pass

1

mysql> use mysql; update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root'; flush privileges;

Depuis mysql 5.6 on peut multi threader la repli

Nombre de thread

1

slave_parallel_workers

Mémoire par thread

1

slave_pending_jobs_size_max

defaut 16M
/!\ The value for this option must not be less than the master’s value for max_allowed_packet

Ignore foreign key

1
2
3

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;