Galera not registered with MySQL server

[Steve Barker]

Hi guys,

I'm new to LINUX/MySQL and am trying to get up multi-master replication using Galera with MySQL on LINUX Red Hat EL6. I'm starting with two servers to prove the concept, although I am aware that a minimum of three servers are required in production to avoid split-brain issues.

I've been using the following two articles to help me set this up:

http://opentodo.net/2012/12/mysql-multi-master-replication-with-galera/

http://openionate.blogspot.co.uk/2011/11/galera-clustering-in-mysql.html

I'm at the point where I believe I've downloaded and installed MySQL and Galera from the Galera downloads page and have configured MySQL to use Galera but when I run the command:

show status like 'wsrep%';




...on either box I get:

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

mysql> show status like 'wsrep%';

+--------------------------+----------------------+

| Variable_name            | Value                |

+--------------------------+----------------------+

| wsrep_cluster_conf_id    | XXXX |

| wsrep_cluster_size       | 0                    |

| wsrep_cluster_state_uuid |                      |

| wsrep_cluster_status     | Disconnected         |

| wsrep_connected          | OFF                  |

| wsrep_local_index        | XXXX |

| wsrep_provider_name      |                      |

| wsrep_provider_vendor    |                      |

| wsrep_provider_version   |                      |

| wsrep_ready              | ON                   |

+--------------------------+----------------------+

10 rows in set (0.00 sec)

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

I was expecting to see "| wsrep_provider_name | Galera |" in the output.

Any suggestions on where to start looking to diagnose this much appreciated!

Thanks,

Steve.

[Steve Barker]

P.S. Here are the recent MySQL logs, including the results of a restart. It's strange that Galera isn't mentioned anywhere...?

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

130705 08:38:54 mysqld_safe WSREP: Running position recovery with --log_error=/tmp/tmp.JLfVI1NYa2

130705 08:38:56 mysqld_safe WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1

130705  8:38:56 [Note] WSREP: wsrep_start_position var submitted: '00000000-0000-0000-0000-000000000000:-1'

130705  8:38:56 [Note] Plugin 'FEDERATED' is disabled.

130705  8:38:56 InnoDB: The InnoDB memory heap is disabled

130705  8:38:56 InnoDB: Mutexes and rw_locks use GCC atomic builtins

130705  8:38:56 InnoDB: Compressed tables use zlib 1.2.3

130705  8:38:56 InnoDB: Using Linux native AIO

130705  8:38:56 InnoDB: Initializing buffer pool, size = 128.0M

130705  8:38:56 InnoDB: Completed initialization of buffer pool

130705  8:38:56 InnoDB: highest supported file format is Barracuda.

130705  8:38:56  InnoDB: Waiting for the background threads to start

130705  8:38:57 InnoDB: 1.1.8 started; log sequence number 1595843

130705  8:38:57 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306

130705  8:38:57 [Note]   - '0.0.0.0' resolves to '0.0.0.0';

130705  8:38:57 [Note] Server socket created on IP: '0.0.0.0'.

130705  8:38:57 [Note] Event Scheduler: Loaded 0 events

130705  8:38:57 [Note] WSREP: Read nil XID from storage engines, skipping position init

130705  8:38:57 [Note] WSREP: wsrep_load(): loading provider library 'none'

130705  8:38:57 [Note] /usr/sbin/mysqld: ready for connections.

Version: '5.5.29'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL), wsrep_23.7.3.r3853

130705  9:47:33 [Note] /usr/sbin/mysqld: Normal shutdown

130705  9:47:33 [Note] Event Scheduler: Purging the queue. 0 events

130705  9:47:33  InnoDB: Starting shutdown...

130705  9:47:33  InnoDB: Shutdown completed; log sequence number 1595843

130705  9:47:33 [Note] /usr/sbin/mysqld: Shutdown complete

130705 09:47:33 mysqld_safe mysqld from pid file /var/lib/mysql/XXX.pid ended

130705 09:47:34 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

130705 09:47:34 mysqld_safe WSREP: Running position recovery with --log_error=/tmp/tmp.cPGq8ZLa3J

130705 09:47:35 mysqld_safe WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1

130705  9:47:36 [Note] WSREP: wsrep_start_position var submitted: '00000000-0000-0000-0000-000000000000:-1'

130705  9:47:36 [Note] Plugin 'FEDERATED' is disabled.

130705  9:47:36 InnoDB: The InnoDB memory heap is disabled

130705  9:47:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins

130705  9:47:36 InnoDB: Compressed tables use zlib 1.2.3

130705  9:47:36 InnoDB: Using Linux native AIO

130705  9:47:36 InnoDB: Initializing buffer pool, size = 128.0M

130705  9:47:36 InnoDB: Completed initialization of buffer pool

130705  9:47:36 InnoDB: highest supported file format is Barracuda.

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

[Sam Bashton]

On Mon, Jul 8, 2013 at 10:08 AM, Steve Barker <steve....@pearson.com> wrote:

I'm at the point where I believe I've downloaded and installed MySQL and Galera from the Galera downloads page and have configured MySQL to use Galera but when I run the command:

show status like 'wsrep%';




...on either box I get:

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

mysql> show status like 'wsrep%';

+--------------------------+----------------------+

| Variable_name            | Value                |

+--------------------------+----------------------+

| wsrep_cluster_conf_id    | XXXX |

| wsrep_cluster_size       | 0                    |

| wsrep_cluster_state_uuid |                      |

| wsrep_cluster_status     | Disconnected         |

| wsrep_connected          | OFF                  |

| wsrep_local_index        | XXXX |

| wsrep_provider_name      |                      |

| wsrep_provider_vendor    |                      |

| wsrep_provider_version   |                      |

| wsrep_ready              | ON                   |

+--------------------------+----------------------+

10 rows in set (0.00 sec)

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

I was expecting to see "| wsrep_provider_name | Galera |" in the output.

Any suggestions on where to start looking to diagnose this much appreciated!

Steve,

Are you sure you have the line 'wsrep_provider = /usr/lib64/galera/libgalera_smm.so' in your my.cnf file, and that you have the galera RPM?

The output suggests you have a patched version of MySQL installed, but aren't actually loading the Galera plugin.

Regards,

--

Sam Bashton, Director: Bashton Ltd

Bashton: Business Focussed Open Source

Building highly scalable Linux systems since 2004

www.bashton.com | 0161-424-9600 | DDI 0161-424-9611

[Barker, Steve]

Hi Sam,

Thanks very much for taking a look at this!

The blog I am following tells me to add that line to the file /etc/mysql/conf.d/wsrep.cnf. Here is my Nano output for that file:

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

 GNU nano 2.0.9        File: /etc/mysql/conf.d/wsrep.cnf

# Full path to wsrep provider library or 'none'

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

# Group communication system handle

wsrep_cluster_address="gcomm://xx.xx.xx.xx"

# State Snapshot Transfer method

wsrep_sst_method=rsync

# SST authentication string. This will be used to send SST to joining nodes.

# Depends on SST method. For mysqldump method it is root:

wsrep_sst_auth=administrator:XXXXX

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

Does that setting need to go in my.cnf as well?

I double checked my RPM for Galera:

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

[root@XXXX administrator]# yum install galera-23.2.4-1.rhel6.x86_64.rpm

Setting up Install Process

Examining galera-23.2.4-1.rhel6.x86_64.rpm: galera-23.2.4-1.rhel6.x86_64

Cannot install package galera-23.2.4-1.rhel6.x86_64. It is obsoleted by installed package galera-23.2.4-1.rhel6.x86_64

Error: Nothing to do

[root@XXXX administrator]# /etc/init.d/mysql restart

Shutting down MySQL...                                     [  OK  ]

Starting MySQL....                                         [  OK  ]

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

As you can see it's installed already. I then restarted MySQL and reran the status command in MySQL, which outputs:

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

mysql> show status like 'wsrep%';

+--------------------------+----------------------+

| Variable_name            | Value                |

+--------------------------+----------------------+

| wsrep_cluster_conf_id    | XXXX |

| wsrep_cluster_size       | 0                    |

| wsrep_cluster_state_uuid |                      |

| wsrep_cluster_status     | Disconnected         |

| wsrep_connected          | OFF                  |

| wsrep_local_index        | XXXX |

| wsrep_provider_name      |                      |

| wsrep_provider_vendor    |                      |

| wsrep_provider_version   |                      |

| wsrep_ready              | ON                   |

+--------------------------+----------------------+

10 rows in set (0.00 sec)

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

Any ideas? Thanks again!

Steve.

--

Steve Barker
Development Specialist
Messaging & Collaboration Team 
Pearson Technology
 
M: +44  7737 905 116

Pearson 
Always Learning
Learn more at www.pearson.com

[Steve Barker]

P.S. I double checked the /usr/lib64/galera/libgalera_smm.so file and it does definitely exist on my server:

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

[root@XXX administrator]# cd /usr/lib64/galera/

[root@XXX galera]# dir

libgalera_smm.so

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

Steve.

On Monday, 8 July 2013 10:08:26 UTC+1, Steve Barker wrote:

[Oli Sennhauser]

Hello all,

We have prepared packages of the Galera Load Balance v1.0.0 for
different distributions (Ubuntu, Debian, CentOS, SLES and OpenSuSE):
http://www.fromdual.com/download#glb

Please let us know how we can make them better...

Regards,
Oli

[Sam Bashton]

On Mon, Jul 8, 2013 at 11:51 AM, Barker, Steve <steve....@pearson.com> wrote:

Hi Sam,

Thanks very much for taking a look at this!

The blog I am following tells me to add that line to the file /etc/mysql/conf.d/wsrep.cnf. Here is my Nano output for that file:

Hi Steve,

This file won't be read by MySQL by default.  Debian distros have a line '!includedir /etc/mysql/conf.d/', but EL6 doesn't.

You could either add this to /etc/my.cnf yourself, or put the contents of the wsrep.cnf file you've created into /etc/my.cnf

Regards,

[Jay Janssen]

Galera = WSREP (Write Set Replication or similar)

If you see wsrep status variables, Galera is enabled, you just may not have configured it?  If you don't have any 'wsrep%' global variables in your my.cnf, I'd expect about what you're seeing.

On Jul 8, 2013, at 5:14 AM, Steve Barker <steve....@pearson.com> wrote:

P.S. Here are the recent MySQL logs, including the results of a restart. It's strange that Galera isn't mentioned anywhere...?

Jay Janssen, MySQL Consulting Lead, Percona

http://about.me/jay.janssen

[Barker, Steve]

There wasn't even a my.cnf file in my etc directory so I created it and added the !includedir line in the file.

This has moved me on a bit now. When I restart the MySQL service I get:

error: Found option without preceding group in config file: /etc/mysql/conf.d/wsrep.cnf at line: 2

Contents of that file are:

========

# Full path to wsrep provider library or 'none'

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

# Group communication system handle

wsrep_cluster_address="gcomm://xx.xx.xx.xx"

# State Snapshot Transfer method

wsrep_sst_method=rsync

# SST authentication string. This will be used to send SST to joining nodes.

# Depends on SST method. For mysqldump method it is root:

wsrep_sst_auth=administrator:XXXX

========

Looks like I need something else before line 2, but not sure what that should be!

Thanks,

Steve.

[Jay Janssen]

Add a 

[mysqld]

to the top of the file.  

On Jul 8, 2013, at 8:37 AM, "Barker, Steve" <steve....@pearson.com> wrote:

error: Found option without preceding group in config file: /etc/mysql/conf.d/wsrep.cnf at line: 2

[Barker, Steve]

Great! We're making more progress. Thanks Jay.

So, now the MySQL won't start, which is progress.

When I try and start it I get this:

Starting MySQL....................................The server quit without updating PID file (/var/lib/mysql/XXX.pid).           [FAILED]

I suspect that this is because I haven't yet got connectivity between my two servers on the port I have specified. I've opted for port 4567. Is that a good idea? Is there a "recommended port" for Galera? Is the error message consistent with MySQL/Galera not being able to talk to it's buddies?

Steve.

--
You received this message because you are subscribed to a topic in the Google Groups "codership" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/codership-team/TndXte389D0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to codership-tea...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Jay Janssen]

On Jul 8, 2013, at 9:23 AM, "Barker, Steve" <steve....@pearson.com> wrote:

I suspect that this is because I haven't yet got connectivity between my two servers on the port I have specified.

Probably.

I've opted for port 4567. Is that a good idea? Is there a "recommended port" for Galera?

yes, 4567.  Note that state transfers use different ports (4568 for IST, and 4444 for SST).  

Is the error message consistent with MySQL/Galera not being able to talk to it's buddies?

Find your error log -- that's what you should be looking at.  Init script errors could mean almost anything.  

[Barker, Steve]

Yeah, log files suggested timeouts on communication ports.

So will I need to open 4568 and 4444 as well as 4567?

I'll hang fire until these ports are open before doing any further debugging.

Thanks again.

[Jay Janssen]

http://www.percona.com/doc/percona-xtradb-cluster/

http://www.percona.com/doc/percona-xtradb-cluster/faq.html#q-what-tcp-ports-are-used-by-percona-xtradb-cluster

[Barker, Steve]

Cheers.

[Steve Barker]

Just to let you know I now have replication up and running with two servers. I don't imagine that introducing two more servers will cause a problem as the process is the same.

Thanks guys for your awesome assistance. I really appreciate it.

Steve.

On Monday, 8 July 2013 10:08:26 UTC+1, Steve Barker wrote:

[Steve Barker]

I've just noticed something. Although we said that Galera needs four ports open in order to work:

3306 (MySQL default port)

4444 (State transfer)

4567 (Group communication)

4568 (Incremental state transfer)

I've just released that only 3306 and 4567 are open between my two test servers, but they do appear to be working OK! What are the risks of not opening these ports, and is it advisable to re-configure Galera so that the two state transfer jobs are done on port 4567 as well?

Thanks,

Steve.

On Monday, 8 July 2013 10:08:26 UTC+1, Steve Barker wrote: