=====================

Let me say NO, but nothing really bad just annoying and thanks to Oli (always great) less annoying then how it could be.

Anyhow, I had to put all up in a RED HAT box on EC2.

So I start to take a look here an there to see what is the best way to do it, I also take the configurator from Severalnines, but for the moment I am not going to use it,

and in any case I never use it for the my.cnf parameters.

But what was the scope of all this?

Overview

=================

I have a customer that could be interested in using a scaling MySQL solution, for his business.

Requirements as usual are 100% HA (OK we know this is not possible), scaling in writes as much as possible, geographic distribution.

Data set will be starting from 40G (peanuts) to 6 Tb (well not so peanuts) in 2 years.

More the scalability need to be "out" and "in" so architecture need to be able to scale as we like in relation to the "seasons".

 

What from there?

=====================

Well first of all define the generic architecture that should serve better, then find few good candidates that could fit in from the technological part

do some POCs and take numbers...

So here we are POCs and Numbers but first of all Installation.

As said I need to do that using REDHAT 6 EL so no way to use the pre-compile version, but taking a try will not harm.

Time to test and see how it works.

What I want to have as start:

3 nodes as start

data replicate from to each node

do stress test

download and installation

=====================

Excluding the demo package (that I don't want to use)

Any how I have found the site confuse, what I have to take? the package with ONLY the wsrep patch, or also the galera provider, all none both?

wget http://launchpad.net/codership-mysql/5.5/5.5.15-21.2/+download/mysql-5.5.15-wsrep_21.2-linux-x86_64.tar.gz

I had to download and extract to see that the galera libraries where not in the wsrep version, noting bad having it clear and on the same page could help.

Then I took the file from several nine using the configurator ...

DO I REALLY need it ??

mmm let us see

use http://www.severalnines.com/galera-configurator/map

You will deploy on ec2, using redhat, and x86_64 architecture. You can also go into s9s-galera-xyz/scripts/install/.s9s/config

to make adjustments (but be careful) - if you get it wrong, refresh this page and start over with fresh deployment package.

To install:

Upload your keypair to /root/.ssh/Availng.pem on the cluster-control server

tar xvfz s9s-galera-1.0.0.tar.gz

cd s9s-galera-1.0.0/mysql/scripts/install

./deploy.sh 2>&1 |tee cc.log

A detailed log is also written to $HOME/s9s_deploy.log

As said I decide not to use it for the moment.

My directory layout will be:

 

+-- bin

+-- etc
+-- galera -> /opt/galera_templates/galera¦   +-- bi

¦   +-- lib64

¦   +-- shar

+-- games

+-- include
+-- lib
+-- lib64
¦   +-- perl5
+-- libexec
+-- mysql -> /opt/mysql
¦   +-- bi

¦   +-- data

¦   +-- docs

¦   +-- include

¦   +-- lib

¦   +-- man

¦   +-- mysql-test

¦   +-- scripts

¦   +-- share

¦   +-- sql-bench

¦   +-- support-files

+-- sbin

+-- share

¦   +-- applications

¦   +-- info

¦   +-- man

¦   +-- perl5
+-- src

 

Next I had to do the usual thing:

setup MySQL user and group

setup directories

Configuring my.cnf

 

Start Galera

==========

I am in rush (never good), so I did start and configure MySQL, but then when I run it:

111118 17:34:55 [Note] Event Scheduler: Loaded 0 events
111118 17:34:55 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/galera/lib64/galera/libgalera_smm.so'
111118 17:34:55 [ERROR] WSREP: wsrep_load(): dlopen(): libssl.so.6: cannot open shared object file: No such file or directory
111118 17:34:55 [ERROR] WSREP: wsrep_load(/usr/local/galera/lib64/galera/libgalera_smm.so) failed..
111118 17:34:55 [Note] WSREP: wsrep_load(): loading provider library 'none'
111118 17:34:55 [ERROR] Aborting
111118 17:34:55 [Note] WSREP: Service disconnected.
111118 17:34:56 [Note] WSREP: Some threads may fail to exit.
111118 17:34:56  InnoDB: Starting shutdown...
111118 17:34:57  InnoDB: Shutdown completed; log sequence number 1595682
111118 17:34:57 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

Ok Ok I know they said that so no way I need to recompile and install.

As Oli wrote issue is set up all before so let me see for RED HAT:

Take MySQL as for Oli instructions http://www.fromdual.com/building-galera-replication-from-scratch

and patch: wget http://launchpad.net/codership-mysql/5.5/5.5.15-21.2/+download/mysql-5.5.15-wsrep_21.2.patch

apply patch and download the galera replicator

wget http://launchpad.net/galera/1.x/21.1.0/+download/galera-21.1.0-src.tar.gz

 

BEFORE COMPILE install

yum -y install openssl

yum -y install boost-devel

yum -y install openssl-devel.x86_64

yum -y install check-devel.x86_64

 

Then run :

scons

Have a coffee or two ...

cp libgalera_smm.so /opt/mysql/lib/plugin/

cp garb/garbd /opt/mysql/bin/

 

WOW I GOT IT

11122 18:19:53 [Note] WSREP: Flow-control interval: [8, 16]
111122 18:19:53 [Note] WSREP: Restored state OPEN -> JOINED (0)
111122 18:19:53 [Note] WSREP: Member 0 (galera1) synced with group.
111122 18:19:53 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
111122 18:19:53 [Note] WSREP: New cluster view: group UUID: 7bd4ac4b-1560-11e1-0800-19942f7ebcc0, conf# 1: Primary, number of nodes: 1, my index: 0, first seqno: 1, protocol version 1
111122 18:19:53 [Warning] WSREP: wsrep_notify_cmd is not defined, skipping notification.
111122 18:19:53 [Note] WSREP: Assign initial position for certification: 0, protocol version: 1
111122 18:19:53 [Note] WSREP: Synchronized with group, ready for connections
111122 18:19:53 [Warning] WSREP: wsrep_notify_cmd is not defined, skipping notification.

 

 

 

 

 

 

 

 

 

 

mysql> SHOW global STATUS LIKE "wsrep%";
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid | 7bd4ac4b-1560-11e1-0800-19942f7ebcc0 |
| wsrep_protocol_version | 1 |
| wsrep_last_committed | 0 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 135 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced (6) |
| wsrep_cert_index_size | 0 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 7bd4ac4b-1560-11e1-0800-19942f7ebcc0 |
| wsrep_cluster_status | PRIMARY |
| wsrep_connected | ON |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 1.dev(rXXXX) |
| wsrep_ready | ON |
+----------------------------+--------------------------------------+
38 rows IN SET (0.00 sec)
 
 

 

 

As state in the internal text file a note about the LIMITATIONS


1) Currently replication works only with InnoDB storage engine. Any writes to
tables of other types, including system (mysql.*) tables are not replicated.
However, DDL statements are replicated in statement level, and changes
to mysql.* tables will get replicated that way.
So, you can safely issue: CREATE USER...,
but issuing: INSERT INTO mysql.user..., will not be replicated.

2) DELETE operation is unsupported on tables without primary keys.
Rows in tables without primary keys may appear in different order on
different nodes. As a result SELECT...LIMIT... may return slightly
different sets.

3) Unsupported queries:
* LOCK/UNLOCK tables is not supported in multimaster configuration.
* lock functions (GET_LOCK(), RELEASE_LOCK()... )

4) Query log cannot be directed to table. If you enable query logging,
you must forward the log to a file:
log_output = FILE
Use general_log and general_log_file to choose query logging and the
log file name

5) Maximum allowed transaction size is defined by wsrep_max_ws_rows and
wsrep_max_ws_size. Anything bigger (e.g. huge LOAD DATA) will be rejected.

6) Due to cluster level optimistic concurrency control, transaction issuing
COMMIT may still be aborted at that stage. There can be two transactions
writing to same rows and committing in separate cluster nodes, and only one
of the them can successfully commit. The failing one will be aborted.
For cluster level aborts, MySQL/galera cluster gives back deadlock error
code (Error: 1213 SQLSTATE: 40001  (ER_LOCK_DEADLOCK)).

7) XA transactions can not be supported due to possible rollback on commit.

8) Currently supported only in master-slave mode without parallel applying
(will be fixed in the nearest release):
* cascading foreign key constraints,
* secondary unique indexes and
* primary varchar indexes with non-binary collation.


Creating the full environment
=======================

Now that I have all up and working, I can finally use my current EC2 as template and add boxes to my cluster.
From here the real Fun will start ...

 

NEXT
====

-> Expanding the cluster
-> Starting my dataset
-> Pushing on throttle and take numbers

 

References

http://www.codership.com/downloads/download-mysqlgalera/%22

http://www.codership.com/wiki/doku.php?id=galera_parameters

http://www.codership.com/products/mysql_galera

http://www.fromdual.com/building-galera-replication-from-scratch

http://codership.blogspot.com/2011/10/mysqlgalera-release-10-replication.html

http://www.severalnines.com/galera-configurator/map


Latest conferences

We have 4604 guests and no members online