codership's galera cluster installation and quickstart webinar march 2016
TRANSCRIPT
![Page 1: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/1.jpg)
Galera Cluster InstallationAnd Quick Start
Philip StoevCodership Oy
![Page 2: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/2.jpg)
2
CodershipEstablished 2007 (Galera development started)
3 co-founder engineers:Seppo Jaakola, CEO, Teemu Ollakka, CTO, Alex Yurchenko, architectTeam have developed three MySQL cluster software earlier.
Based on: http://www.dsn.jhu.edu/~yairamir/Yair_phd.pdfhttp://www.inf.usi.ch/faculty/pedone/Paper/199x/These-2090-Pedone.pdf
Product: Galera Cluster for MySQL, open source
Thousands of users in e-commerce, telecom, gaming, insurance, betting healthcare, media, marketing, advertising, travel, universities, software-as-service, Paas, Iaas, etc
Galera Cluster is included and will be included in Linux and OpenStack distros
![Page 3: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/3.jpg)
3
Galera Cluster variants
API
MySQL
Coderhips’s Galera Replication plugin
API
PerconaServer
API
MariaDB
mergemerge
Percona calls Galera Percona XtraDB Cluster
Galera Cluster for MySQL by CodershipMariaDB calls Galera MariaDB Galera Cluster or MariaDB Enterprise Cluster
![Page 4: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/4.jpg)
Agenda• A very quick overview of Galera Cluster• Adding the Galera package repository• Installing packages• Creating a configuration file• Bootstrapping a 3-node cluster• Monitoring
![Page 5: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/5.jpg)
Galera Cluster OverviewSynchronous
– each transaction is immediately replicated on all nodes at commit– no stale slaves
Multi-Master– read from and write to any node– automatic transaction conflict detection
Replication– a copy of the entire dataset is available on all nodes– new nodes can join automatically
For MySQL– based on a modified version of MySQL (5.5, 5.6 with 5.7 coming up)– InnoDB storage engine
![Page 6: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/6.jpg)
And more …• Recovers from node failures within seconds• Data consistency protections
– avoids reading stale data– prevents unsafe data modifications
• WAN support– nodes can be on different continents– end-to-end encryption– optimized communication with least latency
![Page 7: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/7.jpg)
Package Installation (CentOS, RHEL)1. Remove existing MySQL server packages 2. Configure the Galera Cluster repository
Add a .repo file in /etc/yum.repos.d/:[galera]name = Galerabaseurl = http://releases.galeracluster.com/centos/7/x86_64 gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.comgpgcheck = 1
(note distribution name and release number are specified)
3. Install Galera Cluster Packagesyum –y upgrade mysql-wsrep-shared-5.6
yum –y install galera-3 mysql-wsrep-5.6
![Page 8: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/8.jpg)
Preparing the System• Nodes should have identical hardware• Firewall ports to open:
3306, 4567, 4568, 4444• Disable SELinux
– a policy can be created and enabled later
![Page 9: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/9.jpg)
The my.cnf Configuration File• All Galera configuration is in my.cnf• Keep the my.cnf files on all nodes identical• InnoDB settings are important
optimize individual nodes as you normally would:– InnoDB buffer size– InnoDB log file size
• Galera-specific settings:default_storage_engine=InnoDBbinlog_format=ROWinnodb_autoinc_lock_mode=2wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so wsrep_cluster_address="gcomm://node1,node2,node3... "
![Page 10: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/10.jpg)
Bootstrap1. Start first node with –wsrep-new-cluster:
sudo service mysql start --wsrep-new-cluster
2. Confirm a single-node cluster:SHOW STATUS LIKE 'wsrep_%'| wsrep_cluster_status | Primary || wsrep_cluster_size | 1 |
3. Start all other nodes normallysudo service mysql start
4. Confirm nodes have joined:SHOW STATUS LIKE 'wsrep_%'| wsrep_cluster_size | 3 |
![Page 11: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/11.jpg)
Monitoring
Traditional MySQL monitoring methods are used:• SHOW STATUS• SHOW PROCESSLIST• The MySQL error log
– all Galera messages go there• Queries against INFORMATION_SCHEMA
![Page 12: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/12.jpg)
SHOW STATUS• Node health variables:
– wsrep_ready = ON– wsrep_cluster_size– wsrep_cluster_status = Primary– wsrep_local_status = Synced
• Replication health variables:– wsrep_flow_control_paused < 0.05– wsrep_local_bf_aborts + wsrep_local_cert_failures
(do not forget periodic FLUSH STATUS)
![Page 13: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/13.jpg)
SHOW PROCESSLIST• Queries to watch out for:
– long “Query end” phase• replication takes place during that phase
– long-running DDL• under default settings may block cluster
![Page 14: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/14.jpg)
INFORMATION_SCHEMA• Avoid non-InnoDB tables:
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE != 'InnoDB'AND TABLE_SCHEMA NOT IN ('mysql','performance_schema', 'information_schema');
• Avoid tables with no primary key:
SELECT T.TABLE_SCHEMA, T.TABLE_NAME FROM information_schema.tables AS T WHERE (table_schema, table_name)NOT IN (SELECT table_schema, table_name FROM information_schema.table_constraints AS C WHERE T.table_name = C.table_name AND T.table_schema = C.table_schema )AND table_schema NOT IN ('mysql','information_schema','performance_schema');
![Page 15: Codership's galera cluster installation and quickstart webinar march 2016](https://reader036.vdocuments.us/reader036/viewer/2022083117/5883e7351a28ab62358b7a65/html5/thumbnails/15.jpg)
Thank You
Downloads, documentation:
http://www.galeracluster.com
Discussion group: