Skip to content

GALERA - XtraDB cluster

Musime mit aspon 3 stroje, ktere pokud je to mozne maji vuci sobe plny pristup.

Instalace

Dale pokud pouzijeme mariadb jako na easy, tak ta je na galeru pripravena. Jinak by se musela pouzit specificka verze mysql.

Na vsechny 3 stroje se tedy nainstaluje mariadb (z ansible). Nyni musime na kazdy stroj pridat konfiguraci, ktera dany stroj identifikuje a odkazuje na dalsi stroje.

pro db1 je to soubor /etc/mysql/mariadb.conf.d/51-galera.cnf zde jsou pridane komntare

[mysqld]
# zapneme podporu pro galeru, zde se da jednoduse vypnout, pokud by z toho mela byt normalni mysql
wsrep_on=1
# knihovna, musi existovat
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# volby
wsrep_provider_options="gcache.size=2G;"
# zde jsou uvedeny ip adresy a port 4567 vcech uzlu, my mame 3, tedy mame 3x ipadresa:port
wsrep_cluster_address=gcomm://136.243.76.209:4567,148.251.198.2:4567,136.243.77.80:4567
# nazev celeho clusteru, musi byt shodny pro vsechny stroje
wsrep_cluster_name='ayesa_easy_cluster'
# ip tohoto stroje-uzlu
wsrep_node_incoming_address='136.243.76.209'
#  nazev tohoto stroje-uzlu
wsrep_node_name='db1.de.easy2.cloud'
# od koho budeme brat data pri nasem vypadku, obvykle zde jsou zbyle stroje
wsrep-sst-donor=db2.de.easy2.cloud,db3.de.easy2.cloud
# a zbylee parametry, shodne pro vsechny stroje-uzly
wsrep_sst_method=rsync
#wsrep_sst_auth=cluster_sync:M2mKWNQMEs
wsrep_sst_auth=root:6shqVb3mDgZVZZIo9eM4oaXsB3c4ci06
wsrep_log_conflicts=1
binlog_format=ROW

pro db2 je to soubor /etc/mysql/mariadb.conf.d/51-galera.cnf

[mysqld]
wsrep_on=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=2G;"
wsrep_cluster_address=gcomm://136.243.76.209:4567,148.251.198.2:4567,136.243.77.80:4567
wsrep_cluster_name='ayesa_easy_cluster'
wsrep_node_incoming_address='148.251.198.2'
wsrep_node_name='db2.de.easy2.cloud'
wsrep_sst_method=rsync
wsrep-sst-donor=db1.de.easy2.cloud,db3.de.easy2.cloud
#wsrep_sst_auth=cluster_sync:M2mKWNQMEs
wsrep_sst_auth=root:RXSOUbYuqUTS6nBKL5RKAmNQVgaSgJQy
wsrep_log_conflicts=1
binlog_format=ROW

pro db3 je to soubor /etc/mysql/mariadb.conf.d/51-galera.cnf

[mysqld]
wsrep_on=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=2G;"
wsrep_cluster_address=gcomm://136.243.76.209:4567,148.251.198.2:4567,136.243.77.80:4567
wsrep_cluster_name='ayesa_easy_cluster'
wsrep_node_incoming_address='136.243.77.80'
wsrep_node_name='db3.de.easy2.cloud'
#wsrep_sst_method=xtrabackup-v2
wsrep_sst_method=rsync
wsrep-sst-donor=db1.de.easy2.cloud,db1.de.easy2.cloud
wsrep_sst_auth=root:Ur9M9xGSzGF6bv42UPfMGQDNWsWFvr8Q
wsrep_log_conflicts=1
binlog_format=ROW

Start:

Prvni uzel se staruje odlisne

galera_new_cluster

Ostatni uzly jiz normalne startujeme mysql

tento prikaz nam rika, kolik mame uzlu, po kazdem spusteni musi ukazat prirustek 

mysql -e"SHOW STATUS LIKE 'wsrep_cluster_size';"

PROXYSQL

Slouzi jako chytrejsi HA proxy pro databazi, ktera rozumi provozu a muze diky tomu napriklad rozkladat zatez. Nebo routovat pozadavky podle jejich typu.

1. Nainstalujeme proxysql

lze postupovat podle https://proxysql.com/documentation/installing-proxysql/ 

apt-get install -y lsb-release
wget -O - 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | apt-key add -
echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.1.x/$(lsb_release -sc)/ ./ \
| tee /etc/apt/sources.list.d/proxysql.list


apt-get update
apt-get install proxysql OR apt-get install proxysql=version

2. Upravime systemd sluzbu

V soucasne verzi je potreba v proxysql.service zakomentovat radky, aby se dalo dat start a stop (tedy presun do /etc/systemd/system/proxysql.service). V dalsich verzi to snad nebude potreba

[Service]
Type=forking
RuntimeDirectory=proxysql
#PermissionsStartOnly=true
#ExecStartPre=/usr/bin/mkdir -p /var/run/proxysql /var/run/proxysql
#ExecStartPre=/usr/bin/chown -R proxysql: /var/run/proxysql/
ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS
PIDFile=/var/lib/proxysql/proxysql.pid
#StandardError=null  # all output is in stderr
SyslogIdentifier=proxysql
Restart=no
User=proxysql
Group=proxysql
#PermissionsStartOnly=true
#UMask=0007
#LimitNOFILE=102400
#LimitCORE=1073741824
#ProtectHome=yes
#NoNewPrivileges=true

3. Pridame DB uzly

Jednotlive uzly galery, oznacene jako GALERA1,GALERA2,GALERA3 na portu 3306. GALERA1 bude zaroven mroonga server.

cat servers.txt # hostgroup s id 1 obsluhuje defaultne bezne dotazy, 2 mame pro mroongu
insert into mysql_servers (hostgroup_id,hostname,port) values (1,"GALERA1",3306);
insert into mysql_servers (hostgroup_id,hostname,port) values (1,"GALERA2",3306);
insert into mysql_servers (hostgroup_id,hostname,port) values (1,"GALERA3",3306);
insert into mysql_servers (hostgroup_id,hostname,port) values (2,"GALERA1",3306);
LOAD MYSQL SERVERS TO RUNTIME; ### DULEZITE, NASTAVENI NENI JINAK PERZISTENTNI (per login session nebo per beh) ###
SAVE MYSQL SERVERS TO DISK;

mysql -u admin -padmin -h 127.0.0.1 -P6032 < servers.txt   ### konfigurace pres vlastni/konfiguracni db na jinem portu

4. Zalozime uzivatele s jejich hesly

cat users.txt
insert into  mysql_users (username,password,default_hostgroup) values ('root','LDaSzJjgx66mRi0V7oAtesoCKZ8baXsT',1);
....
atd
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
mysql -u admin -padmin -h 127.0.0.1 -P6032 < users.txt

5. Mroonga

dotazy na mroongu musime nasmerovat na zvoleny server GALERA1. To se da udelat bud vyctem prikazu na fulltext, nebo vyctem tabulek type=mroonga

cat rules.txt
INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'375813.dev2.easysoftware.com','(from|into|update|into table) easy_fts_targets',2,1)
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
mysql -u admin -padmin -h 127.0.0.1 -P6032 < rules.txt