Percona xtradb cluster node2 won’t start

galerapercona-serverxtradb-cluster

I have configured Percona Xtradb cluster 5.6. The first node starts fine when bootstrapped, but the second node seems to have an issue with this variable setting:

wsrep_provider=/usr/lib/libgalera_smm.so

I am running the cluster nodes on Ubuntu 14.04. For the error log, this is what is throws at me:

150608 18:43:03 mysqld_safe Starting mysqld daemon with databases from 
/var/lib/mysql

150608 18:43:03 mysqld_safe Skipping wsrep-recover for empty datadir: 
/var/lib/mysql
150608 18:43:03 mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 
to wsrep_start_position
2015-06-08 18:43:03 0 [Warning] Using unique option prefix key_buffer instead
of key_buffer_size is deprecated and will be removed in a future release. 
Please use the full name instead.
2015-06-08 18:43:03 0 [Warning] TIMESTAMP with implicit DEFAULT value is    
deprecated. Please use --explicit_defaults_for_timestamp server option (see 
documentation for more details).
2015-06-08 18:43:03 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-72.2-56-log)  
starting as process 5977 ...
2015-06-08 18:43:03 5977 [Note] WSREP: Read nil XID from storage engines, 
skipping position init
2015-06-08 18:43:03 5977 [Note] WSREP: wsrep_load(): loading provider library 
'/usr/lib/galera3/libgalera_ssm.so'
2015-06-08 18:43:03 5977 [ERROR] WSREP: wsrep_load(): dlopen():   
/usr/lib/galera3/libgalera_ssm.so: cannot open shared object file: No such 
file or directory
2015-06-08 18:43:03 5977 [ERROR] WSREP:
wsrep_load(/usr/lib/galera3/libgalera_ssm.so) failed: Invalid argument (22). 
Reverting to no provider.
2015-06-08 18:43:03 5977 [Note] WSREP: Read nil XID from storage engines,  
skipping position init
2015-06-08 18:43:03 5977 [Note] WSREP: wsrep_load(): loading provider library 
'none'
2015-06-08 18:43:03 5977 [ERROR] Aborting

2015-06-08 18:43:03 5977 [Note] WSREP: Service disconnected.
2015-06-08 18:43:04 5977 [Note] WSREP: Some threads may fail to exit.
2015-06-08 18:43:04 5977 [Note] Binlog end
2015-06-08 18:43:04 5977 [Note] /usr/sbin/mysqld: Shutdown complete

I have tried commenting out #wsrep_provider and starting the node with the following command

$sudo /etc/init.d/mysql start --wsrep_provider="/usr/lib/libgalera_ssm.so"

But this does not seem to help. The node still fails to start.

The libgalera_ssm.so file is actually present on both servers, hence I am kinda confused why it works on one server and not the other. But it is worth pointing out that even node one gives the same error when I try to start it without using bootstrap-pxc and just use /etc/init.d/mysql start.

Best Answer

The log entries

2015-06-08 18:43:03 5977 [ERROR] WSREP: wsrep_load(): dlopen(): /usr/lib/galera3/libgalera_ssm.so: cannot open shared object file: No such file or directory
2015-06-08 18:43:03 5977 [ERROR] WSREP: wsrep_load(/usr/lib/galera3/libgalera_ssm.so) failed: Invalid argument (22). Reverting to no provider.

show that MySQL cannot find the file /usr/lib/galera3/libgalera_ssm.so so it cannot use it as a WSREP provider. Check that this file exists and is accessible. If your system is 64-bit, it might be in /usr/lib64/.

Also please post the error message you get when starting the node -- without reading the exact error message it's a shot in the dark.

Related Solutions

Mysql – Integrity constraint violation in Percona XtraDB Cluster

This is not an error on Galera, but a "logical error" at application level. Thank Galera that the error ocurred and it didn't corrupt your topology or break consistency, as it would have happened with standard replication.

This is happening because you are trying to insert almost simultaneously auto-inc records on both nodes. Galera has optimistic locking for performance reasons, that is why you get an error.

You can solve this very easily by setting different auto-increment series for both nodes.

This can be easily done by configuring (assuming a 3-node setup):

Node 1
======

auto_increment_increment = 3
auto_increment_offset = 0


Node 2
======

auto_increment_increment = 3
auto_increment_offset = 1

Node 3
======

auto_increment_increment = 3
auto_increment_offset = 2

Then restart. This will tell MySQL to insert only 1, 4, 7, etc. on node 1; 2, 5, 8,... on node 2 and 3, 6, 9,... on 3. Be careful on not running out of ids!

As I said, this is actually cool to be happening- errors are clearly shown and fixed quickly instead of silent problems. This would have not work very well on standard replication (updates would continue being a problem), but not on Galera- where updates on the same row by default are retried at least once before rollbacking.

MySQL 5.5 Replication to Galera/Percona XtraDB Cluster

Yes, it is very possible.

As the Documentation says, you should have a minimum of three nodes in a Percona XtraDB Cluster. You only need one node out of the three nodes.

Make sure all nodes in the PXC have unique server_ids.
Make sure the Master you are replicating from has a unique server_id from the PXC nodes
Make sure the master and every PXC node is configured with the following
- binlog_format=ROW
- log-slave-updates
Make sure the master and every PXC node is using binlog_format=ROW

Pick one node and set it up as you would a DB Server designated as a Slave via CHANGE MASTER TO.

If you are going to set up any replication filters, restrict it to just the PXC node that is your Slave. Personally, I would stay away from using replication filters altogether in this setup since one of Percona's Galera Instructors does not feel good about it.

Please be aware that data takes time to be on all nodes at the same instant of time (See Investigating MySQL Replication Latency in Percona XtraDB Cluster)

By the way, you can use any PXC as a Master to StandAlone Slaves as well

Jun 07, 2013 : Percona XtraDB Cluster and MySQL Replication
Jan 29, 2014 : Native replication from Percona XtraDB Cluster to a standard stand-alone slave
Nov 24, 2014 : What is the Best way to set Disaster recovery setup for percona Xtra DB Cluster?

HEADS UP #1

Don't be surprised by replication lag in the PXC node acting as the MySQL Slave. After all, three nodes must write data and become eventually consistent before all committing changes together. See Follow these basics when migrating to Percona XtraDB Cluster for MySQL for more info.

HEADS UP #2

Since you are using MySQL 5.5, do not use PXC 5.6. Use PXC 5.5. Why ? Row-based binlogging in MySQL 5.5 can sometimes not be properly interpreted by a MySQL 5.6 mysqld process. This can break replication from MySQL 5.5 to MySQL 5.6. I wrote a post about this two months ago. The work around was to switch to binlog_format=STATEMENT, but you cannot do that for Percona XtraDB Cluster as it requires binlog_format=ROW.