Before version 5.5.28 of Percona XtraDB Cluster, the easiest way was to join the cluster using wsrep_urls in [mysqld_safe] section of my.cnf.
So with a cluster of 3 nodes like this :
node1 = 192.168.1.1
node2 = 192.168.1.2
node3 = 192.168.1.3
we defined the setting like this :
1 | wsrep_urls=gcomm://192.168.1.1:4567,gcomm://192.168.1.2:4567,gcomm://192.168.1.3:4567 |
With that line above in my.cnf on each node, when PXC (mysqld) was started, the node tried to join the cluster on the first IP, if no node was running on that IP, the next IP was tried and so on…. until the node could join the cluster or after it tried and didn’t find any node running the cluster, in that case mysqld failed to start.
To avoid this, when all nodes where down and you wanted to start the cluster, it was possible to have wsrep_urls defined like this :
1 | wsrep_urls=gcomm://192.168.1.1:4567,gcomm://192.168.1.2:4567,gcomm://192.168.1.3:4567,gcomm:// |
That was a nice feature, especially for people that didn’t want to modify my.cnf after starting the first node initializing the cluster or people automating their deployment with a configuration management system.
Now, since wsrep_urls is deprecated since version 5.5.28 what is the better option to start the cluster ?
In my.cnf, [mysqld] section this time, you can use wsrep_cluster_address with the following syntax:
1 | wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3 |
As you can see the port is not needed and gcomm:// is specified only once.
Note:In Debian and Ubuntu, the ip of the node cannot be present in that variable due to a glibc error:
130129 17:03:45 [Note] WSREP: gcomm: connecting to group 'testPXC', peer '192.168.80.1:,192.168.80.2:,192.168.80.3:'
17:03:45 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
[...]
/usr/sbin/mysqld(_Z23wsrep_start_replicationv+0x111)[0x664c21]
/usr/sbin/mysqld(_Z18wsrep_init_startupb+0x65)[0x664da5]
/usr/sbin/mysqld[0x5329af]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x8bd)[0x534fad]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xed)[0x7f3bb24b676d]
/usr/sbin/mysqld[0x529d1d]
So what can be done to initialize the cluster when all nodes are down ? There are two options:
- modify my.cnf and set wsrep_cluster_address=gcomm:// then when the node is started change it again, this is not my favourite option.
- start mysql using the following syntax (it works only on RedHat and CentOS out of the box):
/etc/init.d/myslqd start --wsrep-cluster-address="gcomm://"
As there is no need to modify my.cnf, this is how I recommend to do it.
Hi
Just so i understand this right: You still has your ‘wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3’ in the my.cnf file?
Hi Patrick,
Yes indeed, I don’t modify my.cnf and I keep the same value on all the nodes, which is easier for large or automated deployments.
That’s why I prefer that method to initialized my cluster.
Sorry, a few things aren’t clear to me.
Question 1) if you only specify gcomm:// once now in wsrep_cluster_address, how do you define the ‘fallback’ that is to start a brand new cluster (reset the node) if the other two nodes aren’t available?
e.g, if I have:
node1
node2
node3
wouldn’t I want to make it likeso:
node1: wsrep_cluster_address=gcomm://node2,node3,gcomm://
node2: wsrep_cluster_address=gcomm://node1,node3,gcomm://
node3: wsrep_cluster_address=gcomm://node1,node2,gcomm://
Will that work with an extra gcomm:// at the end?
Question 2) What happens if node1 goes down and node2 receives writes (e.g failover in haproxy to write to node2 instead of node1)… when node1 comes up, node2 assumes node1 is its first machine to replicate from no? will node1 replicate the missing changes during its downtime from node2?
Thanks!
Hi mig5,
For question 1, wsrep_cluster_address doesn’t allow that syntax, you cannot have and empty gcomm:// at the end (this is also not supported: wstep_cluser_address=gcomm://node1,node2,node3,) [see the last comma].
Also in my example, when all your nodes are running, you should not have any wsrep_cluser_address=gcomm:// in your my.cnf, but have the list of all the nodes.
gcomm:// (empty) is used only to initialize the cluster.
The downside of this is that is all nodes crash and restarts, the cluster will never be initialized… but if all nodes crashes do you really want to have them back automatically ? A manual intervention is required to start the first node (it could be any node of the cluster, where you modify my.cnf to have wsrep_cluser_address=gcomm:// or by forcing the value using the init script as described in the post)
2) if when all your nodes are running you have in my.cnf of each nodes the following line wsrep_cluster_address=gcomm://node1,node2,node3 when node1 will restart it will join the cluster using node2 if that node is running and if not try join the cluster using node3 and if none of the two are available, mysqld will just stop.
I hope I clarified it.
Regards,
UPDATE: The latest version of PXC (http://www.mysqlperformanceblog.com/2013/01/30/announcing-percona-xtradb-cluster-5-5-29-23-7-1/) fixes the issue related to the glibc in debian and ubuntu 😀
Hi
I cant get this to work on RHEL6.
I have 2 nodes and 1 garbd. In the my.cnf on both the nodes i have the same ‘wsrep_cluster_address’:
wsrep_cluster_address = gcomm://172.1.1.93,172.1.1.94
I start the first node as a standalone cluster with –wsrep-cluster-address=”gcomm://”
Then i get the uuid and seqnr and create the grastate.dat on the second node and tries to start that. It will crash with the message:
gcomm: connecting to group ‘my_cluster’, peer ‘172.1.1.93:,172.1.1.94:’
09:41:58 UTC – mysqld got signal 11 ;
This could be because you hit a bug.
If i remove the ip that belongs to the node i try to start from the ‘wsrep_cluster_address’ it works. Dont know if this is the same as the Ubuntu and Debian thing.
Tested with binary version:
Percona-XtraDB-Cluster-5.5.28-23.7.373.Linux.x86_64
Percona-XtraDB-Cluster-5.5.29-23.7.1.387.Linux.x86_64
Hi,
And if you want to automatically start the cluster if all machines go down?
Thanks
here is my.cnf file for my 3 nodes:
[mysqld]
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_name=xdb
wsrep_cluster_address=gcomm://172.16.6.221,172.16.6.222,172.16.6.223
wsrep_slave_threads=4
wsrep_sst_method=xtrabackup # default is mysqldump, mysqldump/rsync/xtrabackup
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
I could run /etc/init.d/mysql start –wsrep-cluster-address=”gcomm://” on 172.16.7.221
but how could I start the cluster ?
run /etc/init.d/mysql start –wsrep-cluster-address=”gcomm://” on other 2 nodes ?
or just run /etc/init.d/mysql start on the first node?
I read the doc and google for a while, but I didn’t get any specify instructions …
zx1986,
You need to use the extra –wsrep-cluster-address=”gcomm://” only on the first node that will bootstrap the cluster. When you have already one node started in your cluster, to start the other ones, you just start them with /etc/init.d/mysql start.
Also I recommend you to set a SST method in your my.cnf like this :
wsrep_sst_method=rsync
or
wsrep_sst_method=xtrabackup
wsrep_sst_auth=login:password
Regards,
This had me stumped for too long, so hopefully it will help someone out there. The /etc/init.d/mysql script on Debian can be edited to support the –wsrep-cluster-address=”gcomm://” option, or anything else you want to send along to mysqld. I needed this to bootstrap my cluster, so I don’t have to edit files and remember to remove the edits afterwards. Add “$@” to the parameters to the mysqld_safe call and insert a new line containing the single word shift in the line directly above that. The “shift” will get rid of the $1 values of “start” so anything you give after the start parameter will then be sent to mysqld via $@. This is how your /etc/init.d/mysqld should look after editing:
shift
“${PERCONA_PREFIX}”/bin/mysqld_safe “$@” > /dev/null 2>&1 &
This was done on 5.5.30, but should work for other versions too.
Ciao!
There’s a typo in one of the commands at the very end of the article:
‘/etc/init.d/myslqd’ has mysql misspelled 🙂
Hi Frederick,
I have 3 nodes in Percona cluster and have defined as you suggested as below:
Node1:
wsrep_cluster_address=gcomm://node1,node2,node3
wsrep_cluster_name=Percona_XYZ
wsrep_node_name=
wsrep_node_address=node1
Node2:
wsrep_cluster_address=gcomm://node1,node2,node3
wsrep_cluster_name=Percona_XYZ
wsrep_node_name=
wsrep_node_address=node2
Node3:
wsrep_cluster_address=gcomm://node1,node2,node3
wsrep_cluster_name=Percona_XYZ
wsrep_node_name=
wsrep_node_address=node3
In case my first node(node1) which bootstrapped the cluster goes down due to some reason then will I have to restart each node to restart/revive the cluster? How can I bring one node out of rotation of cluster to avoid any downtime.
Thanks,
Ashish
Hi Ashish,
If your node1 goes out of the cluster for any reason, the other two nodes still communicates together and won’t bring down the cluster.
wsrep_cluster_address is used ONLY when the node is started (and not bootstrapped), this setting just informs the node which other nodes it has to try to contact to join a cluster.
so if you have on nodeX : wsrep_cluster_address=gcomm://node1,node2,node3 ; nodeX will try first to connect the cluster using node1, if node1 is not responding, then it will try node2 and if node2 is down, then it tries node3… if this one is also unavailable then the node won’t start,
Regards,
Hai Frederic,
Thanks four your tips “/etc/init.d/mysql start –wsrep-cluster-address=”gcomm://” , it’s very very useful 😀
hi can anyone tell me why? What is the problem…?????
FreeBSD FreeBSD91 9.1-RELEASE-p13 FreeBSD 9.1-RELEASE-p13 #0
2014-05-30 08:50:57 2044 [Note] InnoDB: Percona XtraDB (https://www.percona.com) 5.6.16-64.1 started; log sequence number 1626067
2014-05-30 08:50:57 2044 [ERROR] /usr/local/libexec/mysqld: unknown variable ‘wsrep_provider=/usr/local/lib/libgalera_smm.so’
2014-05-30 08:50:57 2044 [ERROR] Aborting
My my.cnf is standard:
[mysql]
port = 3306
socket = /var/db/mysql/mysql.sock
[mysqld]
user = mysql
default-storage-engine = InnoDB
socket = /var/db/mysql/mysql.sock
pid-file = /var/db/mysql/mysql.pid
datadir = /var/db/mysql/mysql/
log-bin = /var/db/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
log-error = /var/db/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/db/mysql/mysql-slow.log
wsrep_provider=/usr/local/lib/libgalera_smm.so
wsrep_cluster_name=xdb
wsrep_cluster_address=gcomm://
wsrep_slave_threads=4
wsrep_sst_method=mysqldump # default is mysqldump, mysqldump/rsync/xtrabackup
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
ls -al /usr/local/lib/libgalera_smm.so
-r–r–r– 1 root wheel 23672582 May 30 08:26 /usr/local/lib/libgalera_smm.so
Thx
jonny
Hi Jonny, this is an old post so not the best place to ask your question. I suggest sharing on the Percona discussion forums… here’s the specific url to the Percona XtraDB Cluster board: https://www.percona.com/forums/questions-discussions/percona-xtradb-cluster
Thx 😉
Dear Mr Frédéric
Thanks for tutorial, very help me, awesome
WOW! I found this post again.
Thank you, Frederic!
And, is there any regular way the shutdown all my XtraDB cluster ?
or I just need to /etc/init.d/mysql stop every node ?
I am having problems with setting up a 4 node cluster on centos 6.6, I get everything installed and configured and I can start the cluster without errors. The issue I have is when I go to view the status of each node, they show cluster_size of 1. It’s like non of them are trying to connect to each other / like its a 1 node cluster for some strange reason.
If I start the cluster from node1 and try to start mysql from any of the other nodes to start, when I look at their status it says cluster_status Disconnected and connected OFF. The only way to make them show connected and ON I have to use the /etc/init.d/myslqd start –wsrep-cluster-address=”gcomm://” from all the nodes but again non of them are connecting to each other. All of them are on the same subnet, all are ping able, selinux off, iptables off… Any ideas?
Thanks
Hello, I have a problem. When starting /etc/init.d/mysql bootstrap-pxc
Starting MySQL (Percona XtraDB Cluster)…… ERROR! The server quit without updating PID file (/var/lib/mysql/localhost.localdomain.pid).
ERROR! MySQL (Percona XtraDB Cluster) server startup failed!
my.conf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #1 address
wsrep_node_address=192.168.0.145
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=my_centos_cluster
# Authentication for SST method
wsrep_sst_auth=”ecostela:46493113″
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
and mysql.log is:
2015-04-28 06:38:13 3815 [ERROR] WSREP: Permission denied
2015-04-28 06:38:13 3815 [ERROR] WSREP: failed to open gcomm backend connection: 13: error while trying to listen ‘tcp://0.0.0.0:4567?socket.non_blocking=1’, asio error ‘Permission denied’: 13 (Permission denied)
at gcomm/src/asio_tcp.cpp:listen():783
2015-04-28 06:38:13 3815 [ERROR] WSREP: gcs/src/gcs_core.cpp:long int gcs_core_open(gcs_core_t*, const char*, const char*, bool)():206: Failed to open backend connection: -13 (Permission denied)
2015-04-28 06:38:13 3815 [ERROR] WSREP: gcs/src/gcs.cpp:long int gcs_open(gcs_conn_t*, const char*, const char*, bool)():1379: Failed to open channel ‘my_centos_cluster’ at ‘gcomm://’: -13 (Permission denied)
2015-04-28 06:38:13 3815 [ERROR] WSREP: gcs connect failed: Permission denied
2015-04-28 06:38:13 3815 [ERROR] WSREP: wsrep::connect() failed: 7
2015-04-28 06:38:13 3815 [ERROR] Aborting
2015-04-28 06:38:13 3815 [Note] WSREP: Service disconnected.
2015-04-28 06:38:14 3815 [Note] WSREP: Some threads may fail to exit.
2015-04-28 06:38:14 3815 [Note] Binlog end
2015-04-28 06:38:14 3815 [Note] /usr/sbin/mysqld: Shutdown complete
150428 06:38:14 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended
Thank you.
Hi Emiliano,
Do you have selinux or apparmor enabled on your system ?
Im having the same issue too, please help, it was running before and now it wont.:
A few things that may be useful to find out why
———————–the error———————————————————————
root@pxc1:/var/lib/mysql# /etc/init.d/mysql bootstrap-pxc
* Bootstrapping Percona XtraDB Cluster database server mysqld
* The server quit without updating PID file (/var/lib/mysql/pxc1.server.local.pid).
————————my.cnf ———————————————————————–
[mysqld]
# GENERAL #
datadir= /var/lib/mysql
user= mysql
bind-address = 0.0.0.0
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 3
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 1G
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
# PERCONA XTRADB CLUSTER SETTINGS #
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.0.151,192.168.0.152,192.168.0.153
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #3 address
wsrep_node_address=192.168.0.153
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=mypxc
# Authentication for SST method
wsrep_sst_auth=”user:pass”
———————— apparmor status ————————————————————-
/var/lib/mysql# apparmor_status
apparmor module is loaded.
4 profiles are loaded.
4 profiles are in enforce mode.
/sbin/dhclient
/usr/lib/NetworkManager/nm-dhcp-client.action
/usr/lib/connman/scripts/dhclient-script
/usr/sbin/tcpdump
0 profiles are in complain mode.
1 processes have profiles defined.
1 processes are in enforce mode.
/sbin/dhclient (517)
0 processes are in complain mode.
0 processes are unconfined but have a profile defined.
————————- mysql error log ————————————————————–
150515 15:18:13 mysqld_safe mysqld from pid file /var/lib/mysql/pxc1.rapidassign.local.pid ended
2015-05-15 15:18:25 1673 [Note] WSREP: (6e7b32c4, ‘tcp://0.0.0.0:4567’) reconnecting to f39a453a (tcp://192.168.0.153:4567), attempt 390
2015-05-15 15:19:59 1673 [Note] WSREP: (6e7b32c4, ‘tcp://0.0.0.0:4567′) reconnecting to f39a453a (tcp://192.168.0.153:4567), attempt 420
150515 15:20:24 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150515 15:20:24 mysqld_safe WSREP: Running position recovery with –log_error=’/var/lib/mysql/wsrep_recovery.nJElWo’ –pid-file=’/var/lib/mysql/pxc1.rapidassign.local-recover.pid’
2015-05-15 15:20:24 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
150515 15:20:27 mysqld_safe WSREP: Recovered position d96a1bdf-f666-11e4-aaaf-f7286e556fab:12
Log of wsrep recovery (–wsrep-recover):
2015-05-15 15:20:24 10940 [Note] Plugin ‘FEDERATED’ is disabled.
2015-05-15 15:20:24 10940 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-05-15 15:20:24 10940 [Note] InnoDB: The InnoDB memory heap is disabled
2015-05-15 15:20:24 10940 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-05-15 15:20:24 10940 [Note] InnoDB: Memory barrier is not used
2015-05-15 15:20:24 10940 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-05-15 15:20:24 10940 [Note] InnoDB: Using Linux native AIO
2015-05-15 15:20:24 10940 [Note] InnoDB: Using CPU crc32 instructions
2015-05-15 15:20:24 10940 [Note] InnoDB: Initializing buffer pool, size = 1.0G
2015-05-15 15:20:24 10940 [Note] InnoDB: Completed initialization of buffer pool
2015-05-15 15:20:24 10940 [Note] InnoDB: Highest supported file format is Barracuda.
2015-05-15 15:20:24 10940 [Note] InnoDB: 128 rollback segment(s) are active.
2015-05-15 15:20:24 10940 [Note] InnoDB: Waiting for purge to start
2015-05-15 15:20:24 10940 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.22-72.0 started; log sequence number 1637042
2015-05-15 15:20:24 10940 [Warning] InnoDB: Skipping buffer pool dump/restore during wsrep recovery.
2015-05-15 15:20:25 10940 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2015-05-15 15:20:25 10940 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2015-05-15 15:20:25 10940 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
2015-05-15 15:20:25 10940 [Note] – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
2015-05-15 15:20:25 10940 [Note] Server socket created on IP: ‘0.0.0.0’.
2015-05-15 15:20:25 10940 [Note] WSREP: Recovered position: d96a1bdf-f666-11e4-aaaf-f7286e556fab:12
2015-05-15 15:20:25 10940 [Note] Binlog end
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘partition’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘PERFORMANCE_SCHEMA’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘ARCHIVE’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘BLACKHOLE’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_CHANGED_PAGES’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_SYS_DATAFILES’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_SYS_TABLESPACES’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN_COLS’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_SYS_FIELDS’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_SYS_COLUMNS’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_SYS_INDEXES’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_SYS_TABLESTATS’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_SYS_TABLES’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_FT_INDEX_TABLE’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_FT_INDEX_CACHE’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_FT_CONFIG’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_FT_BEING_DELETED’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_FT_DELETED’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_FT_DEFAULT_STOPWORD’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_METRICS’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_BUFFER_POOL_STATS’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE_LRU’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX_RESET’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_CMPMEM_RESET’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_CMPMEM’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_CMP_RESET’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_CMP’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_LOCK_WAITS’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_LOCKS’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘INNODB_TRX’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘XTRADB_RSEG’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘XTRADB_INTERNAL_HASH_TABLES’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘XTRADB_READ_VIEW’
2015-05-15 15:20:25 10940 [Note] Shutting down plugin ‘InnoDB’
2015-05-15 15:20:25 10940 [Note] InnoDB: FTS optimize thread exiting.
2015-05-15 15:20:25 10940 [Note] InnoDB: Starting shutdown…
2015-05-15 15:20:27 10940 [Note] InnoDB: Shutdown completed; log sequence number 1637052
2015-05-15 15:20:27 10940 [Note] Shutting down plugin ‘MyISAM’
2015-05-15 15:20:27 10940 [Note] Shutting down plugin ‘MRG_MYISAM’
2015-05-15 15:20:27 10940 [Note] Shutting down plugin ‘CSV’
2015-05-15 15:20:27 10940 [Note] Shutting down plugin ‘MEMORY’
2015-05-15 15:20:27 10940 [Note] Shutting down plugin ‘sha256_password’
2015-05-15 15:20:27 10940 [Note] Shutting down plugin ‘mysql_old_password’
2015-05-15 15:20:27 10940 [Note] Shutting down plugin ‘mysql_native_password’
2015-05-15 15:20:27 10940 [Note] Shutting down plugin ‘wsrep’
2015-05-15 15:20:27 10940 [Note] Shutting down plugin ‘binlog’
2015-05-15 15:20:27 10940 [Note] /usr/sbin/mysqld: Shutdown complete
2015-05-15 15:20:27 0 [Note] WSREP: wsrep_start_position var submitted: ‘d96a1bdf-f666-11e4-aaaf-f7286e556fab:12’
2015-05-15 15:20:27 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2015-05-15 15:20:27 10974 [Note] WSREP: Read nil XID from storage engines, skipping position init
2015-05-15 15:20:27 10974 [Note] WSREP: wsrep_load(): loading provider library ‘/usr/lib/libgalera_smm.so’
2015-05-15 15:20:27 10974 [Note] WSREP: wsrep_load(): Galera 3.9(r93aca2d) by Codership Oy loaded successfully.
2015-05-15 15:20:27 10974 [Note] WSREP: CRC-32C: using hardware acceleration.
2015-05-15 15:20:27 10974 [Note] WSREP: Found saved state: d96a1bdf-f666-11e4-aaaf-f7286e556fab:-1
2015-05-15 15:20:27 10974 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 192.168.0.151; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false
2015-05-15 15:20:27 10974 [Note] WSREP: Service thread queue flushed.
2015-05-15 15:20:27 10974 [Note] WSREP: Assign initial position for certification: 12, protocol version: -1
2015-05-15 15:20:27 10974 [Note] WSREP: wsrep_sst_grab()
2015-05-15 15:20:27 10974 [Note] WSREP: Start replication
2015-05-15 15:20:27 10974 [Note] WSREP: Setting initial position to d96a1bdf-f666-11e4-aaaf-f7286e556fab:12
2015-05-15 15:20:27 10974 [Note] WSREP: protonet asio version 0
2015-05-15 15:20:27 10974 [Note] WSREP: Using CRC-32C for message checksums.
2015-05-15 15:20:27 10974 [Note] WSREP: backend: asio
2015-05-15 15:20:27 10974 [Note] WSREP: restore pc from disk successfully
2015-05-15 15:20:27 10974 [Note] WSREP: GMCast version 0
2015-05-15 15:20:27 10974 [Note] WSREP: (6e7b32c4, ‘tcp://0.0.0.0:4567’) listening at tcp://0.0.0.0:4567
2015-05-15 15:20:27 10974 [Note] WSREP: (6e7b32c4, ‘tcp://0.0.0.0:4567’) multicast: , ttl: 1
2015-05-15 15:20:27 10974 [Note] WSREP: EVS version 0
2015-05-15 15:20:27 10974 [Note] WSREP: gcomm: bootstrapping new group ‘rapidassign_pxc’
2015-05-15 15:20:27 10974 [Note] WSREP: start_prim is enabled, turn off pc_recovery
2015-05-15 15:20:27 10974 [ERROR] WSREP: Address already in use
2015-05-15 15:20:27 10974 [ERROR] WSREP: failed to open gcomm backend connection: 98: error while trying to listen ‘tcp://0.0.0.0:4567?socket.non_blocking=1’, asio error ‘Address already in use’: 98 (Address already in use)
at gcomm/src/asio_tcp.cpp:listen():783
2015-05-15 15:20:27 10974 [ERROR] WSREP: gcs/src/gcs_core.cpp:long int gcs_core_open(gcs_core_t*, const char*, const char*, bool)():206: Failed to open backend connection: -98 (Address already in use)
2015-05-15 15:20:27 10974 [ERROR] WSREP: gcs/src/gcs.cpp:long int gcs_open(gcs_conn_t*, const char*, const char*, bool)():1379: Failed to open channel ‘rapidassign_pxc’ at ‘gcomm://192.168.0.151,192.168.0.152,192.168.0.153’: -98 (Address already in use)
2015-05-15 15:20:27 10974 [ERROR] WSREP: gcs connect failed: Address already in use
2015-05-15 15:20:27 10974 [ERROR] WSREP: wsrep::connect() failed: 7
2015-05-15 15:20:27 10974 [ERROR] Aborting
2015-05-15 15:20:27 10974 [Note] WSREP: Service disconnected.
2015-05-15 15:20:28 10974 [Note] WSREP: Some threads may fail to exit.
2015-05-15 15:20:28 10974 [Note] Binlog end
2015-05-15 15:20:28 10974 [Note] /usr/sbin/mysqld: Shutdown complete
150515 15:20:28 mysqld_safe mysqld from pid file /var/lib/mysql/pxc1.rapidassign.local.pid ended
2015-05-15 15:21:33 1673 [Note] WSREP: (6e7b32c4, ‘tcp://0.0.0.0:4567’) reconnecting to f39a453a (tcp://192.168.0.153:4567), attempt 450
————————————————————-
Dear Mr Frederick
I am a beginner of PXC.
I really want start pxc automatically.
————————————————————
1.For example, three nodes simultaneously power failure, how to check the status of each node in the cluster and recovery automatically start the first node ?
2.if all nodes crash and restarts，what kind of manual intervention is needed? is it too complicate to automatically be detected and repaired ?
3.If I want to achieve through a script, I need to conside what ?
Regards
Hi Frederic,
I need your help in understanding some conditions in 3 node xtradb cluster. As per your blog, we have 3 node cluster and we have “wsrep_urls=gcomm://192.168.1.1:4567,gcomm://192.168.1.2:4567,gcomm://192.168.1.3:4567” configuration in our my.cnf file for each node.
Condition 1. What will be our connecting string? will we be using particular node name to connect or just cluster name which we have given in my.cnf file. I mean to say here, we will try to connect to cluster using cluster name not by node name. Because if connect using node name and if that particular node goes Offline then we need to make changes in our application to connect to 2nd available node.
Condition 2. As xtradb cluster works on InnoDB storage. Let’s say we have configured 3 node cluster and it is running. Now if we add new user on Node 1 (using Grant, because by using “Create” statement it will works but we also need to grant privileges), will that be replicated to others Node as user tables are in MyISAM. If not, then we need to create user on all the Nodes in cluster.
Thanks,
Jitender
I have 3 nodes in Percona cluster and have defined as you suggested as below:
Node1:
wsrep_cluster_address=gcomm://node1,node2,node3
wsrep_cluster_name=Percona_XYZ
wsrep_node_name=
wsrep_node_address=node1
Node2:
wsrep_cluster_address=gcomm://node1,node2,node3
wsrep_cluster_name=Percona_XYZ
wsrep_node_name=
wsrep_node_address=node2
Node3:
wsrep_cluster_address=gcomm://node1,node2,node3
wsrep_cluster_name=Percona_XYZ
wsrep_node_name=
wsrep_node_address=node3
I use service mysql bootstrap-pxc to start node1,and use service mysql start to start node2,3
i wonder that node1 down,how to make node1 join back to the cluster???