Verified with PXC 5.5 and PXC 5.6. wsrep-on=OFF is not working either set in my.cnf or command-line.
With PXC 5.5:
[root@percona-pxc55-1 ~]# ps -ef | grep mysql
mysql 2855 964 1 14:59 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-new-cluster --log-error=/var/lib/mysql/error.log --pid-file=/var/lib/mysql/percona-pxc55-1.pid --wsrep_start_position=eb3828f9-2125-11e4-b71b-3ff545ea7ce6:10 --wsrep-on=OFF
root 2881 2837 0 14:59 pts/1 00:00:00 grep mysql
[root@percona-pxc55-1 ~]#
[root@percona-pxc55-1 ~]#
[root@percona-pxc55-1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.37-35.0-55-log Percona XtraDB Cluster (GPL), Release rel35.0, Revision 756, WSREP version 25.10, wsrep_25.10.r3985
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on | ON |
+---------------+-------+
1 row in set (0.00 sec)
-------------------------------------------------------
With PXC 5.6:
-------------------------------------------------------
root@debian:~# ps -ef | grep mysql
mysql 5629 4714 3 15:03 pts/3 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --wsrep-provider=/usr/lib/libgalera_smm.so --wsrep-new-cluster --log-error=/var/lib/mysql/debian.err --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --wsrep_start_position=3671fad1-123e-11e4-8ee5-7febff796263:41 --wsrep-on=OFF
root 5660 4714 0 15:03 pts/3 00:00:00 grep mysql
root@debian:~#
root@debian:~#
root@debian:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.19-67.0-56-log Percona XtraDB Cluster (GPL), Release 25.6, wsrep_25.6.r4111
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on | ON |
+---------------+-------+
1 row in set (0.01 sec)
BUT, if we are setting it with SET GLOBAL then it works.
mysql> show global variables like 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> SET GLOBAL wsrep_on = OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on | OFF |
+---------------+-------+
1 row in set (0.00 sec)
Verified with PXC 5.5 and PXC 5.6. wsrep-on=OFF is not working either set in my.cnf or command-line.
With PXC 5.5:
[root@percona- pxc55-1 ~]# ps -ef | grep mysql /var/lib/ mysql --plugin- dir=/usr/ lib64/mysql/ plugin --user=mysql --wsrep-new-cluster --log-error= /var/lib/ mysql/error. log --pid-file= /var/lib/ mysql/percona- pxc55-1. pid --wsrep_ start_position= eb3828f9- 2125-11e4- b71b-3ff545ea7c e6:10 --wsrep-on=OFF pxc55-1 ~]# pxc55-1 ~]# pxc55-1 ~]# mysql -uroot -p
mysql 2855 964 1 14:59 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=
root 2881 2837 0 14:59 pts/1 00:00:00 grep mysql
[root@percona-
[root@percona-
[root@percona-
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.37-35.0-55-log Percona XtraDB Cluster (GPL), Release rel35.0, Revision 756, WSREP version 25.10, wsrep_25.10.r3985
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like 'wsrep_on'; ------- --+---- ---+ ------- --+---- ---+ ------- --+---- ---+
+------
| Variable_name | Value |
+------
| wsrep_on | ON |
+------
1 row in set (0.00 sec)
mysql> quit pxc55-1 ~]# cat /etc/my.cnf | grep "wsrep_on" pxc55-1 ~]#
Bye
[root@percona-
wsrep_on=OFF
[root@percona-
------- ------- ------- ------- ------- ------- ------- ------ ------- ------- ------- ------- ------- ------- ------
With PXC 5.6:
-------
root@debian:~# ps -ef | grep mysql /var/lib/ mysql --plugin- dir=/usr/ lib/mysql/ plugin --user=mysql --wsrep- provider= /usr/lib/ libgalera_ smm.so --wsrep-new-cluster --log-error= /var/lib/ mysql/debian. err --pid-file= /var/run/ mysqld/ mysqld. pid --socket= /var/run/ mysqld/ mysqld. sock --port=3306 --wsrep_ start_position= 3671fad1- 123e-11e4- 8ee5-7febff7962 63:41 --wsrep-on=OFF
mysql 5629 4714 3 15:03 pts/3 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=
root 5660 4714 0 15:03 pts/3 00:00:00 grep mysql
root@debian:~#
root@debian:~#
root@debian:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.19-67.0-56-log Percona XtraDB Cluster (GPL), Release 25.6, wsrep_25.6.r4111
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like 'wsrep_on'; ------- --+---- ---+ ------- --+---- ---+ ------- --+---- ---+
+------
| Variable_name | Value |
+------
| wsrep_on | ON |
+------
1 row in set (0.01 sec)
mysql> quit
Bye
root@debian:~# cat /etc/mysql/my.cnf | grep "wsrep_on"
wsrep_on=OFF
root@debian:~#
BUT, if we are setting it with SET GLOBAL then it works.
mysql> show global variables like 'wsrep_on'; ------- --+---- ---+ ------- --+---- ---+ ------- --+---- ---+
+------
| Variable_name | Value |
+------
| wsrep_on | ON |
+------
1 row in set (0.00 sec)
mysql> SET GLOBAL wsrep_on = OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'wsrep_on'; ------- --+---- ---+ ------- --+---- ---+ ------- --+---- ---+
+------
| Variable_name | Value |
+------
| wsrep_on | OFF |
+------
1 row in set (0.00 sec)
Even I have tested, it will not replicate.